Voice of the DBA

Tag: postgresql

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
Connecting to PostgreSQL from Azure Data Studio

I’ve been doing a bit of work with PostgreSQL as part of my work with Redgate. PostgreSQL is a relational platform that is open source, free to use, available as a supported commercial product from various companies, and has been in active development for over 35 years.

More and more organizations are looking at PostgreSQL for relational data stores as both SQL Server and Oracle are very expensive, and this is a viable alternative.

This post looks at the basics of getting started with PostgreSQL by connecting to the platform with Azure Data Studio.

If you haven’t installed PostgreSQL, or want to work with a container, check out my previous post: Creating a PostgreSQL Docker Container with a Volume on Windows

Azure Data Studio

Azure Data Studio (ADS) is a fork of Visual Studio Code, but specialized for databases. I don’t use it a lot, but I find it useful for some coding. They also have added an extension that allows connections to PostgreSQL.

If you click the Extensions blade and then search for postgresql, you’ll find it. You can see it installed on my system below. Before you do this, the button that says uninstall would say install.

Once installed, the connection is like any connection in ADS.

First, make sure you have PostgreSQL installed. If you want to do this in a container, see my link above.

Connecting

To connect, first open a new Query window.

At the top there is a “Connect” button. Click this.

Now the connection blade opens. As you can see below, there are now two choices for the connection type: SQL Server and PostgreSQL. Choose PostgreSQL.

I choose localhost, as that’s where my instance is running. I also change the authentication type to Login and enter “postgres” as the default user. If you haven’t set up a user, use this. Then enter the password.

One last thing, as I often run databases in containers, I’ll change the port. To do that for PostgreSQL, click the “Advanced” button. I often use 54320, 54321, 54322 for my ports, so you can see I’ve entered that below:

Click Ok if you’re added a port, and you should see these details:

Click Connect, and you should connect immediately. Then you can verify your connection with a query or two. As you can see, the database is defaulted to postgres and my queries have returned the version and tables in this database.

That’s it. Now time to work on some pgsql.

January 18, 2023