Postgres with docker for local development

Posted by : on

Category : Infrastructure

Why you may want to read this article

Disclaimer: This article DOES NOT contain any rocket science information, or anything extraordinary, it is created for convenience only to have everything on hand.

During my last 5 years of experience, there were a lot of projects that used PostgreSQL as the main database.

For sure, to develop such projects - you need to set up a database locally all the time. In my opinion docker - is the most convenient and quick way to set up external services for local development.

On top of that, I had experience setting up Postgres for production in an on-prem environment.

So this article is about a bunch of commands that you may find useful. I decided to collect them all together in one place to not googling them all the time.



For sure you could use direct installation by exe (Windows) or command line. There are different drawbacks.

I prefer docker because you :

  • keep your app isolated
  • keep your app more controllable
  • you can easily turn it off/on
  • you easily control all configs including ports, user/password
  • you can set up multiple instances of Postgres on different ports

You should have an understanding of basic things in docker. It is better to learn them outside, but I will leave definitions here for convenience.

PgAdmin (optional)

This is a pretty convenient UI application for the Postgres database. But, as a true SE, we are too lazy to install it separately - so we will use the command line.


Image - set of instructions about how to make your container

Container - is running or not running an application inside the docker

Volume - is a mechanism to persist data out of the conteiner’s life. So if the container is dropped the data stays persisted by volume. The mechanism is usually syncing data from the container in the host operating system.

Use case: Setting up using docker run

Simple use case if you have raw docker, you could leverage the command line.

First, create volume:

docker volume create pgdata

Then let’s create and run a postgres container with the name “postgres”, on default port 5432, with “root” login and “root” password, and map “pgdata” volume to Postgres’ data.

docker run -p 5432:5432 --name postgres -v pgdata:/var/lib/postgresql/data -e POSTGRES_PASSWORD=root -e POSTGRES_USER=root -d postgres:14

Use case: Setting up using docker-compose

Just create docker-compose.yml file somewhere and put this into it:

version: "3.7"

    image: postgres
    container_name: postgres
      - 5432:5432
      - db-data:/var/lib/postgresql/data
      POSTGRES_USER: root


Then go to the folder that contains docker-compose.yml and run from it using cmd:

docker-compose up -d


With this command, we did pretty much the same as using just run command: mapped volume to Postgres’ data, created a container with root login and root password, and default port.


Go inside the container:

docker exec -it postgres bash

Connect to database:

psql -h -p 5432 -U root


To list all databases:


Create database:

#option 1

psql -h -p 5432 -U root


#option 2

psql -h -p 5432 -U root -c 'CREATE  DATABASE "Test2"'

Drop database:

#option 1

psql -h -p 5432 -U root


#option 2

psql -h -p 5432 -U root -c 'DROP  DATABASE "Test2"'

Connect to database:


List tables in the connected database:



To make a backup (the file will be created on host machine):

docker exec postgres pg_dump Test  > dump.sql

To make a backup of single table in SQL statements (instead of COPY) statements

docker exec postgres pg_dump --column-inserts --data-only --table={table-name} PetForPet > dump.sql

Restore database from backup (you first should create db using commands above):

docker cp ./dump.sql postgres:/dump.sql

docker exec -it postgres bash

psql -h -p 5432 -U root -c 'CREATE DATABASE "Test2"'

psql -h -U root -d Test2 < dump.sql

About Andrii Bui

Hi, my name is Andrii. I'm Software Engineer at Microsoft with 5 years of experience.