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 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 which to replicate the data. Your database must have a connection to the source database. Use the CONNECT TO VERTICA statement to create the connection if your database is not already connected.

Privileges

Superuser

Examples

Replicate a single table from a database named verticadb:

=> CONNECT TO VERTICA verticadb USER dbadmin 
           PASSWORD 'mypassword' ON 'vertica_node01', 5433;
 CONNECT
=> REPLICATE customers FROM verticadb;
REPLICATE

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

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

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

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