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?

Prurigo

Do it in 2 steps. Backup your schema (with functions) and then selectively table data. – Itemize

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. – Prurigo

ok, that doesn't work because overwrite first file. – Prurigo

re a): -b is for large objects, not for bytea columns (Postgres' equivalent to a BLOB is bytea) – Alcalde

so what are large object? table with lot of rows? – Prurigo

@JuanCarlosOropeza (for future sake) you can use --blobs to include blob objects in dumps. – Workmanlike

Answer 1

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.

Japha

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? – Prurigo

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

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 – Trichomonad

Answer 2

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"

Bustup

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