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())) * 100as "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) tGROUP BY schema_nameORDER 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 privillegespg_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_dbYou 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_dbYou 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';




0 comments:

Post a Comment

 
Top