Geek Answers Handbook

Use pg_dump result as input for pg_restore

I need to clone a database (schema only) on the same server. I would like to use pg_dump input and pass it to pg_restore. I know that this can be done using psql, but psql does not have the "-c clean" option.

Is this possible, but with pg_restore?

pg_dump --schema public dbName | psql dbNameTest
+4
source share
2 answers

The following is approaching:

 pg_dump --schema-only --format c dbName | \ pg_restore --schema-only --clean --dbname=dbNameTest

Except that it does not work if dbNameTest does not exist yet. The following does the job (although she complains that dbNameTest already exists. I can live with this)

 createdb dbNameTest pg_dump --schema-only --format c dbName | \ pg_restore --schema-only --clean --dbname=dbNameTest

Oneliner with short options will be:

 createdb dbNameTest ; pg_dump -s -F c dbName | pg_restore -s -c -d dbNameTest

A sh script pg_copy_schema will look something like this:

 #!/bin/sh if [ -z "$2" ] ; then echo "Usage: `basename $0` original-db new-db" ; exit 1 ; fi echo "Copying schema of $1 to $2" createdb "$2" 2> /dev/null pg_dump --schema-only --format c "$1" | pg_restore --schema-only --clean --dbname="$2"
+6
source

http://www.postgresql.org/docs/9.1/static/app-pgdump.html

You need to use -F in combination with the -c, -d or -t option with pg_dump to use it with pg_restore. You cannot use pg_restore with plain-text SQL dump.

+2
source

More articles:


All Articles