Is a pg_dump DB dump 'at-that-time' dump?

Question

I know PostgresQL pg_dump guarantees consistency even if DB is modified while dump is going on, but I want to know if the final dump contains any data modified/inserted after the dump started.

To elaborate:

1. Time t: I start pg_dump
2. Time t+1: There are updates/inserts to the database
3. Time t+x: pg_dump completes.

I don't want any of the modifications done in the 'x' period to be present in the dump file.

Is this possible?

Maytime

If in doubt read the manual: "It makes consistent backups even if the database is being used concurrently" – Atalante

Answer 1

This was answered in another SO question: Doing pg_dump while still many active transaction.

The short answer is yes: pg_dump opens a transaction and no changes made to the database will be visible to it during the dump, so pg_dump snapshots are atomic and are a consistent snapshot (iow, no foreign key or other constraints will be violated in the snapshot).

HTH

Frock