REPLICATE

Copies data directly from one Eon Mode database's communal store to another.

Eon Mode only

Copies table or schema data directly from one Eon Mode database's communal storage to another. REPLICATE copies all table data and metadata. When called multiple times, this statement only copies data that has changed in the source database since the last call. The tables do not have to exist in the target database before replication. If the table does exist in the target, it is overwritten.

Caution

Replication overwrites any data in the target table. You do not receive any notification that the data in the target database will be overwritten, even if the target table's design does not match the source table. Verify that the tables you want to replicate either do not exist in the target database or do not contain data that you need. Be especially cautious when replicating entire schemas to prevent overwriting data in the target database.

Similarly, changes to a replicated table in the target database are overwritten each time you replicate the table. When performing scheduled replications, only grant read access to replicated tables in the target database. Limiting these tables to read-only access will help prevent confusion if a user makes changes to a replicated table.

Syntax

REPLICATE { table | schema | "schema.table" 
          |  INCLUDE "inc_pattern" { EXCLUDE "ex_pattern"} }
          { FROM | TO } database_name;

Arguments

*table* | *schema* | "*schema*.*table*"

The name of a single table or schema to replicate. If you want to specify both the schema and table name, enclose the pair in double quotes.

inc_pattern

A string containing a wildcard pattern of the schemas and/or tables to include in the replication.

ex_pattern

A string containing a wildcard pattern of the schemas and/or tables to exclude from the set of tables matched by the include pattern.

Note

When using wildcard patterns, be sure to anchor the patterns with a period to indicate the separation between the schema and the table name. For example, suppose you wanted to replicate all tables in the public schema that started with the letter t, except the table public.t3. This pattern statement does not work because the exclude wildcard only applies to schemas:

=>  REPLICATE INCLUDE "public.t*" EXCLUDE "*3" FROM verticadb;

The following statement does work because the period in the EXCLUDE wildcard forces the wildcard to apply to the table:

=>  REPLICATE INCLUDE "public.t*" EXCLUDE "*.*3" FROM verticadb;

database_name

The name of the database from or to which data is replicated. The replication steps depend on whether the call is initiated from the source or target database:

Target-inititated replication: use the CONNECT TO VERTICA statement to create a connection to the source database, and specify the source database in the REPLICATE statement using a FROM source_db clause.
Source-inititated replication: use the CONNECT TO VERTICA statement to connect to the target database, and specify the target database in the REPLICATE statement using a TO target_db clause.

Both of these methods copy the shard data directly from the source communal storage location to the target communal storage location.

Privileges

Superuser

Examples

Connect to the source database from the target database and then replicate the source database's customers table to the target database:

=> CONNECT TO VERTICA source_db USER dbadmin 
           PASSWORD 'mypassword' ON 'vertica_node01', 5433;
 CONNECT

=> REPLICATE customers FROM source_db;
REPLICATE

The following example performs the same data replication as the previous example, but the following replication is initiated from the source database instead of the target database:

=> CONNECT TO VERTICA target_db USER dbadmin 
           PASSWORD 'mypassword' ON 'vertica_node01', 5433;
 CONNECT

=> REPLICATE customers TO target_db;
REPLICATE

Replicate all tables in the public schema that start with the letter t:

=> REPLICATE INCLUDE "public.t*" FROM source_db;

Replicate all tables in the public schema except those that start with the string customer_:

=> REPLICATE INCLUDE "public.*" EXCLUDE '*.customer_*' FROM source_db;