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?
Best Answer
It appears you have an 8.4 database that isn't managed by
pg_wrapper
, and have installed 9.2 from Ubuntu packages and allowed it to create apg_wrapper
managed cluster in the default location, which isn't where you want the DB.pg_upgradecluster
frompg_wrapper
isn't suitable for this, since you hand-initdb'd the old cluster.If the 9.2 cluster doesn't have any data of value in it yet, drop it, destroying all its data:
Then re-create it in the desired location. Assuming your mounted HD's root is
/mydata
, you'd:Now dump the old database and load it into the new one. Start both 8.4 and 9.1 on different ports; I'll assume 8.4 is on 5432 (default) and 9.2 is on 5433. Something like this should copy the dump from one directly into the other. Make sure you use the 9.1 versions of
pg_dumpall
andpsql
:... but personally I'd do a
pg_dumpall --globals-only
using the 9.2pg_dumpall
against the 8.4 DB, followed by individualpg_dump
s of each database in the old cluster (again using the 9.2pg_dump
). I'd then restore the globals dump followed by each individual dump into the new cluster.Alternately, you can drop the
pg_wrapper
managed 9.2 cluster, not re-create it, and follow the instructions forpg_upgrade
to do a binary upgrade from the 8.4 to the 9.2 DB. This will create a DB that is not managed bypg_wrapper
. You can either convert it to be managed bypg_wrapper
by moving and symlinkingpostgresql.conf
andpg_hba.conf
, or you can just manage it like you did 8.4, using your own init scripts.