Database Technologies

The people using PostgreSQL and the Streaming Replication feature seem to ask many of the same questions: 1. How best to monitor Streaming Replication? 2. What is the best way to do that? 3. Are there alternatives, when monitoring on Standby, to using the pg_stat_replication view on Master? 4. How should I calculate replication lag-time, in seconds, minutes, etc.? In light of these commonly asked questions, I thought a blog would help. The following are some methods I’ve found to be useful. Monitoring is critical for large infrastructure deployments where you have Streaming Replication for: 1. Disaster recovery 2. Streaming Replication is for High Availability 3. Load balancing, when using Streaming Replication with Hot Standby PostgreSQL has some building blocks for replication monitoring, and the following are some important functions and views which can be use for monitoring the replication: 1. pg_stat_replication view on master/primary server.    This view ...

Since, PostgreSQL 9.1 has already been released and there are lot of new features added in it, so, I thought to blog about each features. Today, I am discussing about Synchronous and Asynchronous Replication supported in PostgreSQL 9.1. For Asynchronous Replication, user can use following method: 1. Change Following Parameters in postgresql.conf file of Primary Database: archive_command = cp -i %p /Users/postgres/archive/%f archive_mode = on max_wal_senders = 3 # Maximum 'wal_senders', processes responsible for managing a connection with a standby server wal_keep_segments = # How many WAL segments (=files) should be kept on the primary, whatever may happen (you won't have to copy them manually on the standby if the standby gets too far behind) wal_level = hot_standby 2. Make Following changes in pg_hba.conf host replication postgres [Ipv4 address of Standby Server]/32 trust host replication postgres [Ipv4 address of Master Server]/32 tr...