Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

How to Move particular Postgres Schema to other database ?

Requirement is:
On we have 3 schemas in the default Postgres database-
1. chennai
2. mumbai
3. kolkatta

Now we would like to know if we can move the chennai Schema out of postgres and make it a separate database in the same server. We then need to start replication  between the DC-DR( 192.168..2.160 to


Step.1 Before moving Note the ownership and grants permission
Export "chennai" schema from postgres database with ownership (no need to skipping grants and ownership)

--checking schema count
db=# \dn+

-- find the number of tables only for a specific schema:
select count(*) from information_schema.tables where table_schema = 'chennai';

---check the postgres db schema size:
db=#  SELECT schema_name, 
    pg_size_pretty(sum(table_size)::bigint) as "disk space",
    (sum(table_size) / pg_database_size(current_database())) * 100
        as "percent"
     SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
     FROM   pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace 
             ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name;
check schemas size and percentage of database usage:

Step 2. Take the chennai schema Backup from postgres database:
pg_dump -xO -n chennai postgres > /backup/billing.dump     ---->excluding privilleges
pg_dump -Fc -n 'chennai'  postgres > /backup/billing.dump  ---->including privilleges
Step 3.Rename the Existing "chennai" schema name as "chennai_old" thens create schema name as "chennai" and database name as "chennai_db"
postgres=# alter schema chennai rename to chennai_old;
postgres=# create database chennai_db;
postgres=# CREATE SCHEMA IF NOT EXISTS chennai; --->No need if you restore the schema on chennai_db it will automaticlly created chennai Schema
Step 4.after created chennai_db,connect as chennai_db then check the schema
postgres=# \c chennai_db
You are now connected to database "chennai_db" as user "postgres".
billing_db=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |
(1 row)
Note:No chennai schema was not here

Step 5.Restore the Billing schema on chennai_db:
$ pg_restore  -d chennai_db  /backup/billing.dump
  ( Or)
$ pg_restore --dbname "chennai_db" "/backup/billing.dump" 
Step 6.Checking the schema integrity:
--Connect as chennai_db and check the schema after restored successfully
postgres=# \c chennai_db
You are now connected to database "chennai_db" as user "postgres".

billing_db=# \dn
  List of schemas
  Name   |  Owner
chennai | postgres
 public  | postgres
(2 rows)
--Check the number of tables only for chennai schema if matching with renamed schema
select count(*) from information_schema.tables where table_schema = 'chennai';


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL pgBadger