PostgreSQL is a popular Open Source relational database platform. It is available at https://www.postgresql.org, and it was designed as a better Ingres database. Across the last 30 years, it has improved and grown quite a bit. This was the first open source database Microsoft added to its Azure PaaS service, and many companies use PostrgeSQL.

At Redgate, I've been running into more and more companies that use multiple data stores, and PostgreSQL comes up as one of those. Recently I needed to run a project on PostgreSQL, and decided to use a container rather than installing anything. This article looks at the process of getting running, which wasn't quite a smooth as I expected, though I shouldn't have made some assumptions.

Getting Started

When I heard I needed a PostgreSQL database, I immediately thought container. That's my go to first thought these days, so I went to the Docker Hub and searched for postgresql. That got me to the PostgreSQL page. I really just wanted to confirm the name of the image, and saw that it was "postgresql".

The code on the right was what I ran. This is the main way to get images, and it's what I did.

docker pull postgres

This gets me the read only image on my machine. This is the postgreSQL install on Linux, as I'm running Linux containers. The image comes down in layers, and extracts itself. I had a previous versions of PostgreSQL already, so I had the "already exists" message for some layers.

Once I have the image down, I need to start it. On the Docker Hub page, the first example is this:

docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres

I alter that to this:

docker run --name hamshack-e POSTGRES_PASSWORD=Re@llyStr0ngP2ssword -d postgres

Ignore the name, that's Grant's fault. He built the project we're working on. I run this, and then open Azure Data Studio. Because of some of my work, I've added the PostgreSQL extension, so I enter the local address.

I try to connect and get this:

That should have clued me in, but I kept stopping and restarting the container, changing some names, etc. Why isn't this port there? I see this from Docker logs:

I see this with my container:

Port 5432 is listed, right?

Explicit Networking

I have been using scripts to run containers for some time, which has removed some of the basics of containers from my short term memory. I should have paid more attention, and I did realize this, but I wasn't paying close attention. The default run command doesn't include a -p parameter for port mapping.

My mistake was assuming this default run command on the Hub would work, and that the port mapping was handled by default. It isn't. This is what my start command should look like:

docker run --name hamshack -e POSTGRES_PASSWORD=@mazingPassword4U -p 5432:5432 -d postgres

This would give me the container listing like this:

If you look, you see that 5432 is mapped to 5432. In the image above this section, the port is just listed, but it's not mapped. Once I re-run the container with the correct port mapping, I can connect.

From Here

PostgreSQL is a nice platform, and while I've only lightly used it, more than a few people I respect have spent time here and given me some perspective on things that work well. Overall, I still prefer SQL Server, but this is becoming a multi-platform world, and having a little knowledge of how other platforms work well, and their limitations, is valuable.

Using containers is a quick way to experiment and start using other platforms for experiments and little projects. To date this year, I've actually run SQL Server, Oracle, PostgreSQL, and Redis containers for various purposes. I find containers to be a good way to experiment with something and walk away. They're useful for development, and I can stop or remove them to limit the impact on my local host, whether in the configuration or resource usage.

I urge you to start using Docker and experiment in places where you can. If you want to give PostgreSQL a try and compare it to SQL Server, containers are an easy way to do that.