x386.org: Debian and Ubuntu Documentation

|  Home  |  About  |  Contact  |  License  |  Privacy Policy  |

Copyright (C) 2020 - 2024 Exforge exforge@x386.org

This document is free text: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or any later version.

This document is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program. If not, see .


Postgresql Tutorial On Debian

0. Specs

0.0. Notes

Debian 12 and Ubuntu 24.04 LTS Server has packages for different versions of Postgresql (15 and 16).

For the ease of following the tutorials, I prepared different versions for Debian and Ubuntu.

0.1. Infrastructure



0.2. Resources

PostgreSQL 14 Administration Cookbook by Simon Riggs & Gianni Ciolli
Learn PostgreSQL by Luca Ferrari & Enrico Pirozzi

1. Introduction

1.1. Terminology:

Cluster: A PostgreSQL Instance. Can contain many databases

Database: Accessed by allowed users. Can contain schemas (namespaces)

Schema: Used for organizing database objects. Can contain database objects.

Database Objects: Tables, functions, triggers, data types etc.

1.2. Basic Information

2 types of users: Normal and Superuser

All the data and configuration information is kept in PGDATA directory.

PostgreSQL supports information schema, but has catalog which is more detailed.

Postmaster: The first process, responsible for all the executions.

WAL (Write Ahead Logs): Database change log, mainly used for recovering.

1.3. Software Components:

Debian postgresql package installs postgresql-client by default

2. Installation and Basic Management

2.1. Installation

Update repositories

sudo apt update

Install necessary packages

Debian 12 installs Postgresql version 15

sudo apt install --yes postgresql

Check status

systemctl status postgresql

2.2. Cluster Management

Debian allows running more than 1 clusters (instances) on a server.

See the list of clusters on a server:


Sample output:

Ver Cluster Port Status Owner    Data directory              Log file
15  main    5432 online postgres /var/lib/postgresql/15/main /var/log/...

Values of Ver and Cluster is important for us. We'll use them for pg_ctlcluster command. Ours are 15 and main.

pg_ctlcluster is a wrapper command for the original pg_ctl command of postgres.

See the status of a cluster:

sudo pg_ctlcluster 15 main status

Start a cluster

sudo pg_ctlcluster 15 main start

Stop a cluster

sudo pg_ctlcluster 15 main stop

There are 3 modes of stop: smart (wait for connections to stop), fast (stop all connections), immediate (immediately). immediate option may cause database to crash. Default mode is fast.

sudo pg_ctlcluster 15 main stop -m smart
sudo pg_ctlcluster 15 main stop -m fast
sudo pg_ctlcluster 15 main stop -m immediate

Restart, reload a cluster

sudo pg_ctlcluster 15 main restart
sudo pg_ctlcluster 15 main reload

2.3. Adding and Deleting Clusters

There might be more than 1 clusters on a server. At the first sight it may not make sense, but for example if you need of 2 different admins for 2 different databases this solution could be very useful.

Currently we only have main cluster. We will add a second one with the name second.

Create another Postgres 15 cluster with the name second

sudo pg_createcluster 15 second

Start it

sudo pg_ctlcluster 15 second start

Create another Postgres 15 cluster with the name third and start it

sudo pg_createcluster 15 third --start

Delete (drop) third cluster

sudo pg_dropcluster 15 third --stop

Rename second cluster to secondary

sudo pg_renamecluster 15 second secondary

List clusters:


Sample output:

Ver Cluster   Port Status Owner    Data directory                   Log file
15  main      5432 online postgres /var/lib/postgresql/15/main      /var/log/..
15  secondary 5433 online postgres /var/lib/postgresql/15/secondary /var/log/..

Here we can understand that, our 15 main cluster listens on port 5432 (default postgres listening port), and 15 secondary cluster listens on port 5433.

Directory of configuration files for both clusters:
/var/lib/postgresql/14/main/ and /var/lib/postgresql/14/secondary.

2.4. Service vs Cluster Management

Postgres and its clusters can be managed by systemctl command too. Stop postgres (all the clusters)

sudo systemctl stop postgresql

Stop 15-main postgres cluster

sudo systemctl stop postgresql@15-main

Other systemctl options (like restart, stop, enable, disable, reload) can be used too.

2.5. Login to Postgres shell

With the default installation; postgres linux user can login to psql shell without the need of password authentication.

sudo -u postgres psql

Type exit to quit from postgres shell

As you may guess, you logged in to 15 Main cluster. To login 15 secondary cluster:

sudo -u postgres psql -p 5433

3. User and Connection Management

After installing Postgres, postgres user is able to login psql shell with Linux authentication. No other users are defined and noone can login remotely.

We will implement a scenario for user management.

3.0. Backup Configuration Files

cd /etc/postgresql/15/main/
sudo cp postgresql.conf postgresql.conf.backup
sudo cp pg_hba.conf pg_hba.conf.backup

3.1. Scenario

3.2. Create users

Give their passwords too

sudo -u postgres createuser --pwprompt rwuser
sudo -u postgres createuser --pwprompt rouser

3.3. Create test1 database and give R/W and R/O permissions.

Create database

sudo -u postgres createdb test1

Create a sample table and fill it with sample data.

Run psql to connect test1 database

sudo -u postgres psql test1

Run on psql shell

CREATE TABLE Employees (Name char(15), Age int, Occupation char(15));
INSERT INTO Employees VALUES ('Joe Smith', '26', 'Ninja');

3.4. Configure Postgres to allow remote connections

Edit postgres.conf file to allow network connections

sudo nano /etc/postgresql/15/main/postgresql.conf

Uncomment and change the line below (around line 60)

#listen_addresses = 'localhost'         # what IP address(es) to listen on;

as below

listen_addresses = '*'                  # what IP address(es) to listen on;

Edit pg_hba.conf file to allow rwuser and rouser to allow connections from specified ip/networks.

sudo nano /etc/postgresql/15/main/pg_hba.conf

Add following lines to the file

host    test1           rwuser        scram-sha-256
host    test1           rouser          scram-sha-256

Restart our cluster

sudo pg_ctlcluster restart 15 main

3.5. Connection test from Workstation (

!! Run on workstation !!

Install Postgres Client to the workstation

sudo apt update
sudo apt install postgresql-client --yes    

Connect with rwuser and test adding data (test must be successfull)

psql -h -U rwuser test1

Run on psql shell

INSERT INTO Employees VALUES ('John Doe', '33', 'Kedi');

Connect with rouser and test reading and adding data
(reading test must be successfull, adding test must fail)

psql -h -U rouser test1

Run on psql shell

SELECT * from Employees;
INSERT INTO Employees VALUES ('Halim Selim', '41', 'Hirsiz');

If you try to use psql from another workstation in network, you will see that rwuser cannot connect and rouser can connect.

4. Backup and Restore

You can backup a database or a whole cluster. When backing up a database, users (roles) and any other clusterwide data is not backed up.

So if you backup a database and restore it on another cluster, you have to create users and (if necessary) access permissions there too.

4.0. Considerations

We need to use postgres user for backup and restore. When we sudo for this user; we need to change the directory to /tmp, because postgres user does not have permissions on our home directory.

4.1. Backup a database

Backing up a database is performed with pg_dump command:

pg_dump dbname > dumpfile

pg_dump command's connection parameters are like psql command's.

Move to /tmp directory

cd /tmp

Backup test1 database on 15 main cluster to test1.pg file:

sudo -u postgres pg_dump test1 > /tmp/test1.pg

Backup postgres database on 15 secondary cluster to sdb.pg:
We need to specify the port of 15 secondary cluster

sudo -u postgres pg_dump -p 5433 postgres > /tmp/sdb.pg

4.2. Restore a database

You can restore a database dump with psql command:

psql dbname < dumpfile

Restore test1 database back on 15 main cluster

sudo -u postgres psql test1 < /tmp/test1.pg

Lets restore test1 db to secondary cluster:
We need an empty test1 database.

Create test1 database on 15 secondary

sudo -u postgres createdb -p 5433 test1

Create users rwuser and rouser on secondary cluster.
createuser command's connection parameter are like psql command's too. So

we need to specify port number 5433 for secondary cluster.

sudo -u postgres createuser -p 5433 --pwprompt rwuser
sudo -u postgres createuser -p 5433 --pwprompt rouser

Import the database

sudo -u postgres psql -p 5433 test1 < /tmp/test1.pg

4.3. Backup and Restore Whole Cluster

When we backup a cluster, all clusterwide data including users and access rights are backed up too. But remember, you have to change the configuration files by yourself.

Cluster dump is made by pg_dumpall command. This command too has the same connection parameters as psql command.

Backup 15 main cluster to main.pg file

sudo -u postgres pg_dumpall > /tmp/main.pg

Restore main.pg file to 15 secondary cluster

sudo -u postgres psql -p 5433 -f /tmp/main.pg

5. psql - PostgreSQL Shell

5.1. The Command

psql command is used to open a Postgres shell. At the fresh install, only postgres Linux user has the right to connect to Postgres shell. So we need to run it by impersonating postgres user:

sudo -u postgres psql

If we want another Linux user to login to psql shell, we have to create them using createuser command and add connection permission to them at pg_hba.conf configuration file. I don't prefer that method. In my very humble opinion, 1 database admin is enough.

5.2. The command arguments

psql command has a lot of arguments. If no arguments are given; it tries to connect with the current Linux user name as the user name and again current Linux user name as the database name. My Linux user name is exforge, so when I run psql command, I get the following error message.

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432"
failed: FATAL:  role "exforge" does not exist

If I go beyond and create exforge user (role actually, user=role in postgres) only I have a different error message:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "exforge" does not exist

If I go even beyond and create a database named exforge then everything goes fine.

Well, of course instead of creating a database with my user name, I can connect to psql with specifying an existing database.

psql postgres

As we have seen before we can specify port number with -p switch, host name with -h switch. We can also specify postgres user with -U switch.

Full list of arguments can be seen with --help switch

psql --help

5.3. psql Commands

You can run SQL commands at psql shell. You can also run psql commands, some of which are very useful.

6. Bonus: Postgres 15 and Postgres 16 together

For testing purposes we will install Postgresql 14 on the same server.

That way we will have different postgres clusters with different versions.

Debian 12 has Postgres 15 in its repositories. For Postgres 16 we need to add Postgres repositories.

6.1. Add Postgresql Repositories

We are going to need, gpg and curl. Let's install them:

sudo apt install gpg curl --yes

Add keys

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

Add repository

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

6.2. Install Postgresql 16

sudo apt update
sudo apt install postgresql-16

6.3. List clusters:


Output will be like below, now we have 3 clusters namely 14-main, 15-main, and 15-secondary running on the same computer:

Ver Cluster   Port Status Owner    Data directory                   Log file
15  main      5432 online postgres /var/lib/postgresql/15/main      /var/..
15  secondary 5433 online postgres /var/lib/postgresql/15/secondary /var/..
16  main      5434 online postgres /var/lib/postgresql/16/main      /var/..

6.4. Connecting to the clusters with psql

Connect to the first cluster (15-main), remember it runs on port 5432

sudo -u postgres psql -p 5432

Connect to the second cluster (15-secondary), remember it runs on port 5433

sudo -u postgres psql -p 5433

Connect to the third cluster (16-main), remember it runs on port 5434

sudo -u postgres psql -p 5434

6.5. Upgrade Cluster

Upgrade 15 secondary cluster to Postgresql 16

sudo pg_upgradecluster 15 secondary -v 16