PostgreSQL 11 Windows 10 Truncates All Tables on Shutdown

Question

i have a new Windows 10 laptop with beefed up processors, 16GB RAM and 2TB SSD storage. I have installed PostgreSQL 11.2 on this laptop, and use it to compile large datasets from various sources and run various analyses and reports against this data.

I had been running PostgreSQL 9.x on my older Windows 7 laptop for years with no real issues. But on my new laptop, it seems that every time I shut down the laptop and go back into the database, every one of the tables have been truncated. All of the tables are still there, but just plumb empty.

Is there a configuration setting that needs to be changed somewhere?

• SQLRaptor: Does the client tool that you use have 'autocommit' setting turned off by any chance?
• Michael Sheaver: I mostly use Jetbrains Datagrip for my day-to-day work so let me look at the settings there tomorrow.
• jjanes: Are all of your tables declared as "unlogged" for some reason in the new database?
• Patrick Mevzek: Are you using temporary tables? How are you tables created?
• Michael Sheaver: jjanes & Patrick Mevzek, I suspect you both might have hit the button on the head here. When I was using my older, much slower laptop, I discovered that creating all my tables unlogged made a HUGE improvement in the performance of subsequent operations. I never dreamed that this would lead to the current unintended consequence. I will go in, remove the unlogged qualifier from all the table creation code, and test it out. I will definitely report back on the results. Are there any other "gotchas" I need to look out for?

Answer 1

Solution

Remove the UNLOGGED option from all table create commands

Result

The data no longer disappears during system shutdown or reboot

I had been using CREATE UNLOGGED for a long time, as I found that in earlier versions of PostgreSQL, it made a HUGE impact in the execution time of my SQL scripts. In one case, it dropped from 15 minutes to around 45 seconds. Every once in a great while, like once or twice a year, I would find the tables had emptied out. I would just go ahead and reload the datasets, never really thinking more about it.

But when I migrated my datasets to PostgreSQL 11.x, I found that the tables emptied out every time I shut down the laptop. Thanks to the comments above, I learned that the UNLOGGED option was indeed the culprit here. I removed that option from all the table creation commands, and the data no longer disappears during reboot.

Added Benefit

I did not find any appreciable decline in performance while doing this i PostgreSQL 11.x!