Ubuntu: upgrading postgresql 8.4 database cluster for use with 9.2 server I have installed PG 9.2 on my machine. I have a database cluster that was created about a year ago, using initdb, using postgreSQL version 8.4.

The (8.4) data cluster consumes several Gb and so its on its own separate hard disk. I instructed the old 8.4 server to use the correct data location by passing it a -D /path/to/data/folder when the server was started.

My question is this:

I have a (v8.4) database cluster (containing about 10 databases) located at /some/path/to/datadir

I have a freshly installed postgreSQL 9.2 server.

How do I upgrade the cluster (and its databases), so that the new server can use the cluster - bearing in mind that I want the data to remain in the current data location (a physically separate device mapped to '/').

I tried running the server against the old cluster file by modifying the configuration file, and I (unsurprisingly), got the following error:

 * Starting PostgreSQL 9.2 database server                                                                                                     * The PostgreSQL server failed to start. Please check the log output:
2012-11-18 23:50:31 GMT FATAL:  database files are incompatible with server
2012-11-18 23:50:31 GMT DETAIL:  The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.2.1.


So how can I upgrade an 8.4 cluster (in a specified data directory) to be used by a 9.2 server?
Don't know about 2) and 3) (I don't use Ubuntu). 

But to migrate the data from 8.4 to 9.1 pg_dump.

Assuming that that 8.4 runs on port 5432 and 9.1 runs on port 5433 something like the following will copy all the data from 8.4 to 9.1:

/usr/lib/postgresql/9.1/bin/pg_dump -p 5432 -U -c -C postgres my_db | /usr/lib/postgresql/9.1/bin/psql -p 5433 


This assumes that your current user can log on to both databases. 

The full path to pg_dump is necessary to ensure that the dump is taken using the target version. 

Of course you can also use an intermediate file if you prefer that. More details are in the manual: PostgreSQL: Documentation: 9.1: Backup and Restore

Using the pg_dump binary from the new version is intentional.

From the manual: 17.6. Upgrading a PostgreSQL Cluster - 17.6.1. Upgrading Data via pg_dump


  It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL