Re: Postgres service stops when I kill client backend on Windows

Greetings,

2015-10-11 0:18 GMT+07:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
> 2015-10-10 18:04 GMT+02:00 Dmitry Vasilyev <d(dot)vasilyev(at)postgrespro(dot)ru>:
>
>>
>> On Сб, 2015-10-10 at 10:55 -0500, Tom Lane wrote:
>> > Dmitry Vasilyev <d(dot)vasilyev(at)postgrespro(dot)ru> writes:
>> > > I have written, what service stopped. This action is repeatable.
>> > > You can run command 'psql -c "do $$ unpack p,1x8 $$ language
>> > > plperlu;"'
>> > > and after this windows service will stop.
>> >
>>
>
> so it is expected behave. After any unexpected client fails, the server is
> restarted
>

I can confirm this too. In linux (i use Fedora 22), this is what happens
when a server is killed:

=== 1. before:
$ sudo systemctl status postgresql.service
postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled)
Active: active (running) since Jum 2015-10-09 16:25:43 WIB; 1 day 14h ago
Process: 778 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p
${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
Process: 747 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA}
(code=exited, status=0/SUCCESS)
Main PID: 783 (postgres)
CGroup: /system.slice/postgresql.service
├─ 783 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
├─ 812 postgres: logger process
├─ 821 postgres: checkpointer process
├─ 822 postgres: writer process
├─ 823 postgres: wal writer process
├─ 824 postgres: autovacuum launcher process
├─ 825 postgres: stats collector process
└─17181 postgres: postgres test [local] idle

=== 2. killing and attempt to reconnect:
$ sudo kill 17181

test=# select 1;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

=== 3. service status after:
$ sudo systemctl status postgresql.service
postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled)
Active: active (running) since Jum 2015-10-09 16:25:43 WIB; 1 day 14h ago
Process: 778 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p
${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
Process: 747 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA}
(code=exited, status=0/SUCCESS)
Main PID: 783 (postgres)
CGroup: /system.slice/postgresql.service
├─ 783 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
├─ 812 postgres: logger process
├─ 821 postgres: checkpointer process
├─ 822 postgres: writer process
├─ 823 postgres: wal writer process
├─ 824 postgres: autovacuum launcher process
├─ 825 postgres: stats collector process
└─17422 postgres: postgres test [local] idle

===

The service status is still active (running), and new process 17422 handles
the client.

But this is what happens in Windows (win 7 32 bit, postgres 9.4):

=== 1. before:
C:\Windows\system32>sc queryex postgresql-9.4

SERVICE_NAME: postgresql-9.4
TYPE : 10 WIN32_OWN_PROCESS
STATE : 4 RUNNING
(STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0
PID : 3716
FLAGS :

=== 2. killing & attempt to reconnect:
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
2080
(1 row)

C:\Windows\system32>taskkill /F /PID 2080
SUCCESS: The process with PID 2080 has been terminated.

postgres=# select 1;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

=== 3. service status after:
C:\Windows\system32>sc query postgresql-9.4

SERVICE_NAME: postgresql-9.4
TYPE : 10 WIN32_OWN_PROCESS
STATE : 1 STOPPED
WIN32_EXIT_CODE : 0 (0x0)
SERVICE_EXIT_CODE : 0 (0x0)
CHECKPOINT : 0x0
WAIT_HINT : 0x0

===

The client cannot reconnect. The service is dead. This is nasty, because
any client can exploit some segfault bug like the one in perl Dmitry
mentoined upthread, and the postgresql service is down.

Note: killing the server process with pg_terminate_backend isn't causing
this behavior to happen. The client reconnects normally, and the service is
still running.

Regards,
Ali Akbar