6

I have two Postgres 9.3.5 instances in RDS, both in one security group that allows all inbound traffic from within the security group and all outbound traffic. I'm trying to set up one database to be able to select from a few tables from the other via postgres_fdw.

I've created the server - 

create server master 
foreign data wrapper postgres_fdw 
OPTIONS (dbname 'main', 
         host 'myinstance.xxxxx.amazonaws.com');

as well as the requisite user mapping and foreign table - 

create foreign table condition_fdw (
    cond_id integer,
    cond_name text
) server master options(table_name 'condition', schema_name 'data');

However, a simple select count(*) from condition_fdw gives me

ERROR:  could not connect to server "master"
DETAIL:  could not connect to server: Connection timed out
        Is the server running on host "myinstance.xxxxxx.amazonaws.com" (xx.xx.xx.xx) and accepting
        TCP/IP connections on port 5432?

I can connect to both databases via psql from an EC2 instance. I know until recently RDS didn't support postgres_fdw, but I'm running the newer versions that do.

In the create server statement, I have tried replacing "myinstance.xxxxxx.amazonaws.com" with the IP address it resolves to, no luck.

Any ideas?

Further Testing

I installed postgres on an ec2 instance with the same security group, foreign tables to the master server behave as expected.

postgres_fdw between databases on the same RDS instance works.

This all leads me to think it must some issue with outgoing connections from postgres_fdw on my Postgres RDS instance.

pail
  • 193
  • 4
  • 10
  • 1
    I have the exact problem. I noticed that if I open 5432 up to everyone it works. I don't want to do this and want to understand what do I have to open up from an inbound perspective. https://forums.aws.amazon.com/thread.jspa?messageID=604858򓪺 – Brad Ruderman Feb 28 '15 at 07:24
  • @BradRuderman I was able to query between databases on the same RDS instance using `host 'localhost'` in the create server command options. If you're using `host 'myinstance.xxxxxxx.amazonaws.com`, you'll get a timeout as you describe. It's strange that editing inbound connection rules helped, as outbound connections from RDS are disallowed no matter what the security group rules are. – pail Feb 28 '15 at 16:37
  • @BradRuderman, I just tried the same thing and fdw connection seems to work only if I open 5432 to everyone, which sucks....and If I try to whitelist only a certain server is doesn't work? Did you find a way around it? – Tomislav Mikulin May 07 '19 at 09:02

4 Answers4

2

To get postgres_fdw to work between two instances in AWS RDS (with VPC) I had to:

  1. Enable custom_dns_resolution https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.CustomDNS
  2. Add the public IP of the querying database in the inbound rules of the Security group OR add the internal IP of the RDS instance as server host. https://forums.aws.amazon.com/thread.jspa?threadID=235154

The first one is documented pretty well but I could not get it to work until stumbling over the second one.

bheden
  • 21
  • 3
1

It would appear that Amazon does not allow outgoing connections from RDS instances, so until that changes using postgres_fdw across RDS instances is not possible. I'll have to run an ec2 instance as my postgres server in order to use a foreign table to a database on another server.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.RDSSecurityGroups.html

pail
  • 193
  • 4
  • 10
  • 1
    The documentation you linked doesn't say anything about not allowing outgoing connections. – Juraj Petrik Jul 14 '16 at 13:32
  • 1
    It's totally possible (and we are using it). You just must not use DB Security Groups and use VPC Security Groups. – Viktor Yarmak Jul 18 '17 at 18:58
  • 2
    Seems that this is no longer true: https://aws.amazon.com/about-aws/whats-new/2018/05/amazon-rds-supports-outbound-network-access-from-postgresql-read-replicas/ – krlmlr Mar 07 '19 at 17:33
0

I experienced a similar problem and found that postgres_fdw would only work in the EU-WEST-1 RDS region when the master and remote instances were in the same availability zone and on 9.3.5, if you crossed AZs it wouldn't connect. My security group was 5432 TCP inbound only and all outbound.

James King
  • 1
  • 1
  • 2
    did this really work for you? not two dbs on the same instance, or postgres on EC2 instances, but two separate RDS instances? and you passed `host XYZ.eu-west-1.rds.amazonaws.com` or something similar to `CREATE SERVER`? i tried this just now, and couldn't get it to work, even in the same AZ and opening inbound 5432 to 0.0.0.0/0, i expect because of what @pail mentioned: amazon says they block *all* outbound network connections from RDS instances. – ryan Oct 09 '15 at 23:08
0

If you are in the same availability zone, there is no reason why it wouldn't work.

Tisha
  • 827
  • 3
  • 11
  • 34