xDB Replication Tutorial for Postgres Plus®
How to Install and Setup the Postgres Plus xDB Replication Server
The Postgres Plus xDB Replication Server is an asynchronous, master-to-slave replication system enabling multiple replication options including:
- replication of data between Postgres Plus databases (Standard and Advanced Servers),
- replication of data from Oracle® to Standard Server, or
- bi-directional replication between Oracle and Advanced Server
Introduction
This EnterpriseDB Quick Tutorial helps you get started with advanced configurations of the Postgres Plus Standard Server and Postgres Plus Advanced Server database products. It is assumed that you have already downloaded and installed one of these products on your desktop or laptop computer.
This Quick Tutorial is designed to help you expedite your Technical Evaluation of Postgres Plus. In this Quick Tutorial you will learn how to set up the Postgres Plus xDB Replication Server. For more informational assets on conducting your evaluation of Postgres Plus, visit the self-service web site, Postgres Plus Open Source Adoption.
In this Quick Tutorial you will learn the following:
- Basic features of the xDB Replication Server
- What Postgres Replication Can Do For You
- Fundamental replication concepts and vocabulary
- The physical and logical components of a replication system
- How to install and configure the xDB Replication Server
- How to register publication and subscription servers
- How to start replication
NOTE: Complete documentation for Postgres Plus xDB Replication Server will be released shortly after general availability of Postgres Plus Advanced Server 8.4.
Feature Description
Postgres Plus xDB Replication Server is an asynchronous, master-to-slave replication system enabling replication of data between one or more Postgres Plus databases (Standard and Advanced Servers). The xDB Replication Server also supports replicating data from Oracle® to Standard Serveror bi-directionally between Oracle and Advanced Server.
The following are some of the features of xDB Replication Server:
- Different replication methods are available for different needs. Two types of replication methods are supported. Snapshot replication takes a copy of the entire table. Synchronization replication takes only the modifications made to the table data since the last replication.
- Replication data can be filtered. A filter can be defined specifying a subset of rows in a source table or view to include during replication. All other rows are excluded from replication.
- Replication occurs asynchronously. Once xDB Replication Server is started, it can run unattended.
- Replication can be scheduled. Replication can be scheduled to run repeatedly at a specific time interval, daily at a specific time of day, weekly on specific days of the week, or monthly on specific months.
- Replication history can be viewed. Two levels of replication history are available. The date and time when a replication occurred is recorded. In addition, the specific inserts, updates, and deletions made to synchronize a replicated table can be viewed.
- Replication can be distributed. The source and target databases can reside on different hosts, as well as the individual components of xDB Replication Server to optimize performance.
- Replication can run from the command line. In addition to a graphical user interface, a command line interface allows scripts to embed commands to configure and run replication.
The following sections introduce you to the concepts and components of xDB Replication Server.
What Can Postgres Replication Do For You?
Replication of data can be employed in a variety of use cases in organizations where it is important to use the same data in multiple settings. This allows users to work with 'real' data that will yield 'real' results that are reliable in more than one setting. Some of the more popular uses of replication include:
- Improve Performance by Offloading Reporting and BI Queries In this use case, users will take all or just a subset of data from a production OLTP system and replicate it to another database whose sole purpose is to support reporting queries. This can have multiple benefits: a) reporting loads are removed from the OLTP system improving transaction processing performance, b) query performance improves as well without being subordinated to transactions on the system, and c) in Oracle installations, the reporting server duties can be handled by a product like Postgres Plus Advanced Server reducing licensing costs for a reporting server.
- Warm Standby servers When many organizations wish to improve the availability of their data, a cost effective solution is often the use of warm standby servers. These are database servers kept up to date with the online system through replication and which can be brought online quickly in the event of a failure in the production system. Warm standby servers can also be used for regular maintenance by gracefully switching over to the standby server so that the production server can be brought offline for regular maintenance.
- Testing Systems in Parallel Often times, upgrading or moving to a new database system requires that the old and new systems be up and running in parallel to allow for testing and comparing results in real time. Replication can be employed in this use case and is frequently used in development and testing environments.
- Migrating Data Similar to running in parallel, is the situation where data may be migrated from one system to another in a sort of 'seeding' operation. Replication can very effective in this situation by quickly copying data.
Replication Concepts
A publish and subscribe architecture is used by xDB Replication Server. The data to be made available for copying is made public as a publication. To get a copy of that data, you must subscribe to that publication.
A publication is defined as a named set of tables and views within a database. The database that contains the publication is called the publication database of that publication.
To get a copy of a publication, you must create a subscription. A subscription is a named association with a publication. Along with its association with a publication, a subscription is associated with a database to which the publication is to be copied. This database is called the subscription database.
Replication is said to occur when xDB Replication Server initiates and completes either of the following processes: 1) applies changes that have been made to rows in the publication since the last replication occurred, to rows in tables of the subscription database (called synchronization); or 2) copies rows of the publication to empty tables of the subscription database (called a snapshot).
The subscription tables are the tables in the subscription database where each subscription table is created from a corresponding table or view in the publication.
Note: xDB Replication Server creates tables in the subscription database for views in the publication.
The following illustrates a basic replication system:
xDB Replication Server Physical Components
xDB Replication Server is not a single executable program, but rather a set of programs that work together to form a replication system. The following diagram shows the programs and database components of xDB Replication Server and how they are used to form a complete replication system.
xDB Replication Server is the engine that enables replication system configuration, scheduling, monitoring, maintenance, replication, and recovery. xDB Replication Server consists of the following software components:
- xDB Replication Console. Graphical user interface that allows you to configure and run a replication system. (Commands written using the xDB Replication Server CLI can be used in place of the xDB Replication Console.)
- Publication server. The program that configures the publication database for replication and initiates replication.
- Subscription server. The program that configures the subscription database for replication.
- xDB Control database. The Postgres Plus database containing the metadata of the replication system.
- xDB Replication Configuration file. Text file containing connection and authentication information used by the publication server and subscription server upon startup to connect to the xDB Control database. Also used to authenticate registration of the publication server and subscription server from the user interface when creating a replication system.
The entire replication system is completed with the addition of the following components:
- One or more publication databases
- One or more subscription databases
xDB Replication Server Logical Components
The logical components are created when you build a replication system using the xDB Replication Console or the xDB Replication Server CLI. The logical components are stored as part of the replication system metadata in the xDB Control database.
Creating a replication system requires the following steps:
- Register the publication server
- Create a publication database definition
- Create a publication
- Register the subscription server
- Create a subscription database definition
- Create a subscription
Each of these steps creates a logical component that is represented by a node in the replication tree window of the xDB Replication Console. A detailed description of each of these steps is presented later in this tutorial.
Tutorial Steps
This section provides directions for installing and configuring xDB Replication Server, and then performing your first replication.
For a detailed description of the steps for installing and configuring xDB Replication Server, and then performing your first replication, please download the PDF:
To download a PDF version of this How To Setup Postgres Plus xDB Replication Server, tutorial, click the link below:
