Product Documentation

Appearance

Setting Up Replication ​

This section explains how to set up replication between two Yellowbrick appliances: a source system and a target system. The specific commands that you need to run are listed alphabetically as part of the main SQL Commands section.

To set up replication on two Yellowbrick appliances:

  1. Make sure that the appliances have network connectivity and that SSL trust is configured. See Configuring SSL Trust.

  2. On the source system, create a remote replication server by running the CREATE REMOTE SERVER command. This server object must point to a compatible Yellowbrick appliance. This appliance will contain target databases that receive replicated data from the source databases on the source system.

For example:

premdb=# create remote server yb_repl_svr
with (host 'yb007', sql_port 5432);
CREATE REMOTE SERVER
  1. Optionally, on the remote system, create an empty "hot-standby" database. The source database will replicate data to the hot-standby database on the remote system.

This step is optional because the ADD REPLICA command, later in this procedure, will create the database for you.

For example:

yellowbrick=# create database premdb_hot with hot_standby on;
CREATE DATABASE

You can also alter an existing empty database to be a hot-standby database by using an ALTER DATABASE command.

  1. Optionally, seed the hot-standby database by restoring it from a full backup of the source database, using a standard ybrestore command. See Seeding a Replica.

  2. On the source system, create a "replica" that is associated with the source database. Identify the target database and set the replication frequency, which defines how often the replication cycle will run when it is started. In this example, the target database is named premdb_hot, and replication is set to run every 60 seconds.

For example:

premdb=# alter database premdb
add replica premdb_replica to yb_repl_svr
with (alias premdb_hot, frequency 60);
ALTER DATABASE

Note: If the ALIAS database does not exist, the system creates it as a hot-standby on the target system. If you do not include the ALIAS option, the system automatically creates a hot-standby target database on the target system with the same name as the source database.

See the ALTER DATABASE ADD REPLICA command for details about other options you can set for the replica.

  1. On the source system, start replication by naming the source database and its replica.

For example:

premdb=# alter database premdb
alter replica premdb_replica start;
ALTER DATABASE

By default, updates to the source database will be replicated to the target at the specified time interval (60 seconds in this example). If the target database was not seeded with a full restore, the first replication cycle may take a long time. After the initial cycle, smaller, faster incremental restores are streamed to the target database.

See ALTER DATABASE ALTER REPLICA for more details about this command.

  1. Verify that replication has started running successfully. Log into the SMC, then go to Monitor > Replication Processes. Select the replica you created and check that its status is Running.

  2. Monitor replication cycles by using the SMC or running queries against the sys.replica_statusandsys.log_replica_status views.

You can see replication status in the SMC via a tabular at-a-glance view for all current replicas or a detailed view per replica. The second view contains a replication performance history. See Monitoring Replication.

In This Section

Parent topic:Database Replication