howto:install:postgresql [SavaPage

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== How to install PostgreSQL =====

==== Install ====

<code bash>
sudo apt install postgresql postgresql-contrib
</code>

==== 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:

<code bash>
sudo -u postgres psql postgres
</code>

Now you are in ''psql'', the command interface for postgresql, and in the database postgres. 
In your terminal it looks like this:
<code>
$ postgres=#
</code>    

To change your password:

<code>
$ \password postgres
</code>
    
... 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:

<code bash>
sudo vi /etc/postgresql/9.6/main/postgresql.conf
</code>
... and change ...
<code>
#listen_addresses = localhost
</code>
... to ...
<code>
listen_addresses = '*'
</code>

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 [[https://www.postgresql.org/docs/devel/static/auth-pg-hba-conf.html|pg_hba.conf]]. For each client the ''auth-method'' must be specified.

<code bash>
sudo vi /etc/postgresql/9.6/main/pg_hba.conf
</code>

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:

<code>
# Allow SavaPage Server
host    savapage     savapage     x.x.x.x/32     md5
</code>

==== Restart ====

Changes made above need a restart:
<code bash>
sudo systemctl restart postgresql.service
</code>

==== Create Database ====

<code bash>
# 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
</code>

==== How to use psql =====

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

<code bash>
# start interactive shell
psql -h localhost -U postgres
-- list all databases.
postgres=# \l
-- list all user accounts.
postgres=# \du
</code>

<code bash>
# start interactive shell
psql -h localhost -U savapage savapage
-- enter sql statements (autocomplete is active)
postgres=# select * from tbl_printer;
</code>