public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: PetSerAl <[email protected]>
To: [email protected]
Subject: Re: About backups
Date: Thu, 29 Jan 2026 10:37:31 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKygsHTWYHSE+UkNb0dzRXOOsm5tu=BZJ0SH0qAuCvSw2U8GjQ@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAKygsHTWYHSE+UkNb0dzRXOOsm5tu=BZJ0SH0qAuCvSw2U8GjQ@mail.gmail.com>



On 1/29/26 9:58 AM, PetSerAl wrote:
> On Thu, Jan 28, 2026 at 8:32 PM Guillaume Lelarge
> <[email protected]> wrote:
>> Doesn't matter at all. You'll get a consistent backup.
> 
> But be aware of not MVCC-safe commands like TRUNCATE.
> If transaction with such command intersect with beginning of backup,
> then it may be not consistent.
> It will see effects of TRUNCATE, but will not see effects of other
> commands in such transaction.
> 
> 

 From here:

https://www.postgresql.org/docs/current/mvcc-caveats.html

"Some DDL commands, currently only TRUNCATE and the table-rewriting 
forms of ALTER TABLE, are not MVCC-safe. This means that after the 
truncation or rewrite commits, the table will appear empty to concurrent 
transactions, if they are using a snapshot taken before the DDL command 
committed. This will only be an issue for a transaction that did not 
access the table in question before the DDL command started — any 
transaction that has done so would hold at least an ACCESS SHARE table 
lock, which would block the DDL command until that transaction completes."

And the more general case described here:

https://www.postgresql.org/message-id/[email protected]


"

 > My questions are: can making DDL changes during a dump cause this 
error?  Are the queries used by pg_dump transactionally consistent, i.e. 
do they run in a transaction and get a single view of the database 
system catalogs?  Other than finer coordination of jobs, how can this 
situation be avoided?

...

The window for this sort of thing isn't very large, because the first
thing pg_dump does is acquire AccessShareLock on every table it intends
to dump, and past that point it won't be possible for anyone to modify
the table's DDL.  But it can happen.

...
"

There is a small window for this happening in any case. Read the rest of 
the case for suggestions to mitigate.

view thread (10+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: About backups
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox