CODEWITHSUNDEEP
postgresql
johnlemon
johnlemon

Reputation: 21449

Use pg_dump result as input for pg_restore

I need to clone a database (only schema) on the same server. I would like to use the input from pg_dump and pipe it to pg_restore. I know this can be done with psql but psql doesn't have a "-c clean" option.

Is this possible but with pg_restore ?

 pg_dump --schema public dbName | psql dbNameTest

Upvotes: 21

Views: 35539

Answers (2)

Dondi Michael Stroma
Dondi Michael Stroma

Reputation: 4800

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

You need to use -F combined with -c , -d, or -t option with pg_dump in order to use it with pg_restore. You can't use pg_restore with a plain-text SQL dump.

Upvotes: 2

artm
artm

Reputation: 3678

The following comes close:

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

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

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

A oneliner with short options would be:

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

A sh script pg_copy_schema would go something like:

#!/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"

Upvotes: 30

Related Questions