Postgresql Error After Upgrade & Restore

[kransom]

Hi,

I did a fresh installation for PMG v8.1 on a new VM. I downloaded a backup of my current installation of PMG running v7.3. I restored the back from v7.3 on v8.1, and received this error.

DBI connect('dbname=Proxmox_ruledb;host=/var/run/postgresql;port=5432','root',...) failed: 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? at /usr/share/perl5/PMG/DBTools.pm line 78.

After following looking through syslog and following a few threads, I ended up purging my postgresql db 15. Is there any way to resolve this, or would a fresh installation suit this situation better?

 

[Stoiko Ivanov]

Anything in the logs that explains why postgresql 15 was not running?

How did you install PMG 8.1 in the new VM? (PMG ISO, on top of Debian.... as a container?)

 

[kransom]

How did you install PMG 8.1 in the new VM? (PMG ISO, on top of Debian.... as a container?)

Using the PMG ISO. Regular install.

Anything in the logs that explains why postgresql 15 was not running?

Apr 09 09:54:21 gclab-pmgvm systemd[1]: Starting postgresql@15-main.service - PostgreSQL Cluster 15-main...
Apr 09 09:54:22 gclab-pmgvm postgresql@15-main[1377]: Error: /usr/lib/postgresql/15/bin/pg_ctl /usr/lib/postgresql/15/bin/pg_ctl start -D /var/lib/postgresql/15/main -l /var/log/postgresql/postgresql-15-main.log -s -o -c config_file="/etc/postgresql.conf" exited with status 1:>
Apr 09 09:54:22 gclab-pmgvm postgresql@15-main[1377]: 2024-04-09 14:54:21.978 GMT [1382] LOG: unrecognized configuration parameter "stats_temp_directory" in file "/etc/postgresql/15/main/postgresql.conf" line 479>
Apr 09 09:54:22 gclab-pmgvm postgresql@15-main[1377]: 2024-04-09 14:54:21.978 GMT [1382] FATAL: configuration file "/etc/postgresql/15/main/postgresql.conf" contains errors>
Apr 09 09:54:22 gclab-pmgvm postgresql@15-main[1377]: pg_ctl: could not start server
Apr 09 09:54:22 gclab-pmgvm postgresql@15-main[1377]: Examine the log output.
Apr 09 09:54:22 gclab-pmgvm systemd[1]: postgresql@15-main.service: Can't open PID file /run/postgresql/15-main.pid (yet?) after start: No such file or directory
Apr 09 09:54:22 gclab-pmgvm systemd[1]: postgresql@15-main.service: Failed with result 'protocol'.
Apr 09 09:54:22 gclab-pmgvm systemd[1]: Failed to start postgresql@15-main.service - PostgreSQL Cluster 15-main.

Checking /etc/postgresql/15/main/postgresql.conf line 479:
stats_temp_directory = '/var/run/postgresql/15-main.pg_stat_tmp'

 

[Stoiko Ivanov]

see https://pmg.proxmox.com/wiki/index.php/Upgrade_from_7_to_8#Breaking_Changes

it seems you have overridden the postgresql template in your install (and I'd guess everything else as well) - https://pmg.proxmox.com/pmg-docs/pmg-admin-guide.html#pmgconfig_template_engine

 

[kransom]

I commented out the temp directory in the config file based on that being depreciated (per the docs). It allowed postgresql to get up and running. I can run pmgconfig dump now, as it did not work earlier and produced that error above. Are there any for sure tests to see if this is working?

I'm guessing when restoring from backup, checking the system configuration box, will over write the current templates on your PMG instance? So, if I am restoring version 7.3 with the depreciated stats_temp directory, that will over write version 8.1 which does not include that?

 

[Stoiko Ivanov]

I assume the issue is that you have at some point in the past copied all templates/or at least the postgresql.conf.in template from /var/lib/pmg/templates to /etc/pmg/templates.
if you restore from a pmg-backup /etc/pmg/templates gets restored as well.

Unless you need to modify the postgresql config - just remove that template from /etc/pmg/templates (do so as well for all other templates that you do not modify).

 

[kransom]

Ah okay, I see. Yes, I did copy /var/lib/pmg/templates/ to /etc/pmg/templates.
I would like to keep the changes I've made in specific template files, but I did not alter all of them. I will delete the ones not in use.
Thank you for that.

The other issue I had is when I was troubleshooting this earlier I had ran apt purge postgresql-15 postgresql-client-15 on my new instance. Running pg_lsclusters shows nothing (deleted/purged my main ver 15 cluster of psql). Is there a way to reinstall or get it back to its original state upon install? Or would I have to create a new installation using the ISO?

 

[kransom]

Testing for errors, I still receive connection error. Should .s.PGSQL.5432 be removed?

root@host:~# psql -c 'SELECT version();'
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?