Portable postgres development environment with Docker Compose

I use postgres a lot both at work and in my personal projects. Recently I finally learned an easy way to set up the development environment for an application that uses postgres, utilizing docker-compose.

Overview

This is the directory structure of my project:

1
2
3
4
5
6


|-- compose.yml
|-- conf
|   `-- pgadmin_servers.json
|-- data
|   |-- 001-schema.sql
|   `-- 002-data.sql

Both conf and data directories will be mounted into containers.

Compose file

I like using pgAdmin along with postgres to have a GUI for managing the development database.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38


# compose.yml

services:
  postgres:
    image: postgres:17
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      PGDATA: /data/postgres
    volumes:
       - postgres:/data/postgres
       - ./data/:/docker-entrypoint-initdb.d/
    ports:
      - 54321:5432
    networks:
      - postgres

  pgadmin:
    image: dpage/pgadmin4:9
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@admin.com
      PGADMIN_DEFAULT_PASSWORD: pass
      PGADMIN_CONFIG_SERVER_MODE: 'False'
    volumes:
      - pgadmin:/var/lib/pgadmin
      - ./conf/pgadmin_servers.json:/pgadmin4/servers.json
    ports:
      - 5050:80
    networks:
      - postgres

networks:
  postgres:
    driver: bridge

volumes:
  postgres:
  pgadmin:

pgAdmin config file

This file is used to automatically add a connection to the database upon startup of the pgAdmin container.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


// conf/pgadmin_servers.json

{
    "Servers": {
        "1": {
            "Group": "Servers",
            "Name": "dev",
            "Host": "postgres",
            "Port": 5432,
            "MaintenanceDB": "postgres",
            "Username": "postgres",
            "SSLMode": "prefer"
        }
    }
}

Database initialization scripts

The sql scripts under data directory will be executed when the postgres container starts. The scripts are executed in alphabetical order, so in the above example, 001-schema.sql will be executed before 002-data.sql.

I usually have

001-schema.sql for creating tables and indexes
002-data.sql for inserting initial data

Usage

To start the development environment, run:

1

docker-compose -f compose.yml up -d

pgAdmin will be available at http://localhost:5050 with the default email and password.

Update schema

One common situation is that you have done some changes to the schema via pgAdmin, and you want to persist the changes for future development sessions. To do this, you can use the pg_dump command to dump the schema to a file:

1
2


docker exec -it <postgres_container_name> pg_dump --schema-only -U postgres postgres > back.sql
docker cp <postgres_container_name>:/back.sql ./data/001-schema.sql

Conclusion

I’ve been liking this method, and have been using it for multiple projects. It’s very easy to spin up the environment once you have the config files set up, and the whole environment can be version-controlled, making it very portable.