Installation of postgresql-15 seems to fail

Question

Apologies in advance if this is a stupid question - I am a developer not a sysadmin.

Since running apt-get update/upgrade on my ubuntu VM (Oracle VirtualBox) I cannot seem to get my postgresql database online. I believe this is may be because postgresql-15 has been upgraded from beta-3 to beta-4.

This is a real nuisance for me because it has trashed our development environment. (I am assuming when we are ready to release 15 will be fully available and I would really like to be able to use merge statements in my code)

I wiped postgresql from the host and am now attempting a fresh install following these instructions.

At Step 3. sudo -u postgres psql I receive the follwing error : psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket?

which, essentially, is the problem I was faced with after the failed upgrade (despite having run systemctl start postgresql)

Can anyone advise what it is I need to do to get postgres-15 (preferably beta-4) up and running?

---

hugh@ububtuvm:~$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; disabled; vendor p>
     Active: active (exited) since Sun 2022-09-18 10:47:19 UTC; 31min ago
    Process: 2349 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 2349 (code=exited, status=0/SUCCESS)
        CPU: 905us

Sep 18 10:47:19 ububtuvm systemd[1]: Starting PostgreSQL RDBMS...
Sep 18 10:47:19 ububtuvm systemd[1]: Finished PostgreSQL RDBMS.


hugh@ububtuvm:~$ journalctl -xeu postgresql.service
░░ Subject: A stop job for unit postgresql.service has begun execution
░░ Defined-By: systemd
░░ Support: http://www.ubuntu.com/support
░░
░░ A stop job for unit postgresql.service has begun execution.
░░
░░ The job identifier is 1675.
Sep 18 10:47:19 ububtuvm systemd[1]: Starting PostgreSQL RDBMS...
░░ Subject: A start job for unit postgresql.service has begun execution
░░ Defined-By: systemd
░░ Support: http://www.ubuntu.com/support
░░
░░ A start job for unit postgresql.service has begun execution.
░░
░░ The job identifier is 1675.
Sep 18 10:47:19 ububtuvm systemd[1]: Finished PostgreSQL RDBMS.
░░ Subject: A start job for unit postgresql.service has finished successfully
░░ Defined-By: systemd
░░ Support: http://www.ubuntu.com/support
░░
░░ A start job for unit postgresql.service has finished successfully.
░░
░░ The job identifier is 1675.

---

root@ubuntuvm:/home/hugh# cat /usr/lib/systemd/system/postgresql.service
# postgresql.service is the meta unit for managing all PostgreSQL clusters on
# the system at once. Conceptually, this unit is more like a systemd target,
# but we are using a service since targets cannot be reloaded.
#
# The unit actually managing PostgreSQL clusters is postgresql@.service,
# instantiated as postgresql@15-main.service for individual clusters.

[Unit]
Description=PostgreSQL RDBMS

[Service]
Type=oneshot
ExecStart=/bin/true
ExecReload=/bin/true
RemainAfterExit=on

[Install]
WantedBy=multi-user.target

(installed by apt install)

---

I found this which contains the following paragraphs.

After updating from an alpha or beta release I get an error about CATALOG_VERSION After upgrading from an alpha or beta package, I get an error like:

The database cluster was initialized with CATALOG_VERSION_NO X, but the server was compiled with CATALOG_VERSION_NO Y. This is because the PostgreSQL data format can change between alpha releases - and occasionally even between beta releases if there's a really pressing need.

If you need to access the old data, you will need to use the package version you were running before, and use pg_dump or pg_upgrade. The necessary binaries should have been saved in /var/tmp/postgresql*. Alternatively, look for the old packages in your /var/cache/apt/archives.

I am still struggling, though - I have not found the previous version anywhere yet.

Answer 1

Ubuntu has some clever support for running multiple postgres instances. The postgresql.service unit is just a dummy unit: it does nothing but run /bin/true. This may seem useless, but it's not! The key is that it is the dependencies of this unit that are important, and those are generated dynamically at boot time by /lib/systemd/system-generators/postgresql-generator.

This looks at all the /etc/postgresql/*/*/postgresql.conf files on your system and uses these to generate instances of the postgresql@.service template unit.

When you install the postgresql-15 package, this should:

1. Provide the file /etc/postgresql/15/main/postgresql.conf (and other files in that directory), and
2. Automatically start the postgresql@15-main.service unit.

I've reproduced this on an Ubuntu 22.04 system, where I started with postgresql-14, then installed postgresql-15 following the link in your question, and now I have:

root@ubuntu:~# systemctl list-units postgresql\*
  UNIT                       LOAD   ACTIVE SUB     DESCRIPTION
  postgresql.service         loaded active exited  PostgreSQL RDBMS
  postgresql@14-main.service loaded active running PostgreSQL Cluster 14-main
  postgresql@15-main.service loaded active running PostgreSQL Cluster 15-main

I can successfully connect to the database, but since here I'm running two different postgres instances I need to specify an explicit port. Looking at the config files in /etc/postgresql/..., I see that my postgres 15 instance is using port 5433, so I run:

root@ubuntu:/# sudo -u postgres psql -p 5433
psql (15beta4 (Ubuntu 15~beta4-1.pgdg22.04+1))
Type "help" for help.

postgres=#

---

If the Postgresql 15 service fails to start, to see the service status I would run:

systemctl status postgresql@15-main.service

And to see the logs I would run:

journalctl -u postgresql@15-main.service