Docker postgresql volume windows

This post looks at how to set up a PostgreSQL container on Windows using Docker for Windows. I’ve seen a few posts, but I had to cobble together some instructions from places, so I decided to make my own post to help me remember and keep things simple.

tl;dr: do this:

• Create a folder c:\docker\pgdev
• get the Docker image: docker pull postgresql:latest
• Run the container, command below:

docker run --name pgdev -e POSTGRES_PASSWORD=Str0ngP@ssword -d -p 5432:5432 -v C:\Docker\pgdev:/var/lib/postgresql/data  postgres

That’s it. Then you have a PostgreSQL instance running on port 5432 (default) with a user, postgresql, and a password, Str0ngP@ssword.

More detailed instructions below

Create a place for data

Containers are ephemeral, which isn’t what we want for a database. We want to keep data around, so let’s make a place for this. This will be a volume for our container, which we will map to a particular location inside the container. Then if the container dies, we can map this to another PostgreSQL container and have our data appear.

Create a c:\Docker folder on your machine. This is a good spot for any Docker related volumes.

Now create a pgdev folder under c:\docker. This is the place we’ll keep data for this PostgreSQL container. It should be empty.

Картинка с сайта: voiceofthedba.com

Get the Image

Container images are available from Docker. I won’t cover installing Docker or setting up Linux containers, but you do need to do this. I used Windows 10, and I have WSL v2, as you can see:

Docker Desktop is running Linux containers. You can see that since it say “Switch to Windows containers”.

Next, use Docker Pull. I assume you are working with the default Docker registry, so this command should work:

Docker image pull postgres:latest

This will start downloading the image.

Картинка с сайта: voiceofthedba.com

When this completes, move on.

Starting the container

Once we have the image, we can start the container. You could do this without the folder above, but your data would be in the container and if the container were ever deleted, then the data is lost.

The basic command for starting the container requires a few parameters. Here is a list of what I provided in the command at the beginning:

• –name – This is a name you can use in docker commands to refer to the container. You can put anything. I chose “pgdev”.
• The password in the database system for the postgres user. This is a default user and you send this in as an environment variable with -e. The password I used here is: Str0ngP@ssword
• -d runs this detached, rather than interactively. This means your command shell can return command to you. Otherwise, all output from the container appears in the shell and you can’t type anything.
• -p is the port mapping. This is host:container. In this case, we map 5432 on the host (where we use some postgreSQL driver to connect) to 5432 inside the container. You can choose any unused port for the first number, but 5432 is needed for the second number as the postgresql service is listening on 5432.
• -v is the volume mapping. Here we map our host folder to a container folder (host:container). We enter the folder we created above and then map this to the place where postgresql stores data. That’s in /var/lib/postrgresql/data
• postgres is the image name.

Here is the command again:

docker run --name pgdev -e POSTGRES_PASSWORD=Str0ngP@ssword -d -p 5432:5432 -v C:\Docker\pgdev:/var/lib/postgresql/data  postgres

Once this executes, we should see a long hex code returned, which is the container identifier.

Картинка с сайта: voiceofthedba.com

We also see our folder is now filled with postgresql specific data files and folders:

That’s it, our container is running.

I was looking for something short about getting started with Docker Compose and I found this video: Docker Compose in 12 Minutes (Mar 14, 2017) — Learn how to use Docker Compose to run multi-container applications easily. This is the second video in this Docker series.

It is a nice video with a very simple example well explained. There is a project to play with on GitHub.

After this sample I did remember this post: ASP.Net Core Web API with Docker Compose, PostgreSQL and EF Core. and I like to try it. Here the source code.

But before that I wanted to start using PostgreSql with Docker. So I followed this post: Setup PostgreSQL on Windows with Docker and this one: Stop Installing Postgres on Your Laptop : Use Docker Instead.

So this was my first idea for a docker container:

1

docker run -p 5432:5432 --name postgres_db -e POSTGRES_PASSWORD=password -d postgres

with all databases data in the container.

Looking on Postgres Official Docker Image I found the use of mapping a host’s folder with postgres data folder inside the container in order to maintain the data if you delete the container or want to use that data with another container for another project.

So this was the evolution:

1

docker run -p 5432:5432 --name postgres_db -e POSTGRES_PASSWORD=password -v /c/Users/myuser/DockerProjects/postgres/postgres_db:/var/lib/postgresql/data -d postgres

But with Docker Toolbox for Windows 10 Home (my case), my postgres database didn’t work.

Looking with docker logs postgres_db I found the problem:

1

FATAL: data directory "/var/lib/postgresql/data" has wrong ownership

Here one solution:

1
2
3
4
5
6
7
8

postgresql:
  image: postgres:93
  volumes:
  - postgres:/var/lib/postgresql/data
  - ./postgresql/conf:/etc/postgresql/
...
volumes:
  postgres:

Here something similar:

To summarize: The workaround is to create a (local) volume with:

1

$ docker volume create --name data-postgresql --driver local

And the docker-compose.yml looks something like that:

1
2
3
4
5
6
7
8

services:
  pgsql:
    volumes:
      - data-postgresql:/var/lib/postgresql

volumes:
  data-postgresql:
    external: true

I also looked at Start a container with a volume in the Use Volume — Docker Documentation.

This is my solution for a test container:

1
2
3

$ docker volume create --name postgres-volume

$ docker run -p 5432:5432 --name postgres_db -e POSTGRES_PASSWORD=password -v postgres-volume:/var/lib/postgresql/data -d postgres

At the end I’m able to play with Postgres

This post is licensed under CC BY 4.0 by the author.

Картинка с сайта: dotnetninja.net

Today at work we were setting up a development environment for a .Net Core project using PostgreSql as it’s datastore. We decided that we set up the database server running in a container in the same way I have been running SQL Server (See recent article: Running Microsoft SQL Server in a Container on Windows 10) for the local development environment. Using the docker-compose file from this article as a basis and referring to the documentation for the postgres docker image on Docker Hub we put together a docker-compose file for PostgreSQL that looked similar to this:

version: "3"
services:
  postgres:
    image: "postgres"
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: "MyUser"
      POSTGRES_PASSWORD: "Password!23"
      POSTGRES_DB: "example"
    volumes: 
      - C:\Docker\PostgreSql\data:/var/lib/postgresql/data

Upon running docker-compose we were greeted with the following output containing an error message:

Creating postgresql_postgres_1 ... done
Attaching to postgresql_postgres_1
postgres_1  | The files belonging to this database system will be owned by user "postgres".
postgres_1  | This user must also own the server process.
postgres_1  |
postgres_1  | The database cluster will be initialized with locale "en_US.utf8".
postgres_1  | The default database encoding has accordingly been set to "UTF8".
postgres_1  | The default text search configuration will be set to "english".
postgres_1  |
postgres_1  | Data page checksums are disabled.
postgres_1  |
postgres_1  | fixing permissions on existing directory /var/lib/postgresql/data ... ok
postgres_1  | creating subdirectories ... ok
postgres_1  | selecting dynamic shared memory implementation ... posix
postgres_1  | selecting default max_connections ... 20
postgres_1  | selecting default shared_buffers ... 400kB
postgres_1  | selecting default time zone ... Etc/UTC
postgres_1  | creating configuration files ... ok
postgres_1  | running bootstrap script ... 2020-02-25 02:38:12.326 UTC [80] FATAL:  data directory "/var/lib/postgresql/data" has wrong ownership
postgres_1  | 2020-02-25 02:38:12.326 UTC [80] HINT:  The server must be started by the user that owns the data directory.
postgres_1  | child process exited with exit code 1
postgres_1  | initdb: removing contents of data directory "/var/lib/postgresql/data"
postgresql_postgres_1 exited with code 1

Notice line 19: “FATAL: data directory “/var/lib/postgresql/data” has wrong ownership”. After reading the error message we noted on line 12 it reads “fixing permissions on existing directory /var/lib/postgresql/data … ok”. Also near the top of the output on line 3 it reads “The files belonging to this database system will be owned by user “postgres”.” followed by “This user must also own the server process.”. Interesting…

So after digging around a bit we found that indeed the user “postgres” must own the files in order for the db system to read them and that the container starts up as root. It appears that line 12 is trying to fix the issue, and from what we found online it will… If the data directory is on a Linux file system. Since we are attempting to mount these files from a Windows file system, it appears that “fixing the permissions” fails. No major surprise there. So what is the work around for us poor developers working on Windows machines?

Named Volumes to the Rescue

In order to get this to work we set up a named volume. In this scenario, Docker takes care of handling the files and where they are actually stored, so we don’t readily have access to the files, but we don’t really care all that much. We just want our data to persist and not get blown away when the container gets deleted.

Here is the new (working) docker-compose file with the named volume:

version: "3"
services:
  postgres:
    image: "postgres"
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: "MyUser"
      POSTGRES_PASSWORD: "Password!23"
      POSTGRES_DB: "example"
    volumes: 
      - psql:/var/lib/postgresql/data

volumes:
  psql:

Using this approach you may want to keep an eye on the named volumes on your system and clean them up when you are no longer using them. To get a list of the volumes on your machine use the following command:

docker volumes ls

That will dump out a list of volumes on your machine that looks something like:

DRIVER              VOLUME NAME
local               600de9fcef37a60b93c410f9e7db6b4b7f9966faf5f6ba067cc6cb55ee851198
local               ae45bfac51d4fb1813bd747cc9af10b7d141cf3affa26d79f46f405ebfa07462
local               b94806ba697f79c7003481f8fd1d65599e532c0e2223800b39a2f90b087d5127
local               d02adf9ab33dfa22e154d25e13c5bb383a5969c19c1dd98cfa2ac8e560d87eb4
local               postgresql_psql

Notice the last entry named “postgresql_psql”? That is the one we just created above. To remove it use the following command (Note: It will not allow you to remove the volume if it is referenced by a container, running or not, so you’ll want to stop and remove the container first):

docker volume rm postgresql_psql

Last Updated :
16 Feb, 2024

Docker and Postgres are two widely used tools in the software development field. Docker simplifies the deployment process by encapsulating applications within a container while Postgres provides a robust and reliable database to store and manage data. In this guide, I will first briefly discuss Docker and Postgres. Then I will guide you through the various steps to persist your Postgres container data using docker volumes.

What is Docker

Docker encapsulates the application and its dependencies into compact units called containers. Containers contain everything that an application needs to run such as libraries, system tools, code, and runtime. This approach greatly enhances portability and scalability. It removes the dependencies of building, testing, and running an application on a particular operating system and hardware. Docker is a very fast, lightweight, and resource-efficient tool. Unlike the traditional virtualization techniques, docker containers share the host operating system kernel which helps the developers to run more containers on a single host. This results in maximizing resource utilization and reducing infrastructure costs. Overall, we can say that Docker has become a very important tool for developers and organizations to accelerate their software deployment and delivery pipelines.

What is Postgres

PostgreSQL is commonly referred to as Postgres. Postgres is an open-source relational database that is used for storing and managing data efficiently. It ensures data integrity as it follows the ACID properties. Postgres provides a variety of features including transactions, complex queries, indexing, and replication. In addition to its core features, Postgres also supports internationalization and text search. These features make Postgres a suitable choice for diverse linguistic and text processing needs. Postgres is widely used in many industries such as healthcare, finance, and telecommunications. We can say overall Postgres is a comprehensive and versatile solution for storing, managing, and analyzing data.

Pre-requisites

Before moving to the next section make sure you have installed Docker on your system. If you have not installed follow these detailed geeksforgeeks articles to install docker on your system.

• For Windows users: Docker – Installation on Windows
• For Ubuntu users: How To Install and Configure Docker in Ubuntu?

Steps to Persist Data In A Dockerized Postgres Using Volumes

Step 1: Create a docker volume. This volume will help in persisting the data.

docker volume create postgres_volume

Картинка с сайта: www.geeksforgeeks.org

Step 2: Run a Postgres docker container using docker volume.

docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=gfg \
  -v postgres_volume:/var/lib/postgresql/data \
  postgres:latest

Картинка с сайта: www.geeksforgeeks.org

Step 3: Go inside the docker container.

docker exec -it postgres psql -U postgres

Картинка с сайта: www.geeksforgeeks.org

Step 4: Create a database.

CREATE DATABASE demo_db;

Картинка с сайта: www.geeksforgeeks.org

Step 5: Try to connect the database.

\c demo_db

Картинка с сайта: www.geeksforgeeks.org

Step 6: Create a table inside the database and insert some demo data . Then exit.

CREATE TABLE gfg_articles (id SERIAL PRIMARY KEY, name VARCHAR(255));

Картинка с сайта: www.geeksforgeeks.org

INSERT INTO gfg_articles (name) VALUES ('Docker 1'), ('Jenkins 2'), ('K8s 3');

Картинка с сайта: www.geeksforgeeks.org

You can use the below command to exit the Postgres terminal.

\q

Step 7: Now delete the Postgres docker container.

docker stop postgres
docker rm postgres

Картинка с сайта: www.geeksforgeeks.org

Step 8: Create again a Postgres docker container using the docker volume (created in Step 1).

docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=gfg \
  -v postgres_volume:/var/lib/postgresql/data \
  postgres:latest

Картинка с сайта: www.geeksforgeeks.org

Step 9: Finally go inside the docker container and verify that whether the database and table is present or not. (run the commands below one by one )

docker exec -it postgres psql -U postgres
\c demo_db
SELECT * FROM gfg_articles;

Картинка с сайта: www.geeksforgeeks.org

Conclusion

Here in this guide we first learn what is Docker . Then learned some basics about Postgres . Then we have followed various steps to persist the Postgres data using docker volumes . We started by creating a Postgres docker container using a docker volume . Then added some dummy database and dummy table to the database . Then deleted the Postgres docker container and recreate the Postgres docker container to verify whether the Postgres data persists or not .