CYBERTEC PostgreSQL Logo

PostgreSQL shutdown modes

12.2013 / Category: / Tags:

When doing trainings or consulting sessions I have the feeling that most people are not fully aware of PostgreSQL's shutdown modes. However, knowing about this functionality is pretty important. Without this information sysadmins may cause unintended damage and issues. This can easily be avoided.

PostgreSQL has 3 shutdown modes

When doing pg_ctl --help the system will reveal its secrets:

What we see here is that there are three ways to shut down a PostgreSQL database.

smart - the default

If you run pg_ctl -D ... stop you will do a so called “smart” shutdown. What does it mean? Well, PostgreSQL will shut down as soon as all open connections have been terminated. This is very nice because no queries will be interrupted, and nobody will lose his database connection.

The downside here is: What if database connections are never terminated because there is some sort of connection pool around or so?

In this case it is necessary to shut the PostgreSQL server down in a more aggressive way

fast - usually used in init scripts

“fast” will terminate all open connections in a “nice” way and therefore terminate the database a lot faster than in “smart” mode. The main advantage here is that you can terminate the database server a lot faster than normal. This is also the reason why “fast” is the default mode used by most binary packages.

In most cases “-m fast” is the desired method to perform a shutdown.

immediate - be careful

If “fast” is still not fast enough, you can rely on “immediate” (which is the cruellest method to do a shutdown). As stated before, “fast” kills all open connections in a “nice” way. In this context “nice” means that a process will receive a signal, which can be caught by the process. The process can then perform a proper cleanup and exit in a proper way.

In case of immediate a process will receive a harsher signal and will not be able to respond anymore. In short: It is shot in the head. The consequences of this type of shutdown are: PostgreSQL is not able to finish its disk I/O and therefore has to do a recovery when it comes back up. This will work but it is not the nicest way of doing things. Therefore we don't recommend this type of shutdown.

kill - 9 is not your friend

Sometimes it is not about shutting down an entire database instance. In many cases users just want to get rid of one single database connection. Of course PostgreSQL provides ways to do that cleanly. However, some rookie administrators tend to use brute force methods to kill a connection: “kill -9”.

But, what are the consequences of a hard kill? Consider the following scenario: Let us imagine a database connection is about to modify some shared memory segment. What happens if it dies somewhere during this operation because it is killed? Well, nobody knows what has been changed in shared memory - in fact, there is a fair chance of spreading some corruption due to some half-completed write operation. To avoid this, PostgreSQL will instantly throw out everybody and enter recovery mode to sort out potential problems.

From a user's point of view: All database connections are lost and PostgreSQL is doing something strange. Most users will not understand what is about to happen. We recommend avoiding “kill -9” unless you know precisely what you are doing!

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

4 responses to “PostgreSQL shutdown modes”

  1. immediate shutdown have another unpleasant consequence. any unlogged table is zeroed during the crash recovery.
    I've seen people using the unlogged feature because is faster, not fully aware of the consequence of an unclean shutdown.

    • yes, this is not what it has been meant for.
      i have seen this as well. it seems people like to lose data as fast as possible.

  2. Hello, Master! "terminate all open connections in a “nice” way", by using the fast mode means that it waits for the ongoing transactions to commit or rollback them immediately?

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram