Postgresql: backup all table structures but only a few data table

Question

I have a database with some tables for the application settings, lists like users, departments, cities. I want the structure and the data for those tables. So if i get a new user the backup will save it.

But also have some data for historic and calculated data, that data came from another sources and only work for some time and then expire, so backup that data will be a waste. But will need have the structure so the restore will create the tables need it for the application.

right now I'm using this command but this save all table and all data.

pg_dump -U "postgres" -h "local" -p "5432" 
        -d dbName -F c -b -v -f c:\uti\backup.dmp

I have 2 additional questions regarding pg_dump.

A) docs say option -b is for blob data. I have very big tables, but i guess this options is for only tables with a BLOB field, so shouldn't make any difference in my backup because i don't have those fields ?.

B) I see pg_dump options are for tables and schemas. How you specify if want save the functions code?

Do it in 2 steps. Backup your schema (with functions) and then selectively table data. — mlt, Jul 01 '14 at 18:59
So i made a pg_dump to a file `backup` ... and then another pg_dump to the same file? If that is the case, just submit it as answer so i can vote for it. — Juan Carlos Oropeza, Jul 01 '14 at 19:07
re a): `-b` is for large objects, not for `bytea` columns (Postgres' equivalent to a `BLOB` is `bytea`) — , Jul 01 '14 at 20:18
@JuanCarlosOropeza (for future sake) you can use `--blobs` to include blob objects in dumps. — JP Silvashy, Dec 05 '18 at 16:45

score 68 · Accepted Answer · answered Jul 01 '14 at 20:07

68

Exclude the tables you do not want to backup

pg_dump -U "postgres" -h "local" -p "5432" 
        -d dbName -F c -b -v -f c:\uti\backup.dmp
        --exclude-table-data '*.table_name_pattern_*'
        --exclude-table-data 'some_schema.another_*_pattern_*'

The function creation code is part of the schema.

answered Jul 01 '14 at 20:07

Clodoaldo Neto

118,695
26
233
260

1

So even i exclude the table the `CREATE TABLE` statement still will be in the backup? Also ... looks like i can put multiple `--exclude-table-data` parameter? – Juan Carlos Oropeza Jul 01 '14 at 20:12
2

Yes it only excludes the table data. The table creation statement will be output to the backup. Yes multiple `--exclude-table-data` – Clodoaldo Neto Jul 01 '14 at 21:04
older than 9.2 has to do this in two steps: The first step is to backup database schema `pg_dump --schema-only` The second step is to backup table data excluding certain table `pg_dump --data-only --exclude-table` – FiruzzZ May 18 '15 at 18:26

score 0 · Answer 2 · answered Feb 10 '21 at 15:17

0

Clodoaldo Neto's is the way to go. However, I witnessed a strange behavior when using pg_dump with capital letters. The issue is also described here

So in my case the table to ignore was named ChangeHistory and the trick to ignore it was wildcarding capital letters as below

pg_dump [omitted for brievity] --exclude-table "*hange*istory"

answered Feb 10 '21 at 15:17

Bohdan Stupak

1,455
8
15

1

try this --exclude-table '\*.\"ChangeHistory\"""' – Александр Пушкин Oct 15 '21 at 14:03

Postgresql: backup all table structures but only a few data table

2 Answers2

Linked