How to manage a PostgreSQL Database with Docker

Many times we need to quickly access a Database while developing and testing applications. In this scenario, using a Container image of the Database can speed up your development/testing. In this tutorial we will learn how to manage PostgreSQL Database from a Docker container.

First off, let’s learn how to start a PostgreSQL Database using Docker. Make sure Docker is started:

$ sudo service docker start

Now start PostgreSQL version 10.5 from the shell as follows (replace credentials with the one you need):

docker run --name pg -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=changeme -e POSTGRES_DB=pg_database -p 5432:5432 postgres

As we have named the Container image as “pg” we can use this alias to connect to the Container as follows:

$ docker exec -it pg bash

If we want to enter the psql tool, first change the user to:

su postgres

Now, launch the psql, passing as arguent the Database name ad the Username:

psql pg_database postgres

You are logged into PostgreSQL DB. Try listing the Database schemas:

pg_database=# \l
 pg_database | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres    | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0   | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
             |          |          |            |            | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
             |          |          |            |            | postgres=CTc/postgres

An useful shortcut to execute SQL Commands, is the docker exec command which allows us to return the output of a SQL Command from the Host machine in just one command. Example:

docker exec -it pg psql -Upostgres -a pg_database -c '\l'
 
                                  List of databases
    Name     |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-------------+----------+----------+------------+------------+-----------------------
 pg_database | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres    | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0   | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
             |          |          |            |            | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
             |          |          |            |            | postgres=CTc/postgres

Finally, it worth mentioning that managing PostgreSQL Database is a lot easier if you use Docker-compose. Once that you have installed it, just create a docker-compose.yml file as the following one:

version: '3.5'

services:
  postgres:
    container_name: postgres_container
    image: postgres:10.5
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-changeme}
      POSTGRES_DB: pg_database
      PGDATA: /data/postgres
    volumes:
       - ./data:/var/lib/postgresql/data
    ports:
      - "5432:5432"

Within this file, we have also declared a Volume so that Database data will survive a Container restart. In our case, we will locally store the Database data in the current “data” directory.

In orderto start PostgreSQL with Docker-compose, just run:

$ docker-compose up

As we have named our service “postgres”, we can run into the bash of the Container with just:

$ docker-compose run postgres bash