search

EnterpriseDB / mongo_fdw

PostgreSQL foreign data wrapper for MongoDB
GNU Lesser General Public License v3.0
330 stars 70 forks source link

Connect to Mongo Atlas #144

Closed aliabbasrizvi closed 3 years ago

aliabbasrizvi commented 3 years ago

Hi

I am trying to connect to Atlas Mongo DB cluster and running into connection issues. Could it be that I am defining the address incorrectly when creating the server. My command looks like:

CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address 'cluster_name.mongodb.net')

I get the following error on trying to SELECT from a foreign table I define:

ERROR:  could not connect to server mongo_server
HINT:  Mongo error: "No suitable servers found (`serverSelectionTryOnce` set): [Failed to resolve 'cluster_name.mongodb.net']"

When I create a server with the protocol, the command looks like: CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address 'mongodb+srv://cluster_name.mongodb.net')

I get the following error on trying to SELECT from a foreign table I define:

ERROR:  could not connect to mongodb+srv://cluster_name.mongodb.net:27017
HINT:  Mongo driver connection error.

Please let me know if there are examples of how to connect to Atlas Mongo.

Thanks

aliabbasrizvi commented 3 years ago

Figured out. Closing.

guns2410 commented 2 years ago

Hey @aliabbasrizvi I am having the same issue. Could you post here how to managed to resolve it please.

I create the server as 

CREATE SERVER mongo_server
    FOREIGN DATA WRAPPER mongo_fdw
    OPTIONS (address '*****.mongodb.net', replica_set 'replica_set_here', port '27017');

and I see the error as 

ERROR: could not connect to server mongo_server
Hint: Mongo error: "No suitable servers found (`serverSelectionTryOnce` set): [Failed to resolve ***.mongodb.net']"
aliabbasrizvi commented 2 years ago

@guns2410 I do not recall the exact resolution but I think it was a case of updating Postgres for me.

SawyerHopkins commented 2 years ago

Looking at the code for the mongo wrapper

https://github.com/EnterpriseDB/mongo_fdw/blob/b4c1d76783a071a2a2733bebd0446940708e129e/mongo_wrapper_meta.c#L37

This library doesn't user the +svr mongo protocol for handling clusters with a unified address. From altas when getting the connection information make sure to select a version < 3.0.0.

Then your options should look something like

ALTER SERVER my_mongo_fdw_server
    OPTIONS (SET address 'xxx-shard-00-00.xxx.mongodb.net:27017,xxx-shard-00-01.xxx.mongodb.net:27017,xxx-00-02.xxx.mongodb.net:27017', SET replica_set 'the_replica_set', SET ssl 'true', SET authentication_database 'admin');