Many organizations use PostgreSQL as their primary database server to power all business operations. PostgreSQL database server development has been active for over 35 years. This proofs PostgreSQL is mature, reliable and stable relational database management system. PostgreSQL has a good reputation for its performance, reliability, data integrity, and high level of extensibility. In this article we provide the steps used to install, configure, and use PostgreSQL 16 on Debian 12/11/10 Linux system.

At the time of publishing this article, the latest stable release of PostgreSQL available is version 16. This was released to the general public on 14th September 2023 as a successor to PostgreSQL 15. Some of the top features available in PostgreSQL 16 are:

  • Privilege admin: In version 16 there was an overhaul of privilege administration. It now allows users to grant privileges in roles only if they possess the ADMIN OPTION for those roles.
  • Performance Improvements: There are enhancements in query execution to allow for parallel execution of FULL and RIGHT JOINs, as well as the string_agg and array_agg aggregate functions.
  • Enhancements in Logical replication: Logical replication from physical replication standbys, replication without a primary key, initial table synchronization in binary format, e.t.c.
  • Monitoring enhancements: The pg_stat_io view was introduced in PostgreSQL 16 to allow for better insight into I/O activity of the Postgres system.
  • Better Access Control & Security: Finer-grained options for access control and enhances other security features.
  • And many more great features

Installing PostgreSQL 16 on Debian 12/11/10

Follow the steps in the next sections to install and configure PostgreSQL 16 on Debian Linux system.

1) Update your system

Let’s make sure our system is updated.

sudo apt update && sudo apt -y full-upgrade

If a reboot is required perform it.

[ -f /var/run/reboot-required ] && sudo reboot -f

2) Add PostgreSQL APT repository

Next we configure PostgreSQL repository on our Debian system. It contains all PostgreSQL 16 packages.

Install dependency packages that we’ll use.

sudo apt install vim gnupg2 -y

Import repository GPG key.

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc|sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

Run the following commands to configure PostgreSQL repository:

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Update repository package index.

sudo apt update

3) Install PostgreSQL 16 packages

Now that the repository is configured, let’s proceed to install the packages.

sudo apt install postgresql-16

Accept installation prompts.

Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libgdbm-compat4 libjson-perl libllvm14 libperl5.36 libpq5 libxslt1.1 libz3-4 perl perl-modules-5.36 postgresql-client-16 postgresql-client-common postgresql-common ssl-cert
Suggested packages:
  perl-doc libterm-readline-gnu-perl | libterm-readline-perl-perl make libtap-harness-archive-perl postgresql-doc-16
Recommended packages:
  libjson-xs-perl sysstat
The following NEW packages will be installed:
  libgdbm-compat4 libjson-perl libllvm14 libperl5.36 libpq5 libxslt1.1 libz3-4 perl perl-modules-5.36 postgresql-16 postgresql-client-16 postgresql-client-common postgresql-common ssl-cert
0 upgraded, 14 newly installed, 0 to remove and 0 not upgraded.
Need to get 56.8 MB of archives.
After this operation, 252 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

After installation start the service and set to come up at system boot.

sudo systemctl start [email protected] && sudo systemctl enable [email protected]

Confirm status of postgresql service.

$ systemctl status [email protected]
● [email protected] - PostgreSQL Cluster 16-main
     Loaded: loaded (/lib/systemd/system/[email protected]; enabled; preset: enabled)
     Active: active (running) since Thu 2023-11-23 13:00:47 UTC; 12s ago
   Main PID: 4605 (postgres)
      Tasks: 6 (limit: 4531)
     Memory: 18.3M
        CPU: 298ms
     CGroup: /system.slice/system-postgresql.slice/[email protected]
             ├─4605 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf
             ├─4606 "postgres: 16/main: checkpointer "
             ├─4607 "postgres: 16/main: background writer "
             ├─4609 "postgres: 16/main: walwriter "
             ├─4610 "postgres: 16/main: autovacuum launcher "
             └─4611 "postgres: 16/main: logical replication launcher "

Nov 23 13:00:45 deb12 systemd[1]: Starting [email protected] - PostgreSQL Cluster 16-main...
Nov 23 13:00:47 deb12 systemd[1]: Started [email protected] - PostgreSQL Cluster 16-main.

You can check installed PostgreSQL version:

$ psql --version
psql (PostgreSQL) 16.1 (Debian 16.1-1.pgdg120+1)

Or with PostgreSQL SELECT statement.

$ sudo -u postgres psql -c "SELECT version();"
                                                       version
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.1 (Debian 16.1-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

4). Configure Authentication method

PostgreSQL has support for several authentication methods. The default method is ident authentication which associates UNIX user with the Postgres role.

Other authentication methods available are:

  • Password authentication – Uses a password to connects to PostgreSQL database.
  • Trust authentication – Authorize access based the conditions defined in the pg_hba.conf file.
  • Peer authentication – Similar to ident but only supported on local connections.

To configure database server for remote connections edit below file.

sudo vim /etc/postgresql/16/main/postgresql.conf

Locate and modify the the following lines.

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all

Allow password authentication using the command below.

sudo sed -i '/^host/s/ident/md5/' /etc/postgresql/16/main/pg_hba.conf

Next we change identification method from peer to trust with the command:

sudo sed -i '/^local/s/peer/trust/' /etc/postgresql/16/main/pg_hba.conf

Finally, allow PostgreSQL to be accessed remotely by editing the below lines:

$ sudo vim /etc/postgresql/16/main/pg_hba.conf
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0           scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0                md5

Restart the database server after making the changes.

sudo systemctl restart postgresql

If you have UFW firewall active, accept incoming connections.

sudo ufw allow 5432/tcp

5) Connect to PostgreSQL 16 server

You can connect to the PostgreSQL database server directly:

$ sudo -u postgres psql
psql (16.1 (Debian 16.1-1.pgdg120+1))
Type "help" for help.

postgres=#

For remote connections use psql command with the following command options.

psql 'postgres://<username>:<password>@<host>:<port>/<db>?sslmode=disable'

See below example.

psql 'postgres://mydbuser@172.20.40.5:5432/mydb?sslmode=disable'

6) Changing superuser password

Access PostgreSQL shell.

sudo -i -u postgres
psql

Let’s secure postgres user by updating its password.

ALTER USER postgres PASSWORD 'Str0ngDBPassw0rd';

Replace the password with your own desired password string and save the file.

postgres=# \q

Now test if the password is working:

$ psql -h localhost -U postgres
Password for user postgres: Enter_set_password
psql (16.1 (Debian 16.1-1.pgdg120+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=#

Our guide only demonstrated how you can install and configure PostgreSQL 16 on Debian Linux system. For more administrative and usage guides of PostgreSQL visit the official documentation pages.

RELATED ARTICLESMORE FROM AUTHOR

2 COMMENTS

LEAVE A REPLY

Please enter your comment!
Please enter your name here