Migrating legacy PostgreSQL databases to Amazon RDS or Aurora PostgreSQL using Bucardo
If you are using PostgreSQL earlier than 9.4, you are using an unsupported version of PostgreSQL, and may have limited options to migrate or replicate your databases in Amazon RDS or Amazon Aurora PostgreSQL. This is primarily because PostgreSQL versions older than 9.4 can’t perform logical replication.
Bucardo is an open-source utility that can replicate data changes asynchronously to multiple secondary or multiple masters. It is a trigger-based replication and proven to be consistent and stable for more extensive migrations and ongoing replications. Bucardo can perform full load for tables without a primary key. However, to replicate delta data changes from Primary, create a primary key before you start the setup.
This post demonstrates how to set up Bucardo and replicate data changes from PostgreSQL 8.4 to PostgreSQL 9.6.
Before getting started, you must have the following:
- One EC2 instance with Ubuntu 16.04 for Bucardo (Bucardo Server: 172.31.88.4)
- One EC2 instance with RHEL 6 with PostgreSQL 8.4.2 (PostgreSQL 8.4.2: 172.31.16.177)
- One RDS PostgreSQL 9.6 in us-east-1 (RDS 9.6)
This post uses PostgreSQL 8.4.2 on Amazon EC2; however, the PostgreSQL database might be running on-premises.
This solution installs Bucardo 5.4.1 on Ubuntu 16.04, which means that the repository for Bucardo is on the same host running on a PostgreSQL 9.6 instance. The following diagram shows the architecture of the data replication flow.
Installing Bucardo binaries
There are several packages that you must install before installing Bucardo. See the following code:
Connect to CPAN and install
DBI,DBD::Pg,DBIx::Safe. See the following code:
Download the Bucardo binaries into your local directory and
untar. See the following code:
Creating superusers and the repository database
You must create a Bucardo superuser and repository database to control and track the replications between environments. Connect to
DB-APP1 using the PSQL client or
pgadmin4 and create the superuser and repository on
DR-App1. See the following code:
After you create the superuser and repository database, exit from PSQL and run “bucardo install” from the terminal where Bucardo software is staged. This creates a set of tables in the Bucardo database (the database owner should be a Bucardo superuser).
Installing the Bucardo repository
To install the Bucardo repository, enter the following code:
This installs the Bucardo database into an existing PostgreSQL cluster. You must have compiled PostgreSQL with Perl support, and connect as a superuser. See the following code:
Whitelisting Bucardo and the PostgreSQL database server to connect with each other
pgpass to set up passwordless authentication to connect the source and target databases securely. On the Bucardo server, enter the following code:
Verify that the Bucardo server can connect the source and target databases without a password with the following code:
Resolving a permission denied error
Because RDS is a managed service, AWS doesn’t provide superuser privileges for security reasons. To perform a trigger-based replication, you must enable the parameter
You can use the security definer function
rds_session_replication_role, which helps you to set the parameter to
replica when an event occurs. To be consistent across all environments, this post creates the security definer function in EC2 PostgreSQL (8.4.2) and RDS.
plpgsql; with the following code:
Also, make changes to the bucardo.pm file at lines 5397 and 5330 with the following code:
Alternatively, you can download the updated bucardo.pm file and move the file to the server where Bucardo is running under the location
/usr/local/share/perl/5.22.1/Bucardo.pm. If you are running in production, please test it before using it.
Generating a sample source database and initiating target full load
On the source database, generate some test data using
pgbench. This post generates four tables, three with the primary key enabled and one without the primary key enabled. See the following code:
The following code is the generated sample data in
Verify the data count and table structures. See the following code:
Migrating repdb from the source database using pg_dump and pg_restore
Back up the source database using
pg_dump. See the following code:
Log in to RDS PostgreSQL and create the database
repdb. See the following code:
Restore the dump file generated in the newly created
repdb in RDS PostgreSQL using
pg_restore. See the following code:
For more information, see Importing Data into PostgreSQL on Amazon RDS.
Configuring Bucardo to replicate tables with a primary key
A typical Bucardo setup consists of steps to add the source and target databases, add tables with a primary key to the group, and create and enable the sync to start replicating the changes from source.
To add the source database, enter the following code:
To add the target RDS database, enter the following code:
To add tables to the herd, enter the following code:
To add the database group, enter the following code:
You can have multiple databases in a particular database group.
Check the Bucardo sync status before you start to make sure that you see the parameters created. See the following code:
Start Bucardo and verify its status. See the following code:
Current State is
Good and no inserts, updates, and deletes are happening currently in the source database.
To test the replication, generate a test load in the source database using
pgbench and monitor changes on the target. See the following code:
After you run
pgbench, it generates some transactions, but Bucardo can’t move to the target due to a permission issue. Therefore, the status of
Current State is
Bad. See the following code:
If you encounter this error, follow the steps to resolve a permission denied error. In this example, security definer functions were not created in source and target databases and caused the preceding error. After implementing the security definer, restart Bucardo. See the following code:
Current State is now
Good, and 294 deletes and inserts happened in the database. This confirms that your Bucardo is healthy. You can ignore the entry for
Last error. See the following code:
To debug the replication, Bucardo logs are located in the
/var/log directory. See the following code:
This post demonstrated the complete solution to overcome the challenge of migrating legacy PostgreSQL databases older than 9.4 to Amazon RDS PostgreSQL or Aurora PostgreSQL by using the asynchronous trigger-based replication utility Bucardo.
If you have comments or questions about this solution, please submit them in the comments section.
About the Authors
Rajeshkumar Sabankar is a Database Specialty Architect with Amazon Web Services. He works with internal Amazon customers to build secure, scalable, and resilient architectures in AWS Cloud and help customers perform migrations from on-premise databases to Amazon RDS and Aurora Databases.
Samujjwal Roy is a Database Specialty Architect with the Professional Services team at Amazon Web Services. He has been with Amazon for 15+ years and has led migration projects for internal and external Amazon customers to move their on-premises database environment to AWS Cloud database solutions.