This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing few important points which should perform before populating new database in PostgreSQL.
Populating a new database means, creating another copy of the database on a different server or different location. In the ETL process, populating database is widespread requirements and people are populating the database for various purpose like testing, backup, validation.
But before actual migration, we should follow few steps which also help us to improve the performance of migration activity.
Follow the below steps in the source database (Before migration):
Disable all DDL/DML triggers
Use COPY Command
Instead of creating multiple COPY command, you can create a JSON manifest file and can build only one COPY Command for all the tables
Disable Indexes
Disable Foreign Key Constraints
Disable logs of database
Disable Replication (If any)
Increase maintenance_work_mem
Increase max_wal_size
Follow the below steps in destination database (After migration):
Disable auto-commit
Disable logs of database
Increase maintenance_work_mem
Increase max_wal_size
Check the current value of sequences, and if it is 0 then reset by appropriate value
Check the database character set, default timezone, tablespaces
Enable Indexes, Foreign Keys
Run ANALYZE on database
