Part 1
What follows is a simple explanation of how the Postgres Debian/Ubuntu packaging
system works in order to answer recurring questions that are posted on various
forums. This post will focus on the install and initial setup. The next post
will deal with the commands that are added by the the packaging.
The Debian/Ubuntu packaging is designed to allow for multiple concurrent
instances of Postgres to be run on a single machine. Exactly what you can run is
going to depend on the repositories from which you fetch the packages. The
Debian and Ubuntu native repos will be ‘pinned’ to one version of Postgres. So
for the Ubuntu distribution being used here, 22.04, the version is Postgres 14.
For the previous LTS release, 20.04, it would be version 12. In the Debian case
stable(bullseye) is at version 13 and unstable(sid) is at 15. In either of
these cases the packaging will allow the running multiple cluster/instances of
the distribution version. NOTE: Cluster or instance will be used interchangeably
to refer to the same concept, namely the data directory produced by initdb. For
this article a different repository will be used, the PGDG(PostgreSQL Global Development Group) version. Also referred to as the Postgres community repos. The reason is that these repos can install multiple versions of Postgres on a given distribution. The versions available will be those that currently have community support. Since there is a release a year and approximately 5 year support for each release this means there will be generally 5 versions to choose from. At the time of writing (06/2023) that is 11, 12, 13, 14 and 15. For each major version the repo will have the latest minor version. For releases other then the latest supported production releases visit the links under Archive Repo and Development/Beta Repos below. In order to run multiple instances the packaging system utilizes wrapper code that runs the Postgres utilities on a cluster specific basis. How that is done is explained in the following sections. There are snippets of information available on the Web, but nothing I could find that ties it all together. Hence, this attempt.
Archive repo
Announcing archive repo.
Archive repo
This repo will have older not supported releases. This includes releases no longer
supported by community as well a prior point releases of currently supported
releases.
Development/Beta Repos
Install
In order to explore the packaging it needs to be installed. To install from the PGDG
repos use the following commands per instructions from here Install instructions:
1) Get sources list and repo key.
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Above deprecated in Ubuntu 22.04, use below instead.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pg_repo.asc
2) Install Postgresql 15(latest production version at time of writing).
sudo apt-get update
sudo apt-get -y install postgresql-15
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5
libtypes-serialiser-perl postgresql-15 postgresql-client-15
postgresql-client-common postgresql-common ssl-cert
Suggested packages:
postgresql-doc postgresql-doc-15
The following NEW packages will be installed:
libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5
libtypes-serialiser-perl postgresql postgresql-15 postgresql-client-15
postgresql-client-common postgresql-common ssl-cert
0 upgraded, 12 newly installed, 0 to remove and 0 not upgraded.
Need to get 43.4 MB of archives.
...
3) The above will automatically create an active Postgres cluster that can seen with:
pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
Note the port number 5433 assigned to the new cluster as a pre-existing cluster already has the default port of 5432. Cluster designation follows pattern PostgreSQL version number and cluster name. The default name is main. More on this in commands section.
4) File locations:
a) Binaries
ls -al /usr/lib/postgresql/15/bin/
total 12920
drwxr-xr-x 2 root root 4096 Jun 27 09:13 .
drwxr-xr-x 4 root root 4096 Jun 27 09:13 ..
-rwxr-xr-x 1 root root 56048 May 9 10:05 clusterdb
-rwxr-xr-x 1 root root 60376 May 9 10:05 createdb
-rwxr-xr-x 1 root root 56400 May 9 10:05 createuser
-rwxr-xr-x 1 root root 51888 May 9 10:05 dropdb
-rwxr-xr-x 1 root root 51824 May 9 10:05 dropuser
...
b) Data
sudo ls -al /var/lib/postgresql/15/main/
total 92
drwx------ 19 postgres postgres 4096 Jun 27 09:13 .
drwxr-xr-x 3 postgres postgres 4096 Jun 27 09:13 ..
drwx------ 5 postgres postgres 4096 Jun 27 09:13 base
drwx------ 2 postgres postgres 4096 Jun 27 09:14 global
drwx------ 2 postgres postgres 4096 Jun 27 09:13 pg_commit_ts
drwx------ 2 postgres postgres 4096 Jun 27 09:13 pg_dynshmem
drwx------ 4 postgres postgres 4096 Jun 27 09:13 pg_logical
...
c) Log
ls -al /var/log/postgresql/postgresql-15-main.log
-rw-r----- 1 postgres adm 660 Jun 27 09:13 /var/log/postgresql/postgresql-15-main.log
d) Conf
ls -al /etc/postgresql/15/main/
total 68
drwxr-xr-x 3 postgres postgres 4096 Jun 27 09:13 .
drwxr-xr-x 3 postgres postgres 4096 Jun 27 09:13 ..
drwxr-xr-x 2 postgres postgres 4096 Jun 27 09:13 conf.d
-rw-r--r-- 1 postgres postgres 315 Jun 27 09:13 environment
-rw-r--r-- 1 postgres postgres 143 Jun 27 09:13 pg_ctl.conf
-rw-r----- 1 postgres postgres 5002 Jun 27 09:13 pg_hba.conf
-rw-r----- 1 postgres postgres 1636 Jun 27 09:13 pg_ident.conf
-rw-r--r-- 1 postgres postgres 29735 Jun 27 09:13 postgresql.conf
-rw-r--r-- 1 postgres postgres 317 Jun 27 09:13 start.conf
Connecting
Initial connection
At this point the only user that is present in the database cluster is postgres, as setup by the package install. To connect as that user there needs to be a side trip to the pg_hba.conf file where hba stands for Host Based Authentication. It is the configuration file that controls who can access a database from what host with what authentication method. More information can be found at pg_hba.conf.
The pg_hba.conf file is set up by the package install in the location below:
sudo vi /etc/postgresql/15/main/pg_hba.conf
…
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
…
Note the lines:
Database administrative login by Unix domain socket
local all postgres peer
This sets up the postgres user to log in via the local socket using peer authentication.
Where peer authentication uses the logged in OS user name as the database user
name to login in as. The package install creates the OS user postgres as part of the process. This OS user does not have a home directory nor can you directly log in as that user. To operate as that OS user you indirectly log in using sudo. This means the initial log in can be done as:
aklaver@arkansas:~$ sudo -i -u postgres psql -p 5433
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
Type "help" for help.
NOTE: I already have a Postgres instance running on port 5432 so the new 15 instance
was assigned port 5433 on install.
The above uses the OS postgres user to login to the cluster using the psql
client. psql without any further arguments defaults to using the OS user name as
the database name to connect to and the database user name to connect as. Also
since no host was specified the connection will be made to the local socket.
Therefore the pg_hba.conf line shown above will be used as it is the first one
that matches the connection parameters.
The command below will show you the current databases in the cluster.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype |
-----------+----------+----------+-------------+-------------+ ...
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 ...
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 ...
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 ...
(3 rows)
These where created by the initdb done by the install. More information on the template databases can be found here Template database. For now it is sufficient to know template0 is read only and template1 is the default template used to create new databases. The postgres database is there to operate as a default connection database to do further operations on the cluster. These databases should not be used as end user databases. For that new application specific databases should be created.
It is a good idea to set up the postgres user to work with the lines below from pg_hba.conf.
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
To that end run the command below with the strong password of your choice:
postgres=# alter role postgres with password 'pg_test_SimplePWD';
ALTER ROLE
This then allows for logging from a OS user account without going through sudo.
psql -d postgres -h localhost -U postgres -p 5433
Password for user postgres:
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=#
In the above the -d postgres specifies connecting to the postgres database, -h
localhost makes the connection be a host one and -U postgres means connect as
the database user postgres.
It is also a good idea to create a non superuser database user you can also log in as. Example shown below.
postgres=# create role aklaver with login createdb createrole password 'TEST_pwd_Cat';
CREATE ROLE
psql -d postgres -h localhost -U aklaver -p 5433
Password for user aklaver:
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.
postgres=>
It would also not hurt to go over the server configuration file:
sudo vi /etc/postgresql/15/main/postgresql.conf
The above holds the configuration settings for the operation of the cluster. It is out of the scope of this post to go through what they all mean. That information can be found here:
There is another file set up by the Postgres package install and deals with how the
server is started. It can be viewed here:
sudo vi /etc/postgresql/15/main/start.conf
# Automatic startup configuration
# auto: automatically start the cluster
# manual: manual startup with pg_ctlcluster/postgresql@.service only
# disabled: refuse to start cluster
# See pg_createcluster(1) for details. When running from systemd,
# invoke 'systemctl daemon-reload' after editing this file.
auto
The commented(#) section explains what settings are available and what they do.
By default the server startup will be set to auto which means it will start the
server on boot/reboot.
The below deals with some basic commands to manage the server via the command
line. There will be more on this in the next post.
Managing server state.
Using pg-common(installed by package) commands
aklaver@arkansas:~$ sudo pg_ctlcluster stop 15 main
aklaver@arkansas:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15 main 5433 down postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
aklaver@arkansas:~$ sudo pg_ctlcluster 15 main start
aklaver@arkansas:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
NOTE: The action(stop/start) can be before or after the cluster designation.
Other actions are restart, reload, status and promote. More on this in commands
section.
Using systemd.
aklaver@arkansas:~$ sudo systemctl stop postgresql@15-main.service
aklaver@arkansas:~$ sudo systemctl status postgresql@15-main.service
postgresql@15-main.service - PostgreSQL Cluster 15-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; vendor preset: enabled)
Active: inactive (dead) since Tue 2023-06-27 09:36:51 PDT; 1min 10s ago
Process: 3669737 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 15-main start (code=exited, status=0/SUCCESS)
Process: 3669769 ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast 15-main stop (code=exited, status=0/SUCCESS)
Main PID: 3669742 (code=exited, status=0/SUCCESS)
CPU: 215ms
Jun 27 09:35:56 arkansas systemd[1]: Starting PostgreSQL Cluster 15-main…
Jun 27 09:35:58 arkansas systemd[1]: Started PostgreSQL Cluster 15-main.
Jun 27 09:36:51 arkansas systemd[1]: Stopping PostgreSQL Cluster 15-main…
Jun 27 09:36:51 arkansas systemd[1]: postgresql@15-main.service: Deactivated successfully.
Jun 27 09:36:51 arkansas systemd[1]: Stopped PostgreSQL Cluster 15-main.
aklaver@arkansas:~$ sudo systemctl start postgresql@15-main.service
aklaver@arkansas:~$ sudo systemctl status postgresql@15-main.service
postgresql@15-main.service - PostgreSQL Cluster 15-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; vendor preset: enabled)
Active: active (running) since Tue 2023-06-27 09:38:42 PDT; 5s ago
Process: 3669790 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 15-main start (code=exited, status=0/SUCCESS)
Main PID: 3669795 (postgres)
Tasks: 6 (limit: 4676)
Memory: 18.6M
CPU: 139ms
CGroup: /system.slice/system-postgresql.slice/postgresql@15-main.service
├─3669795 /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/main -c config_file=/etc/postgresql/15/main/postgresql.conf
├─3669796 "postgres: 15/main: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" """"" "" "" "" "" ">
├─3669797 "postgres: 15/main: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "">
├─3669799 "postgres: 15/main: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ">
├─3669800 "postgres: 15/main: autovacuum launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" >
└─3669801 "postgres: 15/main: logical replication launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" >
Jun 27 09:38:39 arkansas systemd[1]: Starting PostgreSQL Cluster 15-main…
Jun 27 09:38:42 arkansas systemd[1]: Started PostgreSQL Cluster 15-main.
In either case pg_lsclusters will also show you the correct state.
aklaver@arkansas:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log