Weblog

Postgres 8.1.x and later versions

Created by Rocael Hernández Rizzardini, last modified by Gustaf Neumann 24 May 2013, at 05:06 PM

Postgres 8.x (e.g. 8.1.* and later) require a few changes in the configuration file for backward compatibility:

1. Configure postgres8 with all compatibility ON in postgresql.conf:

add_missing_from = on
regex_flavor = extended
default_with_oids = on

2. If you are upgrading an OpenACS site between versions 4.5.2 to 5.2 and not continuing to 5.3, then: After the createdb step to create the OpenACS database, generate the next function before you import the pre-existing OpenACS database.

 From your shell prompt enter:

psql <dbname>

From the psql prompt enter the follow plpgsql code to create the bitfromint4 function:

create or replace function bitfromint4 (integer)
returns bit varying as '
begin
return $1::bit(32);
end;' language 'plpgsql' immutable strict;

Exit psql:

\q 

This is the original thread  

For a full script on how to install PG 8.2 with ltree and tsearch2 look at Malte's install script  

OpenACS compatibility matrix

Created by Joel Aufrecht, last modified by Gustaf Neumann 02 Nov 2012, at 12:13 PM

OpenACS requires, at a minimum, an operating system, database, and webserver to work. Many additional programs, such as a build environment, Mail Transport Agent, and source control system, are also needed for a fully effective installation.

The value in the cells correspond to the last version of that release, and not necessarily to all minor releases. Empty cells denote an unknown status.

hstore

Created by Michael Aram, last modified by Michael Aram 30 Apr 2010, at 12:21 AM

hstore  is a postgresql module, which is used optionally by newer versions of xowiki and xowf (xowiki content flow ). The supporting functions (creating indices, etc) are currently mostly in in xowf but will be moved eventually into xowiki.

Under Ubuntu, you can install it by locating the appropriate sql file on your system (you may need to install the postgres-contrib first), and then install it as postgresql user via:

psql -d <yourdb> -f /usr/share/postgresql/8.3/contrib/hstore.sql

You can then verify the successful installation on the shell using:

::xo::db::has_hstore

Install Postgresql

Created by OpenACS community, last modified by Ryan Gallimore 03 Oct 2008, at 06:27 PM

A quick install guide for Postgres 8.2 which takes everything into account can be found at http://cognovis.de/developer/en/pg82 

For RPMs, see http://yum.pgsqlrpms.org/

---

These instructions should work for 7.4 to 8.2.

Get the source code

http://www.postgresql.org/download/

Install from source

Unpack PostgreSQL 7.4.7. If you have downloaded the postgresql tarball to /var/tmp/postgresql-7.4.7.tar.gz

[root root]# cd /usr/local/src
[root src]# tar xzf /var/tmp/postgresql-7.4.7.tar.gz
[root src]# 
cd /usr/local/src
tar xzf /var/tmp/postgresql-7.4.7.tar.gz

If you have downloaded the postgresql tarball to /var/tmp/postgresql-7.4.7.tar.bz2

[root root]# cd /usr/local/src
[root src]# tar xfj /var/tmp/postgresql-7.4.7.tar.bz2
[root src]# 
cd /usr/local/src
tar xfj /var/tmp/postgresql-7.4.7.tar.bz2

Create the Postgres user. Create a user and group (if you haven't done so before) for PostgreSQL. This is the account that PostgreSQL will run as since it will not run as root. Since nobody will log in directly as that user, we'll leave the password blank.

[root src]# groupadd web
[root src]# useradd -g web -d /usr/local/pgsql postgres
[root src]# mkdir -p /usr/local/pgsql
[root src]# chown -R postgres.web /usr/local/pgsql /usr/local/src/postgresql-7.4.7
[root src]# chmod 750 /usr/local/pgsql
[root src]#
groupadd web
useradd -g web -d /usr/local/pgsql postgres
mkdir -p /usr/local/pgsql
chown -R postgres.web /usr/local/pgsql /usr/local/src/postgresql-7.4.7
chmod 750 /usr/local/pgsql

Set up postgres's environment variables. They are necessary for the executable to find its supporting libraries. Put the following lines into the postgres user's environment.

[root src]# su - postgres
[postgres ~] emacs ~postgres/.bashrc

Paste this line into .bash_profile:

source $HOME/.bashrc

Paste these lines into .bashrc:

export PATH=/usr/local/bin/:$PATH:/usr/local/pgsql/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib

Test this by logging in as postgres and checking the paths; you should see /usr/local/pgsql/bin

[root src]# su - postgres
[postgres pgsql]$ env | grep PATH
LD_LIBRARY_PATH=:/usr/local/pgsql/lib
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/bin/X11:/usr/X11R6/bin:/root/bin:/usr/local/pgsql/bin:/usr/local/pgsql/bin
[postgres pgsql]$ exit

Don't continue unless you see correct output from env | grep PATH

Compile and install PostgreSQL. Change to the postgres user and run ./configure to set the compilation options automatically. This is the point at which you can configure PostgreSQL in various ways. To see what the possibilities, run ./configure --help.

[root src]# su - postgres
[postgres pgsql]$ cd /usr/local/src/postgresql-7.4.7
[postgres postgresql-7.4.7]$ ./configure
creating cache ./config.cache
checking host system type... i686-pc-linux-gnu
(many lines omitted>
linking ./src/makefiles/Makefile.linux to src/Makefile.port
linking ./src/backend/port/tas/dummy.s to src/backend/port/tas.s
[postgres postgresql-7.4.7]$ make all
make -C doc all
make[1]: Entering directory `/usr/local/src/postgresql-7.4.7/doc'
(many lines omitted)
make[1]: Leaving directory `/usr/local/src/postgresql-7.4.7/src'
All of PostgreSQL successfully made. Ready to install.
[postgres postgresql-7.4.7]$ make install
make -C doc install
make[1]: Entering directory `/usr/local/src/postgresql-7.4.7/doc'
(many lines omitted)
Thank you for choosing PostgreSQL, the most advanced open source database
engine.
su - postgres
cd /usr/local/src/postgresql-7.4.7
./configure 
make all
make install

Start PostgreSQL. The initdb command initializes the database. pg_ctl is used to start up PostgreSQL.

[postgres postgresql-7.4.7]$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
(17 lines omitted)
or
    /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
[postgres postgresql-7.4.7]$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/server.log start
postmaster successfully started
[postgres postgresql-7.4.7]$
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/server.log start

PostgreSQL errors will be logged in /usr/local/pgsql/data/server.log

Install Pl/pgSQL.Set up plpgsq and allow your user to have access. Plpgsql is a PL/SQL-like language. We add it to template1, which is the template from which all new databases are created. We can verify that it was created with the createlang command in list mode.

[postgres postgresql-7.4.7]$ createlang plpgsql template1
[postgres pgsql]$ createlang -l template1
Procedural languages
  Name   | Trusted?
---------+----------
 plpgsql | t
(1 row)

[postgres pgsql-7.4.7]$
createlang plpgsql template1
createlang -l template1

If you are installing Postgres 8.x, follow additional instructions at en:How_to_install_in_Postgres_8.x

If you are installing tsearch2, follow additional instructions at en:postgresql-tsearch2

Test PostgreSQL (OPTIONAL). Create a database and try some simple commands. The output should be as shown.

[postgres pgsql]$ createdb mytestdb
CREATE DATABASE
[postgres pgsql]$ psql mytestdb
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

mytestdb=# select current_timestamp;
          timestamptz
-------------------------------
 2003-03-07 22:18:29.185413-08
(1 row)

mytestdb=# create function test1() returns integer as 'begin return 1; end;' language 'plpgsql';
CREATE
mytestdb=# select test1();
 test1
-------
     1
(1 row)

mytestdb=# \q
[postgres pgsql]$ dropdb mytestdb
DROP DATABASE
[postgres pgsql]$ exit
logout

[root src]#

Set PostgreSQL to start on boot. First, we copy the SERVERROOT/packages/acs-core-docs/www/files/postgresql.txt init script, which automates startup and shutdown, to the distribution-specific init.d directory. Then we verify that it works. Then we automate it by setting up a bunch of symlinks that ensure that, when the operating system changes runlevels, postgresql goes to the appropriate state. Red Hat and Debian and SuSE each work a little differently. Be sure to check OS distribution notes for specifics (and examples)

Once installed, PostgreSQL should start automatically each time you boot up and it should shutdown gracefully each time you shut down.