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

Question

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/<version>/<cluster>/environment instead.

[ -r /usr/share/postgresql-common/init.d-functions ] || exit 0

. /usr/share/postgresql-common/init.d-functions

So, first I look in /etc/postgresql/9.5/main/environment. That file is empty except for comments. The first line says # environment variables for postmaster process. Is something supposed to go in here that points to my desired data directory?

The file includes /usr/share/postgresql-common/init.d-functions. That is a 112 line file that clearly points to /etc/postgresql/<version number> and attempts to start all clusters that it finds there. It also clearly references postgresql.conf. So it seems that this is how the default cluster is being started, but it is not clear to me that this can or should be edited.

Answer 1

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

You can do 2 things:

1. edit the startup script file (or make a simbolic link) pointing to the new directory.
2. 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:

1. http://apt.postgresql.org
2. https://wiki.debian.org/PostgreSql
3. http://manpages.ubuntu.com/manpages/trusty/en/man8/pg_createcluster.8.html