This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Instead of a separate database, PostgreSQL DBAs are suggesting to create schemas in Postgres. The reason is cross database queries which is not supported in Postgres.
In your database, you cannot select any data from another database of the same server. For that, you need to configure DB link which you can find in below article.
PostgreSQL [Video]: Cross Database Queries using DbLink Extension
So, I have seen most of the PostgreSQL servers where user created lots of schema for a different purpose.
Previously, I shared an article on how to take table backup in PostgreSQL.
Recently, I got one request from a developer like they need one schema backup from Production Server to Development Server.
In this post, I am sharing the script to take backup of your Schema and using the single command you can also copy your schema from one server to another server.
Here, I am using pg_dump utility to perform this exercise.
Take only schema backup in .sql file:
|
1 |
pg_dump --schema=schema_name db_name > backupfile.sql |
Restore schema from backup .sql file:
|
1 |
psql -d db_name -h localhost -U user_name < backupfile.sql |
Copy or Restore schema backup into another PostgreSQL Server:
|
1 |
pg_dump source_db_name --schema schema_name | psql -h destination_hostname -U distination_user_name -d distination_db_name |
Note: While copying the schema from one server to another, please make sure that require owner or roles created on the destination server.
Leave a Reply