5. Postgres Database Introduction

Company administration Postgres database SQL migrations for NocoDB (including custom columns and relationships defined using NocoDB interface) (repo/company_backend_pginit/README.md)

Generate and obtain a SQL dump from a running PostgreSQL container

Good for backing up a database
Also good for transferring a database to another server
Also good for getting fresh SQL of specific NocoDB instance to act as a blueprint
Make sure to remove nc_users_v2 contents and any auth0 contents before committing a blueprint

docker container ls -a | grep postgres | grep company
docker exec -it <container_id> /bin/sh
su postgres
cd /etc/postgresql
pg_dump -U company_admin company_admin_db > nocofreshdump-YYYYMMDD.sql
exit
exit
docker cp <container_id>:/etc/postgresql/nocofreshdump-YYYYMMDD.sql .

Docker Compose Snippet


services:
  # PostgreSQL database for Public Presentation
  presentation_db:
    image: "postgres:..."
    environment:
      - POSTGRES_DB=${PRESENTATION_POSTGRES_DATABASE}
      - POSTGRES_USER=${PRESENTATION_POSTGRES_USER}
      - POSTGRES_PASSWORD=${PRESENTATION_POSTGRES_ROOT_PASSWORD}
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ..."]
      start_period: 15s
      interval: 12s
      timeout: 5s
      retries: 5
    volumes:
      - ./presentation_backend/README.md:/README.md:ro
    networks:
      - common_network
    restart: unless-stopped
    logging:
      driver: ...
      options:
        syslog-address: ...
        tag: ...
    
  # PostgreSQL database for Company Administration
  company_admin_db:
    image: "postgres:..."
    environment:
      - POSTGRES_DB=${COMPANY_POSTGRES_DATABASE}
      - POSTGRES_USER=${COMPANY_POSTGRES_USER}
      - POSTGRES_PASSWORD=${COMPANY_POSTGRES_ROOT_PASSWORD}
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ..."]
      start_period: 15s
      interval: 12s
      timeout: 5s
      retries: 5
    volumes:
      - ./company_backend/README.md:/README.md:ro
      - ./company_backend_pginit/single-sql-init-migration:...
      - ./company_backend_pginit/single-sql-init-entrypoint.sh:...
    command: ...
    networks:
      - common_network
    restart: unless-stopped
    logging:
      driver: ...
      options:
        syslog-address: ...
        tag: ...
        
  # dev only: adminer for databases
  adminer:
    image: adminer:...
    profiles:
      - dev
    environment:
      ADMINER_DEFAULT_SERVER: ...
    networks:
      - common_network
    restart: unless-stopped