DEV Community

Cover image for Using PostgreSQL Official Docker image on Windows 10 and Ubuntu 22.10 kinetic.
Be Hai Nguyen
Be Hai Nguyen

Posted on • Updated on

Using PostgreSQL Official Docker image on Windows 10 and Ubuntu 22.10 kinetic.

#postgres #docker #windows #ubuntu

Discussing a basic set up process to use the PostgreSQL Official Docker image on Windows 10 Pro, and Ubuntu 22.10 kinetic running on an older HP laptop. Then backup a PostgreSQL database on Windows 10 Pro machine, and restore this backup database to the newly set up Docker PostgreSQL Server 15.1 on the Ubuntu 22.10 machine.

The PostgreSQL Server Docker official images are at this address postgres Docker Official Image.

This is the full documentation for these images. Please note, this page has links to Docker official documents on volumes, etc., which are necessary to run images such as this.

This post also makes use of PostgreSQL Server password file, whose official documentation is 34.16. The Password File.

The objectives of this post are rather basic. ❶, getting the Docker container to store the data in a specific location on the host, of my own choosing. ❷, implementing the password file on the host and pass it to the Docker container as per official documentation above.

Of course, the final goal is to connect to a PostgreSQL server running in a Docker container with whatever clients we need.

Table of contents

Downloading and Storing the Image Locally

To download:

E:\docker-images>docker pull postgres:latest
Enter fullscreen mode Exit fullscreen mode

To save the image locally to E:\docker-images</code>:

E:\docker-images>docker save postgres:latest --output postgres-latest.tar
Enter fullscreen mode Exit fullscreen mode

postgres-latest.tar is also used in Ubuntu 22.10 later on. This Docker image contains PostreSQL Server version 15.1 (Debian 15.1-1.pgdg110+1).

Environments

  1. PostreSQL Server Docker official image -- version 15.1 (Debian 15.1-1.pgdg110+1).
  2. Windows 10 Pro -- version 10.0.19045 Build 19045.
  3. Ubuntu -- version 22.10 kinetic. The machine it runs on is an older HP Pavilion laptop. The name of this machine is HP-Pavilion-15, the rest of this post will use this name and Ubuntu 22.10 interchangeably.
  4. Windows 10 pgAdmin 4 -- version 6.18. Older versions might not work: when trying to connect, they fail with different errors.
  5. On Windows 10, “docker” CLI ( Docker Engine ) -- version 20.10.17.
  6. On Ubuntu 22.10, “docker” CLI ( Docker Engine ) -- version 20.10.22.

On Windows 10

Since I already have PostgreSQL Server 14 installed on Windows 10 Pro, I have to turn its service process off, before setting up another server in Docker container.

❶ I select to store PostgreSQL data in D:\docker_data\postgresql</code>. After creating this directory path, on the docker run command, it can be mounted as:

--mount type=bind,source=//d/docker_data/postgresql,target=/var/lib/postgresql/data
Enter fullscreen mode Exit fullscreen mode

My trial and error runs show that the host directory, which is D:\docker_data\postgresql</code> translated to //d/docker_data/postgresql in this case, must be COMPLETELY empty, otherwise Docker raises an error.

The image has already been loaded when first pulled. The run command is:

docker run -d -it -p 5432:5432 --name postgresql-docker -e POSTGRES_PASSWORD=pcb.2176310315865259 --mount type=bind,source=//d/docker_data/postgresql,target=/var/lib/postgresql/data postgres:latest
Enter fullscreen mode Exit fullscreen mode

❷ Now, stop and remove the postgresql-docker container:

C:\>docker stop postgresql-docker
C:\>docker rm postgresql-docker
Enter fullscreen mode Exit fullscreen mode

Verify that container postgresql-docker has been removed, run:

C:\>docker ps -a
Enter fullscreen mode Exit fullscreen mode 
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES
Enter fullscreen mode Exit fullscreen mode

I have no other containers, your list is likely to be different. We should be able to confirm that postgresql-docker is not in the list anymore.

Initial PostreSQL Server files and folders should now be created under D:\docker_data\postgresql</code>, around 23 ( twenty three ) items, most are folders.

❸ Now create password file secrets\pgpass.conf under D:\docker_data\postgresql</code>:

🐘 Content of D:\docker_data\postgresql\secrets\pgpass.conf:

localhost:5432:postgres:postgres:pcb.2176310315865259
Enter fullscreen mode Exit fullscreen mode

As per official documentation, the password file is passed to the container as:

-e POSTGRES_PASSWORD_FILE=/var/lib/postgresql/data/secrets/pgpass.conf
Enter fullscreen mode Exit fullscreen mode

Recall that /var/lib/postgresql/data/ is the host translated directory //d/docker_data/postgresql in the first mount:

--mount type=bind,source=//d/docker_data/postgresql,target=/var/lib/postgresql/data
Enter fullscreen mode Exit fullscreen mode

❹ The final command is, then:

docker run -d -it -p 5432:5432 --name postgresql-docker --mount type=bind,source=//d/docker_data/postgresql,target=/var/lib/postgresql/data -e POSTGRES_PASSWORD_FILE=/var/lib/postgresql/data/secrets/pgpass.conf postgres:latest
Enter fullscreen mode Exit fullscreen mode

Please note that,I have to do two ( 2 ) commands to get the password file to work. I did try to run only the final command on the empty //d/docker_data/postgresql, it did not work. Please try for yourself.

The obvious question is, can we store the password file in a directory other than the mounted host data directory D:\docker_data\postgresql</code>? I don't know if it is possible, if it is possible, then I don't know how to do it yet.

To connect pgAdmin 4 to the just set up Docker PostgresSQL Server 15.1, register a new server as:

  1. Host name/address: localhost
  2. Port: 5432.
  3. Username: postgres -- I am using the default as per official document.
  4. Password: pcb.2176310315865259

Please note, the Windows 10 version of pgAdmin 4 is 6.18. Older versions might not work: when trying to connect, they fail with different errors.

Docker PostgresSQL Server 15.1 is now ready in Windows 10.

On Ubuntu 22.10 kinetic

On Ubuntu 22.10, I did not do any of the trial and error runs as Windows 10. I assume that, what do not work on Windows 10, will also not work on Ubuntu 22.10.

❶ Copy the image to /home/behai/Public/docker-images/, then load the image with:

behai@HP-Pavilion-15:~$ sudo docker load --input /home/behai/Public/docker-images/postgres-latest.tar
Enter fullscreen mode Exit fullscreen mode

❷ I want to store data under /home/behai/Public/database/postgresql/, create the directories database/postgresql/ under /home/behai/Public/, and run the first command:

$ sudo docker run -d -it -p 5432:5432 --name postgresql-docker -e POSTGRES_PASSWORD=pcb.2176310315865259 --mount type=bind,source=/home/behai/Public/database/postgresql,target=/var/lib/postgresql/data postgres:latest
Enter fullscreen mode Exit fullscreen mode

❸ Then stop and remove the postgresql-docker container:

behai@HP-Pavilion-15:~$ sudo docker stop postgresql-docker
behai@HP-Pavilion-15:~$ sudo docker rm postgresql-docker
Enter fullscreen mode Exit fullscreen mode

Verify that the Docker container postgresql-docker has been removed:

behai@HP-Pavilion-15:~$ sudo docker ps -a
Enter fullscreen mode Exit fullscreen mode 
CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES
Enter fullscreen mode Exit fullscreen mode

Even if the list is not empty, postgresql-docker should not be in the container list.

Initial PostreSQL Server files and folders should now be created under /home/behai/Public/database/postgresql/:

053-01.png

❹ Now create secrets/pgpass.conf under /home/behai/Public/database/postgresql/:

🐘 Content of /home/behai/Public/database/postgresql/secrets/pgpass.conf:

localhost:5432:postgres:postgres:pcb.2176310315865259
Enter fullscreen mode Exit fullscreen mode

The password file is passed to the Docker container as:

-e POSTGRES_PASSWORD_FILE=/var/lib/postgresql/data/secrets/pgpass.conf
Enter fullscreen mode Exit fullscreen mode

/var/lib/postgresql/data/ is the host directory
/home/behai/Public/database/postgresql/ in the first mount:

--mount type=bind,source=/home/behai/Public/database/postgresql,target=/var/lib/postgresql/data
Enter fullscreen mode Exit fullscreen mode

Final command:

$ sudo docker run -d -it -p 5432:5432 --name postgresql-docker --mount type=bind,source=/home/behai/Public/database/postgresql,target=/var/lib/postgresql/data -e POSTGRES_PASSWORD_FILE=/var/lib/postgresql/data/secrets/pgpass.conf postgres:latest
Enter fullscreen mode Exit fullscreen mode

❺ Updated on 16/01/2023 -- open port 5432 for external access:

$ sudo ufw allow from any to any port 5432 proto tcp
Enter fullscreen mode Exit fullscreen mode

Since this is a development environment, there is no IP address restriction applied, in a production environment, I imagine only certain IP addresses are allowed. Please be mindful of this.

16/01/2023 update ends.

From Windows 10, to connect pgAdmin 4 to Docker PostgresSQL Server 15.1 running on HP-Pavilion-15, register a new server:

  1. Host name/address: HP-Pavilion-15 -- it's better to use the machine name, since IP addresses can change.
  2. Port: 5432.
  3. Username: postgres -- I am using the default as per official document.
  4. Password: pcb.2176310315865259

Please note, the Windows 10 version of pgAdmin 4 is 6.18. Older versions might not work: when trying to connect, they fail with different errors.

Backup and Restore a Database

I already have PostgreSQL Server 14 installed on Windows 10 Pro. I back up a development database ompdev from this server, and restore the backup data to Docker PostgreSQL Server 15.1 running on Ubuntu 22.10: machine name HP-Pavilion-15.

The database backup command:

"C:\Program Files\PostgreSQL\14\bin\pg_dump.exe" postgresql://postgres:top-secret@localhost/ompdev > ompdev_pg_database.sql
Enter fullscreen mode Exit fullscreen mode

Please note, the above command will not have the create database statement in the dump file, on the target server, we need to manually create a database to restore to.

Restoring to HP-Pavilion-15 involves two simple steps.

⓵ Connect pgAdmin 4 to Docker PostgreSQL Server on HP-Pavilion-15, as discussed. Then create a new database with:

CREATE DATABASE ompdev;
Enter fullscreen mode Exit fullscreen mode

Please note, it does not have to be pgAdmin 4, we can use any other client tools available.

⓶ Then run the following restore command:

"C:\Program Files\PostgreSQL\14\bin\psql.exe" postgresql://postgres:pcb.2176310315865259@HP-Pavilion-15/ompdev &lt; ompdev_pg_database.sql
Enter fullscreen mode Exit fullscreen mode

If everything goes well, we should now have the database restored and ready for connection on Docker PostgreSQL Server 15.1 running on Ubuntu 22.10. The below screen capture showing the ompdev database restored on HP-Pavilion-15:

053-02.png

Other Docker Posts Which I've Written

  1. Docker Compose: how to wait for the MySQL server container to be ready? -- Waiting for a database server to be ready before starting our own application, such as a middle-tier server, is a familiar issue. Docker Compose is no exception. Our own application container must also wait for their own database server container ready to accept requests before sending requests over. I've tried two ( 2 ) “wait for” tools which are officially recommended by Docker. I'm discussing my attempts in this post, and describing some of the pending issues I still have.
  2. Synology DS218: unsupported Docker installation and usage... -- Synology does not have Docker support for AArch64 NAS models. DS218 is an AArch64 NAS model. In this post, we're looking at how to install Docker for unsupported Synology DS218, and we're also conducting tests to prove that the installation works.
  3. Python: Docker image build -- install required packages via requirements.txt vs editable install. -- Install via requirements.txt means using this image build step command “RUN pip3 install -r requirements.txt”. Editable install means using the “RUN pip3 install -e .” command. I've experienced that install via requirements.txt resulted in images that do not run, whereas using editable install resulted in images that do work as expected. I'm presenting my findings in this post.
  4. Python: Docker image build -- “the Werkzeug” problem 🤖! -- I've experienced Docker image build installed a different version of the Werkzeug dependency package than the development editable install process. And this caused the Python project in the Docker image failed to run. Development editable install means running the “pip3 install -e .” command within an active virtual environment. I'm describing the problem and how to address it in this post.
  5. Python: Docker image build -- save to and load from *.tar files. -- We can save Docker images to local *.tar files, and later load and run those Docker images from local *.tar files. I'm documenting my learning experimentations in this post.
  6. Python: Docker volumes -- where is my SQLite database file? -- The Python application in a Docker image writes some data to a SQLite database. Stop the container, and re-run again, the data are no longer there! A volume must be specified when running an image to persist the data. But where is the SQLite database file, in both Windows 10 and Linux? We're discussing volumes and where volumes are on disks for both operating systems.
  7. Docker on Windows 10: running mysql:8.0.30-debian with a custom config file. -- Steps required to run the official mysql:8.0.30-debian image on Windows 10 with custom config file E:\mysql-config\mysql-docker.cnf.
  8. Docker on Windows 10: mysql:8.0.30-debian log files -- Running the Docker Official Image mysql:8.0.30-debian on my Windows 10 Pro host machine, I want to log all queries, slow queries and errors to files on the host machine. In this article, we're discussing how to go about achieving this.
  9. pgloader Docker: migrating from Docker & localhost MySQL to localhost PostgreSQL. -- Using the latest dimitri/pgloader Docker image build, I've migrated a Docker MySQL server 8.0.30 database, and a locally installed MySQL server 5.5 database to a locally installed PostgreSQL server 14.3 databases. I am discussing how I did it in this post.

Thank you for reading and stay safe as always.

Top comments (0)

Read next

oliverbennet profile image

Trying to Install Docker on Ubuntu 24?

Oliver Bennet -

jacobandrewsky profile image

Improving Performance of PostgreSQL queries

Jakub Andrzejewski -

marrouchi profile image

Setup SSL with Certbot + Nginx in a Dockerized App

Med Marrouchi -

indrayyana profile image

Building a RESTful API with Go Fiber: An Express-Inspired Boilerplate

Indra Adnyana -