Postgresql – Trying to set up new data_directory on Postgres 9.3

postgresqlpostgresql-9.3

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.

Best Answer

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.

Related Solutions

Postgresql – Authentification failure for PostgreSQL server (9.3)

I found a temporary solution to my problem. I edited the pg_hba.conf of the 9.3 server to say trust in the first two active lines. And after restarting (sudo service postgresql restart) I can connect to the server again using pgadmin. The downside is that the server might now not be password protected (I will open a separate question if I encounter any problems with that).

    # Database administrative login by Unix domain socket
    local   all             postgres                                trust

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5

Postgresql – Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

If you suspect there's something wrong with our database (cluster), make a copy of your database directory before you continue. Just to be sure. On Ubuntu the default directory would be: /var/lib/postgresql/9.3/main/ - but your installation may differ.

Actually, your command should work (in default Ubuntu installations), because the service command starts the postgres server process with appropriate privileges (as system user postgres)

sudo service postgresql start

Translates to something like:

sudo -u postgres /usr/lib/postgresql/9.3/bin/pg_ctl -D /var/lib/postgresql/9.3/main/ -o "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" start

That starts all db clusters you may have. To start a particular one, use the wrapper pg_ctlcluster:

sudo -u postgres pg_ctlcluster 9.3 main start

To get the status of all currently installed db clusters use pg_lsclusters:

sudo -u postgres pg_lsclusters

You must be aware that the data directory is not the same as the config directory in default Debian or Ubuntu Postgres installations. For Postgres 9.3 that would be:

data dir: /var/lib/postgresql/9.3/main/
config dir: /etc/postgresql/9.3/main/

You can check the /etc/postgresql/9.3/main/postgrsql.conf for the actual data dir. It's the line starting with

data_directory =

The error message indicates that Postgres tries to start with the default data dir, but it is not there or the privileges have been changed. Did you check?

Or are you running a non-default installation?

Best Answer

Related Solutions

Postgresql – Authentification failure for PostgreSQL server (9.3)

Postgresql – Error: /var/lib/postgresql/9.3/main is not accessible or does not exist

Related Question