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@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.setar@izum.si <mailto:domen.setar@izum.si>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com