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.

  • To be honest, the description is very clear on the linked page. Taking a dump means you have a dump for the case something does not work. That also means that you don't lose it. Furthermore, there is a link on that page pointing to how to actually install a newer version - for example, on Ubuntu, there is a postgresql-client-9.4 package that contains psql. – dezso Apr 17 '15 at 13:37
  • @dezso So if I uninstall psql 8.4 and go for 9.4, I will lose my old databases? – Alex Jolig Apr 17 '15 at 14:37
  • 3
    If you take a dump of them, then no. You should anyway have regular backups that you test for recoverability, so this should be no issue at all. Also, if you choose to use pg_upgrade, it will keep your DBs - but taking a backup is a must in this case, too. – dezso Apr 17 '15 at 15:07
  • "How can I backup my old databases if" - there is no if. Your databases will almost certainly be perfectly fine after the upgrade but you should always have fresh (preferably tested) backups when performing an operation like this anyway in case of unexpected problems (assume the worst: a power cut or other hardware fault part way might leave you in a position that is difficult to roll either back or forward from). Depending on your regular backup plan you might not need to take extra backups. – David Spillett Aug 24 '17 at 11:51
25

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
  • 3
    I'm not sure why you rejected my edit as "harm readability". It actually makes the commands easier to copy, – nhahtdh Jun 28 '16 at 4:55
  • 1
    I just did an upgrade from 9.2 to 9.6 using this method, and it worked. Thanks ! – EisenHeim Sep 19 '17 at 14:25
  • I think it is impossible to use pgdump to migrate a database with terabytes of data for example. Is there another way to do that? – deFreitas Mar 8 '18 at 13:52
  • FOr the first command, "Yum Install PG9.4", is that tthe right capitalization for everything? – Dave May 8 '18 at 19:30
  • 1
    @deFreitas I'm a bit late but you can use pg_upgrade if upgrading from >=8.4 and if you use the same server. pg_upgrade is usefull in this cases and really faster: on Ubuntu 18 it took me less time making a pg_upgrade than a pg_dump to restore a 1GB database. The con is that you have to install and run 2 postgres versions in the same machine (listening to different ports, of course). – EAmez Sep 24 at 11:51
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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.