CODEWITHSUNDEEP
postgresql
membersound
membersound

Reputation: 86627

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?

Upvotes: 80

Views: 68050

Answers (2)

Virendra Mandhotra
Virendra Mandhotra

Reputation: 9

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

Upvotes: -2

LondonRob
LondonRob

Reputation: 78683

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.

Upvotes: 121

Related Questions