Voice of the DBA

Tag: postgresql

PostgreSQL Support in the Data API Builder

Next week I’m at VS Live in San Diego (register and join me) and one of my talks is on the Data API Builder. I want to show how this gives you REST, GRAPHQL, and MCP access. As a part of the talk, I also wanted to show this working across platforms, so I’m showing this in PostgreSQL as well.

I’ve been experimenting this in a few ways, but I decided to see how hard it was to add PostgreSQL support. I decided to follow a similar structure as my SQL Server demos, which are all in my GitHub repo.

First, I added a .env file, which has the credentials. THIS IS A DEVELOPMENT structure, and not something you’d deploy from a repo. Secure your production stuff! This is my local file (in .gitignore), but it’s a simple connection string.

Next, I copied (well, I guided Claude to do this) my demo01.cmd and edited it to say the type is “postgresql” instead of SQL Server. The new file went into a separate folder as (demo01.cmd)

I had it copy the demo02.cmd and alter this for the PostgreSQL Pagila sample db, which is installed and running on my machine.

Note, this errored out at first, with the note that DAB doesn’t support the PostgreSQL array type. I ended up having Claude create a view that skips this column and the column that is a vector type. Hopefully those will be supported soon by DAB.

Next, dab start, and I had a running API server over my PostgreSQL database in just a few minutes. Here’s my Bruno query of the GraphQL endpoint for Pagila.

Not enough people are using or trying the Data API Builder. This is truly an easy way to get a usable API over your database without a lot of data access layer work for developers.

September 9, 2026
Making a PostgreSQL Backup in a Container
I needed to back up a PostgreSQL database as a part of the repro for an issue I had. I hadn’t ever made a backup of PostgreSQL, so this was a learning exercise for me. Plus, a container made it slightly more complex than SQL Server. This post shows what I did.

In looking over the documentation and in searches, everyone seems to use pg_dump to make a backup. This looks hokey and immature to me, essentially a command line tool to script things out.

There is also a file level backup and a PITR backup strategy, but those are more complex for my use case.

So, how do I run pg_dump?

I found this article, which is helpful, but contains a lot of stuff. Essentially, I need to connect to my container and run from there. I’ll use the exec with the it switch from Docker to do this.

So, first I run this to get a shell inside the container.
```
docker container exec -it pgdev /bin/bash
```
The image below shows me connected to the container with a bash shell.

Now I can run pg_dump. I’ll use this command, which connects to the db with a user and sends a database backup to the /usr location.
```
pg_dump -U postgres -Fc bb_fullrestore > /usr/bbfull.dmp
```
Once this is done, I can go check. First, I’ll ls this folder and I see my file.

Summary

As technology advances and I use containers more, I’m hesitant to keep installing stuff on my machine that I don’t need to. I’d rather have scripts. Trying to just get pg_dump installed is a pain, so this post shows how you can access this in a container and create a backup.

If you’ve mapped your folders in a container to your local machine, then you can easily find this file and most it elsewhere for a restore.
February 19, 2025
Should I Learn PostgreSQL
I got asked this question recently:

I constantly see PostgreSQL on Microsoft slides, email, ads, etc. My MCADAA exam started with an entire section on it. I’m trying to determine if it’s worth focused study and training time. Roughly how much of the Azure cloud database space does PostgreSQL occupy? Should I include it in my personal training program?

It’s a good question, though I am assuming that MCADDA is the Microsoft Certified: Azure Database Administrator Associate cert. That page describing the exam only mentions Azure SQL (SQL Server), and not PostgreSQL, so while I think Azure SQL for PostgreSQL is good to learn, I think the cert preparation needs an update if that topic is on the exam.

In any case, should you learn PostgreSQL?

I’ll give you the DBA answer: it depends.

Ask yourself some questions:
- Does your org use PostgreSQL or are they planning to do so?
- Are you going to stick with your company for a few more years?
- Are there more things you should be learning in the area you do work now?
- Are there things you should become more skilled at that your company values?
- Do you know what the opportunities are for people that know PostgreSQL well?
Depending on the answers, I may or may not recommend you spend time there. If you have other things to learn that might be better at your current job, either the job you have or one you might want, then focus there. If your company does (or doesn’t) value PostgreSQL, then that influences your choice as well.

PostgreSQL is growing fast. DB-Engines shows growth across a few platforms, and PostgreSQL is doing well.

The StackOverflow developer survey shows similar results:

However, those are general results. Your specific situation is different. Think about it and ask the question above to lots of people.
October 18, 2024
Why Learn PostgreSQL?

At Redgate, we’ve spent a lot of time adding PostgreSQL functionality to our DevOps tools. We’ve also hired an advocate, Ryan Booz, to help us spread the word and educate everyone about the platform.

How important is this? It’s hard to know. I know all the major vendors offer flavors of PostgreSQL. Azure has Azure Database for PostgreSQL, AWS has Aurora and RDS, and Google has AlloyDB. I also see plenty of customers using PostgreSQL in some way.

On Redgate.com, there is an article on What is PostgreSQL, and why do businesses need to know more about it? This is a general, high level article that highlights a few things from perspective of business usage. While there aren’t a lot of details, I do find more companies embracing PostgreSQL and other platforms outside of Oracle/SQL Server.

I also noticed a webinar coming tomorrow, PostgreSQL 101: Why PostgreSQL in 2023?, that I am going to try and watch.

I’ve been working with PostgreSQL lightly, and it’s in my Flyway PoC series. I find it both interesting, and in many ways, the same as working with SQL Server. Much of my knowledge transfers, so I’m not worried about learning to use it more in depth if needed.

If you’re worried about your company leaving SQL Server, maybe you want to spend time working with another platform, if for no other reason that you can build some familiarity with tools. However, I don’t know I’d recommend many SQL Server spend time here without a pressing need, and I don’t know that I would advocate for my company to switch. There is a lot to learn, and I think the time spent converting knowledge could outweigh licensing costs.

April 26, 2023