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:
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
local all <user> trust
At this point we may as well configure the network settings too.
<pgdata>/postgresql.conf, set the following:
listen_addresses = '0.0.0.0' port = <port>
Depending on your circumstances, you may want something more restrictive for
port should be something that nobody else is using on this machine.
If the default doesn't suit you, feel free to also set
Then it should be possible to start it up:
pg_ctl -D <pgdata> start
In some cases, the user might not have write permissions in the default socket directory:
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
unix_socket_directories = '.'
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
psql -p <port> postgres
postgres=# SET password_encryption = "scram-sha-256"; postgres=# ALTER USER <user> WITH PASSWORD '<password>';
<user> to log in with
<password>, we add
host <dbname> <user> 0.0.0.0/0 scram-sha-256
<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:
pg_ctl -D <pgdata> restart
You should then be able to create a database using
createdb -p <port> <dbname>
and access it with
psql -p <port> <dbname>
For remote access over the network, simply use
psql -h <host> -p <port> <dbname>
To run the daemon in the foreground, you can run
postgres -D <pgdata>