postgresql always rewrite config file after server rebooted

[karnz]

Hi,

After upgraded by following this guide https://pmg.proxmox.com/wiki/index.php/Upgrade_from_6.x_to_7.0
PMG is working fine but after rebooting once, Postgresql failed to start as its config file /etc/postgresql/13/main/postgresql.conf version number changed from 13 to 11 on all lines.
Tried to edit it back to 13 and chmod 444 but the file will be replaced with a new one after rebooted (or maybe while shutting down) and version number will be 11 again and again.

Any clues for fixing this?

 

[dcsapak]

did you adapt the postgres config in the pmg template ? if yes you must adapt it, see: https://pmg.proxmox.com/pmg-docs/pmg-admin-guide.html#pmgconfig_template_engine

 

[karnz]

Hi @dcsapak

No, never touch postgres config file before.
Even “pmgconfig dump” shows “postgres.version = 13”

Weird thing after successfully upgraded is systemctl shows three service files

postgresql.service
postgresql@11-main.service
postgresql@13-main.service

removed postgresql@11-main.service already but same result.

 

[dcsapak]

did you do the step: 'Upgrade the PostgreSQL database'
from the upgrade guide? because it sounds like you did not remove the old postgres instance...

 

[karnz]

@dcsapak

Yes I followed the guide.

pg_dropcluster --stop 13 main
pg_upgradecluster -v 13 11 main
systemctl unmask postfix pmg-smtp-filter pmgpolicy pmgdaemon pmgproxy
reboot
apt purge postgresql-11 postgresql-client-11

and optional "apt autoremove" as apt recommended.
it will work as long as until reboot.

Do I missed any steps?

Tried to restore PMG 6.4 VM from backup and repeat the upgrade to PMG 7.0 again few times. same result.
Now I have to find/replace version number in postgres config file before rebooting the server

 

[Stoiko Ivanov]

hmm - the version detection usually happens based on the result von psql output upon connecting...

could you please post the following:
* `ls /etc/pmg/templates` #to make sure that no template is in the way
* ` psql --quiet --tuples-only --no-align --command 'show server_version;' Proxmox_ruledb`
* `dpkg -l |grep -i postgres`
* `ss -tlnp` #check which process is listening on 5432

Thanks!

 

[karnz]

Hi @Stoiko Ivanov

ls /etc/pmg/templates

clamd.conf.in               init.pre.in           main.cf.in.bak            master.cf.in.demo      postgresql.conf.in.ucf-dist   spamreport-verbose.tt
clamd.conf.in.ucf-dist      init.pre.in.ucf-dist  main.cf.in.demo           master.cf.in.ucf-dist  razor-agent.conf.in           spamreport-verbose.tt.ucf-dist
fetchmailrc.tt              local.cf.in           main.cf.in.demo.ucf-dist  pg_hba.conf.in         spamreport-custom.tt          v310.pre.in
freshclam.conf.in           local.cf.in.ucf-dist  main.cf.in.ucf-dist       pmgreport.tt           spamreport-short.tt           v320.pre.in
freshclam.conf.in.ucf-dist  main.cf.in            master.cf.in              postgresql.conf.in     spamreport-short.tt.ucf-dist  v320.pre.in.ucf-dist

postgresql.conf.in & postgresql.conf.in.ucf-dist are size equivalent and same content.

psql --quiet --tuples-only --no-align --command 'show server_version;' Proxmox_ruledb

13.3 (Debian 13.3-1)

dpkg -l |grep -i postgres

ii  libdbd-pg-perl                     3.14.2-1+b1                    amd64        Perl DBI driver for the PostgreSQL database server
ii  libpq5:amd64                       13.3-1                         amd64        PostgreSQL C client library
ii  postgresql-13                      13.3-1                         amd64        The World's Most Advanced Open Source Relational Database
ii  postgresql-client-13               13.3-1                         amd64        front-end programs for PostgreSQL 13
ii  postgresql-client-common           225                            all          manager for multiple PostgreSQL client versions
ii  postgresql-common                  225                            all          PostgreSQL database-cluster manager

ss -tlnp

State                     Recv-Q                     Send-Q                                         Local Address:Port                                          Peer Address:Port                    Process
LISTEN                    0                          4096                                                 0.0.0.0:8006                                               0.0.0.0:*                        users:(("pmgproxy worker",pid=4944,fd=6),("pmgproxy worker",pid=4675,fd=6),("pmgproxy worker",pid=538,fd=6),("pmgproxy",pid=536,fd=6))
LISTEN                    0                          4096                                               127.0.0.1:10022                                              0.0.0.0:*                        users:(("pmgpolicy child",pid=28292,fd=5),("pmgpolicy child",pid=28291,fd=5),("pmgpolicy child",pid=28290,fd=5),("pmgpolicy child",pid=28289,fd=5),("pmgpolicy child",pid=28288,fd=5),("pmgpolicy",pid=501,fd=5))
LISTEN                    0                          4096                                               127.0.0.1:10023                                              0.0.0.0:*                        users:(("pmg-smtp-filter",pid=29069,fd=5),("pmg-smtp-filter",pid=29047,fd=5),("pmg-smtp-filter",pid=21562,fd=5))
LISTEN                    0                          4096                                               127.0.0.1:10024                                              0.0.0.0:*                        users:(("pmg-smtp-filter",pid=29069,fd=6),("pmg-smtp-filter",pid=29047,fd=6),("pmg-smtp-filter",pid=21562,fd=6))
LISTEN                    0                          100                                                127.0.0.1:10025                                              0.0.0.0:*                        users:(("smtpd",pid=29639,fd=6),("master",pid=486,fd=28))
LISTEN                    0                          4096                                               127.0.0.1:85                                                 0.0.0.0:*                        users:(("pmgdaemon worke",pid=4941,fd=6),("pmgdaemon worke",pid=4584,fd=6),("pmgdaemon worke",pid=528,fd=6),("pmgdaemon",pid=526,fd=6))
LISTEN                    0                          128                                                  0.0.0.0:22                                                 0.0.0.0:*                        users:(("sshd",pid=136,fd=3))
LISTEN                    0                          244                                                127.0.0.1:5432                                               0.0.0.0:*                        users:(("postgres",pid=151,fd=6))
LISTEN                    0                          100                                                  0.0.0.0:25                                                 0.0.0.0:*                        users:(("postscreen",pid=29573,fd=6),("master",pid=486,fd=21))
LISTEN                    0                          100                                                  0.0.0.0:26                                                 0.0.0.0:*                        users:(("master",pid=486,fd=17))
LISTEN                    0                          128                                                     [::]:22                                                    [::]:*                        users:(("sshd",pid=136,fd=4))
LISTEN                    0                          244                                                    [::1]:5432                                                  [::]:*                        users:(("postgres",pid=151,fd=5))
LISTEN                    0                          100                                                     [::]:25                                                    [::]:*                        users:(("postscreen",pid=29573,fd=7),("master",pid=486,fd=22))
LISTEN                    0                          100                                                     [::]:26                                                    [::]:*                        users:(("master",pid=486,fd=18))

 

[Stoiko Ivanov]

* which process is running as pid 151? (ps auxwf |grep 151)

* which versions of pmg are installed? (pmgversion -v)
* could you please post the complete contents of /etc/pmg/templates/postgresql.conf.in ?

* does running `pmgconfig sync` cause the postgres config to change to version 11?

EDIT:
* please also post the output of `diff -su /var/lib/pmg/templates/postgresql.conf.in /etc/pmg/templates/postgresql.conf.in`

 

[karnz]

Hi @Stoiko Ivanov

I can now fixed this problem by using template and set it to version 13 and it works even after several rebooted. Version doesn't change back to 11 anymore! Thanks for your help

Below are answers for your questions.

I just rebooted the server, so process has changed as following.

ss -tlnp

State                     Recv-Q                     Send-Q                                         Local Address:Port                                          Peer Address:Port                    Process
LISTEN                    0                          4096                                                 0.0.0.0:8006                                               0.0.0.0:*                        users:(("pmgproxy worker",pid=545,fd=6),("pmgproxy worker",pid=543,fd=6),("pmgproxy worker",pid=542,fd=6),("pmgproxy",pid=541,fd=6))
LISTEN                    0                          4096                                               127.0.0.1:10022                                              0.0.0.0:*                        users:(("pmgpolicy child",pid=500,fd=5),("pmgpolicy child",pid=499,fd=5),("pmgpolicy child",pid=498,fd=5),("pmgpolicy child",pid=494,fd=5),("pmgpolicy child",pid=493,fd=5),("pmgpolicy",pid=491,fd=5))
LISTEN                    0                          4096                                               127.0.0.1:10023                                              0.0.0.0:*                        users:(("pmg-smtp-filter",pid=593,fd=5),("pmg-smtp-filter",pid=592,fd=5),("pmg-smtp-filter",pid=513,fd=5))
LISTEN                    0                          4096                                               127.0.0.1:10024                                              0.0.0.0:*                        users:(("pmg-smtp-filter",pid=593,fd=6),("pmg-smtp-filter",pid=592,fd=6),("pmg-smtp-filter",pid=513,fd=6))
LISTEN                    0                          100                                                127.0.0.1:10025                                              0.0.0.0:*                        users:(("smtpd",pid=636,fd=6),("master",pid=488,fd=28))
LISTEN                    0                          4096                                               127.0.0.1:85                                                 0.0.0.0:*                        users:(("pmgdaemon worke",pid=533,fd=6),("pmgdaemon worke",pid=532,fd=6),("pmgdaemon worke",pid=531,fd=6),("pmgdaemon",pid=530,fd=6))
LISTEN                    0                          128                                                  0.0.0.0:22                                                 0.0.0.0:*                        users:(("sshd",pid=135,fd=3))
LISTEN                    0                          244                                                127.0.0.1:5432                                               0.0.0.0:*                        users:(("postgres",pid=154,fd=6))
LISTEN                    0                          100                                                  0.0.0.0:25                                                 0.0.0.0:*                        users:(("postscreen",pid=611,fd=6),("master",pid=488,fd=21))
LISTEN                    0                          100                                                  0.0.0.0:26                                                 0.0.0.0:*                        users:(("master",pid=488,fd=17))
LISTEN                    0                          128                                                     [::]:22                                                    [::]:*                        users:(("sshd",pid=135,fd=4))
LISTEN                    0                          244                                                    [::1]:5432                                                  [::]:*                        users:(("postgres",pid=154,fd=5))
LISTEN                    0                          100                                                     [::]:25                                                    [::]:*                        users:(("postscreen",pid=611,fd=7),("master",pid=488,fd=22))
LISTEN                    0                          100                                                     [::]:26                                                    [::]:*                        users:(("master",pid=488,fd=18))

New process number is 154.

ps auxwf |grep 154

root          80  0.0  0.1 154740  4324 ?        SNl  22:02   0:00  \_ /var/dcc/libexec/dccifd
root         689  0.0  0.0   3180   716 pts/2    S+   22:06   0:00          \_ grep 154
postgres     154  0.0  0.6 213232 28060 ?        Ss   22:02   0:00 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf
root         498  0.0  1.9 154432 83692 ?        S    22:02   0:00  \_ pmgpolicy child

pmgversion -v

proxmox-mailgateway: 7.0-1 (API: 7.0-7/78c8fe17, running kernel: 5.11.22-3-pve)
pmg-api: 7.0-7
pmg-gui: 3.0-4
pve-kernel-5.11: 7.0-6
pve-kernel-helper: 7.0-6
pve-kernel-5.4: 6.4-5
pve-kernel-5.11.22-3-pve: 5.11.22-7
pve-kernel-5.4.128-1-pve: 5.4.128-2
pve-kernel-5.4.119-1-pve: 5.4.119-1
pve-kernel-5.4.106-1-pve: 5.4.106-1
pve-kernel-5.4.103-1-pve: 5.4.103-1
pve-kernel-5.4.78-2-pve: 5.4.78-2
pve-kernel-5.4.73-1-pve: 5.4.73-1
pve-kernel-5.4.65-1-pve: 5.4.65-1
pve-kernel-5.4.60-1-pve: 5.4.60-2
pve-kernel-5.4.44-2-pve: 5.4.44-2
pve-kernel-5.4.41-1-pve: 5.4.41-1
pve-kernel-5.4.30-1-pve: 5.4.30-1
clamav-daemon: 0.103.2+dfsg-2
ifupdown: residual config
ifupdown2: 3.1.0-1+pmx3
libarchive-perl: 3.4.0-1
libjs-extjs: 7.0.0-1
libjs-framework7: 4.4.7-1
libproxmox-acme-perl: 1.3.0
libproxmox-acme-plugins: 1.3.0
libpve-apiclient-perl: 3.2-1
libpve-common-perl: 7.0-6
libpve-http-server-perl: 4.0-2
libxdgmime-perl: 1.0-1
lvm2: not correctly installed
pmg-docs: 7.0-2
pmg-i18n: 2.4-1
pmg-log-tracker: 2.2.0-1
postgresql-13: 13.3-1
proxmox-mini-journalreader: 1.2-1
proxmox-spamassassin: 3.4.6-3
proxmox-widget-toolkit: 3.3-6
pve-firmware: 3.2-4
pve-xtermjs: 4.12.0-1

 

[karnz]

I can't paste the complete content of /etc/pmg/templates/postgresql.conf.in as it has more than 15000 chars but it's equivalent of size and content to /etc/pmg/templates/postgresql.conf.in.ucf-dist and also /var/lib/pmg/templates/postgresql.conf.in

First page of its content.

[% TAGS <* *> -%]
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload".  Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".  Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
#                MB = megabytes                     s   = seconds
#                GB = gigabytes                     min = minutes
#                TB = terabytes                     h   = hours
#                                                   d   = days


#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/var/lib/postgresql/<* postgres.version *>/main'              # use data in another directory
                                        # (change requires restart)
hba_file = '/etc/postgresql/<* postgres.version *>/main/pg_hba.conf'    # host-based authentication file
                                        # (change requires restart)
ident_file = '/etc/postgresql/<* postgres.version *>/main/pg_ident.conf'        # ident configuration file
                                        # (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
external_pid_file = '/var/run/postgresql/<* postgres.version *>-main.pid'                       # write an extra PID file
                                        # (change requires restart)

* pmgconfig sync or restart postgresql don't change the config to version 11.
As my previous tests it seems to change after reboot only.

Thanks!

 

[Stoiko Ivanov]

I can now fixed this problem by using template and set it to version 13

the current template does not contain a hard-coded postgres version anymore (for quite a while actually) - only references to 'postgres.version' (which is substitued for the postgres.version you get from `pmgconfig dump`)

Anyways glad you resolved your issue

I would suggest to remove all templates which are not modified from /etc/pmg/templates - might prevent similar issues in the future