PostgreSQL configuration

Server setup
← Previous	Next →
MySQL config	Web server

If Posgtres isn't already installed, install it:

root@server:~# aptitude install postgresql postgresql-client

On the desktop, install pgadmin4 as well

root@server:~# curl https://www.pgadmin.org/static/packages_pgadmin_org.pub > packages_pgadmin_org.pub
root@server:~# gpg --no-default-keyring --keyring ./temp-keyring.gpg --import packages_pgadmin_org.pub
root@server:~# gpg --no-default-keyring --keyring ./temp-keyring.gpg --export --output packages_pgadmin_org.gpg
root@server:~# cp packages_pgadmin_org.gpg /usr/share/keyrings/
root@server:~# echo "deb [signed-by=/usr/share/keyrings/packages_pgadmin_org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main"
root@server:~# aptitude update && aptitude install pgadmin4

(Following this post for GPG keys.)

Passwords

Connect to the Postres server:

root@server:~# sudo -u postgres psql postgres

Set a password for the "postgres" database role using the command:

postgres=# \password postgres

Ctrl-D to exit

Remote access

Edit /etc/postgresql/9.1/main/pg_hba.conf and modify the default configuration which is very protective. For example, if you want postgres to manage its own users (not linked with system users), you will add the following lines:

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
# Allow connections from across the LAN
host    all         all         192.168.1.0       255.255.255.0     md5
# Allow any connection from this machine
local   all         all                                             md5
host    all         all         127.0.0.1/32                        md5
# IPv6 local connections:                                                                                                                         
host    all             all             ::1/128                     md5                                                                               
host    all             all             aaaa:bbbb:cccc:dddd::/64    md5

Edit /etc/postgresql/9.1/main/postgresql.conf to include

listen_addresses = '192.168.1.251,localhost'

Note that the firewall is configured to only allow connections to PostgreSQL from machines on the LAN.

Updating

It's perfectly possible to have several instances of PostreSQL running at different versions, such as 9.5 and 10.1.

To migrate the data and remove the old version, follow these steps:

Use `pg_lsclusters` to see what's running.

root@desktop:~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
10  main    5433 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

Check what's in what cluster

root@server::# su - postgres 
postgres@server:~$ psql --cluster 9.5/main 
postgres=# select datname from pg_database;
  datname  
-----------
 template1
 template0
 postgres
 ...
 ...
(5 rows)
postgres=# \q

(where `...` indicates some more databases in the cluster)

postgres@server:~$ psql --cluster 10/main  
psql (10.5 (Ubuntu 10.5-0ubuntu0.18.04))
postgres=# select datname from pg_database;
  datname  
-----------
 postgres
 template1
 template0
(3 rows)

this indicates that there's nothing currently in the version 10 cluster.

Stop Posgres

root@server:~# systemctl stop postgresql

Destroy the existing empty cluster in the version 10 cluster

root@server:~# pg_dropcluster --stop 10 main

Move the existing data to version 10

root@server:~# pg_upgradecluster -m upgrade 9.5 main

Drop the 9.5 cluster

root@server:~# pg_dropcluster --stop 9.5 main

Use `aptitude` to purge all the postgresql-9.5 packages.

Restart Postgres

root@server:~# systemctl start postgresql

Passwords

Remote access

Updating

See also