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

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.


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

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.

Downloading and Storing the Image Locally
Environments
On Windows 10
On Ubuntu 22.10 kinetic
Back and Restore a Database
Other Docker Posts Which I've Written

Downloading and Storing the Image Locally

To download:

E:\docker-images>docker pull postgres:latest

To save the image locally to E:\docker-images\:

E:\docker-images>docker save postgres:latest --output postgres-latest.tar

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

PostreSQL Server Docker official image -- version 15.1 (Debian 15.1-1.pgdg110+1).
Windows 10 Pro -- version 10.0.19045 Build 19045.
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.
Windows 10 pgAdmin 4 -- version 6.18. Older versions might not work: when trying to connect, they fail with different errors.
On Windows 10, “docker” CLI ( Docker Engine ) -- version 20.10.17.
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\. 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

My trial and error runs show that the host directory, which is D:\docker_data\postgresql\ 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

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

C:\>docker stop postgresql-docker
C:\>docker rm postgresql-docker

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

C:\>docker ps -a

CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES

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\, around 23 ( twenty three ) items, most are folders.

❸ Now create password file secrets\pgpass.conf under D:\docker_data\postgresql\:

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

localhost:5432:postgres:postgres:pcb.2176310315865259

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

-e POSTGRES_PASSWORD_FILE=/var/lib/postgresql/data/secrets/pgpass.conf

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

❹ 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

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\? 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:

Host name/address: localhost
Port: 5432.
Username: postgres -- I am using the default as per official document.
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

❷ 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

❸ 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

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

behai@HP-Pavilion-15:~$ sudo docker ps -a

CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES

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/:

❹ 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

The password file is passed to the Docker container as:

-e POSTGRES_PASSWORD_FILE=/var/lib/postgresql/data/secrets/pgpass.conf

/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

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

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

$ sudo ufw allow from any to any port 5432 proto tcp

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:

Host name/address: HP-Pavilion-15 -- it's better to use the machine name, since IP addresses can change.
Port: 5432.
Username: postgres -- I am using the default as per official document.
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

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;

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

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:

Table of contents

Downloading and Storing the Image Locally

Environments

On Windows 10

On Ubuntu 22.10 kinetic

Backup and Restore a Database

Other Docker Posts Which I've Written