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