-
BASIC POSTGRESQL
6-
Lecture1.1
-
Lecture1.2
-
Lecture1.3
-
Lecture1.4
-
Lecture1.5
-
Lecture1.6
-
-
VMWARE & POSTGRESQL INSTALLATION
9-
Lecture2.1
-
Lecture2.2
-
Lecture2.3
-
Lecture2.4
-
Lecture2.5
-
Lecture2.6
-
Lecture2.7
-
Lecture2.8
-
Lecture2.9
-
-
POSTGRESQL DATABASE
6-
Lecture3.1
-
Lecture3.2
-
Lecture3.3
-
Lecture3.4
-
Lecture3.5
-
Lecture3.6
-
-
POSTGRESQL TABLE
16-
Lecture4.1
-
Lecture4.2
-
Lecture4.3
-
Lecture4.4
-
Lecture4.5
-
Lecture4.6
-
Lecture4.7
-
Lecture4.8
-
Lecture4.9
-
Lecture4.10
-
Lecture4.11
-
Lecture4.12
-
Lecture4.13
-
Lecture4.14
-
Lecture4.15
-
Lecture4.16
-
-
USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
3-
Lecture5.1
-
Lecture5.2
-
Lecture5.3
-
-
TRANSACTIONS - MVCC
3-
Lecture6.1
-
Lecture6.2
-
Lecture6.3
-
-
POSTGRESQL USER/SCHEMA MANAGEMENT
9-
Lecture7.1
-
Lecture7.2
-
Lecture7.3
-
Lecture7.4
-
Lecture7.5
-
Lecture7.6
-
Lecture7.7
-
Lecture7.8
-
Lecture7.9
-
-
POSTGRESQL CONSTRAINTS
6-
Lecture8.1
-
Lecture8.2
-
Lecture8.3
-
Lecture8.4
-
Lecture8.5
-
Lecture8.6
-
-
POSTGRESQL ADVANCE DATA TYPE
5-
Lecture9.1
-
Lecture9.2
-
Lecture9.3
-
Lecture9.4
-
Lecture9.5
-
-
POSTGRESQL VIEWS
1-
Lecture10.1
-
-
POSTGRESQL MONITORING OBJECT USUAGE/SIZE
1 -
POSTGRESQL DATABASE ARCHITECTURE
4-
Lecture12.1
-
Lecture12.2
-
Lecture12.3
-
Lecture12.4
-
-
POSTGRESQL BACKUP AND RECOVERY
13-
Lecture13.1
-
Lecture13.2
-
Lecture13.3
-
Lecture13.4
-
Lecture13.5
-
Lecture13.6
-
Lecture13.7
-
Lecture13.8
-
Lecture13.9
-
Lecture13.10
-
Lecture13.11
-
Lecture13.12
-
Lecture13.13
-
-
POSTGRESQL PERFORMANCE TUNING
5-
Lecture14.1
-
Lecture14.2
-
Lecture14.3
-
Lecture14.4
-
Lecture14.5
-
-
HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
11-
Lecture15.1
-
Lecture15.2
-
Lecture15.3
-
Lecture15.4
-
Lecture15.5
-
Lecture15.6
-
Lecture15.7
-
Lecture15.8
-
Lecture15.9
-
Lecture15.10
-
Lecture15.11
-
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';