Connecting to Aurora Postgres (Babelfish, 1433)-CodePudding

I'm attempting to connect to a new Aurora PostgreSQL instance with Babelfish enabled.

NOTE: I am able to connect to the instance using the pg library through the normal port 5432 (the Postgres TDAS endpoint).

However, for this test, I am attempting to connect through the Babelfish TDS endpoint (1433) using the standard mssql package.

If I specify a database name (it is correct), I receive the error 'database "postgres" does not exist':

var config = {
    server: 'xxx.us-east-1.rds.amazonaws.com',
    database: 'postgres',
    user: 'xxx',
    password: 'xxx'
};

and the connection closes since the connection fails.

if I omit the database property in the config, like:

var config = {
    server: 'xxx.us-east-1.rds.amazonaws.com',
    user: 'xxx',
    password: 'xxx'
};

It will connect. Also, I can use that connection to query basic things like SELECT CURRENT_TIMESTAMP and it works!

However, I can't access any tables.

If I run:

SELECT COUNT(1) FROM PERSON

I receive an error 'relation "person" does not exist'.

If I dot-notate it:

SELECT COUNT(1) FROM postgres.dbo."PERSON"

I receive an error "Cross DB query is not supported".

So, I can't connect to the specific database directly and if I connect without specifying a database, I can't cross-query to the table.

Any one done this yet?

Or, if not, any ideas on helping me figure out what to try next? I'm out of ideas.

CodePudding user response：

Babelfish databases (that you connect to on port 1433) have nothing to do with PostgreSQL databases (port 5432). Essentially, all of Babelfish lives within a single PostgreSQL database (parameter babelfishpg_tsql.database_name).

You seem to have a single-db setup, because Cross DB query is not supported. With such a setup, you can only have a single database via port 1433 (apart from master and tempdb). You have to use CREATE DATABASE to create that single database (if it isn't already created; ask sys.databases).

I can't tell if it is supported to create a table in PostgreSQL (port 5432) and use it on port 1433 (the other way around is fine), but if so, you have to create it in a schema that you created with CREATE SCHEMA while connected on port 1433.

CodePudding user response：

The answer was that I should be connecting to database "master".

Even though there is no database titled master in the instance, you still do connect to it.

Once connected, running the following:

select current_database();

This will indicate you are connected to database "babelfish_db".

I don't know how that works or why a database would have an undocumented alias.