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
