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
– mlt, Commented Jul 1, 2014 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
– Juan Carlos Oropeza, Commented Jul 1, 2014 at 19:07
re a): -b is for large objects, not for bytea columns (Postgres' equivalent to a BLOB is bytea) — user330315
– user330315, Commented Jul 1, 2014 at 20:18

Clodoaldo Neto · Accepted Answer · 2014-07-01 20:07:27Z

72

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 1, 2014 at 20:07

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Juan Carlos Oropeza Over a year ago

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?

Clodoaldo Neto Over a year ago

Yes it only excludes the table data. The table creation statement will be output to the backup. Yes multiple --exclude-table-data

FiruzzZ Over a year ago

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

Bohdan Stupak · Accepted Answer · 2021-02-10 15:17:54Z

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, 2021 at 15:17

Bohdan Stupak

1,59112 silver badges16 bronze badges

1 Comment

Александр Пушкин Over a year ago

try this --exclude-table '*.\"ChangeHistory\"""'

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Linked

Related