The goal of this guide is simple: get PostgreSQL up and running in the fastest, easiest way. You’ll then learn how to create databases in PostgreSQL and the objects that live within them.

The two easiest ways to get started working in PostgreSQL are to either use containers, or create an account with a cloud provider. In this case, we’ll be using containers.

How to retrieve Docker images using Pull

The goal of this walkthrough is, of course, to have a running PostgreSQL container. However, before a container can be created, the necessary image for that container must be retrieved.

It’s possible to simply issue a docker run command and, if no image is found, it’ll automatically attempt to first pull that image. While this is fine, it’s actually better to issue the pull command separately.

Why use Docker Pull?

For a start, a run command will only run a local version of the image – not necessarily the latest one. Second, a pull command validates the image before execution, ensuring that malformed or malicious containers are not run. And finally, retrieving the image first makes it possible to work fully offline – a nice benefit in itself.

For all these reasons, we’ll use pull as the foundation for building out PostgreSQL containers.

How to use Docker Pull

There are many possible Docker images, but we’ll start with the most generic PostgreSQL one for now. Before getting started, make sure you’re connected to the internet, and that your Docker service is running. Then, run the docker pull postgres:latest command.

It’s worth noting that the :latest part of the command isn’t actually necessary. By default, a pull command, if given no other instruction, will get the latest, stable, image.

Also, depending on the service you’re running, you substitute podman or nerdctl for the docker command. All other aspects of the command will stay the same.

And, to reiterate, the latest image will not be the most cutting edge version under development. Rather, it’s just the newest stable release.

This command could take a while depending on the speed and bandwidth of your internet connection – the progress of which you’ll be able to see in your command line interface. When it’s done, you’re ready to validate the image.

To validate that the Docker image was downloaded successfully, you can run the docker images command.

On a brand new machine, you’ll probably only see a REPOSITORY (with one or two images), postgres, and possibly hello-world.

Also pay attention to the TAG column. Your postgres image should show a tag of ‘latest’. If that’s the case, you’re ready to create a container.

How to pull a specific image with Docker Pull

If you wanted to have a specific version of PostgreSQL, it’s possible by supplying the documented tag of that version. For a complete listing, the PostgreSQL community maintains the Docker PostgreSQL image on the Docker hub here.

To pull a particular version, you just need to supply it. For example, to pull version 17.10, you’d run
docker pull postgres:17.10.

How to create a container using Docker Run

Once an image has been created locally, you can now use that image to create containers. As stated previously, you can simply use docker run to both pull the image and create the container.

We’ll also need to break down the core requirements for creating a PostgreSQL container using docker run. Here’s the full command we’ll be using:

Before we run the script, though, let’s talk about a few important pieces of information here.

How to create a volume on your container (and why you should)

First, I’m going to recommend you create at least one volume on your container (the -v flag.) A volume is way to map an external directory on your machine to a location within the container.

Then, you can place files into that folder and they’ll be readable within the container and outside, in your operating system. If you wanted to have *.sql files or PostgreSQL dump files available within the container, this is the easy way to do it.

Because of this, you’ll need to ensure that you have that folder already created on your local machine. The example script above is for a Rancher Desktop path. You’d need a different path for Windows or Linux unless you’re running everything through WSL on Windows.

You can get very sophisticated with volumes if you choose but, for our purposes, just the ability to easily move scripts into the container is enough.

Naming your container

You don’t have to supply a name to the container, but it does make managing it easier. This is the --name flag.

Port mapping

Next, you have to map between an external and internal port. Since I’m only running one container, I’ll map it to the default PostgreSQL port, 5432 (-p 5432:5432). If you want to run more than one container hosting PostgreSQL, you’ll need to provide a different external port value (the first number.)

For example, if you already have PostgreSQL running within a container (or locally), you might substitute
-p 5433:5432 to map to a different port. You’ll then need to provide that non-default port value to any connection to that PostgreSQL cluster.

Environment variable flags

For a container, a simple role is all that’s needed to connect to the cluster running inside the container. I have to use the environment variable flag, -e to map to a particular key two times: 
POSTGRES_PASSWORD and POSTGRES_USER.

The name supplied to POSTGRES_USER is going to be the default super user (system administrator) of the PostgreSQL cluster running in the container. In the example, I use the default value for the super user in most PostgreSQL installs: postgres.

Image

Finally, I supply the name of the Docker image: postgres:latest.

How to execute the script

When you execute the command, you receive back an identifier for the container you created. You can either use this identifier or the name of the container you created. I recommend the latter, simply because it’s easier.

To validate that the container was successfully created, run this command:
docker ps -f 'name=StartingPostgreSQL'

This should result in something similar to the following:

As you can see from the output, you get the ID for the container itself, as well as the name, the image used in creation, when it was created, and the current status along with any ports. And that’s it! You now have a functional PostgreSQL cluster running within a container.

How to stop/start the container

You can stop the container by issuing the docker stop StartingPostgreSQL command. Then, to restart the container, issue the docker start StartingPostgreSQL command. If you wish to continue following this guide, I advise leaving the container in a running state.

How to gain access to the container operating system (OS)

While running PostgreSQL within a container, certain operations have to be completed with the minimal Linux OS that supports PostgreSQL in its execution within the container. In this part of the guide, we’ll be connecting to PostgreSQL using psql – the default command line utility for running SQL within PostgreSQL.

In other parts of the guide, we may need to change PostgreSQL configuration files or perform other actions within the Linux OS. Therefore, we’ll do a quick overview of how to connect to that OS within the container.

The Docker exec command

The exec command in Docker is used at the command line to open a shell into the running container. From there, you can perform actions directly within the container.

In our case, we’ll be using psql to run SQL queries. The command to start a shell within a container is simple: docker exec -it StartingPostgreSQL "bash"

The -it flag tells Docker that we’re starting an interactive shell command. We have to supply the container to which we want to connect through the container name. Finally, we supply what we wish to execute: a Bash shell.

Running this from the command line, you’ll see your folder change from whatever it was to something like this:
root@6ae8a06273fe:/#

This shows that I’m at the root of the container within Bash. Do note that the numbered value is supplied by Docker so will likely be different on your container.

You can validate that you’re not local by running some simple commands. For example, the ls command will show you the folder and file listing within the container. These will undoubtedly be different than your machine.

A more effective check would be to look for the docker environment values using the ls -l /.dockerenv command. The output should be something similar to this:
-rwxr-xr-x 1 root root 0 Jun 18 13:26 /.dockerenv (and of course, the date will be different in your output).

Another way to validate that you’re running in the PostgreSQL container (assuming you’re not running PostgreSQL locally as a service) would be to look for the $POSTGRES_USER value using
echo $POSTGRES_USER.

If this returns postgres and you’ve followed the scripts as written, then you’re absolutely within a shell in your container.

To leave the shell, simply issue the exit command – dropping you out of the shell within your container and back to the command line. You can leave the shell open for the next part of the guide.

How to connect to PostgreSQL

Now, let’s focus on how to connect to PostgreSQL for the first time. Here’s everything you need to know in this step-by-step walkthrough/guide.

Before we continue on to executing psql, it’s worth double checking that we’re actually connected to the shell in our container. To check this, run the docker exec -it StartingPostgreSQL "bash" command.

What is psql in PostgreSQL?

psql is considered to be the official interface for PostgreSQL. It’s a command line for interacting with, and administering, PostgreSQL clusters. You can issue SQL commands as well as taking advantage of the utilities built into psql.

In this part of the guide, we’ll explore the basic connection to PostgreSQL using psql. We’ll then use psql to create objects within the cluster. If you’ve been following from the start, you’ll be running this within a Bash shell inside your container. However, you wouldn’t run it any differently from the command line of your host OS.

For our container as configured, we connect to it through psql with the psql -d postgres -h localhost -U postgres command. When run inside a container, you won’t be prompted for a password; you’re in a trusted environment.

Otherwise, when running psql, you’ll need to supply the password used when creating the container (in the example, Some*Passw0rd). You’re forced to provide a password in order to keep the password out of the history of commands in your CLI (command line interface) as a beneficial security measure.

Meanwhile, if you’re running psql from an external command line – not the Bash shell as we’re doing – there are a couple of ways to supply a password without typing it each time. One way is to create an environment variable on your machine, but that’s less secure than simply entering the password.

Another way is to create a secured file (chmod 0600) in your file system. We’ll just stick to the password for our examples.

We’re logging in to the default database, postgres, and using the user we created earlier in the guide (postgres).

Your prompt should now change to something like this:

We can validate that we’re successfully connected to PostgreSQL several ways. First, we can use one of the meta commands (so designated by a backslash \ before the command). All meta commands are stored in *.sql files since they can only be run through psql.

To see the connection information of PostgreSQL, we can run the \conninfo command, which will return a bunch of information about your connection:

Do note that, if your CLI window has fewer than 13 lines available, it will ‘page’ the results by default. You’ll need to use q to move to the next page.

Since we’re connected to psql, you can immediately run a query instead of a meta command. One thing to know up front, every connection to PostgreSQL is to a specific database and that database only – there’s no concept of cross-database queries. Any query we issue is going to run against the database specified in the connection.

In this case, it’ll be the postgres database. For clarity, we can use the SELECT version(); query to understand exactly where we’re connected at any given time.

At the time of writing this guide, the output is:

It’ll likely enter into paging.

We can also get a list of databases using the \l command, but this is unlikely to page on our example container because there should only be three databases: postgres (the one we’re connected to), template0, and template1. We’ll discuss templates more later in the series.

To leave a psql session such as this, use the meta command \q. And that’s it: you now have the basics of psql under your belt! In part two, we will start actually working within PostgreSQL.