18

I want to upgrade my PostgreSQL from version 8.4 to 9.4.

The documentation is not very clear to me.

  1. Will I lose my old databases if I do the upgrade?
  2. How can I backup my old databases if I am to lose them after the upgrade?
  3. How can I upgrade my psql?

My PostgreSQL is running on a CentOS 6.6 server.

Oreo
  • 1,566
  • 1
  • 10
  • 22
Ghasem
  • 549
  • 1
  • 5
  • 12

3 Answers3

26

This is how I solved my problem.

Upgrade Postgresql 8.4 to 9.4 in Centos

 1. Yum Install PG9.4
 2. wget http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm
 3. yum install pgdg-redhat94-9.4-1.noarch.rpm
 4. yum install postgresql94-server
 5. service postgresql-9.4 initdb
 6. chkconfig postgresql-9.4 on

Backup Data 

 7. su - postgres

 8. pg_dumpall > dump.sql

Restore Data

 9. service postgresql stop

 10. service postgresql-9.4 start

 11. su - postgres

 12. psql < dump.sql

Config Network Access

vi /var/lib/pgsql/9.4/data/postgresql.conf

 1. listen_addresses = '*'
 2. port = 5432

/var/lib/pgsql/9.4/data/pg_hba.conf

# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          ident
host    all         all         130.51.79.0/24        md5
host    all         all         10.210.29.0/24        md5
# IPv6 local connections:
host    all         all         ::1/128               ident

Remove PG8.4

 1. yum remove postgresql
 2. ln -s /usr/pgsql-9.4/bin/psql /usr/local/bin/psql
ypercubeᵀᴹ
  • 99,450
  • 13
  • 217
  • 306
Ghasem
  • 549
  • 1
  • 5
  • 12
3

service postgresql-9.4 initdb didn't work for me, I had to use sudo /usr/pgsql-9.4/bin/postgresql94-setup initdb (found here).

Thanks for the great instructions, I was able to update from 9.2 to 9.4 without any issues, even though I had to reconfigure my pg_hba.conf file, that was trivial.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Jeff
  • 31
  • 1
1

Better than linking a single postgresql94 psql binary to /usr/(local/)bin is to use the alternatives system:

cd /etc/alternatives/
ls pgsql-* -1 | xargs -L 1 alternatives --auto

Which creates links of binaries, mans, confs, ... of postgresql94 to default directories for CentOS.

Tom V
  • 15,752
  • 7
  • 66
  • 87
Michal Michaláč
  • 11
  • 1