What happens when you KILL any of the mandatory Utility Process in PostgreSQL?

 

What happens when you KILL any of the mandatory Utility Process in PostgreSQL?  

In my scenario here I have killed a “background writer” utility process in order to understand how the database behaves in case if we KILL any of the mandatory Utility processes.

If you are an Oracle DBA and reading this post then you must be aware about the fact that in oracle if we kill any of the mandatory background processes then it will halt the database and we need to manually start the database and then SMON background process comes into the picture which is responsible to perform instance recovery.

However the good thing about the PostgreSQL is that the architecture is designed in a such a way that even in worst case if someone kills the mandatory utility process the “Postmaster” process signals the other processes to roll back the current transaction and exit after that it will start the automatic recovery process and then start the database clusters to accept the new connections. So here no human intervention is required and everything is taken care by the Postmaster process internally.

Following message will be reported in PostgreSQL logfile :

==========================

2024-08-06 03:37:17.595 EDT [3406] LOG: background writer process (PID 3408) was terminated by signal 9: Killed

2024-08-06 03:37:17.595 EDT [3406] LOG: terminating any other active server processes

2024-08-06 03:37:17.597 EDT [3412] WARNING: terminating connection because of crash of another server process

2024-08-06 03:37:17.597 EDT [3412] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

2024-08-06 03:37:17.597 EDT [3412] HINT: In a moment you should be able to reconnect to the database and repeat your command.

2024-08-06 03:37:17.759 EDT [3406] LOG: all server processes terminated; reinitializing

2024-08-06 03:37:17.877 EDT [3578] LOG: database system was interrupted; last known up at 2024-08-06 03:34:35 EDT

2024-08-06 03:37:19.265 EDT [3578] LOG: database system was not properly shut down; automatic recovery in progress

2024-08-06 03:37:19.283 EDT [3578] LOG: redo starts at 0/1690998

2024-08-06 03:37:19.283 EDT [3578] LOG: invalid record length at 0/16909D0: wanted 24, got 0

2024-08-06 03:37:19.283 EDT [3578] LOG: redo done at 0/1690998

2024-08-06 03:37:19.350 EDT [3406] LOG: database system is ready to accept connections

==========================

Now one more question should come here is that What will happen to my existing active database connection?

So, in that case, the answer is even though the Postmaster process killed all the database connections but once the cluster is up it will re-established all the database connections and you will be able to reconnect to the same connection and repeat your command.

You will get the following message in the existing database connection terminal :

==========================

WARNING: terminating connection because of crash of another server process

DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

HINT: In a moment you should be able to reconnect to the database and repeat your command.

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.

==========================