Upgrading PostgreSQL 7.4.X to 8.3.X

The following are instructions on how to upgrade PostgreSQL on Redhat Linux.

In my case, I am upgrading the version that came with the Operating System, 7.4.16, to the newest available binary version, 8.3.1.

If you are currently using PostgreSQL, you will want to take a backup via:

# pg_dumpall > /tmp/postgres_backup.pgsql

Stop the Service:

# service postgresql stop
Stopping postgresql service:                               [  OK  ]

Backup the Data Directory - this will be overwritten when installing the new binaries:

# mv /var/lib/pgsql /var/lib/pgsql_7.4.16

Download the binaries for the new version from PostgreSQL

Install the rpms:

# rpm -Uvh postgresql-server-8.3.1-1PGDG.rhel4.i686.rpm \
       postgresql-libs-8.3.1-1PGDG.rhel4.i686.rpm \
       compat-postgresql-libs-3-2PGDG.rhel4.i686.rpm \
       postgresql-8.3.1-1PGDG.rhel4.i686.rpm \
       postgresql-test-8.3.1-1PGDG.rhel4.i686.rpm \
       postgresql-devel-8.3.1-1PGDG.rhel4.i686.rpm \ 
       postgresql-contrib-8.3.1-1PGDG.rhel4.i686.rpm \ 
       postgresql-docs-8.3.1-1PGDG.rhel4.i686.rpm \
       postgresql-plperl-8.3.1-1PGDG.rhel4.i686.rpm
warning: postgresql-server-8.3.1-1PGDG.rhel4.i686.rpm: V3 DSA signature: NOKEY, key ID 442df0f8
Preparing...                ########################################### [100%]
   1:postgresql-libs        ########################################### [ 11%]
   2:postgresql             ########################################### [ 22%]
   3:compat-postgresql-libs ########################################### [ 33%]
   4:postgresql-server      ########################################### [ 44%]
   5:postgresql-test        ########################################### [ 56%]
   6:postgresql-devel       ########################################### [ 67%]
   7:postgresql-contrib     ########################################### [ 78%]
   8:postgresql-docs        ########################################### [ 89%]
   9:postgresql-plperl      ########################################### [100%]

Initialize the database cluster for the newly installed binaries:

# su - postgres
# /usr/bin/initdb -D /var/lib/pgsql/data

You should see something similar to the following output:

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

fixing permissions on existing directory /var/lib/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers/max_fsm_pages ... 32MB/204800
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    /usr/bin/postgres -D /var/lib/pgsql/data
or
    /usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start

Move the previous pg_hba.conf back to the data directory:

# cp /var/lib/pgsql_7.4.16/data/pg_hba.conf /var/lib/pgsql/data/.

Each version introduces syntax/options changes to the postgresql.conf file. Instead of copying the old file back, I will simply edit the directives in the new file since so many options have changed. In particular, I edit the following directives in postgresql.conf:

listen_addresses = '*'  
port = 5432 
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

Of course, you may have to edit many more directives considering your prior installation.

Next, I download the source distribution from PostgreSQL in order to obtain the startup script:

# gunzip postgresql-8.3.1.tar.gz
# tar -xvf  postgresql-8.3.1.tar

Overwrite the existing startup script:

# cp /u03/postgresql-8.3.1/contrib/start-scripts/linux /etc/init.d/postgresql
cp: overwrite `/etc/init.d/postgresql'? y

This file needs to be edited for your installation, in particular I edited the following lines:

# Installation prefix
prefix=/usr

# Data directory
PGDATA="/var/lib/pgsql/data"

# Where to keep a log file
PGLOG="$PGDATA/pg_log/pgstartup.log"

Restart the PostgreSQL service:

# su - root service postgresql start
Password:
Starting PostgreSQL: ok

Import the previously taken backup:

# psql -d postgres -f /tmp/postgres_backup.pgsql

At this point, your installation should be ready to use. Test as appropriate - I test a remote connection via:

# psql -t -h 192.168.1.102 -U root mydb
Password:
Welcome to psql 7.4.16, 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

mydb=# select * from version();
 PostgreSQL 8.3.1 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9)

Labels: PostgreSQL