Postgresql 

Postgres is an opensource object-relational database.

Ports

Files

Docker Creation

Docker Compose

postgres:
  image: postgres:12-alpine
  restart: "always"
  networks:
    db:
      ipv4_address: {IP}
  environment:
    - POSTGRES_PASSWORD={ADMIN PASS}
    - POSTGRES_USER={ADMIN USER}
    - POSTGRES_DB=postgres
    - TZ=America/Los_Angeles
  volumes:
    - /data/services/postgres/initdb.d:/docker-entrypoint-initdb.d:ro
    - /data/services/postgres/data:/var/lib/postgresql/data
    - /etc/localtime:/etc/localtime:ro

Container should not be mapped via proxy. Don’t expose container.
postgres used as the default DB since this DB is automatically created and offers no additional security with a name change if the container is compromised.
Postgres runs internally as the postgres user, UID/GID of 70. Create a system account with this UID/GID and chown postgres files with it.

Creating A Database

Create a new database for each service that will use this DB backend. This includes user accounts for the specific database.

Create DB user and database.

docker -exec -ti db /bin/bash
psql -U {ADMIN USER} -d postgres

CREATE USER {DB USER} PASSWORD '{DB USER PASS}';
CREATE DATABASE {DB USER};

ALTER DATABASE {DB USER} OWNER TO {DB USER};
GRANT ALL PRIVILEGES ON DATABASE {DB USER} TO {DB USER};

Import A Database

Database dumps may be imported, but explicit table permissions need to be set for the DB user to access the data.

Note

Permissions need to be set if DB is not imported as the DB user. This assumes the database has already been created.

Import DB and set appropriate DB permissions.

docker -exec -ti db /bin/bash
psql -v ON_ERROR_STOP=1 --username {DB ADMIN} {DB} < {DB DUMP}
psql -U {ADMIN USER} -d postgres

ALTER DATABASE {DB USER} OWNER TO {DB USER};
GRANT ALL PRIVILEGES ON DATABASE {DB USER} TO {DB USER};
\q

for tbl in `psql -U {DB ADMIN} -qAt -c "select tablename from pg_tables where schemaname = 'public';" {DB}`; do psql -U {DB ADMIN} -c "alter table \"$tbl\" owner to {DB USER}" {DB}; done
for tbl in `psql -U {DB ADMIN} -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" {DB}`; do psql -U {DB ADMIN} -c "alter sequence \"$tbl\" owner to {DB USER}" {DB}; done
for tbl in `psql -U {DB ADMIN} -qAt -c "select table_name from information_schema.views where table_schema = 'public';" {DB}`; do psql -U {DB ADMIN} -c "alter view \"$tbl\" owner to {DB USER}" {DB}; done

`initdb.d` Scripts

These scripts will be run if the Postgres DB does not exist on first startup. Useful for creating initial DB’s for services automatically. An example script is below, which creates user accounts, imports a database dump if it exists, and ensures permissions are set properly.

0500 postgres postgres /docker-entrypoint-initdb.d/example.sh

#!/bin/bash
set -e
PSQL_ADMIN={{ psql_admin }}
DB={{ db }}
DB_USER={{ db_user }}
DB_DUMP_LATEST={{ db_dump_latest }}

echo "Creating $DB_USER user account ..."
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
  CREATE USER $DB_USER PASSWORD '{{ db_pass }}';
  CREATE DATABASE $DB;
EOSQL

if [ -f "$DB_DUMP_LATEST" ]; then
  echo "Importing $DB_DUMP_LATEST to $DB database ..."
  psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" $DB < "$DB_DUMP_LATEST"
fi

echo "Setting database $DB permissions ..."
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
  ALTER DATABASE $DB OWNER TO $DB_USER;
  GRANT ALL PRIVILEGES ON DATABASE $DB TO $DB_USER;
EOSQL

# https://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql
for tbl in `psql -U $PSQL_ADMIN -qAt -c "select tablename from pg_tables where schemaname = 'public';" $DB` ; do
  psql -U $PSQL_ADMIN -c "alter table \"$tbl\" owner to $DB_USER" $DB;
done
for tbl in `psql -U $PSQL_ADMIN -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" $DB` ; do
  psql -U $PSQL_ADMIN -c "alter sequence \"$tbl\" owner to $DB_USER" $DB;
done
for tbl in `psql -U $PSQL_ADMIN -qAt -c "select table_name from information_schema.views where table_schema = 'public';" $DB` ; do
  psql -U $PSQL_ADMIN -c "alter view \"$tbl\" owner to $DB_USER" $DB ;
done

echo "done."

Database Backup

Backup Entire Instance

This will dump all databases, users and permissions. Remember to pull the data from the instance or the data directory.

Dump Entire Instance.

docker -exec -ti db /bin/bash
pg_dumpall > {DUMP FILE}.sql

Backup A Specific Database

Backup a specific database. Permissions will need to be restored with database.

Dump specific DB.

docker -exec -ti db /bin/bash
pg_dump -U {DB ADMIN} --no-owner {DB} > {DUMP FILE}.sql

Query Active Connections

Display client sessions that are currently connected to database.

select pid as process_id,
     usename as username,
     datname as database_name,
     client_addr as client_address,
     application_name,
     backend_start,
     state,
     state_change
from pg_stat_activity;

Postgresql