Postmaster can't stop with pg_ctl

============================================================================
POSTGRESQL BUG REPORT
============================================================================

Your name : Takuya Koide
Your email address : koide-txa (at) necst (dot) nec (dot) co (dot) jp

Category : runtime: back-end:
Severity : serious

Summary: Postmaster can't stop with pg_ctl

System Configuration
--------------------
Operating System : Red Hat Enterprise Linux ES release 4 (Nahant Update 3)

PostgreSQL version : PostgreSQL 8.2.4 on i686-redhat-linux-gnu,
compiled by GCC gcc (GCC) 3.4.5 20051201 (Red Hat 3.4.5-2)

notice: I use following RPM packages.

$ rpm -qa|grep -i postgresql
postgresql-server-8.2.4-1PGDG
postgresql-plperl-8.2.4-1PGDG
postgresql-8.2.4-1PGDG
postgresql-contrib-8.2.4-1PGDG
postgresql-docs-8.2.4-1PGDG
postgresql-plpython-8.2.4-1PGDG
postgresql-test-8.2.4-1PGDG
postgresql-libs-8.2.4-1PGDG
postgresql-devel-8.2.4-1PGDG
postgresql-pltcl-8.2.4-1PGDG

Compiler used : gcc

Hardware:
---------
x86

Versions of other tools:
------------------------

--------------------------------------------------------------------------

Problem Description:
--------------------
I found that pg_ctl can't stop postmaster processes under some conditions.

If PostgreSQL's process is abnormal condition (stall), I would like to
stop PostgreSQL's process (and restart) with /etc/rc.d/init.d/postgresql
But I couldn't stop its process.

--------------------------------------------------------------------------

Test Case (reproduce procedures):
---------------------------------
I can reproduce with following steps.

1) confirm current status.
# ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -'
postgres 3507 0.1 1.0 21352 2800 ? S 18:48 0:00 /usr/bin/postmaster
-p 5432 -D /var/lib/pgsql/data
postgres 3509 0.0 0.2 11132 568 ? S 18:48 0:00 postgres: logger process
postgres 3514 0.0 0.3 21352 844 ? S 18:48 0:00 postgres: writer process
postgres 3515 0.0 0.2 12132 564 ? S 18:48 0:00 postgres: stats
buffer process
postgres 3516 0.0 0.2 11364 748 ? S 18:48 0:00 postgres: stats
collector process

2) connect with psql command by postgres user
$ id
uid=26(postgres) gid=26(postgres) group=26(postgres)
context=user_u:system_r:unconfined_t
-bash-3.1$ psql template1
template1=#

3) re-confirm status
# ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -'
postgres 3507 0.0 1.1 21352 2804 ? S 18:48 0:00
/usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres 3509 0.0 0.2 11132 568 ? S 18:48 0:00 postgres: logger process
postgres 3514 0.0 0.3 21352 852 ? S 18:48 0:00 postgres: writer process
postgres 3515 0.0 0.2 12132 564 ? S 18:48 0:00 postgres: stats buffer process
postgres 3516 0.0 0.3 11364 772 ? S 18:48 0:00 postgres: stats collector process
postgres 3618 0.0 0.6 8476 1752 pts/3 S+ 18:54 0:00 psql template1
postgres 3619 0.0 0.8 22012 2124 ? S 18:54 0:00 postgres:
postgres template1 [local] idle

4) send 'SIGSTOP' signal to postgres
# kill -SIGSTOP 3619
# ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -'
postgres 3507 0.0 1.1 21352 2804 ? S 18:48 0:00
/usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres 3509 0.0 0.2 11132 568 ? S 18:48 0:00 postgres: logger process
postgres 3514 0.0 0.3 21352 852 ? S 18:48 0:00 postgres: writer process
postgres 3515 0.0 0.2 12132 564 ? S 18:48 0:00 postgres: stats buffer process
postgres 3516 0.0 0.3 11364 772 ? S 18:48 0:00 postgres: stats collector process
postgres 3618 0.0 0.6 8476 1752 pts/3 S+ 18:54 0:00 psql template1
postgres 3619 0.0 0.8 22012 2124 ? T 18:54 0:00 postgres:
postgres template1 [local] idle

5) try to stop PostgreSQL with normal method
# /etc/rc.d/init.d/postgresql stop
postgresql stopping service: [fail]

6) confirm status and confirm that PostgreSQL is not stop. (this is
problem)
# ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -'
postgres 3507 0.0 1.1 21352 2816 ? S 18:48 0:00
/usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres 3509 0.0 0.2 11132 568 ? S 18:48 0:00 postgres: logger process
postgres 3514 0.0 0.3 21352 852 ? S 18:48 0:00 postgres: writer process
postgres 3515 0.0 0.2 12132 564 ? S 18:48 0:00 postgres: stats buffer process
postgres 3516 0.0 0.3 11364 772 ? S 18:48 0:00 postgres: stats collector process
postgres 3618 0.0 0.6 8476 1752 pts/3 S+ 18:54 0:00 psql template1
postgres 3619 0.0 0.8 22012 2124 ? T 18:54 0:00 postgres:
postgres template1 [local] idle

7) try to stop PostgreSQL with SIGINT signal.
# kill -SIGINT 3507

8) confirm status and confirm that PostgreSQL is not stop. (this is
problem,too.)
# ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -'
postgres 3507 0.0 1.1 21352 2816 ? S 18:48 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres 3509 0.0 0.2 11132 568 ? S 18:48 0:00 postgres: logger process
postgres 3514 0.0 0.3 21352 852 ? S 18:48 0:00 postgres: writer process
postgres 3515 0.0 0.2 12132 564 ? S 18:48 0:00 postgres: stats buffer process
postgres 3516 0.0 0.3 11364 772 ? S 18:48 0:00 postgres: stats collector process
postgres 3618 0.0 0.6 8476 1752 pts/3 S+ 18:54 0:00 psql template1
postgres 3619 0.0 0.8 22012 2124 ? T 18:54 0:00 postgres:
postgres template1 [local] idle

9) try to stop PostgreSQL with SIGKILL
# kill -SIGKILL 3507
# ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -'
postgres 3509 0.0 0.2 11132 564 ? S 18:48 0:00 postgres: logger process
postgres 3618 0.0 0.5 8476 1520 pts/3 S+ 18:54 0:00 psql template1
postgres 3619 0.0 0.7 22012 1976 ? T 18:54 0:00 postgres:
postgres template1 [local] idle
# kill -SIGKILL 3509
# ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -'
postgres 3618 0.0 0.5 8476 1520 pts/3 S+ 18:54 0:00 psql template1
postgres 3619 0.0 0.7 22012 1976 ? T 18:54 0:00 postgres:
postgres template1 [local] idle
# kill -SIGKILL 3619
# ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -'
postgres 3618 0.0 0.5 8476 1520 pts/3 S+ 18:54 0:00 psql template1
# kill -SIGKILL 3618
# ps axuw|grep -i postgres|grep -Ev 'grep|bash|su -'
#

--------------------------------------------------------------------------

Solution:
---------
I suggest the method to resolve this issue.
If you think that this idea is good, please use it.

[current status]
a part of /etc/rc.d/init.d/postgresql (rc scripts of postgresql)
-------------------------------------------------------------------
stop(){
echo -n $"Stopping ${NAME} service: "
$SU -l postgres -c "$PGENGINE/pg_ctl stop -D '$PGDATA' -s -m fast" > /dev/null 2>&1 < /dev/null
ret=$?
-------------------------------------------------------------------

In postgresql processes is stalled

1. perform '/etc/rc.d/init.d/postgresql stop'
2. So pg_ctl of line3 is running and return error code (1)
(Please refer to following)

$ pg_ctl stop -m fast
waiting for postmaster to shut down........ failed
pg_ctl: postmaster does not shut down
$ echo $?
1

So when pg_ctl is fail, add performed script to rc script of postgresql.
(following 'add script')

/etc/rc.d/init.d/postgresql (rc scripts of postgresql)
-------------------------------------------------------------------
stop(){
echo -n $"Stopping ${NAME} service: "
...snip...
$SU -l postgres -c "$PGENGINE/pg_ctl stop -D '$PGDATA' -s -m fast" > /dev/null 2>&1 < /dev/null
ret=$?

# when pg_ctl fails, perform following steps
if [ ret value is 1 ]

# try to stop postgresql with pg_ctl
until $SU -l postgres -c "$PGENGINE/pg_ctl stop -D '$PGDATA' -s -m fast" > /dev/null 2>&1 < /dev/null; do

# if pg_ctl needs to times for stopping postgresql
sleep (user's designated time)

# if pg_ctl can not stop postgresql after repeat a few times
if [ loop's times equal user's designated time. ]

# give up using pg_ctl
exit loop
fi
done

# forced terminate postgresql
if [ user hope forced terminate postgresql ]
1. send SIGCONT signal to suspended processes of postgresql.
2. if postgresql can't stop, send SIGKILL signal to processes of postgresql.
3. release shared memory used by postgresql.
(if linux, can use ipcclean)
fi
fi
-------------------------------------------------------------------

--------------------------------------------------------------------------

---
Takuya Koide
NEC System Technologies, Ltd.