After I restart PostgreSQL all my tables are empty (zero rows)

Question

Using Mac OS X 10.10 (Yosemite) and Homebrew (0.9.5), I've installed PostgreSQL 9.4.1. Basically all current versions as of the time of this posting...

I've successfully managed to import a brutally large database (56M records - 15 hours to import), and written an app to work with it locally. I found an app, pgAdmin3, that lets me do all sorts of admin tasks with the GUI. All is good.

Every time I restart the computer, once it was due to a kernel panic from a USB firewire audio interface, once was from a power failure, and two were from user initiated restarts - each time after reboot, the database is empty. Database users/roles are still there, tables and relations are there, however, all the tables are empty. 0 records. Nothing. pgAdmin3 even shows that the table has space allocated for some number of millions of records, with an "undefined" count. Refreshing the count, reveals 0 records. Browsing the data reveals 0 records. My Mac application (using libpq) connects to the database successfully, and gets 0 results from any/all of its SELECT statements. Is it redundant to mention that each re-import of the database takes 15 hours? (rhetorical question)

Any and all suggestions appreciated.

Mac OS X 10.0.2 (Yosemite) Homebrew 0.9.5 PostgreSQL 9.4.1 on x86_64-apple-darwin14.1.0, compiled by Apple LLVM version 6.0 (clang-600.0.56) (based on LLVM 3.5svn), 64-bit

Answer 1

The behaviour you describe would be consistent with your tables being UNLOGGED.

An unlogged table is halfway between a normal table and a temp table. It's like a normal table in that:

• It's usable from multiple sessions
• It isn't cleared when a session exits
• It's stored in the main tablespace, not the temp tablespace

but it's like a temp table in that:

• It isn't crash safe. The contents of UNLOGGED tables are preserved when PostgreSQL shuts down and restarts, but only if it's shut down cleanly. On unclean shutdown, UNLOGGED tables may be truncated, so they still exist but contain zero rows.

• Writing to it doesn't produce write-ahead log records in pg_xlog

• It can't be replicated to other nodes over streaming or log-based replication

• Writing to an unlogged table causes about half the disk I/O as a regular table

See the manual.

At a guess, your tables are defined as UNLOGGED and you aren't shutting PostgreSQL down cleanly when your Mac shuts down. So it's truncating the unlogged tables, since it can't be sure they're in a consistent state.

Shut PostgreSQL down cleanly (e.g. pg_ctl -m fast -w stop, or a launchd configuration that makes sue to do that). Or don't use unlogged tables. Or be willing to re-load your data when you restart.

Since you wondered what unlogged tables are for:

• They're great for data loading and transform, where you want to load a bunch of data, then extract and transform it for loading into other tables.

• Reporting tables

• Work queues and task queues for anything where the whole system can just be restarted, so you don't need to make the lists of in-progress tasks etc crash-safe