waltherman
By waltherman

how to run PostgreSQL on Docker

how to run PostgreSQL on Docker

PostgreSQL database in Docker

Sometimes it is needed to have a local copy of production database or to have a specific pgsql version for dev and test purposes. This small guide will help with it.

Steps to reproduce

Create local folder for database files:

1
2
3
$ mkdir /var/postgres-data/

Or you could use path like ${HOME}/postgres-data/, your choise.

Now we start container by command:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$ docker run -d \

	--name pgsql_for_website \

	-

	-e POSTGRES_PASSWORD=Password312 \

	-v /var/postgres-data/:/var/lib/postgresql/data \

        -p 5432:5432

        postgres

OR you could create dockerfile for PostgreSQL container. Something like this:

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
version: '3.7'

services:

  db:

    container_name: pgsql_for_website

    image: postgres:12-alpine

    restart: always

    ports:

      - "5432:5432"

    environment:

      POSTGRES_USER: postgres

      POSTGRES_PASSWORD: Password312

      POSTGRES_DB: pgdb

    volumes:

      - /var/postgres-data:/var/lib/postgresql/data/

and run it:

1
2
3
$ docker-compose -f compose-postgresql.yml up -d

Check that container is running:

1
2
3
$ docker ps

If you have a lot of containers in output, use grep:

1
2
3
$ docker ps | grep postgre

Output will be like:

1
2
3
4
5
$ docker ps | grep postgre

d63fe2debc4e      postgres:12-alpine      "docker-entrypoint.s…"   5 min ago      Up 5 min      0.0.0.0:5432->5432/tcp   pgsql_for_website

Connect to pgsql container with bash and then psql:

1
2
3
4
5
$ docker exec -it pgsql_for_website bash

$ psql -h localhost -U postgres

To migrate database from other database you need to create a DB dump:

1
2
3
$ pg_dump --username [username] [database name] > pgdump.sql

And restore it to created container as it would be done to any PostgreSQL server:

1
2
3
$ psql -h [host with pgsql container address] -d [database name] --username [username] -f pgdump.sql

In our case it would be:

1
2
3
$ psql -h [host with pgsql container address] -d pgdb --username postgres -f pgdump.sql

At last, connection string would looks like:

1
2
3
postgres://postgres:Password312@[host with pgsql container address]:5432/pgdb

And for deletion you could use next commands:

1
2
3
4
5
$ docker container stop pgsql_for_website 

$ docker container rm pgsql_for_website

Or

1
2
3
$ docker container rm -f pgsql_for_website

Output would contain deleted container name.