Thread: BUG #13757: Able to write to postgres even when the main process has been killed

The following bug has been logged on the website:

Bug reference:      13757
Logged by:          Vijay
Email address:      vijaysam@mailworks.org
PostgreSQL version: 9.4.1
Operating system:   Linux
Description:

Hi,

I am trying to do some tests with basic replication using postgres.

The following is my setup and the scenario.
Postgres 9.4.1 on 2 servers setup as Master and Standby with
repmgr(configured for failover as well). This works fine.

Connection is through java client library.

I connect to the cluster using the multiple IP connection string and load
balancing to connect only to the master server always.
eg.
jdbc:postgresql://192.168.1.100:5432,192.168.1.101:5432/testdb?targetServerType=master

This works fine as long as I shutdown one server and when failover takes
place to the other server. Please note, on proper shutdown of the master
server to failover and continue which the newly promoted standby server to
master. Things work fine.

But, when I try to kill -9 the master server main postgres process. The
client code from java is able to continue to write to the postgres db which
was the old master as there was a connection established to this server and
the connection keeps allowing write to happen as thought the server is still
running. I understand kill -9 is a bad thing. But is it supposed to be
allowed to write with a connection which has been already established though
the servers main process has been killed or crashed whatever and postgres
server on the old master is quite in an inconsistent state? I was able to
write to this server where the main postgres process was killed and the
client process through the java connection was still able to write as though
the server is still properly running without any exceptions or errors. I can
see process that was created for the connection from my java application on
the same server where I killed the main postgres process, which seems to
happily handle the writes and keeps writing to the transaction log/ and even
the database (because, I had a call to a select count(*) from the test table
and I can see the count being increased after each insert).

Once I killall the postgres processes on the server where I specifically
killed the main process, every thing is normal where the client java code
switches to properly write to the newly promoted master.

The same goes if I do a killall postgres to start with instead of a "kill -9
PID" to kill the specific postgres process. If I do a killall postgres then
the proper switching of connection happens and the writes properly go to the
newly promoted master.

If anyone has come across this issue, please respond.

Please note, I understand a kill -9 should have quite weird problems. But is
this problem expected as, with the main postgres process down, I am still
happily able to write to the database which is in an inconsistent state,
behaving as though nothing happened as far as the client connection goes.

Thanks,
-vijay

On Thursday, November 5, 2015 1:11 PM, "vijaysam@mailworks.org" <vijaysam@mailworks.org> wrote:

> Postgres 9.4.1

http://www.postgresql.org/support/versioning/

> when I try to kill -9 the master server main postgres process.
> The client code from java is able to continue to write to the
> postgres db which was the old master as there was a connection
> established to this server and the connection keeps allowing
> write to happen as thought the server is still running.

Before reporting a bug it is a good idea to check whether a fix for
it is available.  Version 9.4.5 includes this:

| * Ensure that the postmaster does not exit until all its child
|   processes are gone, even in an immediate shutdown (Tom Lane)
|
|   Like the previous item, this avoids possible race conditions
|   against a subsequently-started postmaster.


Do you still see the problem when running with fixes for the known
bugs?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

vijaysam@mailworks.org wrote:

> But, when I try to kill -9 the master server main postgres process. The
> client code from java is able to continue to write to the postgres db which
> was the old master as there was a connection established to this server and
> the connection keeps allowing write to happen as thought the server is still
> running. I understand kill -9 is a bad thing.

Good thing that you do.  Don't do it, as it's not a proper shutdown
procedure.  There are three documented signals with varying degrees of
shutdown immediacy: SIGTERM, SIGINT, SIGQUIT.  The postmaster process
will take action to terminate server processes as appropriate if you use
those signals.  SIGKILL is entirely inappropriate.  If you would like to
use SIGKILL, you need to send it to *all* postgres processes, which will
cause a shutdown very similar in nature to SIGQUIT.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Kevin Grittner <kgrittn@ymail.com> writes:
> On Thursday, November 5, 2015 1:11 PM, "vijaysam@mailworks.org" <vijaysam@mailworks.org> wrote:
>> when I try to kill -9 the master server main postgres process.
>> The client code from java is able to continue to write to the
>> postgres db which was the old master as there was a connection
>> established to this server and the connection keeps allowing
>> write to happen as thought the server is still running.

> Before reporting a bug it is a good idea to check whether a fix for
> it is available.

IIUC, the OP is complaining about the fact that backends will keep running
even if the postmaster has died.  That's not a bug, but the designed
behavior.  There's been some recent discussion of changing it, but if it
does change it will only be in HEAD not back branches.

            regards, tom lane

Yes just did a fresh build of 9.4.5 from source and tried the same test.
Seems the problem still exists.

Thanks,
-vijay

On Fri, Nov 6, 2015, at 09:31 PM, Kevin Grittner wrote:
> On Thursday, November 5, 2015 1:11 PM, "vijaysam@mailworks.org"
> <vijaysam@mailworks.org> wrote:
>
> > Postgres 9.4.1
>
> http://www.postgresql.org/support/versioning/
>
> > when I try to kill -9 the master server main postgres process.
> > The client code from java is able to continue to write to the
> > postgres db which was the old master as there was a connection
> > established to this server and the connection keeps allowing
> > write to happen as thought the server is still running.
>
> Before reporting a bug it is a good idea to check whether a fix for
> it is available.  Version 9.4.5 includes this:
>
> | * Ensure that the postmaster does not exit until all its child
> |   processes are gone, even in an immediate shutdown (Tom Lane)
> |
> |   Like the previous item, this avoids possible race conditions
> |   against a subsequently-started postmaster.
>
>
> Do you still see the problem when running with fixes for the known
> bugs?
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

On Sat, Nov 7, 2015 at 7:18 AM, Vijayakumar Krishnan
<vijaysam@mailworks.org> wrote:
> Yes just did a fresh build of 9.4.5 from source and tried the same test.
> Seems the problem still exists.

As Tom mentioned in up thread that is not a bug, it is a design behavior.
There is a recent discussion in community for changing this behavior in [1].
If it gets committed, it will be available from V9.6.

[1]- http://www.postgresql.org/message-id/BF2827DCCE55594C8D7A8F7FFD3AB77159964A51@szxeml521-mbs.china.huawei.com

Regards,
Hari Babu
Fujitsu Australia

OK, thanks for responding. I just wanted to make sure it was a design
behavior.

Thanks,
-vijay

On Sat, Nov 7, 2015, at 03:45 AM, Haribabu Kommi wrote:
> On Sat, Nov 7, 2015 at 7:18 AM, Vijayakumar Krishnan
> <vijaysam@mailworks.org> wrote:
> > Yes just did a fresh build of 9.4.5 from source and tried the same test.
> > Seems the problem still exists.
>
> As Tom mentioned in up thread that is not a bug, it is a design behavior.
> There is a recent discussion in community for changing this behavior in
> [1].
> If it gets committed, it will be available from V9.6.
>
> [1]-
> http://www.postgresql.org/message-id/BF2827DCCE55594C8D7A8F7FFD3AB77159964A51@szxeml521-mbs.china.huawei.com
>
> Regards,
> Hari Babu
> Fujitsu Australia