0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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