Postgres Deployment

docker run -d --rm --name $ctnr -p 5432:5432 \
    -v $(pwd):/home \
    -e POSTGRES_DB=${DB_NAME:-dbp} \
    -e POSTGRES_PASSWORD=${DB_PASSWORD:-postgres} \
    -e POSTGRES_USER=${DB_USER:-postgres} \
    'postgres:12.6-alpine'

• persist: -v $(pwd)/local_pg_data_dir:/var/lib/postgresql/data

Warm Standby

# Limit addresses per 'postgresql.conf'
psql -U 'uzr1' -d 'db1' -c 'show listen_addresses'

• Cannot be localhost if @ Docker swarm cluster

Server Config

• /etc/postgresql/postgresql.conf
• /var/lib/postgresql/data/postgresql.conf
  • @ Alpine variants

Data directory (default is config dir)

• /var/lib/postgresql/data

Show Location:

psql -h $host -U $user -c 'SHOW config_file;'

SHOW config_file;

• Sample config included @ PostgreSQL images:
  • /usr/share/postgresql/postgresql.conf.sample
  • /usr/local/share/postgresql/postgresql.conf.sample
    • @ Alpine variants

Reload @ running server:

• @ bash :: pg_ctl

  pg_ctl reload
  

• @ SQ: :: psql

  SELECT pg_reload_conf()
  

Authentication | pg_hba.conf

Password reset; align with bcrypt method @ services (Golang)

UPDATE users SET pass_hash = crypt('$DB_OPS_PASS', gen_salt('bf', 10))
WHERE (handle = 'app') OR (handle = 'AdminTest') OR (handle = 'UserTest');

@ Docker

13.1, 13, latest
13.1-alpine, 13-alpine, alpine
12.5, 12
12.5-alpine, 12-alpine
11.10, 11
11.10-alpine, 11-alpine
10.15, 10
10.15-alpine, 10-alpine
9.6.20, 9.6, 9
9.6.20-alpine, 9.6-alpine, 9-alpine
9.5.24, 9.5
9.5.24-alpine, 9.5-alpine

@ AWS RDS :: Managed RDBMS Engine per AMI Instance

# Versions list
aws rds describe-db-engine-versions \
    --engine 'postgres' \
    | jq -r '.DBEngineVersions[].EngineVersion'

• 9.5.2, ..., 9.6.8-20, 10.9-15, ..., 11.4-10, ..., 12.2-5 (2021-02-10)

Storage :: Persistence

• How to persistent storage in Docker

  docker volume create --name vol1
  docker run -d -v vol1:/ctnr_path ctnr_image _some_cmd
  

• How to persist dockerized postgres database

  volumes:
    - ./pg_local_data:/var/lib/postgresql/data
  

• How to copy docker volume from one machine to another?

@ AWS :: EC2/EBS

1. Attach EBS volume (disk) to EC2 instance
   • /dev/xvdh
2. Make partition (optional)
   • /dev/xvdh1
3. Make filesystem on the partition/disk
4. Mount filesystem inside EC2 instance
   • /opt/ebs_data
5. Start Docker container with volume
   • /opt/ebs_data:/var/lib/postgresql/data