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
    [, ALIAS 'target_database_name' ]
    [, EXCLUDE 'schema_list' ]
    [, BW_LIMIT megabytes ]
    [, REVERSE_REPLICA 'reverse_replica_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.
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.
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.

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.
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 databases, you can set this option to some number of megabytes per second.
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.
Specify the user resolution mode when replicated users and roles cannot be resolved on the target system, either because they never existed on the target or because they were previously replicated, then dropped. When SECURITY_MODE is set to ALL, references to missing replicated users are mapped to the database owner by default. In turn, default privileges for those users are not replicated. You can change this behavior by creating the replica as follows:
WITH (..., security_mode 'ALL', user_resolution_mode 'placeholder'...)

PLACEHOLDER creates a placeholder role on the target system for the role on the source system and maps all references to that role, including default privileges. DB_OWNER maps all references to the database owner.


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);
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_*');