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

Juan Carlos Oropeza picture Juan Carlos Oropeza · Jul 1, 2014 · Viewed 18.7k times · Source

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?

Answer

Clodoaldo Neto picture Clodoaldo Neto · Jul 1, 2014

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.