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?
2 Answers
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.
3 Comments
Jason Swett
FWIW, --exclude-table was problematic for me due to some keys etc. that existed on the table but --exclude-table-data worked fine for me.
Álvaro González
Note that if you use the table name it'll be ignored altogether. Apparently, you need to prepend the schema, as in
--exclude-table-data="your_schema_here.your_table_here".
Ryan
Also, if you have a capital letter in your table, don't forget to wrap it in ". For example public.File should be
--exclude-table-data='public."File"'You can also add the same in the script
#!/bin/bash CKUPNUM=3 BACKUPDIR=/home/utrade/dbbackup DBBACKUP_FILENAME=Database_dump.sql TARFILE=Database_dump_$(date +%d%h%y).tgz #####Variables Set DBUSER=mutrade DBPASSWD=utrade123 DBNAME=mutradedb cd $BACKUPDIR export PGPASSWORD=$DBPASSWD /usr/pgsql-11/bin/pg_dump -f $DBBACKUP_FILENAME $DBNAME --exclude-table-data='appmaster.ohlc_*' -U $DBUSER tar czf $TARFILE $DBBACKUP_FILENAME rm -f $DBBACKUP_FILENAME #removing old/Extra backups backups_count=`ls -lrt | wc -l` if [[ $backups_count -gt $BACKUPNUM ]] then find $BACKUPDIR -mtime +30 -type f -delete fi 
--exclude-tableparameter: postgresql.org/docs/current/static/app-pgdump.html