Updating PostgreSQL

It is possible to do a file system copy of postgres and use the db tools to perform an upgrade

Here we will upgrade from pg94 (/opt/postgresql/pg94/) to pg95 (/opt/postgresql/pg95/) using a new server (could be done on same server) Create a folder to contain the original data on the new target server

mkdir /opt/postgresql/orig/

On the source (orig) server, stop postgres and move the current (should be small) data folder

/etc/init.d/postgresql-94 stop

Now perform the copy

cd /opt/
rsync -av postgresql root@172.21.40.132:/opt/postgresql/orig/

Restart postgres if still required

/etc/init.d/postgresql-94 start

On the new server copy the existing data folder (just in case)

/etc/init.d/postgresql-95 stop
mv /opt/postgresql/pg95/data /opt/postgresql/pg95/orig95
mkdir /opt/postgresql/pg95/data
chown -R postgres:postgres /opt/postgresql/pg95/data

"su" to the postgres user

su - postgres

Create base db on server

/opt/postgresql/pg95/bin/initdb /opt/postgresql/pg95/data

Exist "su" on new server and start service

exit
mkdir /opt/postgresql/pg95/data/pg_log
chown postgres:postgres /opt/postgresql/pg95/data/pg_log

On the new server run the following "su" to the postgres user

su - postgres
cd /opt/postgresql/pg95/data
/opt/postgresql/pg95/bin/pg_upgrade -v -d /opt/postgresql/orig/postgresql/pg94/data -D /opt/postgresql/pg95/data -b /opt/postgresql/orig/postgresql/pg94/bin/ -B /opt/postgresql/pg95/bin/

If you get a permissions problem "connection to database failed: fe_sendauth: no password supplied" add the following (in bold) to the /opt/postgresql/orig/postgresql/pg94/data/pg_hba.conf file

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all       

When complete exit from the "su" and start the postgres service

exit
/etc/init.d/postgresql-95 start