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 privillegesStep 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 SchemaStep 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