Setting up a PostgreSQL Database with Docker
Including Database Setup, Data Persistence, Using GUI to Operate the Database

PostgreSQL Basic Tutorial: Backend Advancement PostgreSQL Series

Environment Setup - Using Docker-compose

Image Used: postgres: alpine
Reference Article: Getting Started with PostgreSQL using Docker-Compose

docker-compose.yml

version: '3'
services:
  postgres:
    container_name: postgres
    ports: 
      - "5432:5432"
    image: "postgres:alpine"
    volumes:
      - database-data:/var/lib/postgresql/data # persist data even if container shuts down
    restart: always
    environment:
      POSTGRES_USER: superuser
      POSTGRES_PASSWORD: superpassword
      POSTGRES_DB: demo_db
volumes: 
  database-data: # named volumes can be managed easier using docker-compose

Start the container:

docker-compose up

GUI

Installation

As Sequel Ace does not support PostgreSQL, install another GUI for use
Install DBeaver

Java package is required

brew install --cask dbeaver-community

Accessing PostgreSQL container remotely

Obtain the external IP of the remote host and connect to the specified port (5432)
to access the PostgreSQL container on other machines

Data Persistence

Official documentation
Initializing Database and Data When Starting Services with docker-compose (using MySQL as an example)
Different Types of Volumes

Named Volume:

1. Start the container with docker-compose up
2. Verify that the volume is created with docker volume ls
3. Stop the container with docker-compose stop
4. Start the container again with docker-compose up and verify data persistence using GUI connection
5. Remove the container with docker-compose down
6. Check with docker ps -a to ensure the container is removed
7. Start the container again with docker-compose up, and the volume should be mounted successfully

Host Volume

Adjust the target folder location of the volume inside the container to a specific local folder
If multiple containers need to mount the same data together: Official documentation
This method does not show results with docker volume ls as volume management is no longer done through docker

version: '3'
services:
  postgres:
    container_name: postgres
    ports: 
      - "5432:5432"
    image: "postgres:alpine"
    volumes:
      - ./database_data:/var/lib/postgresql/data # persist data even if container shuts down
    restart: always
    environment:
      POSTGRES_USER: superuser
      POSTGRES_PASSWORD: superpassword
      POSTGRES_DB: database_name

1. Modify the path in the yml file under service volumes
2. Start the container with docker-compose up
3. After managing the volume with the path, docker volume ls will not show a response
4. Check the specified path ./database_data, and the volume files should be successfully written
5. Start the container again with docker-compose up and verify data persistence using GUI connection
6. Remove the container with docker-compose down and confirm removal with docker ps -a
7. Start the container again with docker-compose up, and the volume should be mounted successfully

Supplementary

If multiple PostgreSQL containers need to be enabled on the same machine
Port 5432 will be occupied, external ports can be changed to non-conflicting ports
But the internal port remains 5432 If communicating directly within the container network, for example, the port set in Laravel’s .env configuration

When connecting via the container’s external network, use the adjusted ports to connect normally
However, when executing commands like php artisan migrate within the container
The internal port 5432 needs to be used, which can be cumbersome for management

In this scenario, specifying the internal running port when starting the container can resolve the issue

  postgre:
    container_name: stage_postgre
    ports:
      - "5430:5430"
    image: "postgres:12.6"
    volumes:
      - /project/database/stage/database_data:/var/lib/postgresql/data # persist data even if container shuts down
    command: -p 5430
    environment:
      POSTGRES_USER: ${DB_USER}
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_DB: ${DB_NAME}
    restart: always

In the above configuration, command: -p 5430 specifies the internal port used by postgres
So in the ports: setting, the external port 5430 is directly assigned to the internal port 5430
This way, the same port can be used for connections both inside and outside the container

Using GUI to Access PostgreSQL container remotely

Obtain the external IP of the remote host and connect to the 5432 port to access the PostgreSQL container on other machines.