PostgreSQL Migration – Point PostgreSQL on Ubuntu to a New Datadir

migrationpostgresql

I have a new Ubuntu 14.04 install on a new drive, with the old main drive containing a PostgreSQL install from the previous Ubuntu 14.04 install.

I've been trying to move the PG dbs from the old location (/olddrive/var/lib/postgresql/9.3/main) to a new one on the new drive (/newdrive/postgresdbs), but haven't succeeded.

This is what I've done:

sudo service postgresql stop # stop PostgreSQL

sudo pg_dropcluster 9.3 main # remove the default cluster created during the new Ubuntu install

sudo pg_createcluster -d /newdrive/postgresdbs/ 9.3 main # recreate the default cluster in the new location (pg_createcluster is a wrapper for initdb on Debian/Ubuntu)

sudo cp -R /olddrive/var/lib/postgresql/9.3/main/base/nnnnn /newdrive/postgresdbs/base/ # copy across each of the data directories from the base dir in the old location to the base dir in the new location, where nnnnn is a number

sudo chown -R postgres:postgres /newdrive/postgresdbs/ # set postgres to be the owner of the new location

sudo service postgresql start # start PostgreSQL

This seems as if it should work, and PostgreSQL starts fine, but I have to recreate my superuser, and none of the data tables show up – there's just the postgres table there.

I'm now wondering if this is even possible. If not, is there any way to access the data tables from the old location? I could initialise everything from a backup, but that may not be entirely up-to-date (and in any case, where's the fun in that?).

I did look at tablespaces, but would they work here, and would I have to give the tablespace name against every query I ran on tables in the old location?

EDIT after Craig's answer:

For reference, the commands I used to implement Craig's answer were:

sudo service postgresql stop
sudo pg_dropcluster 9.3 main
sudo pg_createcluster -d /newdrive/postgresdbs/ 9.3 main
sudo cp -a /olddrive/var/lib/postgresql/9.3/main/. /newdrive/postgresdbs/
sudo chown -R postgres:postgres /newdrive/postgresdbs/
sudo service postgresql start

Best Answer

You can't just copy base from the old cluster to the new one. It doesn't work like that. You'll be missing the transaction logs, transaction commit statuses, global catalogs, and all the other components that make those databases make any sense.

Instead, pg_createcluster in the new location, then replace the entire data directory with the one from your old system. That's the directory with base, pg_xlog, global, etc in it.

Related Question