Re: Postgresql-11 confusing situtation

On 6/5/19 5:46 AM, Domen Šetar wrote:
> Hi,
>
> I’m using Postgresql 11.3 on Centos 7.6.18.10 server.
>
> I have very unusual issue which I don’t know if it is OK:
>
> I have running postgresql server with default instance:
>
> [root(at)pgpmaster ~]# systemctl status postgresql-11
>
> ● postgresql-11.service - PostgreSQL 11 database server
>
>    Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service;
> enabled; vendor preset: disabled)
>
>    Active: active (running) since Wed 2019-06-05 14:37:21 CEST; 49s ago
>
>      Docs: https://www.postgresql.org/docs/11/static/
>
>   Process: 3552
> ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA}
> (code=exited, status=0/SUCCESS)
>
> Main PID: 3576 (postmaster)
>
>     Tasks: 9
>
>    CGroup: /system.slice/postgresql-11.service
>
> ├─3576 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
>
> ├─3609 postgres: logger
>
> ├─3621 postgres: checkpointer
>
> ├─3622 postgres: background writer
>
> ├─3623 postgres: walwriter
>
> ├─3624 postgres: autovacuum launcher
>
> ├─3625 postgres: archiver
>
> ├─3626 postgres: stats collector
>
>            └─3627 postgres: logical replication launcher
>
> Jun 05 14:37:20 pgpmaster.localhost systemd[1]: Starting PostgreSQL 11
> database server...
>
> Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05
> 14:37:20.923 CEST [3576] LOG:  listening on IPv4 address "0.0.0.0", port
> 5432
>
> Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05
> 14:37:20.924 CEST [3576] LOG:  listening on IPv6 address "::", port 5432
>
> Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05
> 14:37:20.928 CEST [3576] LOG:  listening on Unix socket
> "/var/run/postgresql/.s.PGSQL.5432"
>
> Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05
> 14:37:21.026 CEST [3576] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
>
> Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05
> 14:37:21.123 CEST [3576] LOG:  redirecting log output to logging
> collector process
>
> Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05
> 14:37:21.123 CEST [3576] HINT:  Future log output will appear in
> directory "log".
>
> Jun 05 14:37:21 pgpmaster.localhost systemd[1]: Started PostgreSQL 11
> database server.
>
> If I restart it using systemctl restart postgresql-11 command,
> everything is OK, but if I use commands:
>
> # pg_ctl -D /var/lib/pgsql/11/data/ -m immediate stop
>
> # pg_ctl -D /var/lib/pgsql/11/data/ -m immediate start

Don't do that. You are mixing starting procedures. pg_ctl is directly
starting the server without going through systemd. Since systemd did not
start the server it sees it as dead.

>
> Then I get following situtation:
>
> # systemctl status postgresql-11
>
> ● postgresql-11.service - PostgreSQL 11 database server
>
>    Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service;
> enabled; vendor preset: disabled)
>
> *Active: inactive (dead) since Wed 2019-06-05 14:41:32 CEST; 1min 40s ago*
>
>      Docs: https://www.postgresql.org/docs/11/static/
>
>   Process: 3576 ExecStart=/usr/pgsql-11/bin/postmaster -D ${PGDATA}
> (code=exited, status=0/SUCCESS)
>
>   Process: 3552
> ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA}
> (code=exited, status=0/SUCCESS)
>
> Main PID: 3576 (code=exited, status=0/SUCCESS)
>
> Jun 05 14:37:20 pgpmaster.localhost systemd[1]: Starting PostgreSQL 11
> database server...
>
> Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05
> 14:37:20.923 CEST [3576] LOG:  listening on IPv4 address "0.0.0.0", port
> 5432
>
> Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05
> 14:37:20.924 CEST [3576] LOG:  listening on IPv6 address "::", port 5432
>
> Jun 05 14:37:20 pgpmaster.localhost postmaster[3576]: 2019-06-05
> 14:37:20.928 CEST [3576] LOG:  listening on Unix socket
> "/var/run/postgresql/.s.PGSQL.5432"
>
> Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05
> 14:37:21.026 CEST [3576] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
>
> Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05
> 14:37:21.123 CEST [3576] LOG:  redirecting log output to logging
> collector process
>
> Jun 05 14:37:21 pgpmaster.localhost postmaster[3576]: 2019-06-05
> 14:37:21.123 CEST [3576] HINT:  Future log output will appear in
> directory "log".
>
> Jun 05 14:37:21 pgpmaster.localhost systemd[1]: Started PostgreSQL 11
> database server.
>
> # ps agx | grep postgre
>
> 4592 pts/0    S      0:00 /usr/pgsql-11/bin/postgres -D
> /var/lib/pgsql/11/data
>
> 4593 ?        Ss     0:00 postgres: logger
>
> 4595 ?        Ss     0:00 postgres: checkpointer
>
> 4596 ?        Ss     0:00 postgres: background writer
>
> 4597 ?        Ss     0:00 postgres: walwriter
>
> 4598 ?        Ss     0:00 postgres: autovacuum launcher
>
> 4599 ?        Ss     0:00 postgres: archiver
>
> 4600 ?        Ss     0:00 postgres: stats collector
>
> 4601 ?        Ss     0:00 postgres: logical replication launcher
>
> 4630 pts/0    S+     0:00 grep --color=auto postgre
>
> $ pg_ctl -D /var/lib/pgsql/11/data/ status
>
> pg_ctl: server is running (PID: 4592)
>
> /usr/pgsql-11/bin/postgres "-D" "/var/lib/pgsql/11/data"
>
> Systemctl shows that database is dead, but it is actually running.
>
> Can someone explain this behavioure?
>
> Best regards!
>
> izum
>
>
>
> Domen Šetar
> /Computer Systems Support/
> IZUM – Institute of Information Science| Prešernova ulica 17 | 2000
> Maribor |Slovenia/
> /T: +386 2 25 20 339| M: +386 41 676 342| www.izum.si
> <http://www.izum.si/>|domen(dot)setar(at)izum(dot)si <mailto:domen(dot)setar(at)izum(dot)si>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com