Testing Database replication

Question

Is someone aware of any general approaches for testing out database replication?

We are using a Postgres database which is going to be replicated real-time (synchronously) in a master-slave fashion. The idea is to prepare for failover, guaranteeing high availability and data durability (back-up and recovery).

I can think of few realistic scenarios and test them out manually but it would be good to know if there are some resources/practices available which can enable us to think more exhaustively in this direction and eventually create something re-usable as well (read automation).

Answer 1

I would list down based on my previous experience - Replication Test Effort using SQL Server. My scenario was Transactional Replication. I am not aware of Postgress DB but there should be simlar jobs equivalent to jobs/monitors provided in sql server.

What all areas to test

Initial Setup

• Configuring Replication, Running Replication Scripts on the Database
• Verify if scripts are re-runnable (Install, Uniinstall, Re-install)
• Post Installation verify the publication and subscription articles are setup on Publisher and Subscriber Databases
• For SQL Server there are system tables (Publish DB - SELECT * FROM sysarticles, SELECT * FROM syspublications), Similarly you should be able to query Postgress specific tables
• I hope scripts can be deployed in any environment without any hardcoding / manual steps involved

Initial Data Setup

• If you have millions of records, What is the initial strategy to replicate these records, I have seen cases where the inital snapshot would be created and replication would be setup on top if it. To be more specific it is done using option (EXEC sp_addsubscription set The @sync_type = 'replication support only')
• You will be grouping articles based on latency, I hope you are not setting every table to be realtime

Basic Cases

• Insert few records in source system and verify entries are reflected in replicated databases
• Insert trace tokens in replication monitor and check time taken to reflect in destination database

Replication Latency with Huge Data

• In case of huge data loads in source system verify if there is any blocking caused between replication and application stored procedures
• Verify with high loads the response time of your application

Handling Failure and Alerts

• Password expiration, Service Account Removed Do you have any alerting to report the same
• Verify alerts are setup to monitor failure of jobs in both source and destination systems
• Verify alerts are setup in case of errors reported in logs

Automation

• Custom script to validate the jobs are created, tables enabled for replication. Installation can be verfied

Answer 2

The problem with general approaches is they are just that - general. That may or may not relate to you at all. So the most important thing is to understand or be prepared to learn as much as you can about the underlying system and software - in your case Postgres and some flavor of *nix.

I, like Siva, have tested replication on SQL Server using transactional replication.

The first thing I did was try to understand what the best model was for our needs. The first "bug" I found was my developer had chosen the wrong replication model.

• Setup the replication method several times. Remove it several times.
• Use a realitic amount of data. Then use a large / unrealistic amount of data - say 5 years worth. See any problems?
• Performance can degrade on both the Primary and Replicated DBs so make sure you can setup something similiar to production and see how it works.
• Make sure you can restore from your transactional db in a way that is acceptable. How much data, if any, can you loose?
• What happens when there are communication problems (read network problems) between the two?

I learned a lot of important information from testing replication like:

• Our current DB recovery mode was completely useless with replication. The transaction log would grow and fill the 500GB HD in a few days.
• Several of our DB queries became deadlocked or too slow when the replication process would run and our software was actively being used.
• As I said above we started out with the wrong replication method.

There are so many variables when you start thinking about it. The more you know the better. I suggest finding as much documentation on Postgres replication as you can.