Using Amazon EC2 for postgres_fdw or BDR replication

Solution 1:

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.

Solution 2:

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.