Table of Contents

How to install PostgreSQL

Install

sudo apt install postgresql postgresql-contrib

Change superuser password

First of all you have to set a new password for the superuser postgres. Note: superuser postgres does not have any password by default.

In order to set the password, type the following command line into your terminal:

sudo -u postgres psql postgres

Now you are in psql, the command interface for postgresql, and in the database postgres. In your terminal it looks like this:

$ postgres=#

To change your password:

$ \password postgres

… and type your new password when asked for it. Type \q and hit enter to quit.

Enable TCP connections

By default TCP/IP connections are disabled, so users are not able to remotely access PostgreSQL server from another computer. To enable it:

sudo vi /etc/postgresql/9.6/main/postgresql.conf

… and change …

#listen_addresses = localhost

… to …

listen_addresses = '*'

Note: this allows remote access only from this all IP addresses. If you want remote access from specific computers in your network use a comma separted list of addresses, like this: listen_addresses='addres1,addres2,localhost'

Next, uncomment:
#password_encryption = on
… to:
password_encryption = on

Edit pg_hba.conf

Client authentication is controlled by a configuration file, named pg_hba.conf. For each client the auth-method must be specified.

sudo vi /etc/postgresql/9.6/main/pg_hba.conf

IPv4/IPv6 local connections are configured by default. Any other single IP client, like x,x,x,x SavaPage Server, must be added like this:

# Allow SavaPage Server
host    savapage     savapage     x.x.x.x/32     md5

Restart

Changes made above need a restart:

sudo systemctl restart postgresql.service

Create Database

# As user 'postgres', create a new user 'savapage'
# -P indicates, that this user will have a password,
sudo -u postgres createuser -P savapage
 
# Enter password for new role: 
# Enter it again:
 
sudo -u postgres createdb --owner=savapage savapage

How to use psql

The psql shell can be used to check which databases are present, or to execute SQL statements.

# start interactive shell
psql -h localhost -U postgres
-- list all databases.
postgres=# \l
-- list all user accounts.
postgres=# \du
# start interactive shell
psql -h localhost -U savapage savapage
-- enter sql statements (autocomplete is active)
postgres=# select * from tbl_printer;