• Home

  • Custom Ecommerce
  • Application Development
  • Database Consulting
  • Cloud Hosting
  • Systems Integration
  • Legacy Business Systems
  • Security & Compliance
  • GIS

  • Expertise

  • About Us
  • Our Team
  • Clients
  • Careers

  • Blog

  • VisionPort

  • Contact
  • Expertise

    We have the experience and knowledge to choose the right tools.

    ‹ Return to Expertise Index

    Replication & Bucardo

    Solutions for replication

    Replication is, simply put, copying data from one place to another. Whether you use PostgreSQL, MySQL, or SQL Server for your database system, there are multiple solutions to consider, both built-in and with third-party tools. PostgreSQL includes these capabilities with several built-in replication features such as write-ahead log (WAL) shipping, warm and hot standby, and streaming replication. Some widely-used third-party tools for replication and similar functions include Bucardo (developed by End Point Dev), Slony, Londiste, pgbouncer, and pgpool. MySQL also has built-in replication features for shipping either row data or SQL commands, and can be used in conjunction with other third-party tools.

    Each tool has its own strengths and constraints. End Point Dev’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.

    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, known as slaves or replicas, of your main databases. These replicas can then take 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 version 9.0 and newer also provide a read-only replica option with its “hot standby” feature. End Point Dev can help you determine which of these solutions will be best for you and your business.

    Multi-master replication

    Bucardo also offers two-way replication such that two databases can be complete authoritative repositories of all information, updated in near real-time. This can be achieved across multiple datacenters for risk mitigation, lower latency to local services, or to distribute load. We can help you avoid pitfalls and make the most of a multi-master replication system.

    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 add load to your production system. Some workloads benefit from replicating data to a slave database and exporting that periodically instead. We have experience with other backup solutions such as point-in-time recovery (PITR) and filesystem snapshots, 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.

    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 Dev engineers are versed in the options, and can help you build a solution that will handle your more complex needs.

    Send us a message!

    Related blog posts

    Bucardo 5.6.0 Released

    David Christensen · March 1, 2020

    Disaster Recovery — Miami to Dallas in one hit

    Ian Neilsen · October 3, 2017

    Throw It Away: Suppressing Writes on PostgreSQL Replicas

    Josh Williams · November 30, 2016

    Bucardo replication workarounds for extremely large Postgres updates

    Greg Sabino Mullane · May 31, 2016

    PostgreSQL Point-in-time Recovery: An Unexpected Journey

    David Christensen · January 15, 2016

    Odd pg_basebackup Connectivity Failures Over SSL

    Josh Williams · November 13, 2015