47

How do I take a schema level backup in PostgreSQL database and restore on the another database? Is there any single command available for this? For example, can I pg_dump and restore in single line?

MatthewMartin
  • 32,326
  • 33
  • 105
  • 164

4 Answers4

49
pg_dump --schema=masters oldDB > masters1.sql
cat masters1.sql | psql newDB

or

in single command you can do by this 

pg_dump oldDB --schema masters  | psql -h localhost newDB;
solaimuruganv
  • 27,177
  • 1
  • 18
  • 23
  • 4
    Your solution dumps data as well as schema. – Bogolt Sep 05 '18 at 18:59
  • 1
    @Bogolt The commands dumps a single schema as in "public" is a schema (so schema as a namespace in database). Not schema as is in `CREATE TABLE` kind of things (so not structure). There is this ambiguity in English unfortunately. It's still a valid answer though. The user didn't said which context he means. – Nux Oct 17 '19 at 10:11
40

Backup schema and restore it on system for postgresql as below:

Dump schema for database

pg_dump -s database_name > db.sql

Dump schema for specific table

pg_dump -s database_name -t table_name > db.sql

Restore backed up schema using below command 

psql -d database_name -h localhost -U postgres < path/db.sql
Sumit Munot
  • 3,748
  • 1
  • 32
  • 51
  • 5
    Just a note that this only dumps structure (no data), but for a whole database. This is not for a single schema (namespace within database). – Nux Oct 17 '19 at 10:15
7

-s or --schema-only to exclude data from dump Documentation

Dino
  • 7,779
  • 12
  • 46
  • 85
Frédérick Baldo
  • 71
  • 1
  • 1
5

What's wrong with the documentation?

Example from the manual:

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

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • actually i need in single line for both backup and restore –  Sep 24 '12 at 13:11
  • 5
    Again, this command dumps matching schemas, including the non-blob data contained therein. You need `--schema-only` to exclude data. – beldaz Jul 23 '15 at 00:40
  • 3
    What's wrong with documentation for many people: documentation can take a relatively long while to wade through, whereas SE can get an answer quickly. For a quick task like backing up a DB reading the docs is annoying and tedious, especially when those docs are as daunting as postgres' – Ken - Enough about Monica Sep 20 '18 at 23:53