Mastering PostgreSQL 12 - Third Edition

Extracting subsets of data

Up until now, we have seen how to dump an entire database. However, this may not be what we want to do. In many cases, we just want to extract a subset of tables or schemas. Fortunately, pg_dump can help us to do that while also providing several switches:

• -a: This only dumps the data and does not dump the data structure.
• -s: This dumps the data structure but skips the data.
• -n: This only dumps a certain schema.
• -N: This dumps everything but excludes certain schemas.
• -t: This only dumps certain tables.
• -T: This dumps everything but certain tables (this makes sense if you want to exclude logging tables and so on).

Partial dumps can be very useful to speed things up considerably. Now that we have learned how to ...