pg_dump and pg_restore

Full-fidelity PostgreSQL backups with a pretty face!

While Querious can export to SQL from PostgreSQL database itself, you can also use the full power of PostgreSQL's own pg_dump and pg_restore for perfect backups, and for moving a database between servers. The pg_dump archive formats preserve things a plain SQL export can't.

The benefit of using pg_dump/pg_restore in Querious, is that it's a lot clearer and easier to see the options than the command line, but you still the full exactness of their output and behavior.

Finding the Tools #

The pg_dump and pg_restore tools come with a PostgreSQL installation, not with Querious. Querious scans for installations (Postgres.app, Homebrew, and the usual locations) and lists them in the tools popup. Choose the newest or correct version for your server for Querious to use.

About the Options #

Querious doesn't exactly mimic every command line option of the tools because, frankly, they're awkward. They're powerful, but awkward and definitely oriented towards the command line. Instead, Querious offers the vast majority of the capabilities, with a much easier to understand interface.

Check out the pg_dump/pg_restore documentation for the full capabilities, and you'll see how Querious makes them much more palatable.

Setting Up an Export #

Use the selector at the top of the window to choose the scope of the export: Entire Database, Schemas, or Tables. This decides what the rest of the window shows.
Choose the contents: Structure + Data, Structure Only, or Data Only. Options that don't apply to your choice are turned off and reset to their defaults.
Choose what to dump. For Schemas, check the schemas to include. For Tables, pick a schema and check its tables. Entire Database has nothing to pick.
Set the options on the left. See Export Formats and pg_dump Options below.

The export can't continue until the tools are chosen and something is selected: at least one schema when dumping Schemas, or at least one table when dumping Tables.

Export Formats #

The Output Format determines the kind of file, and the available options follow from it.

Format	Produces	Restore with
Plain	A `.sql` file.	Any SQL client. Readable and diffable, but often not the most optimal choice.
Custom	A compressed `.dump` archive.	`pg_restore`. The default choice for backups: compressed, and you can choose exactly what in the dump to restore.
Directory	A directory of files.	`pg_restore`. The only format supporting parallel restore.
Tar	A `.tar` archive.	`pg_restore`.

Only Custom and Directory supports compressing the output files.

Only the Plain format can include CREATE DATABASE, DROP, or trigger-disabling statements in the file itself. (For archives those are decided at restore time, which is part of why they're more flexible.)

pg_dump Options #

Option	Effect
Schema only / Data only	Structure without rows, or rows without structure.
Section — pre-data, data, post-data	Dumps one phase only. Post-data is indexes and constraints, so you can restore data first and build indexes afterwards.
Schema and table selection	Include or exclude schemas and tables by pattern or explicit list, and exclude the data of particular tables while keeping their structure.
Large objects	Explicitly included or excluded, rather than `pg_dump`'s default (included unless the dump is selective).
`CREATE DATABASE`, `DROP` statements, `IF EXISTS`	Plain format only.
Exclude ownership, exclude privileges	Leaves out `OWNER TO` and `GRANT`. Essential when restoring to a server whose roles don't match the source's.
INSERT statements	Writes `INSERT` instead of `COPY`, optionally with column names, a row count per statement, or `ON CONFLICT DO NOTHING`. Much slower to restore, but portable and tolerant of individual row failures.
Exclude comments, publications, subscriptions, security labels, tablespaces, table access methods, TOAST compression	Leaves out metadata that often doesn't apply on the destination.

Importing an Archive #

Importing a pg_dump archive runs pg_restore. Querious reads the file's header to identify the format and its table of contents so you can see what it holds and choose part of it.

Option	Effect
Target database	Restore into the current database, or name another.
Contents	Structure and data, structure only, or data only.
Table of contents selection	Restore everything, or choose specific schemas or archive entries.
Drop objects before creating, IF EXISTS	Clears existing objects first. This destroys data in the target.
Exclude ownership, exclude privileges	As for export, and usually needed for the same reason.
Single transaction	Restores everything or nothing, but holds one transaction for the whole restore.
Stop on error	On by default. Turn it off to continue past failures, such as a selective restore into a database that already has some of the objects.
Disable triggers	Suppresses triggers during a data-only restore so restored rows don't re-fire application logic.
Parallel jobs	Restores several entries at once, for a considerable speedup on large databases. Directory and Custom archives only.

A plain .sql dump isn't a pg_restore archive. Use Import for those.

Recipes #

To take a backup you can restore selectively

Export with Output Format set to Custom, compression on, and everything included.
Later, restoring a single table is a matter of choosing that entry in the table of contents.

To copy production to a development machine

Export with Custom format, excluding ownership and privileges because the development server lacks production's roles.
Exclude the data of large tables you don't need locally, keeping their structure.
Import the archive against the development connection and choose the target database.
Turn on Parallel jobs to speed up the restore.

To restore one table from a backup

Import the archive and let Querious read its table of contents.
Switch the selection from everything to specific entries and choose that table.
Choose structure, data, or both. If the live table still exists, restore into a scratch database first and compare before replacing it.