upgrading from postgres 9.1 to 9.3 on ubuntu server

Question

I have my production server(ubuntu 13.10) running with postgresql 9.1.

I want to use few features of 9.3, hence want to upgrade.

Could someone help me with upgrading from 9.1 to 9.3 so that there is a downtime of not more than 30 mins. or so?

Prime concern is preventing a data loss or file redundancy.

• Philᵀᴹ: The Postgres docs are really good. google.co.uk/#q=postgres+upgrade+from+9.1+to+9.3
• tink: I'm not a DBA per se (have to look after the odd Postgres install and even odder mysql ;}), and this post is old, but why would you use any Ubuntu X.10 release for prod rather than an X.04 LTS?

Answer 1

There are basically three ways of upgrading PostgreSQL from different major versions (e.g. 9.1 to 9.3).

Upgrading with pg_dump

The first one, and recommended if possible, is to do a dump of the old (9.1) version using the binary of the newer (9.3) version and restore it on a new cluster created of the newer version.

This approach is, generally, the slower one, but also the most feasible. One tip to make it more fast, is using concurrency. To dump with parallel jobs, you can do:

$ pg_dump --format=directory --jobs=4 --no-synchronized-snapshots --file=/path/to/mydump mydatabase

You'll have to do it for each database you have, adjust --jobs=4 value to any value (test some values from 2 to number of cores, and see which gives better speed). Also, during this phase, nobody should be conected to the database, any modification will result in a corrupted dump (because of the non-secure option --no-synchronized-snapshots).

After that, you can restore you dump into the new instance using pg_restore:

$ createdb <options> -T template0 mydatabase
$ pg_restore --exit-on-error --jobs=4 --dbname=mydatabase /path/to/mydump

After that, it is recommended to run ANALYZE on your database:

$ vacuumdb --analyze-only mydatabase

(if you can afford the time, run only --analyze to also VACUUM the database and update the visibility maps)

Upgrading with pg_upgrade

Another option, is to use the contrib pg_upgrade. Using the --link method it provides a really fast way of upgrading PostgreSQL.

Before using you have to make a backup of the entire data directory, because in --link mode, if something goes wrong, you may loose both data (new and old). Also, read the entire docs and specially the notes at the bottom (there are some limitations for pg_upgrade).

UPDATE: Please use the --check option before run the definitive command. Also, for large databases is recommendable to run this command in a screen session.

Upgrade using a trigger based replication tool

Another option to upgrade a version, is using a replication tool based on trigger. Like Slony, Bucardo and Londiste.

This is the option that requires the least downtime possible, but it is the hardest one to work on.

To do that you need to build a master-slave where the master is your current version (9.1) and the slave is the new version (9.3). You then, wait the first sync (with the system still in production), after that you close everyone connected to the database (the downtime starts here), wait for the slave to catch-up, promote it (the slave) to master and redirect all clients/applications to this new version. And you're done.

The Slony documentation provides a step-by-step to upgrade PostgreSQL using Slony.

Which one to choose

Well, as always depends, resuming:

• The dump+restore is the most reliable, but generally the most slow one (the parallelism can give pretty good results though)
• The pg_upgrade is one of the best options for little downtime (if you can use, see the limitations), it often takes only a few minutes, even for big databases
• The trigger replication, is with no doubt the one that gives the least downtime possible (near zero), but it is really hard to achieve and I recommend only for experience people (on both PostgreSQL and the replication tool).

I hope I could help. Good luck.

• JohnMerlino: Will pg_upgrade also work from 8.4.17 to 9.3?
• MatheusOl: @JohnMerlino: yes, pg_upgrade can upgrade from 8.3 or higher versions (it includes 8.3, 8.4, 9.0, 9.1, 9.2, ...).
• theicfire: "using the binary of the newer (9.3) version" might not be that necessary.. I've at least got it to work without doing that bit.
• MatheusOl: @theicfire it actually depends on both versions (the old and new one), it may or may not work. In fact it will work in most cases, except for some error messages that can usually be ingored... The official procedure is to use the binary of the newer version though!
• Matthieu: You might be able to use pg_dumpall to dump all databases in one go.

Answer 2

Follow these steps for upgrading postgres 9.1 to 9.3:

1. First create an /etc/apt/sources.list.d/pgdg.list file with the following contents for ubuntu:

   deb http://apt.postgresql.org/pub/repos/apt/ saucy-pgdg main 
   

2. Add key running following:

    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
   

3. Install developer tools with postgres:

   sudo apt-get install postgresql-9.3 postgresql-contrib-9.3 postgresql-server-dev-9.3 pgadmin3
   

4. To confirm our install, we’ll enter "sudo pg_lsclusters" and see our two versions of PostgreSQL running.

5. Stop Postgressql service:

   sudo service postgresql stop
   

6. Delete the default 9.3 cluster created by the 9.3 install.

   sudo pg_dropcluster --stop 9.3 main
   

7. Create a new 9.3 cluster from the existing 9.1 cluster.

   sudo pg_upgradecluster 9.1 main
   

8. Confirm that the new cluster loads and we’re running PostgreSQL 9.3.

   sudo service postgresql start 9.3
   

9. If everything works drop to 9.1 cluster.

   pg_dropcluster --stop 9.1 main
   

for more information refer to this link

Answer 3

This could be really useful :

https://gist.github.com/ibussieres/11262268

It's for 12.04, but I'm guessing it's fairly transferable.