0

I have db named "mydb". Now How can I create a new db with name "mydb_test" with only schema of "mydb"

Tried the following link PostgreSQL how to create a copy of a database or schema?

As mentioned over there I tried the following command,

createdb -T olddb newdb

This copies including the data into newdb

If I try the second option mentioned in the above link I get following error,

# pg_dump -Cs -U postgres my_test_db > dump_schema_file
# psql -U postgres naggappan_my_test_db < dump_schema_file
 psql: FATAL:  database "naggappan_my_test_db" does not exist

How can I take only schema

Community
  • 1
  • 1
Naggappan Ramukannan
  • 2,564
  • 9
  • 36
  • 59

1 Answers1

0

  • You may create a new dump without data of required schema and then restore from it into a new DB:

    # pg_dump --dbname=source_db_name --username=postgres --encoding=UTF8 --schema=db_schema --schema-only --file=path_to_filename.dump

    # psql --dbname=target_db_name --username=postgres --file=path_to_filename.dump

  • If you have an existing dump with schema and/or data in binary format, you can restore only the schema from it using pg_restore:

    # pg_restore --dbname=target_db_name --username=postgres --schema-only path_to_filename.dump

Dmytro Pavliuchenko
  • 26
  • 5