Tech

Upgrade Postgresql 8.4 to 9.03 (ubuntu10.04)

by Phyllis · April 13, 2011

The painless upgrade from 8.4 to 9.0 does not work for my case , so I change back to the old-school method .

1. Backup Existing Postgres DB (including user accounts DBUSER1 , DBUSER2 , and with databases: USERDB1, USERDB2) :

% su
% mkdir /pgbak
% chown postgres:postgres /pgbak/
% su – postgres
% pg_dumpall –globals-only > /pgbak/globals.sql
% pg_dump –create –oids –format=c –verbose –file=/pgbak/USERDB1 USERDB1
% pg_dump –create –oids –format=c –verbose –file=/pgbak/USERDB2 USERDB2
% cp -r /var/lib/postgresql/8.4 /pgbak/

2. Uninstall existing postgres 8.4
Stop database server , and uninstall existing version 8.4 and pgadmin3 :

% /etc/init.d/postgresql stop
% cp -r /var/lib/postgresql/8.4 /pgbak/
% add-apt-repository ppa:pitti/postgresql
% apt-get update
% apt-get remove postgresql-8.4 postgresql-client-8.4 postgresql-common pgadmin3 pgadmin3-data

3. Install postgres 9.0

% apt-get install postgresql-9.0 postgresql-client-9.0

If receive errors :

% apt-get install -f
% apt-get install postgresql-9.0 postgresql-client-9.0
% rm /etc/rc3.d/S19postgres-8.4

Start Postgresql 9.0

% /etc/init.d/postgresql start

4. Restore Postgres User account :DBUSER1, DBUSER2 and databases : USERDB1,USERDB2

% su – postgres
% psql -f /pgbak/globals.sql
% pg_restore –create -d postgres /pgbak/USERDB1
% pg_restore –create -d postgres /pgbak/USERDB2

Re-Install pgadmin3 :

% apt-get install pgadmin3

Edit /etc/postgresql/9.0/main/postgresql.conf ( Ensure port number is 5432 esp if 9.0 and 8.4 co-exists once , password_encryption must be enable if use MD5) :

listen_addresses = ‘*’
port = 5432
password_encryption = on

Edit /etc/postgresql/9.0/main/pg_hba.conf (I use md5 as connection method because I need all normal postgresql users logon with a password ,as always )

# Database administrative login by UNIX sockets
local all postgres ident
# “local” is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 137.189.56.0/22 md5
# IPv6 local connections:
host all all ::1/128 md5

Restarting PostgreSQL 9.0.3 database server to make changes effective :

% /etc/init.d/postgresql restart

Note :
– under this config file , postgres user cannot logon pgadmin3 running in local machine even type the correct password.
– Other postgres users will require password to logon pgadmin3 running in local machine
– “postgres” user will not require password to logon psql in postgres server :