PostgreSQL Replication
Replication is, simply put, copying data from one place to another. PostgreSQL includes several built-in replication features such as write-ahead log (WAL) shipping, warm and hot standby, and streaming replication. Some widely-used third-party replication tools include Bucardo, developed by End Point, as well as Slony, Londiste, and pgpool.
Each tool has its own strengths and constraints. End Point's consultants have experience determining the tools that work best in various situations and helping you achieve the right balance between cost, availability, and performance.
Put End Point's PostgreSQL Replication experts to work for you!
Why Replication?
Read-only replicas for load distribution
Growing businesses often reach a point where their database systems slow unacceptably under increasingly high demand. Replication allows you to create copies of your main databases, known as "slaves" or "replicas". These replicas can then take up some of the load from reporting jobs and other read-only activities. Bucardo and Slony are popular replication solutions that can support this arrangement. PostgreSQL 9.0 provides another read-only replica option with its "hot standby" feature. End Point can help you determine which of these solutions will be best for you and your business.
Backups
There are many ways to back up a database. The most common PostgreSQL backup utility, pg_dump, is simple and easy to use, but can cause high load on your production system, slowing response time for your application. One way to eliminate this disruption is to replicate data to a slave database and dump that instead. Other common backup solutions exist which don't involve replication, such as point-in-time recovery (PITR) and filesystem snapshots. End Point uses each of these options regularly, both alone and in combination, and can create a solution suited to your needs.
Data warehousing
Businesses have always collected data; many businesses now want to "mine" or analyze their data for marketing, performance, and process optimization purposes. This usually involves very complex queries, which can be expensive both in time and server processing power. A common approach is to periodically transfer the data from production databases into a "data warehouse" to simplify the queries and ease load on the main database servers, in a process known as "Extraction, Transformation, and Loading", or ETL. Bucardo allows seamless transformation of data between master and replica, and can handle ETL processes in a single package.
High availability and failover
Downtime comes at a high cost to any business that depends heavily on its database. End Point engineers can build a cluster of database servers in a high availability configuration for your business, to minimize disruption due to database failure. In such a system, a failure of one system triggers another database server to take over automatically.
Disconnected servers
Sometimes businesses or applications need to distribute changes across long distances or delayed over time. In these scenarios latency and disruptions to connectivity can affect replication in unexpected ways. End Point engineers are versed in the options, and can help you build a solution that will handle your more complex needs.