pg_dumpBack up a PostgreSQL database consistently
pg_dump exports one PostgreSQL database as a consistent snapshot, either as a plain SQL script or in the compressed custom and directory formats that pg_restore can restore selectively and in parallel. It never blocks normal reads and writes.
pg_dump [CONNECTION] [-F p|c|d|t] [-f FILE] [-j N] [-t TABLE] [-n SCHEMA] DBNAMEYou want a readable copy of the shop database before deploying a migration.
pg_dumpPostgreSQL's logical backup client. Its major version must be at least the server's.
pg_dump opens a normal connection and starts a transaction at REPEATABLE READ isolation. Thanks to MVCC, every query in that transaction sees the database exactly as it was when the snapshot was taken, while other sessions keep inserting and updating. It takes an ACCESS SHARE lock on each table it dumps: this doesn't block reads or writes, but it does block ALTER TABLE, DROP and TRUNCATE, and a migration waiting for that lock makes everything behind it wait too. It reads the system catalogs to rebuild the schema (tables, sequences, indexes, constraints, functions, grants) and streams each table's rows with COPY ... TO STDOUT. In plain format that becomes a SQL script for psql. Custom format compresses each table's data and writes a table of contents, so pg_restore can reorder, filter and restore with several jobs. Directory format writes one file per table and can dump in parallel (-j): the workers import the leader's snapshot so they all see the same data. Roles and tablespaces are cluster-wide and need pg_dumpall --globals-only.
pg_dump reads a consistent MVCC snapshot while the application keeps writing; only schema changes have to wait.
At 02:00 cron runs pg_dump, which connects like any other client.
-Fp / -Fc / -Fd / -FtPlain SQL / custom / directory / tar format-f FILEOutput file (or directory for -Fd)-j NParallel dump with N jobs (directory format only)-h, -p, -U, -dHost, port, user, database (or use PG* env vars / a URI)-n SCHEMA / -NOnly / exclude a schema-t TABLE / -TOnly / exclude a table (patterns allowed)-s / --schema-onlyDefinitions only-a / --data-onlyData only-O / --no-ownerDon't emit ALTER ... OWNER (restore as another user)-Z LEVEL / --compressCompression level or method (gzip, lz4, zstd in 16+)-vVerbose progress on stderrpg_restore -l / -j / --cleanList TOC / parallel restore / drop objects firstUsing an older pg_dump against a newer serverIt refuses ('server version mismatch') because it can't understand newer catalogs.
Use pg_dump from the server's major version or newerBacking up only with pg_dump and forgetting rolesRoles, passwords and tablespaces are cluster-wide; restores on a new server fail on owners and grants.
pg_dumpall --globals-only > globals.sql alongside each pg_dumpPlain-format dumps for large databasesNo parallel or selective restore; psql replays everything on one connection.
pg_dump -Fd -j 4 (or -Fc) and pg_restore -jTreating pg_dump as point-in-time recoveryA dump is a snapshot of one moment; everything after it is lost.
Base backups plus WAL archiving (pg_basebackup, pgBackRest) or managed PITRTry these on a Linux machine of your own. Each task has a hint and a solution.
Write the command to dump database 'shop' in custom format to shop.dump, connecting to host 10.0.2.31 as user backup. (No PostgreSQL in the sandbox.)
Write the commands to restore shop.dump into a new database 'shop_test' with 4 parallel jobs, owned by the restoring user.
Write the command that dumps only the schema of table 'orders' as SQL.
Create a ~/.pgpass entry for user backup on 10.0.2.31, any database, with the correct permissions.
pg_dumpBack up a PostgreSQL database consistently
pg_dump exports one PostgreSQL database as a consistent snapshot, either as a plain SQL script or in the compressed custom and directory formats that pg_restore can restore selectively and in parallel. It never blocks normal reads and writes.
pg_dump [CONNECTION] [-F p|c|d|t] [-f FILE] [-j N] [-t TABLE] [-n SCHEMA] DBNAMEYou want a readable copy of the shop database before deploying a migration.
pg_dumpPostgreSQL's logical backup client. Its major version must be at least the server's.
pg_dump opens a normal connection and starts a transaction at REPEATABLE READ isolation. Thanks to MVCC, every query in that transaction sees the database exactly as it was when the snapshot was taken, while other sessions keep inserting and updating. It takes an ACCESS SHARE lock on each table it dumps: this doesn't block reads or writes, but it does block ALTER TABLE, DROP and TRUNCATE, and a migration waiting for that lock makes everything behind it wait too. It reads the system catalogs to rebuild the schema (tables, sequences, indexes, constraints, functions, grants) and streams each table's rows with COPY ... TO STDOUT. In plain format that becomes a SQL script for psql. Custom format compresses each table's data and writes a table of contents, so pg_restore can reorder, filter and restore with several jobs. Directory format writes one file per table and can dump in parallel (-j): the workers import the leader's snapshot so they all see the same data. Roles and tablespaces are cluster-wide and need pg_dumpall --globals-only.
pg_dump reads a consistent MVCC snapshot while the application keeps writing; only schema changes have to wait.
At 02:00 cron runs pg_dump, which connects like any other client.
-Fp / -Fc / -Fd / -FtPlain SQL / custom / directory / tar format-f FILEOutput file (or directory for -Fd)-j NParallel dump with N jobs (directory format only)-h, -p, -U, -dHost, port, user, database (or use PG* env vars / a URI)-n SCHEMA / -NOnly / exclude a schema-t TABLE / -TOnly / exclude a table (patterns allowed)-s / --schema-onlyDefinitions only-a / --data-onlyData only-O / --no-ownerDon't emit ALTER ... OWNER (restore as another user)-Z LEVEL / --compressCompression level or method (gzip, lz4, zstd in 16+)-vVerbose progress on stderrpg_restore -l / -j / --cleanList TOC / parallel restore / drop objects firstUsing an older pg_dump against a newer serverIt refuses ('server version mismatch') because it can't understand newer catalogs.
Use pg_dump from the server's major version or newerBacking up only with pg_dump and forgetting rolesRoles, passwords and tablespaces are cluster-wide; restores on a new server fail on owners and grants.
pg_dumpall --globals-only > globals.sql alongside each pg_dumpPlain-format dumps for large databasesNo parallel or selective restore; psql replays everything on one connection.
pg_dump -Fd -j 4 (or -Fc) and pg_restore -jTreating pg_dump as point-in-time recoveryA dump is a snapshot of one moment; everything after it is lost.
Base backups plus WAL archiving (pg_basebackup, pgBackRest) or managed PITRTry these on a Linux machine of your own. Each task has a hint and a solution.
Write the command to dump database 'shop' in custom format to shop.dump, connecting to host 10.0.2.31 as user backup. (No PostgreSQL in the sandbox.)
Write the commands to restore shop.dump into a new database 'shop_test' with 4 parallel jobs, owned by the restoring user.
Write the command that dumps only the schema of table 'orders' as SQL.
Create a ~/.pgpass entry for user backup on 10.0.2.31, any database, with the correct permissions.