Setup PostgreSQL with Docker for Your C# Database

Time to set up your database for your new .net core application. Stick to what you know. For most of us coming from a corporate environment we know SQL Server. But it is better to avoid SQL Server for personal projects because the license fees are so high.

The next best database at this time for C# applications on Entity Framework is PostgreSQL. It is feature-rich, mature, and well-supported in terms of the database itself and the entity framework providers.

Setting up your PostgreSQL Database Server in Docker

My preference is to spin it up in docker. Why? It’s temporary. Changeable. Simple version management and upgrades. Easy production setup.

Aside: One thing I’ve seen lately that I strongly want to discourage - creating custom builds of application docker images for the only purpose copying over configuration files. Don’t do that. It is unnecessary work and complexity. Use a volume with a mount to keep any configuration you need on the docker host. It makes configuration changes simpler and it is easier to manage the overall solution in a docker compose file(think many containers). I will show you how to do this.

You will probably want to spin up your development PostgreSQL docker container manually. There are two ways to persist your PostgreSQL data on the host machine: Volumes or Bind Mounts. If you’re on a Windows host then you definitely should use volumes since they are managed by Docker, not dependent on the host directory structure, and have consistent behaviour on Windows and Linux hosts. Even on any docker host, volumes have many advantages over bind mounts. However, in my experience bind mounts have been around longer and are more common.

Note: You will have problems if you start the PostgreSQL container with a Bind Mount on a Windows host. Read this article for more details. Deploy to a linux docker host in production.

So first create the volume:

docker volume create postgresql-volume

You can then use docker volume commands inspect or manipulate the volume:

docker volume ls
docker volume inspect <volume-name>
docker volume rm <volume-name>

Now you can spin your your postgresql container referencing the forementioned volume, postgresql-volume.

docker run -p 5432:5432 --name postgresql -e POSTGRES\_PASSWORD=password -d -v 'postgresql-volume:/var/lib/postgresql/data' postgres:10.4

Note: It is a good idea to manage the version of the container you are running. Simply omitting the container version or using latest each time could cause unanticipated changes.

Connect to the command-line management interface on your docker container (to exit afterwards type ‘\q’).

docker exec -it postgresql psql -U postgres

For more details about using psql watch this video: https://www.youtube.com/watch?v=A8dErdDMqb0

docker run -p 5432:5432 --name MarketGardenManagerDB -e POSTGRES\_PASSWORD=password -d -v 'C:/Users/gmccallum/source/MarketGardenManager/db:/var/lib/postgresql/data' postgres:10.4

Due to a limitation with the postgres docker container have to use a docker volume(above) instead of a volume mapping (below). Reference: http://www.lukaszewczak.com/2016/09/run-postgresql-using-docker-with.html

For development I like to keep my database data files in the project folder. Then they are closer at hand to blow them away if you want to re-create from scratch and refresh the container.

It is worth noting the different paradigm here. Infrastructure as code. Before the first push to production all the docker container details will be in the docker compose file and checked into a git repository. Any change to the server configuration is tracked with version control. My SQL Server server instances at my day job are set up and and maintained for as long as possible. Static versus transient. With docker containers you can easily rebuild the server from scratch every time there is a deployment.

Setup your PostgreSQL Administration Tool (in Docker)

The PostgreSQL equivalent of SQL Server Management Studio is pgAdmin. It is a web-based tool. There is a windows installer that runs it on a web server locally for you. Or … you can do what I do … simply spin up the docker container.

Before starting the container ensure that that path to your configuration directory exists (C:/Users/gmccallum/.pgadmin). Then run the command to download and start the container:

docker run -p 8080:80 --name pgAdmin -v "C:/Users/gmccallum/.pgadmin:/var/lib/pgadmin" -e "PGADMIN\_DEFAULT\[email protected]" -e "PGADMIN\_DEFAULT\_PASSWORD=password" -d dpage/pgadmin4

Note: After what I said above about Volumes being better than Bind Mounts … I went and used a Bind Mount for the pgadmin data folder. Old habits die hard. I will switch this to be a Volume for permanent use.

Open it up in your browser at http://localhost:8080. Since this is run locally for you on your dev machine only you can skip the SSL certifcate and https configuration. However, if you plan on accessing this tool over a network (or the internet) see the notes on the docker hub page for further details.

By default docker containers run in bridged networking mode. So when you’re connecting from your pgAdmin container to your postgresql container you have to either connect to the host on the mapped port 5432. Or you can connect to the postrgresql container directly on port 5432. Considering the possible-transient nature of the IP address on your host machine I would consider connecting from container to container directly.

Connect to your PostgreSQL instance by right clicking on ‘Servers’ in the left pain, then ‘Create>’, ‘Server…’.

You can get the IP address of the postgresql container by using the inspect command from your host. Then scroll to the bottom and find the ip address.

docker inspect postgresql

Then use that IP from the pgAdmin Create Server interface and the password from above when you spun up the postresql container (‘postgres’ is the default username).

To start and stop the pgAdmin container (my equivalent to opening and closing an application) just run:

docker start pgAdmin
docker stop pgAdmin

Connect to PostgreSQL From your C# Application

Wes Doyle has an awesome video on how to do this. I dunno how he does the whole thing in one take with zero mistakes. I do it the exact same way.

https://www.youtube.com/watch?v=md20lQut9EE

appsettings.json

"ConnectionStrings": {
"MyConnectionString": "User ID=postgres;Password=password;Server=localhost;Port=5432;Database=MyDatabase"
},

Startup.ConfigureServices

services.AddDbContext(
                options => options.UseNpgsql(Configuration.GetConnectionString("MyConnectionString")),
                    ServiceLifetime.Scoped);