Feedback-ID: i76984098:Fastmail
Message-ID: <b58ae84c-d040-49cb-bdd4-066558f47cb7@aklaver.com>
Date: Thu, 29 Jan 2026 10:37:31 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: About backups
To: PetSerAl <petseral@gmail.com>, pgsql-general@lists.postgresql.org
References: <1730736265.4259921.1769443263077.ref@mail.yahoo.com>
 <1730736265.4259921.1769443263077@mail.yahoo.com>
 <f88a803f-2954-44c5-bd29-e7f1dc5f9d02@aklaver.com>
 <2097370962.4296686.1769449473672@mail.yahoo.com>
 <5efd76b75e527a6558dd69ba4364699f256a813a.camel@tpg.com.au>
 <259609552.3013229.1769632103875@mail.yahoo.com>
 <62048030-5073-4bfc-9565-f8af8084bf6c@dalibo.com>
 <CAKygsHTWYHSE+UkNb0dzRXOOsm5tu=BZJ0SH0qAuCvSw2U8GjQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAKygsHTWYHSE+UkNb0dzRXOOsm5tu=BZJ0SH0qAuCvSw2U8GjQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/b58ae84c-d040-49cb-bdd4-066558f47cb7%40aklaver.com>
Precedence: bulk


On 1/29/26 9:58 AM, PetSerAl wrote:
> On Thu, Jan 28, 2026 at 8:32 PM Guillaume Lelarge
> <guillaume.lelarge@dalibo.com> 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/14781.1266626391@sss.pgh.pa.us


"

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