PostgreSQL Database in Docker
220929
Intro
PostgreSQL ranks as the 1st choice of professional developers according to the Stackoverflow Developer Survey 2022.
Here I want just to show how easy and fast you can set up a Docker working environment for your PostgresSQL development needs, following the latest official PostgresSQL repos at DockerHub.
Prerequisites
- You have to be familiar with Docker, Docker Images, and Containers, and Docker should be installed in your system (preferably a Linux system or a macOS)
Pulling the image
Before pulling the image, it is worth taking a look at the DockerHub. Find the official PostgresSQL docker images at DockerHub here.
As you can also see at the official PostgresSQL site, the latest non-beta version, is the 14.5 version (as it was when I was writing this post on Sep.29, 2022):
Pull the image
docker pull postgres
? Note: When you pull an image without specifying a tag name, Docker will try to pull the image tagged latest. in our case, this is about version 14.5
Check that the image has been created in your local Docker repository
docker images
Create a working PostgresSQL container
$ docker run -d --name postgres145 -p 5462:5432 -e POSTGRES_PASSWORD=mypassw1 -e PGDATA=/var/lib/postgresql/data/pgdata -v $HOME/DOCKER/dockerPostgresDB/datafiles145:/var/lib/postgresql/data postgres
The parameters used here are given to the following table:
| –name: | The name of the container (default: auto-generated). Here we use the container name: “postgres145” |
| -d | It runs the container detached – in the background and prints the container ID |
| -p | The port mapping of the host port to the container port. Here we map the default 5432 Postgres port to the 5462 port of our local host. |
| -e | Here we use an environment variable named “POSTGRES_PASSWORD” which allows us to set our own password “mypassw1” as the superuser (“postgres”) password for PostgreSQL |
| -v | The data volume to use for the database data and log files. Here we use the local folder $HOME/DOCKER/dockerPostgresDB/datafiles145” The folder has to be owned by the Unix user “postgres” or you can set its permissions accessible to other users e.g. “sudo chmod -R o+rw datafiles19c”. If omitted the database will not be persisted over container recreation. |
After the container creation, you will see the container’s id and also you can check it via the docker ps command:
You can stop the container and start it again using standard docker commands, i.e.:
docker stop postgres145 docker start postgres145
Access the container shell and use the Postgres command line tool “psql” to perform some fundamental actions
Access the container shell by executing:
$ docker exec -it postgres145 bash
Get some info about the Linux distro being used (it’s a Debian/Ubuntu-based version)
Connect via psql (within the container)
Connect via psql as user “postgres” without and/or with password (-W):
root@bdee5d7a5fca:/# psql -U postgres
root@bdee5d7a5fca:/# psql -U postgres -W
Fundamental psql commands
Note: Generally, psql uses two types of commands.
– Commands starting with a backslash (/) are for psql (known as “slash” commands). – You can issue the \? command to see the list of available Postgres “slash” commands.
– Commands starting with a valid SQL keyword (for “normal” SQL queries and commands). All SQL commands you enter into psql must be ended with a semicolon.
Below, are just a few very initial fundamental commands:
That’s it for now! You have a working PostgresSQL Docker container!
I hope you enjoyed it!
Thanks for reading and stay tuned!
About Post Author