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

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

7 Steps to configure BDR replication in postgresql

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

vacuumlo - removing large objects orphans from a database PostgreSQL

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory