0

I'm trying to set up a PostgreSQL cluster in my home directory on Debian.

EDIT: There are a couple of reasons why I think this a good idea:

  • I don't use Postgres frequently so I don't currently have the systemd service enabled. I just start it with service whenever I actually need it.
  • The default data directory is on my root partition which is quite small. So I'd like to move it to my home partition which is big.

I've created the cluster and can successfully start the server, but I can't seem to daemonize it.

This is how I created the cluster, using the Debian custom wrapper command:

$ PG_CLUSTER_CONF_ROOT=/home/user/.local/etc/postgresql pg_createcluster -u user -d /home/user/.local/var/lib/postgresql -s /home/user/.local/var/run/postgresql -l /home/user/.local/var/log/postgresql -p 5434 --start-conf manual --locale en_GB.UTF-8 9.5 mycluster
install: cannot change owner and permissions of ‘/home/user/.local/etc/postgresql/9.5’: Operation not permitted
Creating new cluster 9.5/mycluster ...
  config /home/user/.local/etc/postgresql/9.5/mycluster
  data   /home/user/.local/var/lib/postgresql
  locale en_GB.UTF-8
  socket /home/user/.local/var/run/postgresql
  port   5434
Warning: The parent /var/run/postgresql of the selected
stats_temp_directory is not writable for the cluster owner. Not adding this
setting in postgresql.conf.

(I manually added the stats_temp_directory option.)

I can now start the cluster like this (again, with the Debian wrapper command):

$ PG_CLUSTER_CONF_ROOT=/home/user/.local/etc/postgresql pg_ctlcluster --foreground 9.5 mycluster start

And it runs, and I see log messages in my console, and I can use createdb and psql (as long as I set the socket file with -h and the port with -p, which is fine).

But I can't get it to start without the --foregound option. It waits for a short while, and then says:

The PostgreSQL server failed to start. Please check the log output:

I've tried setting the log file, both like this:

$ PG_CLUSTER_CONF_ROOT=/home/user/.local/etc/postgresql pg_ctlcluster -o '-r /home/user/.local/var/log/postgresql/server.log' 9.5 mycluster start

And like this:

$ PG_CLUSTER_CONF_ROOT=/home/user/.local/etc/postgresql pg_ctlcluster 9.5 mycluster start -- -l /home/user/.local/var/log/postgresql/server.log

But I still get the same result. And no log file gets created.

  • Why are you trying to do this? Why not run it as a postgres user? – EEAA Mar 7 '16 at 23:29
  • Well, let's assume I have a good reason for now. If nothing else, I don't want to be defeated by it! But OK, I can see that giving my motivation might help someone to suggest a different approach. – ironchicken Mar 8 '16 at 11:08
  • I've added some (admittedly quite weak) motivations. – ironchicken Mar 8 '16 at 11:14
  • I think regarding the non-postgres user question, I'm sure it ought to be possible. – ironchicken Mar 8 '16 at 11:15
0

It might be simpler in your case to bypass the Debian/Ubuntu wrappers for postgres.

How about initializing and starting a private postgres cluster with:

$ mkdir $HOME/pg

$ /usr/lib/postgresql/9.5/bin/initdb -D $HOME/pg

$ vi $HOME/pg/postgresql.conf
#  change `port` and `unix_socket_directories`, for example:
port= 5495
unix_socket_directories = '/tmp' 

# start the server, will run in background and processes belonging to $USER
$ /usr/lib/postgresql/9.5/bin/pg_ctl -D $HOME/pg -l /path/to/logfile start

# use it (initdb has already created a superuser from your $USER name)
$ psql -p 5495 -h/tmp -d postgres

# stop the server    
$ /usr/lib/postgresql/9.5/bin/pg_ctl -D $HOME/pg -l /path/to/logfile stop

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.