Trying to set up new data_directory on Postgres 9.3

Question

Ubuntu 14.04, recently installed Postgres 9.3.

Trying to set up a new data_directory to an attached, larger hard drive (/data), and I changed the postgresql.conf file to reflect that.

However, I get this error when trying to restart the server:

user@server:/etc/postgresql/9.3/main$ sudo /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.3 database server                                     
 * Error: pid file is invalid, please manually kill the stale server process.

I don't have too much experience setting up Postgres, so I'm not sure what to do from here, and Googling around provides no quick answer.

Answer 1

Ubuntu and Debian use pg_wrapper to manage PostgreSQL. It's in the postgresql-common package.

(As a result, this answer doesn't apply to RHEL/CentOS/Fedora).

If there's no data of value, you can just use pg_dropcluster (part of pg_wrapper) to drop (delete) your old datadir. This will destroy all data in it. Then use pg_createcluster to make a new one in /data. That's simplest, but of course it's destructive.

If you want to retain the data, you must:

• Ensure that the file system on the new storage is compatible with PostgreSQL. Do not attempt to put PostgreSQL on FAT32 / vfat. I wouldn't attempt to use NTFS on Linux either.
• Create every directory up to the location you want for the new datadir.
• Ensure that the postgres user has at least x rights on all directories from the data dir down to the / directory, especially including the mount point. You may want to chmod a+x each directory along the path, or chgrp postgres and chmod g+x it.
• stop PostgreSQL
• mv the data directory, as shown by pg_lsclusters, to the desired new location. If you copy it instead, use cp -aRv to make sure permissions are preserved correctly. Don't just move all contents, move the directory its self.
• Edit /etc/postgresql/mypgversion/postgresql.conf and change data_directory to point to the new data directory location.
• Start PostgreSQL

I think the mistake you made was, in bold above, that you failed to stop PostgreSQL before moving the datadir. It won't stop the server because it isn't sure it'd be killing the right process, so it refuses to act. You must:

sudo pkill -u postgres

then make sure that all PostgreSQL processes have exited (very important) by making sure that this returns no results:

ps -u postgres

and only then attempt to start the server again.

Never delete postmaster.pid.

Answer 2

One good way to do this is to have your PGDATA (/var/lib/postgresql/9.3/main) symlink-ed to /data. Doing it this way no need to change postgresql.conf.