What is the recommended way to autostart a Postgres cluster using a nonstandard data directory?

I am running PostgreSQL 9.5 on Linux Mint 18.

• At installation, apt creates a cluster in /var/lib/postgresql/9.5/main.

• Environment settings (postgresql.conf, pg_hba.conf, pg_ident.conf) are stored in /etc/postgresql/9.5/main.

• postgresql.conf sets data directory to /var/lib/postgresql/9.5/main.

I have a root partition that is deliberately small, and want to have my cluster on a larger data partition. I ran initdb in /data/pg_data. I am able to start and stop this cluster manually using pg_ctl. My question is, how do I start Postgres using this cluster at system startup?

I read 17.3 Starting the Database Server in the Postgres docs. That page describes (a) setting the PGDATA environment variable, or (b) having the server start the postgres daemon at boot time.

Using PGDATA

I tried to accomplish this by adding the following line to /etc/environment:

PGDATA="/data/pg_data"

I then restarted. Postgres started, but is still using default data directory. echo $PGDATA indicates environment variable was set, and if I stop running cluster, pg_ctl start without data directory specified does start desired cluster. But it's not handling it at boot time.

Using init scripts

I can't figure this one out.

The docs say:


Different systems have different conventions for starting up daemons at boot time. Many systems have a file /etc/rc.local or /etc/rc.d/rc.local. Others use init.d or rc.d directories.





-
On Linux systems either add

/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data

to /etc/rc.d/rc.local or /etc/rc.local or look at the file contrib/start-scripts/linux in the PostgreSQL source distribution.


Linux Mint uses init.d folder. I found a postgresql file in there and opened it. It is a 60 line bash script. The first few lines are:

```
# Setting environment variables for the postmaster here does not work; please
# set them in /etc/postgresql///environment instead.

Are You using the official packages[1], supported by the PGDG?

You can do 2 things:

• edit the startup script file (or make a simbolic link) pointing to the new directory.

• Edit the /etc/postgresql/[version]/[cluster]/pg_ctl.conf to point to the new directory;

Please, take a look a documentation from debian website[2] to see more details.

EDIT: Looks like what you are looking for it's the pg_createcluster[3] utility.

Hope it helps!

References:

• http://apt.postgresql.org

• https://wiki.debian.org/PostgreSql

• http://manpages.ubuntu.com/manpages/trusty/en/man8/pg_createcluster.8.html

StackExchange Database Administrators Q#151805, answer score: 2