PostgreSQL is a very popular open-source relational database management systems (RDBMS), for its Extensibility and Feature-Rich, suitable for mission-critical applications, not to mention its active PostgreSQL community.
In the upcoming posts, I will start a series of PostgreSQL study to :
explore PostgreSQL main features, installation, basic administration tasks
deploy PostgreSQL cluster onto K8S with PostgreSQL Operater, validate backup and rolling upgrade
create a simple Flash microservice application to connect PostgreSQL cluster and validate failover
integrate the whole deployment into CICD pipeline for automation
create AWS RDS PostgreSQL, with S3 Block storage as replica
By the end of the series we should be able to have a comprehensive understanding of PostgreSQL from a DevOps perspective
PostgreSQL Basic
To begin, we will
install PostgreSQL as a docker container on a local Ubuntu machine to get it up and running,
Run a simple PostgreSQL database with docker compose
Validate from adminer web console locahost:8080 with password set in the environment variables
Persist data to mount the PostgreSQL container volume, validate data table after start/stop container
PostgreSQL stores its data by default under /var/lib/postgresql/data, here we create a /pgdata folder on local machine to mount PostgreSQL default volume
Connect to DB container and validate
add persist data in docker-compose and run PostgreSQL from compose
Validate the previous table and record from adminer console
Table and record still there because of the persistent data mount
PostgreSQL Configuration
Before jumping into replication, it is more important to explore the PostgreSQL configuration files to have a better understanding of its important config, take the default conf files out of a running database and learn it and make own configuration, then mount these conf files into container, so tell PostgreSQL to use my own configuration files to perform my prefered way.
To achieve this, we need the db user “postgres” has ID of 999 with access to custom conf files.
The official PostgreSQL documentation explains those configuration files as below:
pg_hba.conf:
This file stands for “PostgreSQL Host-Based Authentication.” It controls client authentication based on the host and user information. It specifies which hosts are allowed to connect to the PostgreSQL server, which databases and users they can access, and what authentication methods they must use. It’s a crucial security measure for controlling access to PostgreSQL server.
pg_ident.conf:
This file, “PostgreSQL Identification Mapping,” allows to define mappings between external (e.g., operating system) and internal (PostgreSQL) user names.
postgresql.conf:
Main configuration file for PostgreSQL which contains global settings to tailor its behavior to specific requirements and environment.
Create custom config file
now we can adjust the command by adding environment variables to run PostgreSQL from docker and docker-compose using our custom conf files
Conclusion
Now we can run a PostgreSQL container from docker and docker-compose with Persist data and custom configuration mount into the container, in the next blog we will discover primary and standby replication, WAL (write ahead log) options.