Getting Started with Docker and PostgreSQL

PostgreSQL is an open source relational database management system (RDBMS) that is known for its reliability, robustness, and scalability. It is widely used in enterprise applications and offers features such as data integrity, transactions, and SQL support.

Why PostgreSQL is damn popular?

PostgreSQL is popular for several reasons, including:

1. Reliability and robustness

PostgreSQL is known for its reliability and ability to handle large and complex databases with high concurrency.

2. Open source

PostgreSQL is free and open source, which makes it accessible to anyone and allows for a large community of contributors and users.

3. Advanced features

PostgreSQL offers advanced features such as support for advanced data types, JSON, and full-text search.

4. Standards compliance

PostgreSQL is known for its high level of standards compliance, which makes it easy to integrate with other systems and applications.

5. Extensibility

PostgreSQL is highly extensible, which means it can be easily customized and extended to meet specific needs and requirements.

PostgreSQL Docker Image

The PostgreSQL Docker image is a pre-configured version of PostgreSQL that can be run inside a Docker container. It allows developers and system administrators to quickly set up and run a PostgreSQL instance without having to install it directly on their local machines.

The official PostgreSQL Docker image is available on the Docker Hub, and it is maintained by the PostgreSQL community. It includes the latest version of PostgreSQL and is available for different operating systems and architectures.

The PostgreSQL Docker image can be easily customized using environment variables to set up database users, passwords, and other configuration options. It can also be used in conjunction with other Docker images and services to create scalable and highly available database clusters.

Using the PostgreSQL Docker image can help to simplify the deployment and management of PostgreSQL instances, and it can make it easier to develop, test, and deploy applications that rely on PostgreSQL as their backend database.

Steps to install PostgreSQL using Docker Desktop

here are the general steps to install PostgreSQL using Docker Desktop:

Step 1. Install Docker Desktop

First, you'll need to download and install Docker Desktop on your machine. You can download Docker Desktop from the Docker website, and installation instructions vary depending on your operating system.

Step 2. Pull the PostgreSQL image

Once Docker Desktop is installed, open a terminal or command prompt and run the following command to download the latest PostgreSQL image from Docker Hub:

docker pull postgres

Step 3. Run a container

After the image is downloaded, run the following command to start a PostgreSQL container:

docker run --name my-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres

This command will start a new PostgreSQL container with the name my-postgres and the password mysecretpassword. The -d option runs the container in detached mode, which means it runs in the background.

Step 4. Access PostgreSQL

You can access the PostgreSQL container using the psql command-line utility or a GUI tool like pgAdmin. To access the container using psql, run the following command:

docker exec -it my-postgres psql -U postgres

This command connects to the running PostgreSQL container and opens the psql shell as the postgres user.

That's it! You should now have a working PostgreSQL instance running in a Docker container on your machine.

Setup PostgreSQL Using Customised Dockerfile

To set up PostgreSQL using a customized Dockerfile, you can follow these general steps:

Create a Dockerfile: In the root directory of your project, create a file called Dockerfile and add the following content:

FROM postgres
ENV POSTGRES_DB mydb
ENV POSTGRES_USER myuser
ENV POSTGRES_PASSWORD mysecretpassword
COPY init.sql /docker-entrypoint-initdb.d/

This file starts from the postgres image and sets some environment variables for the database name, user, and password. It also copies an init.sql file to the /docker-entrypoint-initdb.d/ directory, which will be used to initialize the database.

Create the init.sql file

In the same directory as your Dockerfile, create an init.sql file and add any SQL commands you want to execute when the container starts. For example, you could create a table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

Build the image

Run the following command in the same directory as your Dockerfile to build the custom image:

docker build -t my-postgres .

This command builds a new image based on the Dockerfile in the current directory and tags it with the name my-postgres.

Run the container

Finally, run the following command to start a new container based on your custom image:

docker run --name my-postgres -p 5432:5432 -d my-postgres

This command starts a new container with the name my-postgres, maps the container's port 5432 to the host machine's port 5432, and runs the container in detached mode.

That's it! You should now have a working PostgreSQL instance running in a Docker container based on your custom image. You can connect to the database using a client like psql or any other PostgreSQL client.

Using Docker Compose

To use PostgreSQL with Docker Compose, you can define a service for PostgreSQL in your docker-compose.yml file. Here are the general steps to do that:

Create a docker-compose.yml file: In the root directory of your project, create a file called docker-compose.yml and add the following content:

version: '3'
services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: mysecretpassword

This file defines a service called db that uses the postgres image, sets the POSTGRES_PASSWORD environment variable, and specifies that the container should always be restarted if it fails.

Start the service: To start the PostgreSQL service, run the following command in the same directory as your docker-compose.yml file:

docker-compose up -d

This command starts the service in the background (-d option) and creates a new container based on the postgres image.

Access PostgreSQL

You can access the PostgreSQL container using the psql command-line utility or a GUI tool like pgAdmin. To access the container using psql, run the following command:

docker-compose exec db psql -U postgres

This command connects to the running PostgreSQL container and opens the psql shell as the postgres user.

That's it! You should now have a working PostgreSQL instance running in a Docker container managed by Docker Compose. You can also define other services in your docker-compose.yml file and use them to build and run your application.

High Availability PostgreSQL with Replication using Docker

Here is an example YAML file for setting up a High Availability PostgreSQL database with replication using Docker Compose:

version: '3'

services:
  master:
    image: postgres:latest
    environment:
      POSTGRES_USER: your_username
      POSTGRES_PASSWORD: your_password
    volumes:
      - ./master/data:/var/lib/postgresql/data
      - ./master/init.sql:/docker-entrypoint-initdb.d/init.sql
    ports:
      - "5432:5432"
    restart: always

  slave:
    image: postgres:latest
    environment:
      POSTGRES_USER: your_username
      POSTGRES_PASSWORD: your_password
      POSTGRES_MASTER_HOST: master
      POSTGRES_MASTER_PORT: 5432
    volumes:
      - ./slave/data:/var/lib/postgresql/data
    restart: always

In this example, we have two services defined: master and slave.

The master service is the primary database node. It uses the postgres Docker image and sets the environment variables POSTGRES_USER and POSTGRES_PASSWORD to define the superuser account for the database. We also mount a local directory ./master/data to the container's data directory to persist data, and ./master/init.sql file is used to initialize the master database with any initial data.

The slave service is the replica database node. It also uses the postgres Docker image, and sets the POSTGRES_USER and POSTGRES_PASSWORD environment variables. We also define the POSTGRES_MASTER_HOST and POSTGRES_MASTER_PORT environment variables to point the replica to the primary database. The ./slave/data directory is mounted to the container to persist replica's data.

With this configuration, the master service listens on the host's port 5432 and replication is automatically set up for the slave service to follow the master.