Vertica’s Server-Based Replication

Posted July 28, 2023 by Moshe Goldberg, Vertica System Engineer

Empowering Seamless Data Copying in Vertica Eon Mode


In our latest installment of exploring the untapped potential of Vertica, we delve into the exciting new feature introduced in version 23.3 – Server-Based Replication. This cutting-edge functionality allows users to efficiently copy data from one Eon Mode database to another, revolutionizing the way data replication is handled in Vertica. With server-based replication, data is directly copied from the source database’s communal storage location to the target database’s communal storage location. This streamlined process eliminates the need for complex intermediate steps, ensuring swift and seamless replication. One of the most significant advantages of server-based replication is its remarkable versatility. Unlike vbr-based replication, this new feature supports replication across platforms, enabling users to effortlessly transfer data between different types of databases or databases with varying node counts. Are you ready to explore the possibilities of Vertica Version 23.3 and renew the way you handle data replication?

Join us on this journey into the realm of server-based replication and unlock the true potential of your data management endeavors.

Server-Based Replication: Swift, Efficient, and Versatile

Server-based replication, the latest addition to Vertica’s feature set, redefines data replication in Eon Mode databases. By directly copying shard data from the source communal storage to the target communal storage. To initiate data replication between two Eon Mode databases, follow these steps:

  1. Access target database and authenticate as a user with superuser privileges.
  2. Establish a connection to the source database using the CONNECT TO VERTICA statement. By default, invoking CONNECT TO VERTICA occurs over the Vertica private network.
  3. Utilize the REPLICATE statement, providing the name of the table to be replicated, or use the INCLUDE and EXCLUDE options to select multiple tables or schemas for replication.
  4. If desired, execute the DISCONNECT statement to terminate the connection.

In server-based replication lies in its capability to replicate data between Eon Mode databases residing in different clouds and on-premises environments. This opens up a world of possibilities for businesses seeking a seamless data synchronization between databases.

Cautionary Measures: Preventing Data Overwrite

While server-based replication empowers smooth data transfers, users need to be especially cautioned to prevent data overwrite in the target table. Overwriting can occur if the target table’s design does not match the source table. Being vigilant and verifying the existence and content of tables before replication is essential for seamless data management.

Requirements: Setting the Stage for Successful Replication

To enable successful server-based replication, certain requirements must be met. The databases involved must have the same shard segmentation, and the replication commands must be executed on the target database by a user with superuser privileges in both the source and target databases. Additionally, the target database must have access to the source database and its communal storage.

Efficient Replication Steps: Simplifying the Data Copying Process

Replicating data between Eon Mode databases is now a straightforward process. By connecting to the target database and using the REPLICATE statement with the table name or appropriate options, users can easily initiate the replication process. The INCLUDE and EXCLUDE options offer flexibility in selecting tables or schemas for replication, ensuring efficient data transfers.

For example, to replicate a table name mytable from the database name myDBname, do the following:

CONNECT TO VERTICA myDBname USER dbadmin PASSWORD 'mypassword' ON 'vertica_node01', 5433;

REPLICATE mytable FROM myDBname;;

Scheduled Replication: Automating Data Synchronization

Server-based replication allows for scheduled replications, automatically keeping tables in the target database up to date with changes in the source database. Users can optimize data transfer intervals based on factors like data size, resources, network bandwidth, and query time sensitivity. Monitoring ongoing replications can be done using the V_MONITOR.REPLICATION_STATUS system table, ensuring a clear view of the replication process.

For more comprehensive information on Server-Based Replication and detailed instructions on how to set up and use this feature in Vertica Version 23.3, refer to the official documentation available here, Server-based-replication.

The documentation provides in-depth insights, step-by-step guides, and best practices to help you harness the full potential of Server-Based Replication and enhance your data replication strategies in Vertica Eon Mode.

Happy exploring!