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. Commented Apr 17, 2015 at 13:37
  • @dezso So if I uninstall psql 8.4 and go for 9.4, I will lose my old databases?
    – Ghasem
    Commented Apr 17, 2015 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. Commented Apr 17, 2015 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. Commented Aug 24, 2017 at 11:51

3 Answers 3


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


# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all          ident
host    all         all        md5
host    all         all        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
    Commented Jun 28, 2016 at 4:55
  • 1
    I just did an upgrade from 9.2 to 9.6 using this method, and it worked. Thanks !
    – 3isenHeim
    Commented Sep 19, 2017 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
    Commented Mar 8, 2018 at 13:52
  • 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
    Commented Sep 24, 2019 at 11:51
  • 1
    After upgrading to postgres 12, the original postgres moved the unix socket to /tmp. Surprise! I had to add unix_socket_directory = '/var/run/postgresql' to the postgresql.conf file to get it back.
    – raarts
    Commented Apr 28, 2020 at 9:31

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.


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 and acknowledge you have read our privacy policy.

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