Re: Are all unlogged tables in any case truncated after a server-crash? - Mailing list pgsql-general

From David G. Johnston
Subject Re: Are all unlogged tables in any case truncated after a server-crash?
Date
Msg-id CAKFQuwZCCbcPTYw+87zDcv99dJjNq6M687LPhUT3MVm31KQ4yw@mail.gmail.com
Whole thread Raw
In response to Are all unlogged tables in any case truncated after a server-crash?  (sch8el@posteo.de)
Responses Re: Are all unlogged tables in any case truncated after a server-crash?
List pgsql-general
On Thu, Nov 11, 2021 at 11:39 AM <sch8el@posteo.de> wrote:
After
that all unlogged tables remain completely unchanged (no
DML-/DDL-Statements). Hence all of my huge unlogged, "static" tables get
never "unclean" and should not be truncated after a server crash.

The server cannot make this assumption so it truncates unlogged relations upon an unclean shutdown/crash because it has no WAL with which to ensure a proper restoration.

BTW, if I set all unlogged tables to logged after bulk load, it takes
additional 1.5 hours, mainly because of re-indexing, I suppose.

More likely it is writing the entire table, and all of its indexes, to WAL.

I assume
that a restart of the database after a server crash takes another 1.5
hours (reading from WAL) until the database is up and running.

That would be incorrect.  See "CHECKPOINT".


Therefore I am seeking a strategy, to not tagging those tables as
"unclean" and not truncating all unlogged tables on server restart.


There is no middle ground that I am aware of.  Either the contents of the table are in WAL ,or they are not.  If not, they can be lost upon an unclean shutdown.  For manually initiated shutdowns you do have the option to do so cleanly.

This topic (unlogged optimizations) does draw quite a bit of attention every year but so far the problem of proving to the system that the physical file on disk is a truly accurate representation of the post-crash relation is yet unsolved.

David J.

pgsql-general by date:

Previous
From: sch8el@posteo.de
Date:
Subject: Are all unlogged tables in any case truncated after a server-crash?
Next
From: Yessica Brinkmann
Date:
Subject: Pg_hba.conf problem after unexpected IP change