Postgresql – Copying database to different hard disk

copymigrationpostgresqlrailsstorage

Total noob at database administration here, excuse my ignorance.

I have a server running a Rails application that's using PostgreSQL. The database is saved on drive /z/, which seems to have filled up. I want to put a higher capacity drive and shift the database to that. How should I go about doing this? Should I copy the data folder from drive /z/ to the new drive and then change the config files (could someone point me to what config changes I need to make?)

Or could I copy the data folder to the new hard disk, and then mount the disk as /z/ and then I'll need no config changes?

Or is there some helper commands in postgres that provide a functionality to migrate between disks?

I'm running Ubuntu 12.04.5 LTS. Output of version():

PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

Best Answer

if you add a second disk i suggest to put data on to different tablespaces. a tablespace offers additional storage and is VERY easy to use:

mkdir /whatever

then in SQL:

CREATE TABLESPACE myspace LOCATION '/whatever';
ALTER DATABASE x SET TABLESPACE myspace;

of course you can also do this for single tables only. and yes, alternatively you can move the entire data dir and tell the system where the new location is.

Related Question