postgresql always rewrite config file after server rebooted

K

karnz

Renowned Member
Nov 23, 2015
60
3
73
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?
 
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

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

removed postgresql@11-main.service already but same result.
 
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...
 
@dcsapak

Yes I followed the guide.

Code: 
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 :(
 
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!
 
Hi @Stoiko Ivanov

ls /etc/pmg/templates
Code: 
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
Code: 
13.3 (Debian 13.3-1)

dpkg -l |grep -i postgres
Code: 
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
Code: 
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))
 
* 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`
 
Last edited:
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
Code: 
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
Code: 
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
Code: 
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
 
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.
Code: 
[% 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!
 
Last edited:
karnz said:
I can now fixed this problem by using template and set it to version 13
Click to expand...
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
 
  • Like
Reactions: karnz
You must log in or register to reply here.

About

The Proxmox community has been around for many years and offers help and support for Proxmox VE, Proxmox Backup Server, and Proxmox Mail Gateway.
We think our community is one of the best thanks to people like you!

Quick Navigation

Home Get Subscription Wiki Downloads Proxmox Customer Portal About

Get your subscription!

The Proxmox team works very hard to make sure you are running the best software and getting stable updates and security enhancements, as well as quick enterprise support. Tens of thousands of happy customers have a Proxmox subscription. Get yours easily in our online shop.

Buy now!
Community platform by XenForo® © 2010-2024 XenForo Ltd.
Top Bottom