A Dangerous Game in PostgreSQL: Unlogged Table
There is a tricky feature in PostgreSQL: Unlogged Table
I’ll quote the description of the Unlogged Table from PostgreSQL.org:
Data written to unlogged tables is not written to the write-ahead log, which makes them considerably faster than ordinary tables. However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown.
Sounds scary, right?
Let’s see if it’s really terrifying
I created a PostgreSQL 9.6 instance on AWS (RDS) and created two tables:
The user_messages table is a standard table. The user_logs table is an unlogged table.
I added 1 million records into both tables.
I initiated the upgrade from 9.6 to 13.4 via RDS Management Console:
When the upgrade finished, I logged into the PostgreSQL instance:
Whoops… The data in the user_logs table is gone.
Let’s take a look at the logs:
Sounds like something went wrong during the upgrade. It happens, you know. PostgreSQL managed to keep the data of the user_messages table. However, the data in the user_logs table is gone.
Let’s recall the second sentence from the description of the Unlogged Table:
However, they are not crash-safe: an unlogged table is automatically truncated after a crash or unclean shutdown.
Avoid using the Unlogged Tables to avoid data loss.