There are lots of ways to replicate PostgreSQL database including streaming replication, logical replication, trigger-based replication and so on. And sometimes you need all of them depending on you and your business needs. I can say that replication options in Postgres is somewhat limitless.
And this time, I am writing how to replicate and use Amazon RDS as the master and use another as the slaves.
Why would you do that? Why would anyone bring down their cloud servers? This time, my reason is our data analysts needs to –as the name suggests– analyze transactions data in their own machine, which is located in our own data center while the database needed is on the cloud. We don’t allow them to access production servers because, you know, production is scary stuff. That’s what we told them. :grimacing:
Here’s the preparation stuff:
- PostgreSQL servers >= 9.1 in Amazon RDS, this is because we need session replication role
- Server for Bucardo and Postgres destination
- Bucardo, obviously
That would be all.
Bucardo can be installed anywhere and this time I put it in the same server where Postgres destination is located. First, we need to grab Bucardo dependencies consist of some Perl modules including DBIx::Safe, boolean, DBI, DBD::Pg, Test::Simple.
sudo apt-get install libdbd-pg-perl libdbix-safe-perl postgresql-plperl-9.6 postgresql-contrib-9.6
Once they’re done, we can continue to grab the source files and install to the system. Continue reading “[PostgreSQL] Amazon RDS as A Master: How to Replicate from AWS”