Using pg_dump to export multiple schemas from like a par file

user_12091302 0
Viewed 235

In order to export using pg_dump, I'm aware i need to do something like this to export multiple schemas

As an example: pg_dump -n user1 -n user2 -f backup.sql

but what if i have like 10 schemas, instead of using "-n" n number of times is there a better way to define the schema list, like in a text file and somehow render in a pg_dump commandline?

thanks

pg-dump postgresql
1 Answers

Using patterns o regular expressions as appears in official docs

-n pattern --schema=pattern Dump only schemas matching pattern; this selects both the schema itself, and all its contained objects. Multiple schemas can be selected by writing multiple -n switches. The pattern parameter is interpreted as a pattern according to the same rules used by psql's \d commands (see Patterns below), so multiple schemas can also be selected by writing wildcard characters in the pattern. When using wildcards, be careful to quote the pattern if needed to prevent the shell from expanding the wildcards; see Examples below.

To dump all schemas whose names start with east or west and end in gsm, excluding any schemas whose names contain the word test:

   $ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

The same, using regular expression notation to consolidate the switches:

   $ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql

Or with a shell script and a text file containing names of schemas, one by line

#!/bin/bash
cat schemas.txt | while read schema || [[ -n $schema ]];
do
  cmd="pg_dump -n '${schema}' postgres > ${schema}.sql"
  printf '%s\n' "$cmd"
  eval "$cmd"
done

where postgres is your database. And additionally a version for all the selected schemas in one dump file

#!/bin/bash
cat schemas.txt | (while read schema || [[ -n $schema ]];
do
 params+="-n '${schema}' "
done
cmd="pg_dump $params postgres > some_schemas.sql"
printf '%s\n' "$cmd" 
eval "$cmd")
user_150623610
Related