GAMBAS BOOK 3.19.3

If you want to work with PostgreSQL databases on your system, you will need the PostgreSQL database server.

You should observe the following notes and suggestions:

• This description describes the installation, configuration and a suggestion for an initial test of the PostgreSQL database server for Linux Mint 20.1.
• Version 12 of the PostgreSQL database server is used (as of 07/08/2022).
• Reference is only made to the documentation for the PostgreSQL database server.

This allows you to determine which PostgreSQL packages are already installed on your system:

$ dpkg --list | grep postgres
ii  gambas3-gb-db-postgresql 3.16.0+git20210419...~ubuntu20.04.1 amd64
PostgreSQL driver for the Gambas database

The PostgreSQL database server can be installed via the application management. Or you can enter the following lines in a terminal, whereby the last package adds additional features and functionalities (option). The PostgreSQL console client psql is also installed automatically.

$ sudo apt-get update
$ sudo apt-get upgrade
$ sudo apt-get install postgresql postgresql-contrib

The installed version of the PostgreSQL database server is displayed as follows

$ pg_config | grep 'VERSION'
VERSION = PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)

You should then also install the current documentation (here for version 12) of the database server:

$ sudo apt-get install postgresql-doc postgresql-doc-12

During the installation of the PostgreSQL database server, the database user `postgres` is automatically created as a super user.

By default, password authentication for the PostgreSQL administrator `postgres` is deactivated after installation. The easiest way to complete the account for the DB user `postgres` with a password is via the PostgreSQL client programme ‘psql’:

hans@mint20:~$ sudo -u postgres psql postgres
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# \password postgres
Enter new password: 		→ The freely selected password ‘appw’ is entered hidden and without an echo
Enter it again:     		→ Repeat the password
postgres=# \q
hans@mint20:~$

Now you can start the client programme ‘psql’ with the administrator account (DB user: postgres, DB user password: appw) on 127.0.0.1 or localhost on port 5432 and display the properties of the DB administrator postgres with the command \dg or \dg+:

hans@mint20:~$ psql -h localhost -p 5432 -U postgres -W
Password:
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
SSL connection (protocol:TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# \dg
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \q
hans@mint20:~$

Attention: Use the database user `postgres` exclusively for the administration of the PostgreSQL database server!

To determine the status of the PostgreSQL database server, as the PostgreSQL database server is started immediately after installation:

$ service postgresql status

Attention! The PostgreSQL database server is started automatically every time the system is started (default). You can change this with these two commands:

$ sudo systemctl disable postgresql 	-- PostgreSQL aus der Autostart-Liste entfernen
$ sudo systemctl enable postgresql 	-- PostgreSQL der Autostart-Liste wieder hinzufügen

If the PostgreSQL database server is not started at system startup, you can start it yourself with ‘sudo service postgresql start’, for example.

Alternatively, you can determine the current version of the PostgreSQL database server:

$ psql -V
psql (PostgreSQL) 12.11 (Ubuntu 12.11-0ubuntu0.20.04.1)

Comprehensive documentation on the PostgreSQL database server can be found at

LINK1:	 https://www.postgresql.org/docs/12/index.html

Help is also available locally for version 12:

LINK2:	file:///usr/share/doc/postgresql-doc-12/html/index.html

You can view the last entries of the current log file of the PostgreSQL database server with the following command in a console:

$ sudo tail -f /var/log/postgresql/postgresql-12-main.log

You can start, stop and restart a PostgreSQL database server, re-read the configuration file and query the status. The following calls in a terminal control the PostgreSQL database server with the parameters shown in the list:

$ sudo /etc/init.d/postgresql {start|stop|restart|reload|force-reload|status}

Alternatively:

$ sudo service postgresql {start|stop|restart|reload|force-reload|status}

Examples:

$ /etc/init.d/postgresql status 			→ Only the status query does not require increased rights
$ systemctl status postgresql.service

$ sudo /etc/init.d/postgresql reload
[sudo] Passwort für hans:
Reloading postgresql configuration (via systemctl): postgresql.service.

If you want to access the PostgreSQL server from remote hosts in the network, the following requirements must be met:

• The package postgresql-client is installed on the remote computers and you can use the PostgreSQL client `psql` if this should be necessary.
• The PostgreSQL server is configured in the configuration file /etc/postgresql/12/main/postgresql.conf for connection requests in the (local) network.
• In addition, the configuration file /etc/postgresql/12/main/pg_hba.conf defines which hosts (host names) from which networks (IP ranges) are granted access to selected PostgreSQL databases. The suffix ‘hba’ stands for ‘host based access’.

The (console) client `psql` is quickly installed:

$ sudo apt update
$ sudo apt install postgresql-client

You can use the following command to check which IP addresses the PostgreSQL server is currently listening on:

$ ss -nlt
State      Recv-Q   Send-Q          Local Address:Port       Peer Address:Port         Process
...
LISTEN     0        244             127.0.0.1:5432           0.0.0.0:*
...
$

This is correct because, by default, only a PostgreSQL client started on the same computer as the PostgreSQL server can connect to it locally.

Change with elevated rights in the configuration file /etc/postgresql/12/main/postgresql.conf

$ sudo xed /etc/postgresql/12/main/postgresql.conf

only the original entry of

listen_addresses = 'localhost'

to

listen_addresses = '*'

to ensure that the PostgreSQL server is now listening on all IP addresses - depending on the entries in the configuration file /etc/postgresql/12/main/pg_hba.conf. After saving the new configuration, close the editor and start the PostgreSQL server with

$ sudo systemctl restart postgresql

Now you can check again which network interfaces the PostgreSQL server is listening on:

$ ss -nlt
State          Recv-Q   Send-Q    Local Address:Port   Peer Address:Port   Process
LISTEN         0        244       0.0.0.0:5432         0.0.0.0:*
LISTEN         0        244       [::]:5432            [::]:*
...
hans@pc-a-mint20:~$

Change the configuration file ‘pg_hba.conf’ so that remote clients can establish a connection to the database server.

• Save the original configuration file /etc/postgresql/12/main/pg_hba.conf.
• Stop the PostgreSQL server if it has already been started.
• Open the file ‘pg_hba.conf’ in a text editor of your choice.
• Add the required access rights as follows: databases, DB users, IP ranges, authentication method.

Save the original configuration file:

$ sudo cp /etc/postgresql/12/main/pg_hba.conf /etc/postgresql/12/main/pg_hba.conf.old
$ sudo /etc/init.d/postgresql stop
$ sudo xed /etc/postgresql/12/main/pg_hba.conf

Suggestion for a test environment:In the configuration file /etc/postgresql/12/main/pg_hba.conf, add the following two lines above the other entries to enable access to all databases from all hosts for all DB users (with a set password):

# TYPE  DATABASE  USER  CIDR-ADDRESS  METHOD
  host  all       all   0.0.0.0/0     md5

However, you can also assign more restrictive access rights:

# TYPE  DATABASE  USER  CIDR-ADDRESS      METHOD
  host  test      all   192.168.100.0/24  md5

The following restrictions now apply: All DB users may access the ‘test’ database from all remote clients whose IP address begins with 192.168.100.

Save and close the configuration file /etc/postgresql/12/main/pg_hba.conf. Start the PostgreSQL server again:

$ sudo /etc/init.d/postgresql restart

The exercise - executed in a console with the PostgreSQL monitor `psql` (client) - consists of repeating important commands once again, using

• first determine the status of the PostgreSQL database server,
• then start the server (on the default port 5432) if necessary,
• then start the PostgreSQL client psql with the account (postgres, appw),
• then call up the help with help and ask, for example, how to create a new database and
• finally close the PostgreSQL client with the command \q or CTRL+D. As of PostgreSQL version 11, you can also use exit or quit.

$  service postgresql status
$  sudo service postgresql start
$  psql -h localhost -p 5432 -U postgres -W
postgres=# help
postgres-# \h CREATE DATABASE
postgres-# \q
$

A programme for the administration of PostgreSQL databases is pgAdmin3, whose graphical user interface facilitates the administration and development of databases, DB tables and the creation of DB users.

You can install the pgAdmin3 programme via the application administration. Alternatively, enter the following line in a terminal:

$ sudo apt install pgadmin3

Please note, however, that pgAdmin3 is currently (as of 8 November 2021) considered obsolete and no longer works correctly with a new version of PostgresQL (version ≥ 12)!

On the page https://technium.ch/ubuntu-2104-pgadmin4-installieren-tutorial/ you will find instructions on how to install the programme pgAdmin4. As a very good replacement for pgAdmin3/4, you can install the DBeaver programme via a PPA and automatically keep it up to date:

$ sudo add-apt-repository ppa:serge-rider/dbeaver-ce
$ sudo apt-get update
$ sudo apt-get install dbeaver-ce

After installation, you can manage your PostgreSQL databases as well as the databases, database tables and database users for other well-known database types such as SQLite or MySQL/MariaDB quickly and reliably in one programme:

Figure 22.16.0.10.1: Managing connections to many DBMSs

You will appreciate the graphical representation of existing relations in the DBeaver programme, especially for databases with multiple DB tables:

Figure 22.16.0.10.2: ER diagram of an SQLite database (entity relationships)

In the event of a de-installation of the database server, it is always a good idea to create a complete backup of the structure and data of the previously used databases or only of the structure (schema) of selected databases:

$ pg_dump --host=localhost --port=5432 --username=test --inserts --dbname=test --file=test.sql
$ pg_dump --host=localhost --port=5432 --username=test --schema-only --dbname=text --file=test.schema.sql

With the command dpkg for the administration of Debian packages you can search for all installed package names that contain the substring ‘postgres’.

hans@pc-a-mint20:~$ dpkg -l | grep postgres
ii  gambas3-gb-db-postgresql  3.17.3+git202..ubuntu20.04.1 amd64 PostgreSQL driver for the Gambas database
ii  odbc-postgresql:amd64     1:12.01.0000-1               amd64 ODBC driver for PostgreSQL
ii  postgresql                12+214ubuntu0.1              all   object-relational SQL database (supported version)
ii  postgresql-12             12.11-0ubuntu0.20.04.1       amd64 object-relational SQL database, version 12 server
ii  postgresql-client-12      12.11-0ubuntu0.20.04.1       amd64 front-end programs for PostgreSQL 12
ii  postgresql-client-common  214ubuntu0.1                 all   manager for multiple PostgreSQL client versions
ii  postgresql-common         214ubuntu0.1                 all   PostgreSQL database-cluster manager
ii  postgresql-contrib        12+214ubuntu0.1              all   additional facilities for PostgreSQL (supported version)
ii  postgresql-doc            12+214ubuntu0.1              all   documentation for the PostgreSQL database management system
ii  postgresql-doc-12         12.11-0ubuntu0.20.04.1       all   documentation for the PostgreSQL database management system
hans@pc-a-mint20:~$

If you want to get rid of PostgreSQL - here in version 12 - then uninstall either via the Synaptic package management or in a console with the following commands:

sudo apt-get --purge remove {POSTGRES-PACKAGE-NAME}
---------------------------------------------------

sudo apt-get --purge remove  postgresql
sudo apt-get --purge remove  postgresql-12
sudo apt-get --purge remove  postgresql-client-12
...
sudo apt-get --purge remove  postgresql-doc-12

Finally, you can delete the following (data) directories:

$ sudo rm -rf /var/lib/postgresql/
$ sudo rm -rf /var/log/postgresql/
$ sudo rm -rf /etc/postgresql/