Menu
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?
869
PostgreSQL has a DROP TABLE statement that is used to remove an existing table or tables from the database. Syntax: DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT]; Let's analyze the above syntax: We specify the table name after the DROP TABLE keyword to remove the table permanently from the database.
pg_dump doesn't lock the entire database, it does get an explicit lock on all the tables it is going to dump, though.
The easiest but the most efficient way to export data from a Postgres table to a CSV file is by using the COPY command. COPY command generates a CSV file on the Database Server. You can export the entire table or the results of a query to a CSV file with the COPY TO command.
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. The pattern is interpreted according to the same rules as for -t. -T can be given more than once to exclude tables matching any of several patterns.
When both -t and -T are given, the behavior is to dump just the tables that match at least one -t switch but no -T switches. If -T appears without -t, then tables matching -T are excluded from what is otherwise a normal dump.
There are a few examples here.
115
answered Oct 20 '22 20:10
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
