PostgreSQL Docker Setup

The purpose of this repo is to set up a simple PostgreSQL server for basic testing. It is not meant to be secure or production, but rather be very easy to spool up and use to test external PostgreSQL interfaces like DuckDB PostgreSQL extension, ODBC, and Npgsql.

[toc]

Files and Folders

Here is the contents of the PostgreSQL folder:

• data - folder that will contain the PostgreSQL database files
• Dockerfile - builds off of postgres:17.5 container, adds some useful command line utilities.
• Makefile - used to build and run the docker container.
• my-postgres.conf - contains some settings for the PostgreSQL server run in the container.
• PostgreSQL in Docker: A Step-by-Step Guide for Beginners | DataCamp.pdf - an export to pdf of a useful website where much of this came from.
• README.md - this file.
• runPgAdmin.sh - a shell script that contains the docker command to run pgadmin.
• runPostgreSQL.sh - a shell script that contains the docker command to run PostgreSQL server.

Prerequisites

This was developed on MacOS 15.5 with Docker Desktop 4.43.1. This assumes you have a basic understanding of how to run and use Docker Desktop.

You'll also need GNU make in order to run the makefile which helps automate the process (a version of make should be included with MacOS). Bash shell scripts are used to execute the docker run command with some environment variables. If you want to run some SQL on the PostgreSQL server from the local MacOS environment, you'll also need psql. This can be easily done via the MacOS package manager HomeBrew with the command brew install libpq.

If you wanted to run this on a windows box, your best bet is probably to run Docker via WSL which should provide make and bash.

How To

1. Start docker desktop and wait for it to finish starting up.
2. Open a terminal in the PostgreSQL directory that contains the project folders/files.
3. Run make all. This will download and build the necessary containers and then execute docker run commands to start PostgreSQL and PgAdmin.

Other useful makefile options include

1. make stop to shutdown PostgreSQL and PgAdmin.
2. make restart to restart the PostgreSQL container (after modifying config)
3. make clobber completely cleans up containers, images, etc.
4. make psql when run with PostgreSQL server running, this will run simple SQL to test that server is working correctly. Note that it runs the psql command in the docker container and also in the local MacOS environment. This requires that psql is installed on MacOS.
5. make connect connects to the PostgreSQL server container and provides a bash prompt.
6. make db-clean will delete the PostgreSQL database by deleting the contents of the data directory. Make sure the PostgreSQL server is not running before doing this. Starting the PostgreSQL server after deleting the database will create a new empty database.

Given my local environment (MacOS, Windows 11 running in Parallels, PostgreSQL container running in Docker Desktop on MacOS) the following psql command can be run from a windows command line:

C:\Users\charlie>psql -h charlies-MacBook-Pro.local -d postgres -U postgres -p 5432 -c "SELECT current_database(),current_user;"

Password for user postgres:

 current_database | current_user
------------------+--------------
 postgres         | postgres
(1 row) 

References

• docs/postgres/README.md at master · docker-library/docs

• How to Use the Postgres Docker Official Image | Docker

• postgres - Official Image | Docker Hub

  • includes pg_hba.conf config info

• PostgreSQL in Docker: A Step-by-Step Guide for Beginners | DataCamp

  • Explains how to mount external pg_hba.conf file which connectivity fix changes

• PostgreSQL Tutorial

  • includes PostgreSQL C# and Linux Setup

• PostgreSQL: Documentation: 14: 21.1. The pg_hba.conf File

• Setting up PostgreSQL Server in Docker Container on Ubuntu: A Step-by-Step Guide

  • includes pg_hba.conf config info

• PostgreSQL in C# .NET with Npgsql, Dapper, and Entity Framework: The Complete Guide

  • The official package to use Postgres is Npgsql . Add it to your project with dotnet add package Npgsql.
    • Npgsql - .NET Access to PostgreSQL | Npgsql Documentation

• Connecting to Postgres with C-sharp | Tembo

• Correct way to install psql without full Postgres on macOS?

• Intro to Docker Compose – Dataquest