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:
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:
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:
In conclusion, running PostgreSQL in a Docker container offers a convenient and portable way to set up and manage your database environment. Docker allows you to encapsulate the database, its dependencies, and configurations into a single container, providing consistency across different environments and making it easier to share and deploy.