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.

Simple PostgreSQL Container start up

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

PostgreSQL using Docker Compose

Docker Compose is a tool provided by Docker that allows you to define and manage multi-container applications. It uses a YAML file to specify the services, networks, and volumes required for your application, making it easier to configure and deploy complex applications composed of multiple containers.

With Docker Compose, you can define your application’s services, such as web servers, databases, message queues, and more, along with their configurations and dependencies. Docker Compose then handles the creation and management of these containers, ensuring they are started, stopped, and scaled together.

For example, let’s see how to start PostgreSQL with Docker compose, execute some SQL init scripts and start the Adminer interface:

version: '3'
services:
    postgres:
        container_name: postgres
        image: postgres
        extra_hosts: [ 'host.docker.internal:host-gateway' ]
        restart: always
        environment:
            POSTGRES_DB: testdb
            # POSTGRES_USER: postgres
            POSTGRES_PASSWORD: password
        volumes:
            - ./config/init.sql:/docker-entrypoint-initdb.d/init.sql:ro
            - ./local-data:/var/lib/postgresql/data
        ports:
            - 5432:5432
    adminer:
        container_name: adminer
        image: adminer
        extra_hosts: [ 'host.docker.internal:host-gateway' ]
        restart: always
        environment:
            ADMINER_DEFAULT_SERVER: postgres
        depends_on:
            - postgres
        ports:
            - 8888:8080

Then, create a script init.sql under the folder config:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

INSERT INTO users (username, email)
VALUES
    ('John Doe', '[email protected]'),
    ('Jane Blumsey', '[email protected]');

Finally, start the PostgreSQL container with:

docker-compose up

You can access the Adminer interface at: http://localhost:8888/?pgsql=postgres&username=postgres

Enter as credentials:

• Username. postgres
• Password: password

You should be able to access the list of Database and execute SQL scripts on them:

Conclusion