Thread: 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

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 run those as root. I guess you didn't run pg_ctl as root (since it worked)

 

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?

if you bypass systemctl then systemd has no way to tell if the service is up or down just by grep.

 

Best regards!

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

 

 

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Hi Domen,

On 05/06/2019 14:46, Domen Šetar wrote:
> Systemctl shows that database is dead, but it is actually running.
> 
> Can someone explain this behavioure?

As others pointed out - this is due to bypassing of systemd altogether
in order to start the database proces. If you check the configuration of
the service under /usr/lib/systemd/system/postgresql-10.service you can
inspect further how this work (exactly what type of notification is used).

More on the types of services and the way they notify systemd can be
found here:
https://www.freedesktop.org/software/systemd/man/systemd.service.html

Cheers!
Jakub