5

I've been tasked to do two proofs of concept with Postgres on Amazon RDS, one with postgres_fdw and another one with BDR (bi-directional replication extension). After many searches on the internet, it seems to be not possible to do either the replication or setting up postgres_fdw on RDS.

However, someone on the internet (I cannot find the reference) mentioned EC2 as a possible way of creating Postgres Foreign DataWrapper or even building replication from a Postgres DB, let's call it Frankfurt, to connect to a second Postgres instance that we will call Seoul.

Can anyone confirm that both setting up postgres_fdw and also BDR can be set in EC2?

Thanks.

ypercubeᵀᴹ
  • 113
  • 7
Andy K
  • 119
  • 14
  • 1
    Can you clarify whether your "someone" meant _creating an EC2 instance to host the Postgres DB including the foreign data wrapper (FDW)_? That would be quite easy but means giving up RDS. In any case, have a look at [this AWS thread](https://forums.aws.amazon.com/thread.jspa?threadID=235750) – RichVel Jun 30 '17 at 17:05
  • hi @RichVel, the someone is more of a post I read but I could not find. Thanks for the link. – Andy K Jun 30 '17 at 17:12
  • @RichVel one more question : Do you think or know if will EC2 support bidirectional replication? – Andy K Jun 30 '17 at 17:17
  • In this case the "EC2" option is supporting standard Postgres installed on Linux in an EC2 instance. So it will support whatever Postgres supports - seems like installing Postgres 9.6 plus the [BDR extension](https://blog.2ndquadrant.com/bdr-is-coming-to-postgresql-9-6/) will be required - note this BDR extension is quite new so it will probably require some Postgres expertise to set up. – RichVel Jul 01 '17 at 06:33
  • 1
    I think you meant "BDR" not "BDS" in your question, maybe you could edit it if so. – RichVel Jul 01 '17 at 06:33
  • Hi @RichVel yes I meant BDR – Andy K Jul 02 '17 at 13:54
  • hi @RichVel, thanks for your help. Would you mind please posting your answer, please? I will give you the bounty. – Andy K Jul 03 '17 at 19:33

2 Answers2

3

EC2 provides a self-contained VM. As long as BDR doesn’t require privileged access to hardware (it does not), you’re set.

By contrast, RDS provides a managed database service that offers a subset of Postgres functionality. As you’ve discovered, once you want something that RDS doesn’t offer, the next step is generally to run your own databases on top of EC2. Be forewarned that now you get to handle backups, replication, scheduling maintenance, applying updates to the underlying OS, managing the instances that the database runs on, and so forth.

Corey Quinn
  • 106
  • 1
1

We are using postgres_fdw in AWS RDS. There are some issues related with performance, but it works. You just need to use PostgreSQL 9.3 or higher and check that postgres_fdw is in rds.extensions of parameter group.

Following your definition we have a DB Frankfurt that has a foreign server Seoul. And while connected to Frankfurt you can access data on Seoul as if they were local tables.

You need to run something similar to this on Frankfurt DB (and of course be sure that both DB can access each other over the VPC, you don't need to open them to everyone):

    CREATE EXTENSION postgres_fdw;
    CREATE SERVER FOREIGN DATA WRAPPER postgres_fdw 
    OPTIONS (host 'seoul_host_address', port '5432', dbname 'seoul_db_name');
    -- maybe you need this (if you want to map users)
    CREATE USER MAPPING FOR public SERVER 
    seoul OPTIONS (user 'seoul-writer', password 'XXXXXXX');
    -- then just create foreign table
    CREATE FOREIGN TABLE table_name (
    id integer DEFAULT NOT NULL,
    name character varying(64)
    ) SERVER seoul OPTIONS (tablename 'seoul_table_name');

This is not a perfect solution for replication. If you just need a readonly replica - use AWS RDS replication and you will have no problems.

Hope this info will be helpful.

Viktor Yarmak
  • 11
  • 3