How to exclude data from certain tables during PostgreSQL backup

I often want to omit data from certain tables during PostgreSQL database backup. Today I will look into this and post my findings for further reference.

PostgreSQL post-9.2 version

Since PostgreSQL 9.2 excluding data from set of tables is almost effortless as it requires only additional exclude-table-data parameter during backup process.

$ pg_dump --exclude-table-data=logs_table applicationdb > backup.sql

This parameter can contain pattern or can be used multiple times to exclude data from different tables.

PostgreSQL pre-9.2 version

PostgreSQL versions older than 9.2 require two step process to achieve the same results.

The first step is to backup database schema.

$ pg_dump --schema-only applicationdb >  backup_schema.sql

The second step is to backup table data excluding certain table.

$ pg_dump --data-only --exclude-table logs_table  applicationdb > backup_data.sql

Additional notes

Milosz Galazka's Picture

About Milosz Galazka

Milosz is a system administrator working for a successful Polish company and a long time supporter of Free Software Foundation and Debian operating system.

Gdansk, Poland https://sleeplessbeastie.eu