Search code examples
sqldatabasepostgresqldatabase-backupspg-dump

Selective Schema dump using pg_dump

I am new to Postgres and my task is to back up and restore one PG Database to Another , my source Postgres DB has 150 schemas but my task to selectively dump only 75 from 150 and restore the 75 into a new DB server, I plan to use pg_dump for the same. The 75 which I need to exclude from the list have a similar name all start with name PatSchema_x, x can be 1,2,3....N. Is there a was I can pass the Schema name pattern like 'PatSchema' to pg_dump so that it excludes all these schemas and dumps only the remaining ones. Pls let me know.

Thanks

Solution

  • Use the -N option, like the documentation describes:

    -N pattern
    --exclude-schema=pattern

    Do not dump any schemas matching pattern. The pattern is interpreted according to the same rules as for -n.

    So try

    -N "PatSchema_*"