Logical replication with AlloyDB Omni

1. Introduction

This codelab shows how to configure logical replication between independent AlloyDB Omni instances. You will deploy multiple AlloyDB Omni instances on a single Compute Engine VM and set up replication between them using the pglogical extension.

Running multiple instances on a single node is useful for development, testing, or simulating replication environments. At the end of this codelab, you will have a working replication setup with one instance acting as a provider and another as a subscriber.

Prerequisites

• Access to a Google Cloud project and Cloud Shell from the Google Cloud console.
• Google Cloud SDK (gcloud) installed and configured. For details on installing gcloud, refer to gcloud-install

What you'll learn

• How to create and prepare a Compute Engine VM for multiple AlloyDB Omni instances using gcloud.
• How to install AlloyDB Omni RPMs.
• How to configure and initialize multiple AlloyDB Omni instances using systemd templates.
• How to configure logical replication between AlloyDB Omni instances using pglogical.
• How to verify replication by replicating data from provider to subscriber.

What you'll need

• Obtain a URL link to AlloyDB Omni RPMs. The links are sent to your email, including a link to the repository URL server. Initialize the following environment variable to keep the URL ready to use during the codelab.

  export ALLOYDB_OMNI_REPOSITORY_URL="..."
  

• A working terminal with access to Google Cloud. You can use Cloud Shell for this purpose.

2. Setup and Requirements

Project setup

Create a Google Cloud project

1. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
2. Make sure that billing is enabled for your Cloud project. Learn how to check if billing is enabled on a project.

Start Cloud Shell

Although you can operate Google Cloud remotely from your laptop, in this codelab you use the Google Cloud Shell, a command-line environment running in the cloud.

1. Open the Google Cloud console and click the Cloud Shell icon on the top right toolbar.You can also open the Cloud Shell in one of the following ways:
   • If you're using the Google Cloud console, press G and then S.
   • Click this link.

Cloud Shell typically takes a few moments to provision and connect to the environment. When this operation completes, you see output similar to the following:

3. Create the Compute Engine VM

In this section, create a single Compute Engine virtual machine (VM) using the gcloud command-line tool. Use a Rocky Linux 9 image, which is fully compatible with AlloyDB Omni RPMs.

1. Define environment variables for your project ID, VM name, zone, and region.

   export PROJECT="your-project-id"
   export INSTANCE_NAME="alloydb-omni-multi-vm"
   export ZONE="us-central1-c"
   export REGION="us-central1"
   

2. Sign in using your Google Cloud account, and configure the default project.

   gcloud auth login
   gcloud config set project $PROJECT
   

3. To allow SSH connections to your VM using Identity-Aware Proxy (IAP), create a firewall rule.

   gcloud compute firewall-rules create allow-ssh-alloydb-omni \
       --project=$PROJECT \
       --direction=INGRESS \
       --action=allow \
       --rules=tcp:22 \
       --source-ranges="0.0.0.0/0"
   

4. Because the VM is created without a public IP address, configure a Cloud Router and Cloud NAT gateway to let the VM access external package repositories and download required software.

   gcloud compute routers create alloydb-omni-router \
       --project=$PROJECT \
       --network=default \
       --region=$REGION
   
   gcloud compute routers nats create alloydb-omni-nat \
       --project=$PROJECT \
       --router=alloydb-omni-router \
       --region=$REGION \
       --nat-all-subnet-ip-ranges \
       --auto-allocate-nat-external-ips
   

5. Verify that your virtual private cloud (VPC) network has a valid default internet gateway route. This route is required for Cloud NAT to forward outbound traffic to the internet.

   gcloud compute routes create default-internet-route \
       --project=$PROJECT \
       --network=default \
       --destination-range=0.0.0.0/0 \
       --next-hop-gateway=default-internet-gateway
   

6. Create the Compute Engine VM instance with the required specifications (16 vCPUs, 20GB balanced persistent disk, Rocky Linux 9). For enhanced security, use the --no-address flag to make sure that the VM isn't assigned a public IP address.

   gcloud compute instances create $INSTANCE_NAME \
       --project=$PROJECT \
       --zone=$ZONE \
       --machine-type=n2-highmem-16 \
       --image-family=rocky-linux-9 \
       --image-project=rocky-linux-cloud \
       --boot-disk-size=20GB \
       --boot-disk-type=pd-balanced \
       --no-address
   

7. After the VM is created, verify the VM's status.

   gcloud compute instances list --filter="name=$INSTANCE_NAME" --project=$PROJECT
   

4. Prepare VM

In this section, you connect to your newly created VM and prepare the operating system environment.

1. Connect to the VM over SSH using IAP. Because the VM doesn't have a public IP address, gcloud automatically tunnels the SSH connection through IAP.

   gcloud compute ssh $INSTANCE_NAME --zone=$ZONE --project=$PROJECT --tunnel-through-iap
   

2. Define an environment variable for the base data directory.

   export BASE_PGDATA="/var/lib/alloydbomni"
   

3. Create the base data directory.

   sudo mkdir -p $BASE_PGDATA
   

4. Install the policycoreutils-python-utils package so that the SELinux policy can be configured.

   sudo dnf install -y policycoreutils-python-utils
   

5. Install AlloyDB Omni

With the VM prepared, you can now configure the package repository and install the AlloyDB Omni RPM package.

1. Configure the YUM repository on your VM by creating the repository configuration file. Replace $ALLOYDB_OMNI_REPOSITORY_URL with the URL that you received.

   export ALLOYDB_OMNI_REPOSITORY_URL="..."
   sudo tee /etc/yum.repos.d/alloydbomni.repo << EOF
   [alloydbomni]
   name=alloydbomni
   baseurl=$ALLOYDB_OMNI_REPOSITORY_URL
   enabled=1
   repo_gpgcheck=0
   gpgcheck=1
   gpgkey=https://dl.google.com/linux/linux_signing_key.pub
   EOF
   

2. Update the YUM cache.

   sudo yum makecache
   

3. Install version 18 of the AlloyDB Omni RPM package.

   sudo dnf install -y alloydbomni18
   

6. Configure multiple instances

In this section, configure two instances: inst1 and inst2.

1. To support multiple instances, create the systemd template service file alloydbomni18@.service.

   sudo tee /etc/systemd/system/alloydbomni18@.service << EOF
   [Unit]
   Description=AlloyDB Omni Database server instance %I
   After=syslog.target
   After=network-online.target
   
   [Service]
   SyslogIdentifier=alloydbomni-%I
   Type=notify
   
   User=postgres
   Group=postgres
   
   # Load configuration for the instance
   EnvironmentFile=/etc/alloydbomni/%I.conf
   
   OOMScoreAdjust=-1000
   Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
   Environment=PG_OOM_ADJUST_VALUE=0
   
   ExecStartPre=/usr/lib/postgresql/18/bin/alloydbomni18-check-db-dir \${PGDATA}
   ExecStart=/usr/lib/postgresql/18/bin/postgres -D \${PGDATA}
   ExecReload=/bin/kill -HUP \$MAINPID
   
   KillMode=mixed
   KillSignal=SIGINT
   
   TimeoutSec=0
   TimeoutStartSec=0
   TimeoutStopSec=1h
   
   [Install]
   WantedBy=multi-user.target
   EOF
   

2. Reload the systemd manager configuration to recognize the new template service.

   sudo systemctl daemon-reload
   

3. Create the configuration directory for AlloyDB Omni instances.

   sudo mkdir -p /etc/alloydbomni
   

4. Configure the data directory for inst1.

   sudo tee /etc/alloydbomni/inst1.conf << EOF
   PGDATA=/var/lib/alloydbomni/inst1
   EOF
   

5. Configure the data directory for inst2.

   sudo tee /etc/alloydbomni/inst2.conf << EOF
   PGDATA=/var/lib/alloydbomni/inst2
   EOF
   

6. Set appropriate permissions on the configuration files.

   sudo chmod 640 /etc/alloydbomni/*.conf
   sudo chown postgres:postgres /etc/alloydbomni/*.conf
   

7. Create the data directories for both instances and set the correct ownership.

   sudo mkdir -p /var/lib/alloydbomni/inst1
   sudo mkdir -p /var/lib/alloydbomni/inst2
   sudo chown -R postgres:postgres /var/lib/alloydbomni
   sudo chmod 750 /var/lib/alloydbomni/inst1 /var/lib/alloydbomni/inst2
   

8. Optional: Set resource limits for each instance. This prevents one instance from consuming all VM resources. Create systemd override directories and files for both instances.Note: The resource limits used in the following configurations (40%) are example values. Adjust the limits based on your specific workload and VM capacity.For inst1:

   sudo mkdir -p /etc/systemd/system/[e-mail].d
   sudo tee /etc/systemd/system/[e-mail].d/override.conf << EOF
   [Service]
   CPUQuota=40%
   MemoryMax=40%
   EOF
   

   For inst2:

   sudo mkdir -p /etc/systemd/system/[e-mail].d
   sudo tee /etc/systemd/system/[e-mail].d/override.conf << EOF
   [Service]
   CPUQuota=40%
   MemoryMax=40%
   EOF
   

7. Initialize and prepare databases

Initialize and prepare each database instance separately.

1. Configure SELinux to permissive mode for the setup.

   sudo setenforce 0
   

   Caution: Setting SELinux to permissive mode disables enforcement and should be used with caution. It is used in this codelab to simplify the installation. In production, configure appropriate SELinux policies for AlloyDB Omni to run in enforcing mode. You can re-enable enforcement after setup by running sudo setenforce 1.
2. Define a strong password for the PostgreSQL postgres administrator user. This password is used for both instances in this codelab.

   export PGPASSWORD="YourStrongPassword123!"
   

   Note: Passing passwords using environment variables on the command line (e.g., PGPASSWORD=$PGPASSWORD) is used here for simplicity in the codelab, but it can expose the password in process listings. In production, use more secure methods such as a .pgpass file or a secret manager.

Set up inst1

1. Initialize the database directory for inst1.

   sudo PGPASSWORD=$PGPASSWORD \
     PGDATA=/var/lib/alloydbomni/inst1 \
     /usr/lib/postgresql/18/bin/alloydbomni18-setup initdb
   

2. Configure the instance to listen on port 5433 by updating its postgresql.conf file.

   sudo sed -i \
     "s/^#*[[:space:]]*port[[:space:]]*=[[:space:]]*[0-9]*/port = 5433/" \
     /var/lib/alloydbomni/inst1/postgresql.conf
   

3. Prepare the database instance for AlloyDB Omni.

   sudo PGPASSWORD=$PGPASSWORD \
     PGDATA=/var/lib/alloydbomni/inst1 \
     PGPORT=5433 \
     /usr/lib/postgresql/18/bin/alloydbomni18-setup preparedb
   

Set up inst2

1. Initialize the database directory for inst2.

   sudo PGPASSWORD=$PGPASSWORD \
     PGDATA=/var/lib/alloydbomni/inst2 \
     /usr/lib/postgresql/18/bin/alloydbomni18-setup initdb
   

2. Configure the instance to listen on port 5434.

   sudo sed -i \
     "s/^#*[[:space:]]*port[[:space:]]*=[[:space:]]*[0-9]*/port = 5434/" \
     /var/lib/alloydbomni/inst2/postgresql.conf
   

3. Prepare the database instance.

   sudo PGPASSWORD=$PGPASSWORD \
     PGDATA=/var/lib/alloydbomni/inst2 \
     PGPORT=5434 \
     /usr/lib/postgresql/18/bin/alloydbomni18-setup preparedb
   

8. Start AlloyDB Omni services

Now you can start the systemd services for both instances.

1. To recognize the new configuration and overrides, reload the systemd manager configuration.

   sudo systemctl daemon-reload
   

2. Enable and start the service for inst1.

   sudo systemctl enable alloydbomni18@inst1
   sudo systemctl start alloydbomni18@inst1
   

3. Enable and start the service for inst2.

   sudo systemctl enable alloydbomni18@inst2
   sudo systemctl start alloydbomni18@inst2
   

4. Verify that both services are active and running.

   sudo systemctl status alloydbomni18@inst1
   sudo systemctl status alloydbomni18@inst2
   

   Verify that the output for both indicates Active: active (running).

9. Verify AlloyDB Omni Instances

Verify that both instances are functioning correctly and are independent.

1. Connect to inst1 (port 5433). You are prompted for the password that you configured in Initialize and prepare databases.

   /usr/lib/postgresql/18/bin/psql -h localhost -p 5433 -U postgres -W
   

2. Run a query to verify the connection.

   postgres=# SELECT version();
   

3. Disconnect from the instance.

   postgres=# \q
   

4. Connect to inst2 (port 5434).

   /usr/lib/postgresql/18/bin/psql -h localhost -p 5434 -U postgres -W
   

5. Run a query to verify the connection.

   postgres=# SELECT version();
   

6. Disconnect from the instance.

   postgres=# \q
   

10. Configure for pglogical replication

Configure logical replication between the two instances using the pglogical extension. In this scenario, inst1 acts as the provider and inst2 acts as the subscriber.

Note: This codelab uses the postgres superuser for the replication connections. In a production environment, create a dedicated replication user with REPLICATION privileges and grant it access only to the tables being replicated.

Configure instances for pglogical

1. Enable pglogical in shared_preload_libraries and configure other replication parameters in postgresql.conf for inst1.

   # Append pglogical to shared_preload_libraries
   sudo sed -i \
     "s/\(shared_preload_libraries\s*=\s*'\)\([^']*\)'/\1\2,pglogical'/" \
     /var/lib/alloydbomni/inst1/postgresql.conf
   
   # Append replication settings
   sudo tee -a /var/lib/alloydbomni/inst1/postgresql.conf << EOF
   wal_level = logical
   max_replication_slots = 10
   max_wal_senders = 10
   max_worker_processes = 20
   track_commit_timestamp = on
   alloydb.enable_pglogical = on
   alloydb.logical_decoding = on
   EOF
   

2. Configure the settings for inst2 that you configured for inst1.

   # Append pglogical to shared_preload_libraries
   sudo sed -i \
     "s/\(shared_preload_libraries\s*=\s*'\)\([^']*\)'/\1\2,pglogical'/" \
     /var/lib/alloydbomni/inst2/postgresql.conf
   
   # Append replication settings
   sudo tee -a /var/lib/alloydbomni/inst2/postgresql.conf << EOF
   wal_level = logical
   max_replication_slots = 10
   max_wal_senders = 10
   max_worker_processes = 20
   track_commit_timestamp = on
   alloydb.enable_pglogical = on
   alloydb.logical_decoding = on
   EOF
   

3. To apply the configuration changes, restart both instances.

   sudo systemctl restart alloydbomni18@inst1
   sudo systemctl restart alloydbomni18@inst2
   

Set up replication roles and tables

4. On inst1 (provider, port 5433), create a replication role and the table to be replicated.

   /usr/lib/postgresql/18/bin/psql -h localhost -p 5433 \
     -U postgres -d postgres -c "
     DO \$\$
     BEGIN
       IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'app_owner') THEN
         CREATE ROLE app_owner WITH LOGIN PASSWORD 'Password123';
       END IF;
     END
     \$\$;
     GRANT ALL ON SCHEMA public TO app_owner;
     CREATE TABLE IF NOT EXISTS replication_test (
         id SERIAL PRIMARY KEY,
         data TEXT,
         created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
     );
     ALTER TABLE replication_test OWNER TO app_owner;
   "
   

5. On inst2 (subscriber, port 5434), create the same role and table structure.

   /usr/lib/postgresql/18/bin/psql -h localhost -p 5434 \
     -U postgres -d postgres -c "
     DO \$\$
     BEGIN
       IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'app_owner') THEN
         CREATE ROLE app_owner WITH LOGIN PASSWORD 'Password123';
       END IF;
     END
     \$\$;
     GRANT ALL ON SCHEMA public TO app_owner;
     CREATE TABLE IF NOT EXISTS replication_test (
         id SERIAL PRIMARY KEY,
         data TEXT,
         created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
     );
     ALTER TABLE replication_test OWNER TO app_owner;
   "
   

Configure pglogical nodes and subscription

6. Initialize the pglogical provider node on inst1.

   /usr/lib/postgresql/18/bin/psql -h localhost -p 5433 \
     -U postgres -d postgres -c "
     CREATE EXTENSION IF NOT EXISTS pglogical;
     SELECT pglogical.create_node(
         node_name := 'provider',
         dsn := 'host=127.0.0.1 port=5433 dbname=postgres user=postgres password=YourStrongPassword123!'
     );
     SELECT pglogical.replication_set_add_table('default', 'replication_test');
   "
   

7. Initialize the pglogical subscriber node and create the subscription on inst2.

   /usr/lib/postgresql/18/bin/psql -h localhost -p 5434 \
     -U postgres -d postgres -c "
     CREATE EXTENSION IF NOT EXISTS pglogical;
     SELECT pglogical.create_node(
         node_name := 'subscriber',
         dsn := 'host=127.0.0.1 port=5434 dbname=postgres user=postgres password=YourStrongPassword123!'
     );
     SELECT pglogical.create_subscription(
         subscription_name := 'test_subscription',
         provider_dsn := 'host=127.0.0.1 port=5433 dbname=postgres user=postgres password=YourStrongPassword123!'
     );
   "
   

   Note: After creating the subscription, it might take a moment for the initial synchronization to complete.

11. Verify pglogical replication

Verify that the replication is working as expected by inserting data on the provider and querying it on the subscriber.

1. Verify that the subscription is active. It might take a few seconds to transition to replicating.

   /usr/lib/postgresql/18/bin/psql -t -A -h localhost -p 5434 \
     -U postgres -d postgres -c "
     SELECT status FROM pglogical.show_subscription_status() WHERE subscription_name = 'test_subscription';
     "
   

2. Confirm that the output shows replicating before you continue.
3. Insert a test row on the provider (inst1).

   /usr/lib/postgresql/18/bin/psql -h localhost -p 5433 \
     -U postgres -d postgres -c "
     INSERT INTO replication_test (data) VALUES ('Hello from replication provider!');
   "
   

4. To verify that the data has replicated, query the table on inst2.

   /usr/lib/postgresql/18/bin/psql -h localhost -p 5434 \
     -U postgres -d postgres -c "SELECT * FROM replication_test;"
   

   Confirm that you can see the row inserted on inst1.

12. Cleanup

Clean up the resources. First, destroy the AlloyDB Omni instances on the VM, and then delete the Google Cloud resources.

Destroy AlloyDB Omni Instances

Before deleting the VM, you can clean up the AlloyDB Omni instances by stopping and disabling the services, and removing their configuration and data.

1. Stop and disable the service for inst1, and remove its data and configuration.

   sudo systemctl stop alloydbomni18@inst1
   sudo systemctl disable alloydbomni18@inst1
   sudo rm -rf /var/lib/alloydbomni/inst1
   sudo rm -f /etc/alloydbomni/inst1.conf
   

2. Stop and disable the service for inst2, and remove its data and configuration.

   sudo systemctl stop alloydbomni18@inst2
   sudo systemctl disable alloydbomni18@inst2
   sudo rm -rf /var/lib/alloydbomni/inst2
   sudo rm -f /etc/alloydbomni/inst2.conf
   

3. Reload the systemd manager configuration and reset failed units.

   sudo systemctl daemon-reload
   sudo systemctl reset-failed
   

Clean up Google Cloud Resources

After you clean up the instances, delete the Compute Engine VM and other resources.

1. Disconnect from the SSH session.

   exit
   

2. Delete the Compute Engine VM instance.

   gcloud compute instances delete $INSTANCE_NAME --zone=$ZONE --project=$PROJECT --quiet
   

3. Delete the firewall rule.

   gcloud compute firewall-rules delete allow-ssh-alloydb-omni --project=$PROJECT --quiet
   

4. Delete the Cloud NAT and Router.

   gcloud compute routers nats delete alloydb-omni-nat --router=alloydb-omni-router --region=$REGION --project=$PROJECT --quiet
   gcloud compute routers delete alloydb-omni-router --region=$REGION --project=$PROJECT --quiet
   

5. Delete the default internet route.

   gcloud compute routes delete default-internet-route --project=$PROJECT --quiet
   

13. Congratulations

You have successfully deployed and configured multiple AlloyDB Omni instances on a single Compute Engine VM.

What we've covered

• How to provision and prepare a Compute Engine VM.
• How to install AlloyDB Omni RPMs.
• How to configure systemd templates for multiple instances.
• How to initialize, configure, and start multiple independent services on different ports.
• How to verify the independence of each instance.

Learn more

• Refer to the AlloyDB Omni documentation for more information on installation and configuration.
• For details on the replication extension used in this codelab, see the pglogical documentation.