How to dump a postgres db 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?

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.

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