Product Documentation

Appearance

All platforms

Setting Up Loopback Replication ​

Loopback replication is the process of replicating to a local database, a database with an alias that resides on the same system as the source database.

Although only one system is being used for replication, you still need to configure SSL trust.

Follow these steps to set up loopback replication:

  1. Configure SSL trust. See Configuring SSL Trust.

  2. Create a remote server but specify localhost in the command. For example:

premdb=# create remote server yb100local with (host 'localhost', nohostnamecheck true);
CREATE REMOTE SERVER

Query the sys.remote_server system view to check the definition of the new remote server:

premdb=# select * from sys.remote_server;
 remote_server_id |       name        |          host        | port | control_port | no_hostname_check 
------------------+-------------------+----------------------+------+--------------+-------------------
            16419 | yb100local        | localhost            | 5432 |       [NULL] | t
(1 row)

If you are using the default self-signed certificate from the cluster or changing the properties of an existing remote server, see CREATE REMOTE SERVER.

  1. Create a replica and use the ALIAS option to name the target database on the local system. If this database already exists, it must be empty and in HOT_STANDBY mode. If it does not exist, the ADD REPLICA command creates it. For example:
premdb=# alter database premdb add replica local_premdb_replica to yb100local 
with(alias premdb_replica1, frequency 60);
ADD REPLICA

In this case, the replica database was created and placed in HOT_STANDBY mode:

premdb=# select name, is_hot_standby from sys.database where name = 'premdb_replica1';
      name       | is_hot_standby 
-----------------+---------------
 premdb_replica1 | t
(1 row)

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

  1. Start the local replica:
premdb=# alter database premdb alter replica local_premdb_replica start;
START REPLICA

Check the sys.replica system view to make sure replication is running:

premdb=# select name, status, alias from sys.replica;
         name         | status  |      alias      
----------------------+---------+-----------------
 local_premdb_replica | RUNNING | premdb_replica1
(1 row)
  1. Verify that replication has started running successfully and monitor replication cycles by running queries against the sys.replica_statusandsys.log_replica_status views.