Postgres Database Schema Migration

Requirement is:
On 192.168.2.160 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 192.168.2.161).

Solution:

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"
FROM (
     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';