Select a documentation version: <button class="button button-with-icon cloud-select-dropdown__button button-primary" aria-haspopup="true">15.5.4</button>

- [Current (18.4.0)](https://docs.cloud.google.com/alloydb/omni/containers/current/docs/replicate-data-alloydb-omni.md.txt)
- [18.4.0](https://docs.cloud.google.com/alloydb/omni/containers/18.4.0/docs/replicate-data-alloydb-omni.md.txt)
- [18.3.0](https://docs.cloud.google.com/alloydb/omni/containers/18.3.0/docs/replicate-data-alloydb-omni.md.txt)
- [18.1.0](https://docs.cloud.google.com/alloydb/omni/containers/18.1.0/docs/replicate-data-alloydb-omni.md.txt)
- [17.10.0](https://docs.cloud.google.com/alloydb/omni/containers/17.10.0/docs/replicate-data-alloydb-omni.md.txt)
- [17.9.0](https://docs.cloud.google.com/alloydb/omni/containers/17.9.0/docs/replicate-data-alloydb-omni.md.txt)
- [17.7.0](https://docs.cloud.google.com/alloydb/omni/containers/17.7.0/docs/replicate-data-alloydb-omni.md.txt)
- [17.5.0](https://docs.cloud.google.com/alloydb/omni/containers/17.5.0/docs/replicate-data-alloydb-omni.md.txt)
- [16.14.0](https://docs.cloud.google.com/alloydb/omni/containers/16.14.0/docs/replicate-data-alloydb-omni.md.txt)
- [16.13.0](https://docs.cloud.google.com/alloydb/omni/containers/16.13.0/docs/replicate-data-alloydb-omni.md.txt)
- [16.11.0](https://docs.cloud.google.com/alloydb/omni/containers/16.11.0/docs/replicate-data-alloydb-omni.md.txt)
- [16.9.0](https://docs.cloud.google.com/alloydb/omni/containers/16.9.0/docs/replicate-data-alloydb-omni.md.txt)
- [16.8.0](https://docs.cloud.google.com/alloydb/omni/containers/16.8.0/docs/replicate-data-alloydb-omni.md.txt)
- [16.3.0](https://docs.cloud.google.com/alloydb/omni/containers/16.3.0/docs/replicate-data-alloydb-omni.md.txt)
- [15.18.0](https://docs.cloud.google.com/alloydb/omni/containers/15.18.0/docs/replicate-data-alloydb-omni.md.txt)
- [15.17.0](https://docs.cloud.google.com/alloydb/omni/containers/15.17.0/docs/replicate-data-alloydb-omni.md.txt)
- [15.15.0](https://docs.cloud.google.com/alloydb/omni/containers/15.15.0/docs/replicate-data-alloydb-omni.md.txt)
- [15.13.0](https://docs.cloud.google.com/alloydb/omni/containers/15.13.0/docs/replicate-data-alloydb-omni.md.txt)
- [15.12.0](https://docs.cloud.google.com/alloydb/omni/containers/15.12.0/docs/replicate-data-alloydb-omni.md.txt)
- [15.7.1](https://docs.cloud.google.com/alloydb/omni/containers/15.7.1/docs/replicate-data-alloydb-omni.md.txt)
- [15.7.0](https://docs.cloud.google.com/alloydb/omni/containers/15.7.0/docs/replicate-data-alloydb-omni.md.txt)
- [15.5.5](https://docs.cloud.google.com/alloydb/omni/containers/15.5.5/docs/replicate-data-alloydb-omni.md.txt)
- [15.5.4](https://docs.cloud.google.com/alloydb/omni/containers/15.5.4/docs/replicate-data-alloydb-omni.md.txt)
- [15.5.2](https://docs.cloud.google.com/alloydb/omni/containers/15.5.2/docs/replicate-data-alloydb-omni.md.txt)

<br />

This page provides steps to replicate data between Google Cloud AlloyDB and AlloyDB Omni using the `pglogical` extension.

<br />

For an overview of `pglogical` in AlloyDB Omni, its benefits, and limitations, see [About the pglogical extension](https://docs.cloud.google.com/alloydb/omni/containers/15.5.4/docs/about-pglogical.md.txt).


> [!CAUTION]
> The information on this page applies only to AlloyDB Omni for containers. It does not apply to AlloyDB Omni for Kubernetes.

<br />

## Key components of `pglogical`

Key components of the `pglogical` extension are as follows:

- **Node** : reference given for the database within a PostgreSQL cluster. The `pglogical` extension is installed into, and works against any number of databases within the cluster, and each acts as a distinct pglogical node. Each node can be either a provider also known as replication source or subscriber also known as replication target, or both concurrently. Only one node is allowed per database.
- **Replication set** : defined in the provider database as a logical grouping of tables and sequences to be migrated, and the SQL statements such as `INSERT, UPDATE, DELETE, TRUNCATE` that need to replicated. You can assign tables to more than one replication set. By default, three pre-configured replication sets such as `default`, `default_insert_only`, and `ddl_sql` are provided, and you can add any number of additional replication sets to meet your needs.
- **Subscription** : provides details of the changes that are replicated from provider databases and changes that are replicated from provider databases, in the subscriber database. The subscription specifies the provider database through a connection string, and optionally, which replication sets from that provider should be copied. Additional, you can also specify whether to use `apply delay` when you create the subscription.

In this deployment, the Google Cloud AlloyDB service is
the provider and the on-premises AlloyDB Omni is the subscriber.
Note that the opposite configuration is also possible.

## Supported authentication methods

You must consider networking and security between the replication
nodes before implementing the `pglogical` extension on AlloyDB Omni.
The two main [authentication methods](https://www.postgresql.org/docs/current/auth-methods.html)
used with the `pglogical` extension are password and trust authentication
methods.

The recommended authentication method is trust authentication because in
the password authentication method, passwords are stored in plain text format
in database tables owned by `pglogical`. These passwords are visible in plain
text to anyone with database permissions to query these tables, in non-binary backups,
and in the PostgreSQL log files.

If you are using the trust authentication method, you must make specific entries
in the host-based authentication file, `pg_hba.conf` for maximum security. You can
restrict the access by specifying the target databases, permitting only the replication
option or specific databases, the replication user, and only from the subscriber's
specific IP address.

> [!IMPORTANT]
> **Important:** The trust authentication method is not available for the Google Cloud AlloyDB service. For those connections, use the password authentication method.

## Before you begin

You can install `pglogical` as an extension within a given database.

Before implementing the `pglogical` extension on AlloyDB Omni, ensure
that you meet the following system requirements:

- A Google Cloud AlloyDB cluster, and read/write access to the primary instance as a Cloud AlloyDB Admin. For instructions on how to provision a Google Cloud AlloyDB cluster, see [Create and connect to a database](https://docs.cloud.google.com/alloydb/docs/quickstart/create-and-connect.md.txt).
- An AlloyDB Omni server, installed and configured. For instructions on how to install AlloyDB Omni, see [Install AlloyDB Omni](https://docs.cloud.google.com/alloydb/omni/containers/15.5.4/docs/install.md.txt).
- The IP addresses for both the Google Cloud AlloyDB's primary instance and the AlloyDB Omni host server.
- An established and secured network between the Google Cloud AlloyDB and the AlloyDB Omni host server. TCP connectivity on the standard PostgreSQL port of 5432 is required.

## Adjust parameters on the Google Cloud AlloyDB provider

The `pglogical` extension requires a minimal set of parameter adjustments on the
Google Cloud AlloyDB provider cluster. You must set the
`wal_level` parameter to `logical`, and append `pglogical` to the `shared_preload_libraries`
parameter in the `postgresql.conf` file.

```bash
   cp postgresql.conf postgresql.bak
   sed -r -i "s|(\#)?wal_level\s*=.*|wal_level=logical|" postgresql.conf
   sed -r -i "s|(\#)?(shared_preload_libraries\s*=\s*)'(.*)'.*$|\2'\3,pglogical'|" postgresql.conf
   sed -r -i "s|',|'|" postgresql.conf
```

In the Google Cloud AlloyDB service, you can adjust parameters
by setting the appropriate cluster flags.

You must adjust parameters for the following Google Cloud AlloyDB flags:

- `alloydb.enable_pglogical = on`
- `alloydb.logical_decoding = on`

For information about how to set database flags in Google Cloud
AlloyDB, see
[Configure an instance's database flags](https://docs.cloud.google.com/alloydb/docs/instance-configure-database-flags.md.txt).

For the other required provider-node database parameters, you must set the
Google Cloud AlloyDB default values as follows:

- `max_worker_processes`: one per provider database and at least one per subscriber node. At least 10 is the standard for this parameter.
- `max_replication_slots`: one per node on provider nodes.
- `max_wal_senders`: one per node on provider nodes.
- `track_commit_timestamp`: set to `on` if the last or first update wins conflict resolution is required.
- `listen_addresses`: must include the AlloyDB Omni IP address or mention through a covering CIDR block.

You can check these parameters using any query tool, such as `psql`.

## Adjust parameters on the AlloyDB Omni subscriber cluster

The `pglogical`extension requires a minimal set of parameter adjustments on the
AlloyDB Omni subscriber too. You must append `pglogical`
to the `shared_preload_libraries` parameter in the `DATA_DIR/postgresql.conf`
file. If any database within the cluster acts as a provider database, then make
the parameter changes required for provider databases.

Replace <var translate="no">DATA_DIR</var> with the file system path to your data directory---for example, `/home/$USER/alloydb-data`.

1. Adjust the parameters:

   ```bash
   sudo sed -r -i "s|(shared_preload_libraries\s*=\s*)'(.*)'.*$|\1'\2,pglogical'|" DATA_DIR/postgresql.conf
   ```
2. Verify that the parameter is set properly:

   ```bash
   grep -iE 'shared_preload_libraries' DATA_DIR/postgresql.conf
   ```
3. Restart AlloyDB Omni for the parameter change to take effect:

   ```bash
   docker container restart CONTAINER_NAME
   ```

   Replace `CONTAINER_NAME` with the name that you assigned to the AlloyDB Omni container when you installed it.
4. Set the AlloyDB Omni default values for other provider database
   parameters:

   - `max_worker_processes`: One per provider database and one per subscriber node.
   - `track_commit_timestamp`: Set to `on` if the last or first update wins conflict resolution is required.
5. Confirm all parameter values are set correctly:

   ```bash
   docker exec CONTAINER_NAME psql -h localhost -U postgres -c "
   SELECT name, setting
     FROM pg_catalog.pg_settings
    WHERE name IN ('listen_addresses',
                   'wal_level',
                   'shared_preload_libraries',
                   'max_worker_processes',
                   'max_replication_slots',
                   'max_wal_senders',
                   'track_commit_timestamp')
        ORDER BY name;
   "
   ```

## Host-based authentication adjustments to the AlloyDB Omni subscriber cluster

The `pglogical` makes local TCP connections to the AlloyDB Omni subscriber
database. Therefore, you must add the subscriber's host server's IP address to
the AlloyDB Omni `DATA_DIR/pg_hba.conf` file.

1. Add a trust authentication entry for the local server, specific to a new
   `pglogical_replication` user, to the `DATA_DIR/pg_hba.conf` file:

   ```bash
   echo -e "# pglogical entries:
   host all pglogical_replication samehost trust
   " | column -t | sudo tee -a DATA_DIR/pg_hba.conf
   ```

   > [!NOTE]
   > **Note:** Specify and limit connections to the exact database names instead of `all`.

2. Verify that the entry is correct:

   ```bash
   tail -2 DATA_DIR/pg_hba.conf
   ```
3. Restart AlloyDB Omni for the authentication change to take
   effect:

   ```bash
   docker container restart CONTAINER_NAME
   ```

## Create a `pglogical` user in provider and subscriber clusters

You must create a new user in both the provider and subscriber cluster.
`pglogical` requires the user to have both the `superuser` and `replication`
permissions.

1. In the Google Cloud AlloyDB provider cluster, create the
   user and grant the `alloydbsuperuser` role:

   ```sql
   CREATE USER pglogical_replication LOGIN PASSWORD 'secret';
   ALTER USER pglogical_replication WITH replication;
   GRANT alloydbsuperuser TO pglogical_replication;
   ```
2. In the AlloyDB Omni subscriber cluster, create the user and
   grant the `replication` and `superuser` attributes:

   ```sql
   CREATE USER pglogical_replication LOGIN PASSWORD 'secret';
   ALTER USER pglogical_replication WITH replication;
   ALTER USER pglogical_replication WITH superuser;
   ```

## Add `pglogical` and nodes to the Google Cloud AlloyDB provider database

1. Grant required privileges.

   You must install the `pglogical` extension in each database and grant the
   `usage` permission to the pglogical database user. In Google Cloud AlloyDB,
   you must grant privileges on the `pglogical` schema.

   For example, if your database is `my_test_db`, run the following command
   against the Google Cloud AlloyDB provider database:

   ```sql
      \c my_test_db;
    CREATE EXTENSION IF NOT EXISTS pglogical;
    GRANT usage ON SCHEMA pglogical TO pglogical_replication;
   -- For Google Cloud AlloyDB we also need to manually grant privileges:
    GRANT ALL PRIVILEGES ON ALL tables IN SCHEMA pglogical TO pglogical_replication;
   ```
2. Create a `pglogical` node for the provider databases.
   The `node_name` is arbitrary and the `dsn` string must be a valid TCP connection
   back to the same database. For Google Cloud AlloyDB, the
   host part of the `dsn` is the IP address provided for the primary instance.

   For Google Cloud AlloyDB, trust authentication is
   not permitted, and the password argument must be included in the `dsn`.
   parameter.

   For example, for the `my_test_db` database, run the following command:

   ```sql
   SELECT pglogical.create_node(node_name := 'provider', dsn := 'host=SERVER_IP_ADDRESS
   port=5432 dbname=my_test_db user=pglogical_replication password=secret');
   ```

## Create a table and add it to the default replication set

Create a table and add it to the default replication set on the Google Cloud
AlloyDB provider database.

1. Create a test table called `test_table_1` in the provider database:

   ```sql
   CREATE TABLE test_table_1 (col1 INT PRIMARY KEY);
   INSERT INTO test_table_1 VALUES (1),(2),(3);
   ```
2. Grant `SELECT` on the individual tables or run the `GRANT SELECT ON ALL
   TABLES` command. Any tables that are to be part of a replication set must have
   query permission granted to the replication user, `pglogical_replication`.

   ```sql
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO pglogical_replication;
   ```
3. Manually add the test table to the default replication set.
   You can either create custom pglogical replication sets, or you can use the default
   replication sets. Several default replication sets such as `default`, `default_insert_only`,
   and `ddl_sql`were created when you created the extension. You can add tables
   and sequences to the replication sets individually, or all at once for a specified
   schema.

   ```sql
   -- Add the specified table to the default replication set:
   SELECT pglogical.replication_set_add_table(set_name := 'default', relation := 'test_table_1', synchronize_data := TRUE);

   -- Check which tables have been added to all replication sets:
   SELECT * FROM pglogical.replication_set_table;
   ```
4. (Optional) Add all tables in a specified schema, such as `public`:

   ```sql
   -- Add all "public" schema tables to the default replication set:
   SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

   -- Check which tables have been added to all replication sets:
   SELECT * FROM pglogical.replication_set_table;

   -- Add all "public" schema sequences to the default replication:
   SELECT pglogical.replication_set_add_all_sequences('default', ARRAY['public']);

    -- Check which sequences have been added to all replication sets:
   SELECT * FROM pglogical.replication_set_seq;
   ```
5. Remove the table from the `default` replication set.
   If there are any tables in the schema that do not have a primary key, then you
   can either set it up for INSERT only replication or set the columns that uniquely
   identify the row by using the `REPLICA IDENTITY` feature used with the
   `ALTER TABLE` command. If you have added those tables to the `default` replication
   set automatically using the `replication_set_add_all_tables` function, then
   you must manually remove them from that replication set and add them to the
   `default_insert_only` set.

   ```sql
   -- Remove the table from the **default** replication set:
   SELECT pglogical.replication_set_remove_table(set_name := 'default', relation := 'test_table_2');
   ```

   ```sql
   -- Manually add to the **default_insert_only** replication set:
   SELECT pglogical.replication_set_add_table(set_name := 'default_insert_only', relation := 'test_table_2');
   ```

   Optionally, if you want to add the newly created tables to the
   replication set automatically, add the `pglogical_assign_repset` trigger as
   suggested in the [`pglogical` source](https://github.com/2ndQuadrant/pglogical#automatic-assignment-of-replication-sets-for-new-tables).

## Copy the database to the AlloyDB Omni subscriber cluster

1. Create a schema-only backup of the source database using the `pg_dump` utility.

2. Run the `pg_dump`command from your AlloyDB Omni subscriber
   server using the IP address of the Google Cloud AlloyDB
   primary instance.

   > [!NOTE]
   > **Note:** You must run the `pg_dump` command from outside of the AlloyDB Omni container. It is recommended that you run the `pg_dump` command from your AlloyDB Omni host server if you can install the PostgreSQL client. You can also run the command from another server, if necessary. For more information about exporting using `pg_dump` see [Export a DMP file](https://docs.cloud.google.com/alloydb/docs/export-dmp-file.md.txt).

   ```bash
   pg_dump -h SERVER_IP_ADDRESS -U postgres --create --schema-only my_test_db > my_test_db.schema-only.sql
   ```
3. Import the backup into the subscriber database on the subscriber AlloyDB Omni
   server:

   ```bash
   docker exec -i CONTAINER_NAME psql -h localhost -U postgres < my_test_db.schema-only.sql
   ```

Ignore errors such as `alloydbsuperuser not existing`. This role is specific to
Google Cloud AlloyDB.

This creates the database and the schema, without any of the row data. Row
data is replicated by the `pglogical` extension. Manually copy or recreate any
other users or roles that are required.

## Create a node and subscription on the AlloyDB Omni subscriber database

1. Create a node on the AlloyDB Omni subscriber database:

   ```bash
   docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=localhost port=5432 dbname=my_test_db user=pglogical_replication');
   "
   ```
2. Create a subscription in the subscriber database, pointing back to the
   Google Cloud AlloyDB provider database's primary instance.

   > [!NOTE]
   > **Note:** For Google Cloud AlloyDB, trust authentication is not available. You must include the password argument in the `dsn` parameter.

   > [!CAUTION]
   > **Caution:** Ensure that the connection details in the **`dsn`** parameter are accurate.

   ```bash
   docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT pglogical.create_subscription(subscription_name := 'test_sub_1', provider_dsn := 'host=SERVER_IP_ADDRESS port=5432 dbname=my_test_db user=pglogical_replication password=secret');
   "
   ```
3. Based on your table size and data to be replicated, the replication time might vary from seconds to minutes, after which the initial data should have replicated from the provider to the subscriber:

   ```bash
   docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT * FROM test_table_1 ORDER BY 1;
   "
   ```

   Additional rows that are added to the provider database are also
   replicated within seconds.

## Additional `pglogical` deployment considerations

The `pglogical` extension has many advanced features that are not covered in this document.
Many of these features are applicable to your implementation. You can consider the
following advanced features:

- Conflict resolution
- Multimaster and bi-directional replication
- Inclusion of sequences
- Switchover and failover procedures

## What's next

- [Replicate data between AlloyDB Omni and other databases](https://docs.cloud.google.com/alloydb/omni/containers/15.5.4/docs/replicate-data-omni-other-db.md.txt)
- [Switchover and failover with `pglogical` replication](https://docs.cloud.google.com/alloydb/omni/containers/15.5.4/docs/switchover-failover.md.txt)