PostgreSQL Upgrade

From Wiki

Jump to: navigation, search

This tutorial describes the steps you should take in order to upgrade the PostgreSQL for a server running CentOS/RHEL.

Contents

Backup all databases

Check if there are any postgresql databases in use

# su - postgres -c 'psql -l'
postgres  | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

If there are any entries other then the above, you have customer databases and you should backup them.

If there aren't any other entries, you should skip to step 2.

Create a backup of the current databases

# su - postgres -c 'pg_dumpall > pg.sql'
# mv /var/lib/pgsql/pg.sql /root

Check if the dump contains some sensible SQL data

# head -n200 pg.sql|less

If everything looks fine, continue, otherwise repeat this step.

Remove the current postgresql

# yum remove postgresql postgresql-devel postgresql-server

Important: Removing postgresql-libs from your machine via yum will also drag and remove all of its dependencies. This is something you would not want to do. For this purpose you should use the following command in order to remove the PostgreSQL libraries without removing any dependencies:

# rpm -e --nodeps postgresql-libs

Install postgresql84

# yum install postgresql84 postgresql84-devel postgresql84-server postgresql84-libs

Start the new PostgreSQL

Since this a brand new daemon, it still doesn't have a DB directory and we have to create one for it.

In order to do so, first we have to remove the directory from the old pgsql:

# mv /var/lib/pgsql /var/lib/pgsql.old

After that we initialize the DataBase:

# /etc/init.d/postgresql initdb

It is highly probable that this step will fail. This is caused because postgresql84 tries to initialize the DB with incompatible Database dump file. So if the step fail, execute the following command:

# sed -i '/JOHAB --> UTF8/,//D' /usr/share/pgsql/conversion_create.sql

This fixes the dump used for the initialization and you can then remove the old broken initdb:

# rm -rf /var/lib/pgsql

And init the database again

# /etc/init.d/postgresql initdb

After the initialization is done, start the server

# /etc/init.d/postgresql start

Check if you have any issues with PHP

Run this command:

# php -v

If you get error like this:

/usr/bin/php: error while loading shared libraries: libpq.so.4: cannot open shared object file: No such file or directory

You can fix this by simply creating a link from the new library to the old one.

# ln -s /usr/lib/libpq.so.5 /usr/lib/libpq.so.4

If you are on x86_64 machine (64bit) you must also do this:

# ln -s /usr/lib64/libpq.so.5 /usr/lib64/libpq.so.4

Restore databases

If you had any databases you should import them back

# cp /root/pg.sql /var/lib/pgsql/
# su - postgres -c 'psql -f pg.sql'

You are done :)

Personal tools
Namespaces
Variants
Actions
Navigation
Toolbox