Product Documentation

Appearance

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' ])

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 use wildcards to expand the list of excluded schemas.

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 option as a way 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.

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

Parent topic:SQL Commands