Making a PostgreSQL Backup in a Container

Posted on February 19, 2025 by way0utwest

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.

2025-02_0286

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.

2025-02_0287

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.

Posted in Blog | Tagged , , | Comments Off on Making a PostgreSQL Backup in a Container

Is Ransomware Fading?

Posted on February 19, 2025 by way0utwest

There were less ransomware payments in the second half of 2024, according to research from cryptocurrency tracing firms. There were certainly some high profile attacks, but this matches with my impressions from talking with lots of fellow data professionals around the world. This article talks about the trend, and speculates that law enforcement actions may have had some effect.

My guess is that this is likely a temporary decrease as there are no shortage of criminals and so many tools are available on the Internet, especially the dark web. The idea of writing a piece of software to encrypt files isn’t complex, and we’ve had people writing viruses for years. I suspect there are no shortage of smaller criminal organizations and individuals that will step in to continue attacks in the future. Whether that will be a lot of attacks or a few, I don’t know.

One of the other problems is that so many organizations are loathe to disclose they’ve been hacked if they don’t have to do so. Lots of them would prefer to just pay a ransom and get back to work. I don’t know how many IT pros agree with that, though often the employees just want to get past the attack as quickly as possible and restore their systems. I know that they often worry about future attacks, but I also wonder if many IT pros know how to check their systems to be sure the malicious software is gone.

Securing your environment is hard, especially when most users (and IT people) want convenience. Many infrastructure people want to log in with a single account and get things done. Or they want an easy way to switch accounts when necessary, which isn’t always convenient. Some of us are used to the runas command, but I’ve met many people who aren’t.

I do like that much of the world is moving to using managed identities or service accounts for processes, known accounts for CI/CD that can handle deploying code while each of us just approves the deployment with our own credentials rather than directly moving bits. I am glad to see more and more people without rights to log into production, only to submit batches to a system to run and get results sent back. All of those are good things which can prevent an infection from a website or email from spreading to production systems.

However, we still have lots of interconnections between systems for important data stored outside of relational systems. Even storage explorer type access for Delta/parquet files can be a problem if you have that. Databases are safer from ransomware, assuming you can lock down all OS/file system access. Maybe we ought to store more data in databases, even those crazy Excel/Word/etc. documents as binary files.

I’m OK with that, as long as we have a separate instance for those files. I have no desire to see more binary files stored in my OLTP database, or even on the same instance.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

Posted in Editorial | Tagged | Comments Off on Is Ransomware Fading?

T-SQL Tuesday #183 Roundup

Posted on February 18, 2025 by way0utwest

I hosted this month’s T-SQL Tuesday party with my invitation asking about tracking permissions. I didn’t get my own post completed in time, but I’ll add it in the next week sometimes.

In any case, here’s a roundup of the posts I saw. If I missed any, ping me and I’ll add you.

As usual, Rob Farley is first to respond. Since he’s in AUS and I can’t my New Zealand friends to participate, I’m always glad to see his post appear late on a Monday my time. His post looks at how he does health checks for clients, examining the permissions for logins. He doesn’t care if it’s a SQL login or not, but it should be a controlled login of some sort. He has other thoughts, and it’s a good look at the things you should consider when managing security with an eye on the reality of how people acesss data. Read this one.

Shane O’Neil has some PowerShell for us. He works with AGs and separate instances, so he needs to ensure the correct logins are on each replic.

Hugo Kornelis talks about how hard it is to get an overview of permissions. He says there is no permission heaven, but he has some suggestions on how to set up permissions.

Deb the DBA gives us a few things to think about in a large organization. A nice overview of what to consider and how to talk to others.

Rob writes about the importance of visibility in managing permissions.

A relatively quiet month for T-SQL Tuesday (including me). If you are reading this, then I hope you are planning on participating next month. Let us know your thoughts on a topic. If you don’t have a blog, here is how to start.

If you have a blog, where are you? Share some thoughts? Take 15-20 minutes and help pay it forward by helping others.

Posted in Blog | Tagged , | Comments Off on T-SQL Tuesday #183 Roundup

A Little Brainstorming with an AI

Posted on February 17, 2025 by way0utwest

I was asked to do some a little thinking and brainstorming recently. Rather than send some emails or get on a call, I decided to ask my local AI a few things. This talks about the responses and reactions from my local DeepSeek AI..

This is part of a series of experiments with AI systems.

Requests for Feedback

I’m a part a research group that is trying to learn how to work better with others inside or outside of an organization. As a part of this, I was asked for some questions we might use to survey people on how they work as a baseline.

I was a little stumped on where to start. I had some ideas, but everything sounds to vague or too specific and I felt as though I couldn’t find a good place to start. So, I decided to ask my AI.

I have a DeepSeek model running in a container, so I opened my browser and asked this question: What are some questions I can ask employees to understand if they collaborate well with others?

The response was interesting. DeepSeek tries to give you some insight into how it approaches the problem. The initial responses showed a little stream-of-consciousness that reminded me of someone thinking out loud. Here’s the first part:

Okay, so I need to figure out how to determine what questions to ask when employees work together. I’m not really sure where to start, but maybe I should break it down step by step.

First, I think about why we’re asking these questions. If it’s to understand collaboration, then we want to know if the team is working well together, communicating effectively, or if there are areas they’re struggling with. So, what kinds of questions would help us assess that?

I remember reading somewhere about gathering feedback from employees. Maybe those are good ones because they can show how each person feels about the group’s work. But how exactly do I phrase that? Like, “How have you felt about the team’s performance so far?” or something along those lines.

Then there’s communication style. If an employee doesn’t contribute much in the moment, maybe asking them to describe what went on before they spoke would help identify if their approach is effective or not. It sounds a bit direct, but it might reveal issues like being quiet when someone speaks up later.

Collaboration skills are another area. I think it’s important that employees know how to work with others, so asking specific questions about group processes and roles could be useful. Maybe something like, “What do we use for planning meetings?” or, “How often do you give feedback within a week?”

From there it had more explanation and then it said “consider the following structured approach:” and it gave me a set of bullets, which you can see some of in the image below.

2025-02_0298

This wasn’t quite what I was looking for, but it did get me thinking and helped me send a list of possible questions to the group to consider. This was as helpful as if I’d have called someone, but way quicker and I didn’t interrupt anyone else.

I was genuinely pleased by the way this went.

A Recommendation Letter

I had an author ask me for a recommendation letter. This has happened in the past and I’ve written a few of them, but I always struggle to get one written that sounds professional and not like I’m a clown of a DBA/Editor who wears Hawaiian shirts to professional events.

Again, I decided to ask DeepSeek to outline one, giving it the title of an article. It had a few suggestions and then gave me a draft. My prompt was: draft a reference letter for XXX YYY for the article: ipsem lorum ipse lore.

First I got the thinking, making sure I was considering the aspects of the article, should I point out some technical things in my recommendation. It also thinks about including the impact of the article as well. Next, I got a draft with some “insert name” and “insert date” places.

I then added another prompt noting this was for SQL Server Central, which is a respected and long-lived (24 years now) publication well read in the industry. DeepSeek then adjusted its thinking, noting that we want to include info about SSC, how we review articles, etc.  This was the end of the thinking, which I really liked. It’s the type of thing a human might say to let me know what they’re going to do:

2025-02_0299

The draft letter is still a little off, with too much praise for me. This is a good article, but it’s not amazing. In any case, this gave me a decent first draft I could edit and then send off to the author.

It’s not that I couldn’t have done this, but this draft saved me some time and allowed me to clarify my own thinking about what I actually wanted to write.

I think this is slightly better or worse than if I’d asked a human to help give me a draft. Some people might have given me a slightly better draft, some worse, but I’d still be adjusting the tone and editing the content. I know because I’ve used humans for this exact task before.

This was interesting to me to play with an LLM and look for help. On balance, this didn’t take long and I think I did get some help from an AI. It didn’t do my work, but it helped.

Posted in Blog | Tagged , , | Comments Off on A Little Brainstorming with an AI