How to Create PostgreSQL Docker Image Container (Docker-Compose). This article will introduce what PostgreSQL is and how to create Docker Compose. Let’s get started.
What is PostgreSQL
PostgreSQL is a sophisticated, open source object relational database system that has a solid reputation for stability, feature robustness and performance after more than 30 years of active development. The goal of POSTGRES was to introduce as few features as possible to fully support data types. The ability to construct types and explicitly specify relationships was one of these features, which was extensively used yet maintained exclusively by the user.
The database in POSTGRES recognize relationships and can extract data from linked tables using rules in a logical fashion. Many of Ingres’ principles were incorporated in POSTGRES, as it can manage a wide variety of workloads, from single computers to warehouses that store data or Web services with a large number of users that make use of those services concurrently. It is the default database for for macOS Server, as well as Windows, Linux, FreeBSD, and OpenBSD.
- Inheritance: enables users to create clean tables that model their model structures.
- Non-Atomic Columns: PostgreSQL lets columns to have sub values to be accessed via queries.
- JSON Data Support
- Data types Support: PostgreSQL supports monetary, geometric, enumerated, network address, text search, xml, json, array, composite and range types for PostgreSQL object identification and log location.
- Data integrity: PostgreSQL is very reliable because it is ACID (Atomicity, Consistency, Isolation, and Durability) compliant. Queries hold data integrity and return the same output with no error.
- Sharding: Range and list Partitioning support.
- Supports the locking mechanism and is high availability.
- Fault tolerance capacity.
- Multi version concurrency control (MVCC) support.
- PostgreSQL server runs on all operating systems.
Create PostgreSQL Docker Image Container
Step 1): Configure Docker Compose To Use PostgreSQL Container
Next, to use PostgreSQL, we will create a docker-compose.yml file, to successfully configure docker compose. This file should have it’s contents as shown in the screenshot below:
# docker-compose.yml version: '3' services: database: image: "postgres" env_file: - database.env volumes: - database-data: /var/lib/postgresql/data
The database.env file called above to configure postgres has it’s content as shown in the screenshot below:
# database.env POSTGRES_USER=unicorn_user POSTGRES_PASSWORD=magical_password POSTGRES_DB=rainbow_database
Step 2): Log in to PostgreSQL Container
After having successfully configured docker-compose to use the PostgreSQL container, we can now move on and log into PostgreSQL. We will follow the following sub steps to do so:
Initiate the Database
To start our database we will use the command, docker-compose up. After this command is executed, our terminal should like the terminal in the screenshot below:
The PostgreSQL Docker container image will only be downloaded by Docker compose at the first instance of execution of this command.
Make a connection with the database
The database container may be accessed by a range of methods. We will access the database container in this tutorial and utilize the client software, titled psql, that comes pre installed in the container of our database.
docker-compose run database bash # drop into the container shell
database# psql --host=database --username=unicorn_user -- dbname-rainbow_database
Upon receiving a prompt to enter our password, we will use the password we set up while we were configuring our yml file. In this tutorial that password was set as, magical_password.
Create a table in your database
A table in a database is a collection of linked data organized in a table structure. It’s made up of rows as well as columns.
A table is a collection of data components organized that use a structure of columns that are stacked vertically (specifically named) and rows that exist horizontally, with the cell serving as the point where a row and column meet. A table may have several rows, but it must have a certain number of columns. One or more values occurring in a certain subset of a column subset help identify each row. The set of fields that uniquely identify entries in a database are known as the primary key.
The distinction between a table and a relation is that a table is generally a set of multiple rows, but a relation is a set that does not tolerate repetitions. Tables often include information connected with them, such as limitations on the table or values inside certain columns, in addition to the actual data in the rows.
rainbow_database=# \d # verify the table does not already exist
rainbow_database=# CREATE TABLE color_table(name TEXT)
Add data to your database and fetch data
rainbow_database=# SELECT * FROM color_table
INSERT INTO color_table VALUES ('pink')
Step 3): Data Persistence without Containers
Next part of this tutorial about how to Create PostgreSQL Docker Image Container (Docker-Compose)is to store data in a container that has the name volumes. Docker Compose facilitates the creation and destruction of these volumes. These volumes ensure that the data is preserved even if the containers are destroyed.
# docker-compose.yml services: database: ... volumes: - database-data: /var/lib/postgresql/data/
You must give the following command to inform Docker-Compose to delete the volume and its contents:
docker-compose down --volumes
If we delete these volume settings, whatever data we’ve saved will be lost every time our container is destroyed by us.
We can also use host volumes to utilise same functionality by using the configurations shown in the screenshot below in our yml file:
# docker-compose.yml services: database: ... volumes: - ./host-folder/:/var/lib/postgresql/data/
This method allows the data to be saved on the computer of the host. You’ll need to remove the data files manually using the command listed below to erase this data and create a database with new data:
rm -rf ./host-folder/
How to Create PostgreSQL Docker Image Container (Docker-Compose) Conclusion
We can use PostgreSQL and avoid manually creating and filling our database by using Docker-Compose and it’s features. Docker-Compose provides the following advantages: it is quicker, reproducible and automated. It also eliminates the need to install the database on your host computer.
It enables you to have various database versions if each of your projects necessitates the usage of separate versions. Our goal for this article is to create some tables with their respective foreign keys and fill those tables so they can easily be shared across users.
We often need to fill and ultimately share a database with fake data, whether to test out our pipelines or queries, demonstrate the functionality of a new service, or deploy it as a tool to extend testing to company members who are yet to come.
Using or sharing files such as CSV, parquet, or s3 is one of the many alternatives we have to meet this demand, but each of the difficulties we wish to address has restrictions. This is an issue that can be readily remedied by using the capabilities that Docker provides.
It’s perfectly okay to run your database in a Docker container if you’re working on a small project and delivering to a single computer. Make sure the data is durable by mounting a volume and implementing backup procedures. Attempt to restore them in frequent intermissions to ensure that your backups are still valid.
Many users use Docker containers to run their little projects, or docker-compose.yml files to put up their whole stack. It’s practical and suitable for minor tasks involving non critical data.
Hopefully by following this tutorial you have successfully learned a method of creating your PostgreSQl database not manually but in a more efficient manner using the Postgre Docker container coupled with Docker-Compose.