5

I've been wondering for the longest time if a sudo reboot with a running Postgres (namely, 9.2.4) server can (or definitely will) lead to data loss or other problems (e.g., inability to start again the server upon system boot). Or, does reboot send the proper signals to processes that allow them to properly be shut down (including, for instance, allowing transactions to complete, etc.).

1
  • 2
    If you boot, while someone is changing your database, and they are not using transactions properly, you could data might be invalid. But it wouldn't really be corrupt really.
    – Zoredache
    Jun 2, 2013 at 21:23

3 Answers 3

9

If your server and hardware are properly configured you will not lose data even if you press the reset button, yank the power cord, or trigger a kernel panic. PostgreSQL is crash safe because of its write-ahead logging.

The only person who got this vaguely right is "Zoredache" in the comments.

You will only lose data if:

  • You've configured PostgreSQL not to be crash safe by setting fsync=off;
  • You're using UNLOGGED tables, which are not crash-safe (and in this case you'll only lose the data in the unlogged tables);
  • The underlying storage system isn't crash safe or disregards fsync, like many cheap consumer SSDs that do write-back caching to volatile cache without proper backup power;

I wrote a lot more on this in a blog post a few months ago.

Even if you're using a cheap SSD, you still won't generally lose data on a sudden reboot, only when your system actually loses power. I've seen a few systems that power-cycle the disks on reset, though, and these systems would get data loss if using cheap SSDs.

A "clean" shutdown is pretty much optional for PostgreSQL; the only downsides of a sudden reboot are that the database might take a bit longer to start up due to the time required to apply write-ahead-logs during recovery, and that (per the documentation) UNLOGGED tables will be truncated.

Even in a so-called "clean" shutdown, most init scripts will only wait for a limited time for the server to shut down. Most init scripts use "fast" shutdown mode, which will abort current transactions, refuse new sessions, and quickly but cleanly shut down the server. They'll usually time out if that takes too long and just shut down anyway, effectively relying on PostgreSQL's crash safety.

If you want to allow current transactions to finish you need to do a "smart" shutdown manually before shutting your system down, or modify your init scripts to use it. Smart shutdown isn't always very useful because one long running or abandoned connection can stop the whole server shutting down indefinitely, leaving it sitting there refusing all connections. It's useful as a first-try that you let run for a minute or before doing a fast shutdown.

Crash safety is no excuse for failing to take backups - and test them.

7
  • Hardware is scarier than we like to think. Perhaps battery backed cache is good enough, but anything less, well hardware lies. Jun 3, 2013 at 3:29
  • 2
    @RonaldPottol Agreed. I'm a big fan of plug-pull testing, which is exactly what it sounds like. Put the machine under load, yank the power, reboot and repeat. Jun 3, 2013 at 3:50
  • @Wow - this, combined with the blog post linked herein, makes the best answer I've ever received here.
    – orokusaki
    Jun 3, 2013 at 13:15
  • Yeah, there was a discussion on the Linux kernel mailing list about what sync meant. What app people think it means, what it means to kernel people, and what the hardware actually does can be three very different things. At least the Postgres people have shown they have cared about this from day one. Jun 4, 2013 at 21:56
  • @RonaldPottol Sounds interesting - got a link? Jun 4, 2013 at 23:08
2

It should gracefully shut down it and all running services. Take a backup and test!

3
  • Assuming it was properly installed :) Jun 2, 2013 at 16:07
  • @SpacemanSpiff - I compiled from source, but with all ./configure options left as defaults. I ran make check as well, and all tests passed. Does that count, or would there be an extra step in there to ensure this sort of functionality?
    – orokusaki
    Jun 2, 2013 at 16:21
  • 1
    It doesn't need to gracefully shut down. PostgreSQL is crash-safe. Jun 3, 2013 at 2:24
1

reboot is a graceful shutdown. reboot -f / reboot --force is the data killer.

2
  • 1
    This is incorrect - PostgreSQL will survive a force reboot, kernel panic, hard reset or power loss without data loss. The only circumstances where you might lose data are where you disabled PostgreSQL's data protection with settings like fsync=off, you're using volatile write-back caching on an SSD without a decent backup capacitor, you're using a non-journalling filesystem and you have file system metadata issues, etc. Jun 3, 2013 at 2:23
  • @CraigRinger Incorrect is only your understanding of my answer which is a general answer about reboot not specific about a certain application. That reboot even with dangerous parameters does not force an application to lose data (no matter which precautions the application takes) should be really obvious. The question was not "Does PostgreSQL data integrity depend on the shutdown procedure being used?". Jun 3, 2013 at 11:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .