Are all unlogged tables in any case truncated after a server-crash?

From: sch8el(at)posteo(dot)de
To: pgsql-general(at)postgresql(dot)org
Subject: Are all unlogged tables in any case truncated after a server-crash?
Date: 2021-11-11 18:39:49
Message-ID: c9f8b3a5-eb45-d3f6-9fd6-2e1d032eea12@posteo.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi everyone,

every few weeks I use Postgres ability, to import huge data sets very
fast by means of "unlogged tables". The bulk load (consisting of plenty
"copy"- & DML-Stmts) and the spatial index creation afterwards, takes
about 5 hours on a proper server  (pg12.7 & PostGIS-Extension). 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.

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. 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.

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

Cheers and regards.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2021-11-11 21:10:47 Re: Are all unlogged tables in any case truncated after a server-crash?
Previous Message Laurenz Albe 2021-11-11 16:06:36 Re: Why in pgAdmin an active session is marked/highlighted in Red