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
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.
Server:
Workstation:
https://www.postgresql.org/docs
https://www.postgresqltutorial.com
PostgreSQL 14 Administration Cookbook by Simon Riggs & Gianni Ciolli
Learn PostgreSQL by Luca Ferrari & Enrico Pirozzi
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.
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.
Debian postgresql package installs postgresql-client by default
Update repositories
sudo apt update
Install necessary packages
Debian 12 installs Postgresql version 15
sudo apt install --yes postgresql
Check status
systemctl status postgresql
Debian allows running more than 1 clusters (instances) on a server.
See the list of clusters on a server:
pg_lsclusters
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
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:
pg_lsclusters
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.
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.
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
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.
cd /etc/postgresql/15/main/
sudo cp postgresql.conf postgresql.conf.backup
sudo cp pg_hba.conf pg_hba.conf.backup
Give their passwords too
sudo -u postgres createuser --pwprompt rwuser
sudo -u postgres createuser --pwprompt rouser
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');
GRANT ALL ON ALL TABLES IN SCHEMA public to rwuser;
GRANT SELECT ON ALL TABLES IN SCHEMA public to rouser;
exit
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 192.168.1.181/32 scram-sha-256
host test1 rouser 192.168.1.0/24 scram-sha-256
Restart our cluster
sudo pg_ctlcluster restart 15 main
!! 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 192.168.1.196 -U rwuser test1
Run on psql shell
INSERT INTO Employees VALUES ('John Doe', '33', 'Kedi');
exit
Connect with rouser and test reading and adding data
(reading test must be successfull, adding test must fail)
psql -h 192.168.1.196 -U rouser test1
Run on psql shell
SELECT * from Employees;
INSERT INTO Employees VALUES ('Halim Selim', '41', 'Hirsiz');
exit
If you try to use psql from another workstation in 192.168.1.0/24 network, you will see that rwuser cannot connect and rouser can connect.
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.
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.
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
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
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
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.
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
You can run SQL commands at psql shell. You can also run psql commands, some of which are very useful.
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.
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'
sudo apt update
sudo apt install postgresql-16
pg_lsclusters
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/..
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
Upgrade 15 secondary cluster to Postgresql 16
sudo pg_upgradecluster 15 secondary -v 16