ALTER DATABASE ADD REPLICA

Create a replica for a source database. You must be a superuser to run this command.

ALTER DATABASE source_database_name
ADD REPLICA replica_name 
TO remote_server
WITH (FREQUENCY seconds
    [, ALIAS 'target_database_name' ]
    [, EXCLUDE 'schema_list' ]
    [, SECURITY_MODE 'ALL' | 'NONE' ]
    [, BW_LIMIT megabytes ]
    [, REVERSE_REPLICA 'reverse_replica_name' ]
    [, TABLE_LEVEL_RESTART boolean ])

Syntax

source_database_name
Specify the name of a database on the source system. Data will be replicated from this database to the named target database. You can create only 1 replica per database.
Note: You cannot create a replica for the yellowbrick database.
replica_name
Specify a unique name for this replica. A replica is not a database; it is a logical object that identifies a remote server, a source database, and a target database.
remote_server
Specify the name of an existing remote server, as created with the CREATE REMOTE SERVER command.
ALIAS target_database_name
Specify a database name, which will be the name of the target database for replication using this replica. If the named database exists on the target system, it must be empty and in HOT_STANDBY mode. If the named database does not exist, it is automatically created and placed in HOT_STANDBY mode.
Note: The target database does not have to be empty in one exceptional case: if you seed the replica by restoring the database from a backup before starting replication. See Seeding a Replica.

If you do not specify an alias, the command creates a database with the same name as the source database on the target system. For loopback replication, you must replicate to an aliased database.

FREQUENCY seconds
Specify the replication interval, in seconds. The value must be an integer. The minimum frequency is 5 seconds. There is no default value. For example, to use a replication interval of 1 minute, set the frequency to 60. For one hour, set it to 3600.
EXCLUDE 'schema_list'
Optionally, specify a list of schemas that you would like to exclude when replication runs. If you do not specify this option, all objects in all schemas are replicated. Enclose the list in single quotes and separate the schema names with commas. Do not use leading or trailing spaces in the list.
You can also expand the list of excluded schemas by using the wildcard character * (an asterisk). No other wildcard characters are supported. For example, the following syntax excludes the public schema, all schemas with the prefix premdb_, and all schemas with the prefix epl:
EXCLUDE 'public,premdb_*,epl*'
Tip: You can use the EXCLUDE wildcard character to prevent the replication of new schemas that are added to a database, assuming that there is a convention in place for naming schemas with a standard set of prefixes.
SECURITY_MODE 'ALL' | 'NONE'
Specify this option to replicate all or none of the following security-related objects and attributes: users, roles, grants, and ACLs. The default behavior is ALL.
BW_LIMIT megabytes
Specify a bandwidth limit in megabytes per second, which is the maximum speed of transmission traffic for replication operations. Typically, this limit does not need to be imposed, allowing replication to run at full throttle. If you have a business requirement to control the rate of traffic over the link between the two appliances, you can set this option to some number of megabytes per second.
REVERSE_REPLICA
Create a reverse replica for use during a failover procedure. See Replication Failover and Failback.
Note: Allow some time for forward replication to start before trying to create a reverse replica.
TABLE_LEVEL_RESTART boolean
Specify true to recovery from a failed replication cycle without rolling back all the changes in the transaction. The default is false. See Tech Preview: Table-Level Restart Option.

Examples

Create a replica with a target database named premdb_replica_db and a replication interval of 10 seconds.
premdb=# alter database premdb 
add replica premdb_replica to ybd_repl_svr 
with (alias premdb_replica_db, frequency 10);
ADD REPLICA
Create a replica for a different database but use the same remote server. This replica has a frequency of 60 seconds and excludes some schemas:
premdb=# alter database yb100db 
add replica yb100db_replica to ybd_repl_svr 
with (alias yb100db_replica_db, frequency 60, exclude 'public,yb100schema_*');
ADD REPLICA
Create a replica with the table_level_restart option enabled for the initial replication cycle, which is anticipated to be take a long time, then pause and alter the replica for use in subsequent cycles, which are expected to be much shorter.
premdb=# alter database yb100db
add replica yb100db_replica_restart to ybd_repl_svr
with (alias yb100db_replica_db, frequency 300, table_level_restart true);
ADD REPLICA
...
premdb=# alter database yb100db
alter replica yb100db_replica_restart 
start;
ALTER REPLICA
...
-- wait for initial cycle to complete --
...
premdb=# alter database yb100db
alter replica yb100db_replica_restart 
pause;
ALTER REPLICA
...
premdb=# alter database yb100db
alter replica yb100db_replica_restart 
with (alias yb100db_replica_db, frequency 300, table_level_restart false);
ALTER REPLICA
...
-- start replication again --
...
premdb=# alter database yb100db
alter replica yb100db_replica_restart 
start;
ALTER REPLICA

Note that the WITH() options in the ADD REPLICA and ALTER REPLICA commands must match, except for the table_level_restart change. The alias and frequency options must have the same values.