What will happen to dirty data, if PostgreSQL DB or server abnormally shutdown?

Question

Suppose my PostgreSQL instance suddenly abnormally stopped and data is dirty and not saved in RAM or WAL. In that case what will happen ?

Where from I can recover that data? and also if server also shutdown in that case what will happen to dirty data?

Is there any separate file where that dirty data stored to recover?

• Charlieface: Does this answer your question? PostgreSQL crash recovery

Answer 1

data is dirty and not saved in RAM or WAL.

Where would that data be if not in those locations?

Within the Postgres executable there is a strictly defined sequence of when data in memory, WAL and disk files are written. This ensures consistent recovery after system crashes. Transactions which have been committed still show in the data after recovery. Transactions which made changes but were not committed are rolled back. Whatever "dirtyness" they may have caused is wiped clean.

You do not need to do anything manually to achieve this. It is built into the executable.

Answer 2

Dirty data from your transaction are "saved" in RAM (but that will not survive a shutdown), and they may also be saved in the WAL. They may even be written to the data files and persisted to disk.

However, all that does not matter. As long as the transaction was not committed, the transaction is not marked as committed in the commit log, and all the data are invisible. If any of the data from your aborted transaction made it to data files before the shutdown, autovacuum will remove them at some later point.

Answer 3

The PosgreSQL documentation has a whole section on reliability and the Write Ahead Log. It's quite detailed as to how it works, and how data is managed. Once it's committed and written to the WAL, it can be considered to be in the database. If it isn't committed to the WAL, it won't be in the database. There is no more to it than that.