PostgreSQL 13 (For Developers)

PostgreSQL⁠ is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance

Image reason

For development and testing purposes we need pre-setup Postgres server to automate development and auto-testing activity. The container has pre-defined empty database (with flag table to ensure non-production case) and two users.

Spec

Expose ports

• tcp/5432 - Postgres listening endpoint

Directories

• /data - Hold Postgres'es data
• /dbseed - Files *.sql from the folder is executing at startup of a container
  • /dbseed/*.sql - execute in devdb database as postgres user
  • /dbseed/<db_name>/*.sql - execute in <db_name> database as postgres user

Predefined Stuff

• Predefined database devdb
• Predefined flag table "public"."emptytestflag"
• User postgres - superuser (no password)
• User devadmin - owner of the database devdb (no password)
• User devuser - regular user (no password)

Inside

• Alpine Linux 3.19
• PostgreSQL 13.15 Server

Launch

1. Start for development

   docker run --interactive --tty --rm --publish 5432:5432 theanurin/devel.postgres-13
   

   Copy

2. Use connection strings (no password):

   • postgres://postgres@127.0.0.1:5432/postgres - to login as superuser
   • postgres://devadmin@127.0.0.1:5432/devdb - to login as devdb owner
   • postgres://devuser@127.0.0.1:5432/devdb - to login as regular user

Build Own Images With Additional Predefined Data

Based on SQL scripts

In this scenario your init SQL files are placed in ./init-sql directory and will be applied in alphabetical order.

FROM theanurin/devel.postgres-13 AS postgres_builder
COPY ./init-sql/ /.builder-postgres.d/
RUN /usr/local/bin/docker-builder-postgres-13.sh

FROM theanurin/devel.postgres-13
COPY --from=postgres_builder /build/ /

Based on execution shell script

In this scenario your have to provide shell script /.builder-postgres.sh that will execute at build time. The script responsible for data generation.

#!/bin/sh
#
# sample of generate-data.sh
#

set -e

DB_NAME="my-db"
DB_OWNER="${DB_NAME}-owner"

# Execute command via file using Redirecting Input redirections
psql --dbname=postgres --username=postgres --set user="${DB_OWNER}" --file=<(echo 'CREATE USER :"user" WITH LOGIN;')

# Execute command via Here Documents redirections
psql --dbname=postgres --username=postgres --set db="${DB_NAME}" --set db_owner="${DB_OWNER}" <<-'EOSQL'
    CREATE DATABASE :"db" WITH
    OWNER = :"db_owner" ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;
EOSQL

# Execute command via inline SQL
psql --dbname="${DB_NAME}" --username="${DB_OWNER}" --command='CREATE TABLE "test" ("id" INTEGER NOT NULL);'

echo "Wow, ${DB_NAME} was created successfully!"

FROM theanurin/devel.postgres-13 AS build_stage
COPY ./generate-data.sh /.builder-postgres.sh
RUN chmod +x /.builder-postgres.sh
RUN /usr/local/bin/docker-builder-postgres-13.sh

FROM theanurin/devel.postgres-13
COPY --from=build_stage /build/ /

Based on state of a container

# Run container
docker run --detach --name pg_builder --publish 5432:5432 theanurin/devel.postgres-13

# Fill you data into Postgres
./my-data-script.sh postgres://postgres@127.0.0.1:5432/postgres

# Stop container and make new image
docker stop pg_builder
docker commit pg_builder my_devel.postgres-13_with_data

# Use image my_devel.postgres-13_with_data

Support

• Maintained by: Max Anurin⁠
• Where to get help: Telegram⁠