{"id":465,"date":"2023-06-29T14:15:14","date_gmt":"2023-06-29T21:15:14","guid":{"rendered":"https:\/\/aklaver.org\/wordpress\/?p=465"},"modified":"2023-06-29T14:24:28","modified_gmt":"2023-06-29T21:24:28","slug":"postgres-debian-ubuntu-packagingpart-1","status":"publish","type":"post","link":"https:\/\/aklaver.org\/wordpress\/2023\/06\/29\/postgres-debian-ubuntu-packagingpart-1\/","title":{"rendered":"Postgres Debian\/Ubuntu Packaging"},"content":{"rendered":"\n

Part 1<\/strong><\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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<\/strong>: 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.<\/p>\n\n\n\n

Archive repo<\/p>\n\n\n\n

Announcing archive repo.<\/a>
Archive repo<\/a><\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Development\/Beta Repos<\/p>\n\n\n\n

Beta version <\/a><\/p>\n\n\n\n

Development snapshots<\/a><\/p>\n\n\n\n

Install<\/strong><\/p>\n\n\n\n

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<\/a>:<\/p>\n\n\n\n

1) Get sources list and repo key.<\/p>\n\n\n

\nsudo sh -c 'echo "deb http:\/\/apt.postgresql.org\/pub\/repos\/apt $(lsb_release -cs)-pgdg main" > \/etc\/apt\/sources.list.d\/pgdg.list'\n\nwget --quiet -O - https:\/\/www.postgresql.org\/media\/keys\/ACCC4CF8.asc | sudo apt-key add -\n\n# Above deprecated in Ubuntu 22.04, use below instead.\nwget --quiet -O - https:\/\/www.postgresql.org\/media\/keys\/ACCC4CF8.asc | sudo tee \/etc\/apt\/trusted.gpg.d\/pg_repo.asc\n<\/pre><\/div>\n\n\n
2) Install Postgresql 15(latest production version at time of writing).<\/p>\n\n\n
\nsudo apt-get update\n\nsudo apt-get -y install postgresql-15\n\n\nReading package lists... Done\nBuilding dependency tree... Done\nReading state information... Done\nThe following additional packages will be installed:\nlibcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5 \nlibtypes-serialiser-perl postgresql-15 postgresql-client-15 \npostgresql-client-common postgresql-common ssl-cert\nSuggested packages:\npostgresql-doc postgresql-doc-15\nThe following NEW packages will be installed:\nlibcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5 \nlibtypes-serialiser-perl postgresql postgresql-15 postgresql-client-15 \npostgresql-client-common postgresql-common ssl-cert\n0 upgraded, 12 newly installed, 0 to remove and 0 not upgraded.\nNeed to get 43.4 MB of archives.\n\n...\n<\/pre><\/div>\n\n\n
3) The above will automatically create an active Postgres cluster that can seen with:<\/p>\n\n\n
\npg_lsclusters \nVer Cluster Port Status Owner    Data directory              Log file\n14  main    5432 online postgres \/var\/lib\/postgresql\/14\/main \/var\/log\/postgresql\/postgresql-14-main.log\n15  main    5433 online postgres \/var\/lib\/postgresql\/15\/main \/var\/log\/postgresql\/postgresql-15-main.log\n\n<\/pre><\/div>\n\n\n
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. <\/p>\n\n\n\n
4) File locations:<\/p>\n\n\n
\na)  Binaries\n\n    ls -al \/usr\/lib\/postgresql\/15\/bin\/\n    total 12920\n    drwxr-xr-x 2 root root    4096 Jun 27 09:13 .\n    drwxr-xr-x 4 root root    4096 Jun 27 09:13 ..\n    -rwxr-xr-x 1 root root   56048 May  9 10:05 clusterdb\n    -rwxr-xr-x 1 root root   60376 May  9 10:05 createdb\n    -rwxr-xr-x 1 root root   56400 May  9 10:05 createuser\n    -rwxr-xr-x 1 root root   51888 May  9 10:05 dropdb\n    -rwxr-xr-x 1 root root   51824 May  9 10:05 dropuser\n    ...\n\n\nb)  Data\n\n    sudo ls -al \/var\/lib\/postgresql\/15\/main\/\n\n    total 92\n    drwx------ 19 postgres postgres 4096 Jun 27 09:13 .\n    drwxr-xr-x  3 postgres postgres 4096 Jun 27 09:13 ..\n    drwx------  5 postgres postgres 4096 Jun 27 09:13 base\n    drwx------  2 postgres postgres 4096 Jun 27 09:14 global\n    drwx------  2 postgres postgres 4096 Jun 27 09:13 pg_commit_ts\n    drwx------  2 postgres postgres 4096 Jun 27 09:13 pg_dynshmem\n    drwx------  4 postgres postgres 4096 Jun 27 09:13 pg_logical\n\n    ...\n\nc)  Log \n\n    ls -al \/var\/log\/postgresql\/postgresql-15-main.log \n    -rw-r----- 1 postgres adm 660 Jun 27 09:13 \/var\/log\/postgresql\/postgresql-15-main.log\n\n\nd)  Conf\n\n    ls -al \/etc\/postgresql\/15\/main\/\n    total 68\n    drwxr-xr-x 3 postgres postgres  4096 Jun 27 09:13 .\n    drwxr-xr-x 3 postgres postgres  4096 Jun 27 09:13 ..\n    drwxr-xr-x 2 postgres postgres  4096 Jun 27 09:13 conf.d\n    -rw-r--r-- 1 postgres postgres   315 Jun 27 09:13 environment\n    -rw-r--r-- 1 postgres postgres   143 Jun 27 09:13 pg_ctl.conf\n    -rw-r----- 1 postgres postgres  5002 Jun 27 09:13 pg_hba.conf\n    -rw-r----- 1 postgres postgres  1636 Jun 27 09:13 pg_ident.conf\n    -rw-r--r-- 1 postgres postgres 29735 Jun 27 09:13 postgresql.conf\n    -rw-r--r-- 1 postgres postgres   317 Jun 27 09:13 start.conf\n<\/pre><\/div>\n\n\n
Connecting<\/strong><\/p>\n\n\n\n
Initial connection<\/p>\n\n\n\n
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<\/a>.<\/p>\n\n\n\n
The pg_hba.conf file is set up by the package install in the location below:<\/p>\n\n\n
\nsudo vi \/etc\/postgresql\/15\/main\/pg_hba.conf\n<\/pre><\/div>\n\n
\n\u2026\n\n# DO NOT DISABLE!\n# If you change this first entry you will need to make sure that the\n# database superuser can access the database using some other method.\n# Noninteractive access to all databases is required during automatic\n# maintenance (custom daily cronjobs, replication, and similar tasks).\n#\n# Database administrative login by Unix domain socket\nlocal   all             postgres                                peer\n\n# TYPE  DATABASE        USER            ADDRESS                 METHOD\n\n# "local" is for Unix domain socket connections only\nlocal   all             all                                     peer\n# IPv4 local connections:\nhost    all             all             127.0.0.1\/32            scram-sha-256\n# IPv6 local connections:\nhost    all             all             ::1\/128                 scram-sha-256\n# Allow replication connections from localhost, by a user with the\n# replication privilege.\nlocal   replication     all                                     peer\nhost    replication     all             127.0.0.1\/32            scram-sha-256\nhost    replication     all             ::1\/128                 scram-sha-256\n\n\u2026\n<\/pre><\/div>\n\n\n
Note the lines:<\/p>\n\n\n
\nDatabase administrative login by Unix domain socket\n\nlocal all postgres peer\n<\/pre><\/div>\n\n\n
This sets up the postgres user to log in via the local socket using peer<\/a> 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:<\/p>\n\n\n
\naklaver@arkansas:~$ sudo -i -u postgres psql -p 5433\npsql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))\nType "help" for help.\n<\/pre><\/div>\n\n\n
NOTE:<\/strong> I already have a Postgres instance running on port 5432 so the new 15 instance
was assigned port 5433 on install.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
The command below will show you the current databases in the cluster.<\/p>\n\n\n
\npostgres=# \\l\nList of databases\nName | Owner | Encoding | Collate | Ctype |\n-----------+----------+----------+-------------+-------------+ ...\npostgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8  ...\ntemplate0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8  ...\ntemplate1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8  ...\n(3 rows)\n<\/pre><\/div>\n\n\n
These where created by the initdb<\/a> done by the install. More information on the template databases can be found here Template database<\/a>. 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.<\/p>\n\n\n\n
It is a good idea to set up the postgres user to work with the lines below from pg_hba.conf.<\/p>\n\n\n
\n# IPv4 local connections:\nhost    all             all             127.0.0.1\/32            scram-sha-256\n# IPv6 local connections:\nhost    all             all             ::1\/128                 scram-sha-256\n\n<\/pre><\/div>\n\n\n
To that end run the command below with the strong password of your choice:<\/p>\n\n\n
\npostgres=# alter role postgres with password 'pg_test_SimplePWD';\nALTER ROLE\n<\/pre><\/div>\n\n\n
This then allows for logging from a OS user account without going through sudo.<\/p>\n\n\n
\npsql -d postgres -h localhost -U postgres -p 5433\nPassword for user postgres:\npsql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))\nSSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)\nType "help" for help.\n\npostgres=#\n<\/pre><\/div>\n\n\n
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.<\/p>\n\n\n\n
It is also a good idea to create a non superuser database user you can also log in as. Example shown below.<\/p>\n\n\n\n
\npostgres=# create role aklaver with login createdb createrole password 'TEST_pwd_Cat';\nCREATE ROLE\npsql -d postgres -h localhost -U aklaver -p 5433\nPassword for user aklaver:\npsql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))\nSSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)\nType "help" for help.\n\npostgres=>\n<\/pre><\/div><\/div><\/div>\n\n\n\n
It would also not hurt to go over the server configuration file:<\/p>\n\n\n
\nsudo vi \/etc\/postgresql\/15\/main\/postgresql.conf\n<\/pre><\/div>\n\n\n
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:<\/p>\n\n\n\n
Server configuration<\/a><\/p>\n\n\n\n
There is another file set up by the Postgres package install and deals with how the
server is started. It can be viewed here:<\/p>\n\n\n\n
\nsudo vi \/etc\/postgresql\/15\/main\/start.conf\n\n# Automatic startup configuration\n#   auto: automatically start the cluster\n#   manual: manual startup with pg_ctlcluster\/postgresql@.service only\n#   disabled: refuse to start cluster\n# See pg_createcluster(1) for details. When running from systemd,\n# invoke 'systemctl daemon-reload' after editing this file.\n\nauto\n<\/pre><\/div><\/div><\/div>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Managing server state.<\/p>\n\n\n\n
Using pg-common(installed by package) commands<\/p>\n\n\n
\naklaver@arkansas:~$ sudo pg_ctlcluster stop 15 main\n\naklaver@arkansas:~$ pg_lsclusters\nVer Cluster Port Status Owner Data directory Log file\n14 main 5432 online postgres \/var\/lib\/postgresql\/14\/main \/var\/log\/postgresql\/postgresql-14-main.log\n15 main 5433 down postgres \/var\/lib\/postgresql\/15\/main \/var\/log\/postgresql\/postgresql-15-main.log\n\naklaver@arkansas:~$ sudo pg_ctlcluster 15 main start\n\naklaver@arkansas:~$ pg_lsclusters\nVer Cluster Port Status Owner Data directory Log file\n14 main 5432 online postgres \/var\/lib\/postgresql\/14\/main \/var\/log\/postgresql\/postgresql-14-main.log\n15 main 5433 online postgres \/var\/lib\/postgresql\/15\/main \/var\/log\/postgresql\/postgresql-15-main.log\n<\/pre><\/div>\n\n\n
NOTE: <\/strong> 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.<\/p>\n\n\n\n
Using systemd.<\/p>\n\n\n
\naklaver@arkansas:~$ sudo systemctl stop postgresql@15-main.service\n\naklaver@arkansas:~$ sudo systemctl status postgresql@15-main.service\npostgresql@15-main.service - PostgreSQL Cluster 15-main\nLoaded: loaded (\/lib\/systemd\/system\/postgresql@.service; enabled-runtime; vendor preset: enabled)\nActive: inactive (dead) since Tue 2023-06-27 09:36:51 PDT; 1min 10s ago\nProcess: 3669737 ExecStart=\/usr\/bin\/pg_ctlcluster --skip-systemctl-redirect 15-main start (code=exited, status=0\/SUCCESS)\nProcess: 3669769 ExecStop=\/usr\/bin\/pg_ctlcluster --skip-systemctl-redirect -m fast 15-main stop (code=exited, status=0\/SUCCESS)\nMain PID: 3669742 (code=exited, status=0\/SUCCESS)\nCPU: 215ms\n\nJun 27 09:35:56 arkansas systemd[1]: Starting PostgreSQL Cluster 15-main\u2026\nJun 27 09:35:58 arkansas systemd[1]: Started PostgreSQL Cluster 15-main.\nJun 27 09:36:51 arkansas systemd[1]: Stopping PostgreSQL Cluster 15-main\u2026\nJun 27 09:36:51 arkansas systemd[1]: postgresql@15-main.service: Deactivated successfully.\nJun 27 09:36:51 arkansas systemd[1]: Stopped PostgreSQL Cluster 15-main.\n\naklaver@arkansas:~$ sudo systemctl start postgresql@15-main.service\n\naklaver@arkansas:~$ sudo systemctl status postgresql@15-main.service\npostgresql@15-main.service - PostgreSQL Cluster 15-main\nLoaded: loaded (\/lib\/systemd\/system\/postgresql@.service; enabled-runtime; vendor preset: enabled)\nActive: active (running) since Tue 2023-06-27 09:38:42 PDT; 5s ago\nProcess: 3669790 ExecStart=\/usr\/bin\/pg_ctlcluster --skip-systemctl-redirect 15-main start (code=exited, status=0\/SUCCESS)\nMain PID: 3669795 (postgres)\nTasks: 6 (limit: 4676)\nMemory: 18.6M\nCPU: 139ms\nCGroup: \/system.slice\/system-postgresql.slice\/postgresql@15-main.service\n\u251c\u25003669795 \/usr\/lib\/postgresql\/15\/bin\/postgres -D \/var\/lib\/postgresql\/15\/main -c config_file=\/etc\/postgresql\/15\/main\/postgresql.conf\n\u251c\u25003669796 "postgres: 15\/main: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" """"" "" "" "" "" ">\n\u251c\u25003669797 "postgres: 15\/main: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "">\n\u251c\u25003669799 "postgres: 15\/main: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ">\n\u251c\u25003669800 "postgres: 15\/main: autovacuum launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" >\n\u2514\u25003669801 "postgres: 15\/main: logical replication launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" >\n\nJun 27 09:38:39 arkansas systemd[1]: Starting PostgreSQL Cluster 15-main\u2026\nJun 27 09:38:42 arkansas systemd[1]: Started PostgreSQL Cluster 15-main.\n\n<\/pre><\/div>\n\n\n
In either case pg_lsclusters will also show you the correct state.<\/p>\n\n\n
\naklaver@arkansas:~$ pg_lsclusters\nVer Cluster Port Status Owner Data directory Log file\n14 main 5432 online postgres \/var\/lib\/postgresql\/14\/main \/var\/log\/postgresql\/postgresql-14-main.log\n15 main 5433 online postgres \/var\/lib\/postgresql\/15\/main \/var\/log\/postgresql\/postgresql-15-main.log\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"
Part 1 What follows is a simple explanation of how the Postgres Debian\/Ubuntu packagingsystem works in order to answer recurring questions that are posted on variousforums. This post will focus on the install and initial setup. The next postwill deal … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ngg_post_thumbnail":0,"footnotes":""},"categories":[5],"tags":[],"class_list":["post-465","post","type-post","status-publish","format-standard","hentry","category-postgres"],"_links":{"self":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/465","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/comments?post=465"}],"version-history":[{"count":59,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/465\/revisions"}],"predecessor-version":[{"id":524,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/posts\/465\/revisions\/524"}],"wp:attachment":[{"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/media?parent=465"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/categories?post=465"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/aklaver.org\/wordpress\/wp-json\/wp\/v2\/tags?post=465"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}