Running Postgres as a normal user

Whilst testing out some PostgreSQL replication scenarios, I needed to have multiple instances of Postgres running under my user. I could have used multiple Docker machines with ports exposed, but I opted for another solution. Here’s how I did it.

First, I created /home/user/pgdata to hold the data directories, and /tmp/postgres to hold the UNIX socket files.

Then, it’s just a case of running /usr/lib/postgresql/12/bin/initdb /home/user/pgdata/1 -E UTF-8 to create a data directory, then editing postgresql.conf and changing the TCP port from 5432 to, say, 50432, and unix_socket_directories to /tmp/postgresql.

To start the server, run /usr/lib/postgresql/12/bin/pg_ctl -D /home/user/pgdata/1 -l logfile start, and to stop it, replace "start" with "stop". The final step is to create a postgres user with /usr/lib/postgresql/12/bin/createuser -drs postgres -h localhost -p 50432 and you’re away.

Some use cases I can think of for this are:

  • An isolated Postgres instance for integration testing
  • Trying out a new version of Postgres in parallel with your existing version
  • Verifying your disaster recovery procedures

There are probably more.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.