PostgreSQL for Data Architects

Filtering options

The pg_dump utility provides quite a few filtering options. We can use --data-only to generate files that contain only data, without CREATE statements, --schema= to generate the dump for a specific schema. If we need to retrieve only the object definition, which is often the case when we move from development to production environments, we can use the --schema-only option. To move specific tables, the –table= option can be used. Schema filters and table filters accept pattern matching. To generate only object definitions of tables whose names start with myt, we can execute the following command:

pg_dump test --table='myt*' --schema-only

In a similar fashion, it's possible to exclude schemas or tables using the –exclude-schema