2

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.

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
  • I am using Ubuntu repositories, not apt.postgres.org. Debian documentation (link 2) looks promising. I will try that and report back. – Lee Hachadoorian Oct 10 '16 at 16:02
  • This set me on the right track, but following the conventions of SE, I would ask that you elaborate on the answer rather than just link to the solution. I ended up using link 2, as well as the documentation for pg_createcluster discussed therein, to find the right solution. I have blogged it here. I would edit your answer myself, but I don't have enough rep. – Lee Hachadoorian Oct 10 '16 at 21:32
  • nice post @LeeHachadoorian! I will edit the post. Remember to use the packages from the PGDG repo. – Sebastian Webber Oct 10 '16 at 22:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.