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.

• Craig Ringer: Did you stop PostgreSQL before you moved the data directory? If not, I strongly suggest a sudo pkill -u postgres. Never do anything to the data directory while PostgreSQL is running.
• alxlvt: I did not, but I have edited the question with new issues.
• Craig Ringer: Please don't edit questions to change the meaning, it'll confuse people who come here trying to learn about the same issue later. When you have a follow-up or related question please post a new one and link back to this one for context.

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.

• user1822: But he only wants to move the data directory not completely delete everything.
• Craig Ringer: @a_horse_with_no_name Yep, and pg_wrapper should really have a pg_movecluster. But it doesn't. You can move it by hand by twiddling the pg_wrapper metadata, but it's annoying, and if there's nothing interesting in the existing cluster...
• user1822: So just moving the directory and adjusting postgresql.conf does not work on Ubuntu? Seems like a strange way to install it.
• Craig Ringer: @a_horse_with_no_name Actually, in a quick test, it does. pg_lsclusters etc parses postgresql.conf and because pg_wrapper pulls postgresql.conf out of the datadir and puts it in /etc it's possible to do just what you describe. Please post that as an answer (with appropriate info about permissions, stopping the server first, etc).
• user1822: As I don't have any experience with Ubuntu I don't think my answer would be detailed enough. Feel free to add that to yours.

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.