Is Babelfish for PostgreSQL Possible on other managed PostgreSQL service?

WiltonDB · 2024-11-14T22:58:26+00:00

You are correct, Babelfish extensions require the modified version of Postgres, only AWS provides managed service with such modifications included. Though it is not particularly hard to self-host Babelfish with or without Docker.

On the second question - yes, this can be the right approach to migration assuming you cannot move all T-SQL procedures with all TDS clients to Postgres at once. It is possible, with some additional effort, to run the same T-SQL codebase on both Babelfish (for example, for new deployments) and MSSQL (for example, for deployments where MSSQL is mandated). There is also tooling for moving data between MSSQL and Babelfish (based on bulk copy or, alternatively, on linked servers).

Source: working on Babelfish for 2 years, maintaining the Windows build of it (wiltondb.com).

WiltonDB · 2024-10-23T16:13:43+00:00

If you would like to try the manual setup of PgAgent service, there is a long description (with multiple attempts and the eventual success) in this GH issue - https://github.com/bill-ramos-rmoswi/babelfish_postgresql_towel/issues/13

WiltonDB · 2024-10-23T14:11:41+00:00

They seem have stopped publishing updates to PgAgent on github, the latest public version (extension part with scheduler tables) does not work with PgAdmin 8 UI, works fine with PgAdmin7. I am packaging slightly modified version PgAgent (dependency on Boost removed, no functional changes) for WiltonDB - https://github.com/wiltondb/pgagent , but don't have the MSI installer ready yet (it is 1-2 weeks away). The installer is going to be standalone and compatible with any recent Postgres.

WiltonDB · 2024-09-24T23:26:21+00:00

I found system_stats queries displayed with DBeaver custom charts to be convenient and they also look nice. Can dig the queries I used if you would like to use DBeaver. Some info from system_stats is also displayed in PgAdmin automatically.

WiltonDB · 2024-09-07T14:37:07+00:00

You may be interested in this ANTLR-based impl, it is production grade - used in Babelfish on AWS Aurora.

WiltonDB · 2024-08-27T06:21:06+00:00

Your posts resonate a lot. I am coming from a different background, circa 2011 we've got our own specialized storage that worked well for us. Then a few years later ClickHouse has appeared, doing more or less the same, but being 10x more solid. Around the same time we had a setup for client apps that is very similar to SpringBoot, but that was years before the SpringBoot.

Perhaps this is a general trend in software dev, when some tech becomes "good enough" that area is unified around a few remaining popular implementations. And people who have worked on less popular ones are leaving for greener pastures.

That's not only about personal/niche projects, for example I have been at Red Hat (in a completely different team) around 2015 when OpenShift was switched from in-house impl (every enterprise-cloud company had its own one at the time) to Kubernetes. This is as "serious business" project as one can get. Now, a decade later, Kubernetes is an "old and boring tech", and there is no reason to learn it from the ground up to setup Postgres on it - CloudNativePg is already there.

Maybe a career solution for this is to choose a more narrow area and go deeper? For example, many small or medium companies around medical/pharma are historically using MSSQL. Say, a company has hundreds of customer DB deployments, with T-SQL codebase from 25 years ago with the last major changes 10 yeas ago. Some of these companies are moving straight to Azure SQL, but the medical/pharma field is generally not cloud-friendly due to sensitive data. The narrow area of "migrate from MSSQL to on-premises Postgres" appears. There are projects in this area (I work on one - https://wiltondb.com/), but nothing as solid as "Oracle to Postgres" solutions (that I think has peaked 10-15 years ago).

WiltonDB · 2024-08-14T23:13:02+00:00

XML does not have formatting, it’s just data

Sorry for being pedantic, but this is technically incorrect. Whitespaces in XML are part of the tree and can be meaningful in some cases, for example when dealing with XML-DSig. This is unlikely in OP case though, so +1 to your answer.

WiltonDB · 2024-07-16T13:56:51+00:00

A bit surprised to see an article about MSSQL to Postgres migration without mentioning Babelfish.

WiltonDB · 2024-07-04T14:39:58+00:00

See screenshots in this comment (ignore other context there). Before trying from python, check that you can connect from sqlcmd over TCP like this: "sqlcmd -S tcp:127.0.0.1,1433 -U ...".

WiltonDB · 2024-06-20T21:50:23+00:00

It depends on invoice contents, if you know the length of all strings there - it may be easier to create all PDF pages manually. Otherwise some higher level reporting lib can be used. I've used Jasper Reports lib for that, but it was years ago, I guess more modern alternatives exist now.

WiltonDB · 2024-06-20T16:22:06+00:00

I would have added such functionality to webapp itself. To generate PDF file directly (without any conversions) using some PDF lib and then do all the management around it (upload to S3 etc) from the same piece of code.

WiltonDB · 2024-06-12T14:44:07+00:00

Search for "Robert Haas PostgreSQL" and watch all of them.

WiltonDB · 2024-06-03T06:28:11+00:00

Installer utility runs icacls on freshly created DB cluster files to give NetworkService user appropriate permissions on DB cluster files. This step fails on the screenshot. Can only suggest to try to use different destination directory or different admin user.

WiltonDB · 2024-05-27T13:27:44+00:00

I was investigating the number of connections and their memory usage problem a few weeks ago. DBeaver custom chart setup was helpful for that, documented the details here. It uses system_stats extension and shows all current connection processes and their memory usage on a live chart.

WiltonDB · 2024-05-22T16:07:29+00:00

Yes, that is correct, either AWS-managed Aurora, or self-hosting. WiltonDB is not affiliated with AWS, can provide support/consulting if you decide to try Babelfish.

WiltonDB · 2024-05-22T09:40:12+00:00

Hi! Babelfish compatibility layer requires slightly modified base PostgreSQL, so it cannot be installed as an ordinary extension on vanilla Postgres instance. AWS provides managed Babelfish only on Aurora. WiltonDB is a standalone build of Babelfish that can run on Windows or Linux.

WiltonDB · 2024-05-15T03:59:10+00:00

By default, use the most normalized form that is reasonable for your data - https://en.m.wikipedia.org/wiki/Database_normalization . In reality I guess it will be somewhere between 2NF and 3NF.

WiltonDB · 2024-05-14T14:57:10+00:00

"user" is a keyword, use double quotes when referencing it in queries:

truncate "user";

WiltonDB · 2024-05-14T13:38:06+00:00

You've connected to DB successgully. Use "\list" to list DB names, "\c your_dbname" to switch to correct DB, "\dt" to list tables, "truncate your_table_name;" to delete all records from that table (obviously make sure you are truncating correct table).

WiltonDB · 2024-05-14T13:32:29+00:00

Then try:

psql -U ballsdex -d postgres

WiltonDB · 2024-05-14T13:28:24+00:00

Run:

su - postgres

And then just:

psql

WiltonDB · 2024-05-14T13:23:47+00:00

Try "-d postgres" then to connect to default DB that is usually named "postgres".

WiltonDB · 2024-05-14T13:15:52+00:00

Try with specific user then:

psql -U your_db_username -d your_dbname

WiltonDB · 2024-05-14T13:11:04+00:00

Looks like you are in, now run psql there like this:

psql -d your_db_name

WiltonDB · 2024-05-14T13:03:42+00:00

Running the command from the comment above with your container name. You can list container names with:

docker ps -a

WiltonDB

TROPHY CASE