Azure – Change and initialize Postgresql 9.3 default data and log directories on Ubuntu 12.04 LTS

azurepostgresqlubuntu-12.04

On Azure with Ubuntu 12.04 LTS image, we're trying to change Postgres 9.3 default data and log file paths and also initialize them for the first time on a secondary drive /mnt/pgdata. We've starting from the point where Postgres 9.3 was a fresh install via apt-get.

We've stopped postgres: sudo service postgresql stop.

We've tried to run pg_createcluster

The following just displays the help/man pages. Probably because it's missing the version and name params.

pg_createcluster -d /mnt/pgdata/data -l /mnt/pgdata/log --start-conf auto

The following results in "Error: cluster configuration already exists" even though nothing exists in /mnt/pgdata.

pg_createcluster 9.3 main -d /mnt/pgdata/data -l /mnt/pgdata/log --start-conf auto

We're fairly new to Linux in general so please be specific with your answer.

Also, we have the following questions:

  1. Is it ok to use the direct mount path or is it better to use a symbolic link?
  2. Is there anything else that we need to do or change to setup Postgres 9.3 with these default data dir on the default port and to start automatically with the server?
  3. Any other recommendations?

Best Answer

The reason of this error: "Error: cluster configuration already exists" is that the name main is already assigned to the cluster that gets initially created.

If you haven't any data in it, you may drop it before creating your own with:

pg_dropcluster 9.3 main

This will also have the desirable effect that your new cluster will be assigned the port number 5432 instead of the non-default 5433.

Whether you use a direct mount path or a symbolic link doesn't really matter, but note that configuration files will be created in /etc/postgresql/<version>/<clustername>/ outside of the data directory.

According to its manpage, pg_createcluster command expects the options before version and name, so the command would be:

pg_createcluster -d /mnt/pgdata/data -l /mnt/pgdata/log --start-conf auto 9.3 main

Also having the logfile in /mnt/pgdata makes it unreachable to logrotate, so it will grow endlessly. You may look at /etc/logrotate.d/postgresql-common to see how it's configured to process /var/log/postgresql/*.log

Related Topic