This content originally appeared on DEV Community and was authored by Dmitry Romanoff
In today's data-driven world, the ability to replicate databases efficiently and seamlessly across different environments is crucial. One powerful tool for achieving this in PostgreSQL is pglogical, an extension that provides logical replication capabilities. In this article, we'll walk through the steps to set up logical replication between two PostgreSQL databases hosted on AWS using pglogical.
Prerequisites
Before diving into the setup, ensure you have the following:
- Two PostgreSQL databases running on AWS RDS (e.g., dmitry_test_src and dmitry_test_dest).
- PostgreSQL client installed on your local machine for executing commands.
- Necessary permissions to create roles, databases, and extensions.
- Set AWS RDS Source DB and Destination DB instance parameters:
rds.logical_replication=1 to achieve wal_level=logical
max_replication_slots=10
max_wal_senders=15
max_worker_processes=10
Step 1: Create Roles and Databases
Source Database Setup
Start by creating a role and database for the source:
CREATE ROLE dmitry_test_src WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION PASSWORD '1234567';
CREATE DATABASE dmitry_test_src;
GRANT CONNECT ON DATABASE dmitry_test_src TO dmitry_test_src;
GRANT ALL PRIVILEGES ON DATABASE dmitry_test_src TO dmitry_test_src;
GRANT dmitry_test_src TO root;
ALTER DATABASE dmitry_test_src OWNER TO dmitry_test_src;
Destination Database Setup
Next, create a similar setup for the destination:
CREATE ROLE dmitry_test_dest WITH LOGIN NOSUPERUSER INHERIT CREATEDB NOCREATEROLE NOREPLICATION PASSWORD '1234567';
CREATE DATABASE dmitry_test_dest;
GRANT CONNECT ON DATABASE dmitry_test_dest TO dmitry_test_dest;
GRANT ALL PRIVILEGES ON DATABASE dmitry_test_dest TO dmitry_test_dest;
GRANT dmitry_test_dest TO root;
ALTER DATABASE dmitry_test_dest OWNER TO dmitry_test_dest;
Step 2: Install pglogical Extension
Now, connect to both databases and create the pglogical extension.
On Source Database
psql -h localhost -p 5432 -U dmitry_test_src -d dmitry_test_src
CREATE EXTENSION pglogical;
On Destination Database
psql -h localhost -p 5432 -U dmitry_test_dest -d dmitry_test_dest
CREATE EXTENSION pglogical;
Step 3: Configure the Source Node
In the source database, configure the pglogical node:
SELECT pglogical.create_node(
node_name := 'src',
dsn := 'host=dmitry-test-src.abcdeghijklmno.us-east-1.rds.amazonaws.com port=5432 user=root dbname=dmitry_test_src password=********'
);
Next, add all public tables to the replication set:
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
Step 4: Configure the Destination Node
On the destination database, set up the pglogical node:
SELECT pglogical.create_node(
node_name := 'dest',
dsn := 'host=dmitry-test-dest.pqrstuvwxyz.us-east-1.rds.amazonaws.com port=5432 user=root dbname=dmitry_test_dest password=********'
);
Step 5: Create Subscription on the Destination
Finally, create a subscription on the destination database to replicate data from the source:
SELECT pglogical.create_subscription(
subscription_name := 'subscription1',
provider_dsn := 'host=dmitry-test-src.abcdeghijklmno.us-east-1.rds.amazonaws.com port=5432 user=root dbname=dmitry_test_src password=********'
);
Step 6: Monitor Subscription Status
You can monitor the status of your subscription by running:
SELECT subscription_name, status FROM pglogical.show_subscription_status();
Step 7: Drop Subscription (If Needed)
If you need to remove the subscription at any point, you can execute:
SELECT pglogical.drop_subscription('subscription1');
Conclusion
Setting up logical replication using pglogical in PostgreSQL on AWS can greatly enhance data availability and scalability. By following the steps outlined above, you can effectively configure a replication environment tailored to your needs. As always, ensure you monitor the performance and status of your replication setup to maintain data integrity across your databases.
This content originally appeared on DEV Community and was authored by Dmitry Romanoff
Dmitry Romanoff | Sciencx (2024-10-29T13:13:13+00:00) Setting Up Logical Replication with pglogical in PostgreSQL on AWS. Retrieved from https://www.scien.cx/2024/10/29/setting-up-logical-replication-with-pglogical-in-postgresql-on-aws/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.