PostgreSQL

PostgreSQL is a database engine. It has an excellent manual.

Setting up a private instance

Sometimes it's desirable to have a private PostgreSQL instance (for example run from the user's home directory) instead of a database in a centralized (administrator managed) instance. The following shows how to set this up with PostgreSQL 10.

First, we let PostgreSQL create a directory for itself:

1
initdb <pgdata>

This could be anywhere the user has write permissions.

Before we start the daemon, we need to ensure that nobody else will be able to connect to it. This is done by modifying <pgdata>/pg_hba.conf so that the only uncommented line is

1
local all <user> trust

At this point we may as well configure the network settings too. In <pgdata>/postgresql.conf, set the following:

1
2
listen_addresses = '0.0.0.0'
port = <port>

Depending on your circumstances, you may want something more restrictive for listen_addresses, and port should be something that nobody else is using on this machine. If the default doesn't suit you, feel free to also set max_connections.

Then it should be possible to start it up:

1
pg_ctl -D <pgdata> start

In some cases, the user might not have write permissions in the default socket directory:

1
FATAL:  could not create lock file "/var/run/postgresql/.s.PGSQL.<port>.lock": Permission denied

If that's the case, you can ask it to put the socket files in <pgdata> by setting

1
unix_socket_directories = '.'

in <pgdata>/postgresql.conf. Then commands that require the port will also need you to add -h <pgdata> with a full path to <pgdata>, including a leading slash.

Now we can configure it for secure access over the network. First, we connect locally without authenticating using

1
psql -p <port> postgres

Then

1
2
postgres=# SET password_encryption = "scram-sha-256";
postgres=# ALTER USER <user> WITH PASSWORD '<password>';

To allow <user> to log in with <password>, we add

1
host <dbname> <user> 0.0.0.0/0 scram-sha-256

to <pgdata>/pg_hba.conf for each <dbname> we'll be using. You may also choose to use all instead of <dbname>, but that will grant access to the postgres database over the network as well, which might not be something you want.

Now we can restart it with the new settings:

1
pg_ctl -D <pgdata> restart

You should then be able to create a database using

1
createdb -p <port> <dbname>

and access it with

1
psql -p <port> <dbname>

For remote access over the network, simply use

1
psql -h <host> -p <port> <dbname>

To run the daemon in the foreground, you can run

1
postgres -D <pgdata>

instead of pg_ctl.