How to dump a Postgres database excluding one specific table?
I’d like to use pg_dump to backup postgres database content. I only want to ignore one specific table containing cached data of several hundred GB. How could I achieve this with pg_dump? According to the docs, there is an option to –exclude-table which excludes tables from the dump by matching on a pattern (i.e. it allows wildcards):
When to dump object identifiers in PostgreSQL?
Dump object identifiers ( OID s) as part of the data for every table. Use this option if your application references the OID columns in some way (e.g., in a foreign key constraint). Otherwise, this option should not be used. Do not output commands to set ownership of objects to match the original database.
What causes PG _ dump to fail in PostgreSQL?
pg_dump will open njobs + 1 connections to the database, so make sure your max_connections setting is high enough to accommodate all connections. Requesting exclusive locks on database objects while running a parallel dump could cause the dump to fail.
What happens when n is specified in PG _ dump?
When -n is specified, pg_dump makes no attempt to dump any other database objects that the selected schema (s) might depend upon. Therefore, there is no guarantee that the results of a specific-schema dump can be successfully restored by themselves into a clean database. Non-schema objects such as blobs are not dumped when -n is specified.
How can I achieve this with PG _ dump?
How could I achieve this with pg_dump? According to the docs, there is an option to –exclude-table which excludes tables from the dump by matching on a pattern (i.e. it allows wildcards): -T table –exclude-table=table Do not dump any tables matching the table pattern.
Dump object identifiers ( OID s) as part of the data for every table. Use this option if your application references the OID columns in some way (e.g., in a foreign key constraint). Otherwise, this option should not be used. Do not output commands to set ownership of objects to match the original database.
When to run PG _ dump-J against PostgreSQL?
You also need to specify the –no-synchronized-snapshots parameter when running pg_dump -j against a pre-9.2 PostgreSQL server. Dump only schemas matching schema; this selects both the schema itself, and all its contained objects. When this option is not specified, all non-system schemas in the target database will be dumped.