search

babelfish-for-postgresql / babelfish_extensions

Babelfish for PostgreSQL provides the capability for PostgreSQL to work with applications written for Microsoft SQL Server. Babelfish understands the SQL Server wire-protocol and T-SQL, the Microsoft SQL Server query and procedural language, so you don’t have to switch database drivers or rewrite all of your application queries.
https://babelfishpg.org/
Apache License 2.0
277 stars 93 forks source link

[Question]: postgres database already exists #79

Closed palmerj closed 2 years ago

palmerj commented 2 years ago

Contact Details

jpalmer@linz.govt.nz

What's the question?

In single-db mode when I create a database (e.g mydb) it creates the following default user databases master and tempdb. When I try to create a user database with the same name as the postgres database I get the following error:

Msg 33557097, Level 16, State 1, Line 1
postgres database "mydb" already exists

According to the source code this is as expected.

Why is this? I would like to migrate my single MSSQL database to PostgreSQL and have the same MSSQL database name as the one I also use to connect with PostgreSQL client tools.

Cheers, Jeremy

Relevant log output or information

No response

Code of Conduct

robverschoor commented 2 years ago

When the Babelfish instance is created with migration_mode = single-db, you can create only one user database at a time (master and tempdb are always present). If you want to create more than one database, create your instance with migration_mode = multi-db; you cannot change this setting after the instance is created. The difference between the two mode lies in the way the SQL Server schema names for the user databases are mapped to PG schema names (see https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/babelfish.html#babelfish-single_vs_multi_db). In some migration scenarios, the schema name mapping in th case of single-db may be more convenient. In case you are not sure what will be the best option, choose multi-db.

palmerj commented 2 years ago

I'm happy with single-db mode, but I want to have the same MSSQL database name as the one I also use to connect with PostgreSQL client tools. That doesn't look to be possible.

robverschoor commented 2 years ago

When creating an migrated SQL Server database, you are free to choose the name with CREATE DATABASE, e.g. CREATE DATABASE mybigdb. You can then see this database with sp_helpdb and let you application connect to this database for example with sqlcmd ... -d mybigdb. If you are referring to the name of the Babelfish container database, that is a PostgreSQL database, and while you are free to choose its name, that PG database is not visible from T-SQL: when you connect to the TDS port, you will always be placed in this PG database, inside of which we emulate the SQL Server database structure with master, tempdb, and user database(s). Note that in Aurora/PG, the name of the Babelfish container DB cannot be chosen freely and is always babelfish_db.

max-webster commented 2 years ago

Helpful answer from Rob. I'm bookmarking it to consult in my own Babelfish experiments. Looks like the original question has been answered and so I'll close the issue now.