corpusops/setups.postgresql

Name: setups.postgresql

Description: null

Created: 2017-09-05 08:49:01.0

Updated: 2017-11-30 12:27:11.0

Pushed: 2018-01-16 09:55:16.0

Homepage: null

Size: 70

Language: Shell

GitHub Committers

User	Most Recent Commit	# Commits

Other Committers

User	Email	Most Recent Commit	# Commits

README

Docker based image for postgresql

USE/Install with makina-states

• makina-state deployment (legacy) can be found in .salt

Exemple pillar

kina-projects.pgsql:
ata:
backup_disabled: false
pgver: 9.6
mail: sysadmin@foo.com
pg_optim:
  #./pgtune/pgtune -i /etc/postgresql/9.5/main/postgresql.conf -M $((15842612*1024))
  - default_statistics_target = 100
  - maintenance_work_mem = 960MB
  - checkpoint_completion_target = 0.9
  - effective_cache_size = 11GB
  - work_mem = 72MB
  - shared_buffers = 3840MB
sysctls:
  - kernel.shmall: 4026531840
  - kernel.shmmax: 16106127360
databases:
  - x:
      password: "x"
      user: x

corpusops/postgresql (CURRENT)

Description

This setups a nginx reverse proxy on http/https that forward requests to an underlying postgresql worker.

This repository produces all those docker images:

• corpusops/postgresql
• corpusops/postgis

Volumes

• We use two main volumes!
  • a volume setup to share a configuration file to reconfigure fles
  • a volume data to store user data
  • a volume db to store user db files

Initialise setup volume

• To reconfigure any setting upon container (re)start, create/edit /setup/reconfigure.yml

  • See defaults

  r -p local/setup
  >local/setup/reconfigure.yml << EOF
  
  ing: value
  

• To configure (add/modify/remove) new roles, db, & privs (resp. in this order), we use custom corpusops modules which all wraps ansible official modules:

• corpusops.roles/postgresql_role

• corpusops.roles/postgresql_db

• corpusops.roles/postgresql_privs

• Exemple

  _postgresql__roles:
  me: dbuser
  generate/use password inside file: ./local/config/pwd_dbuser
  ssword: "{{
    lookup('password',
           (cops_postgresql_cfg+'/pwd_dbuser '
            'length=15 chars=ascii_letters,digits')) }}"
  _postgresql__databases:
  : db
  mplate: postgis
  ner: dbuser
  : db2
  mplate: postgis
  : db3
  ate: absent
  _postgresql__privs:
  les: dbuser
  tabase: db2
  pe: database
  ivs: ALL
  

• If you need to tune pgsql, you can add something to /setup/reconfigure.yml this way:

  
  _postgresql_sysctls:
  rnel.shmall: 4026531840
  rnel.shmmax: 16106127360
  _postgresql_conf:
  fault_statistics_target = 50
  intenance_work_mem = 960MB
  nstraint_exclusion = on
  eckpoint_completion_target = 0.9
  fective_cache_size = 11GB
  rk_mem = 96MB
  l_buffers = 8MB
  ared_buffers = 3840MB
  x_connections = 80
  

Initialise user data volumes

• You need to preseed some volumes from your image before running it

  • db

    r -p local/db
    er run --rm  -v $PWD/local/db:/ldb --entrypoint rsync \
    corpusops/postgresql:9.6.5 \
    "/var/lib/postgresql/" "/ldb/" \
    av --delete
    

  • data

    r -p local/data
    er run --rm  -v $PWD/local/data:/ldata --entrypoint rsync \
    corpusops/postgresql:9.6.5 \
    "/srv/projects/postgresql/data/" "/ldata/" \
    -av --delete --exclude "pwd_*" --delete-excluded
    

Run this image through docker

• To pull & run this image (PRODUCTION)
  Note that The folllowing command implicitly create 2 volumes against local directories and the goal is to prepopulate the directories from the image content on the first run.
  Indeed, the -v option does not feed host directories, even if empty from an image content.

  cker pull corpusops/postgresql:<TAG>
  er pull corpusops/postgresql:9.6.5
  er run \
  name=my-postgresql-container \
   /sys/fs/cgroup:/sys/fs/cgroup:ro \
   $(pwd)/local/setup:/setup:ro \
   "$(pwd)/local/data:/srv/projects/postgresql/data" \
   "$(pwd)/local/db:/var/lib/postgresql" \
  security-opt seccomp=unconfined \
   -d -i -t corpusops/postgresql:9.6.5
  

• In development, you can add the following knob to indicate that you want to edit files.

  UPEREDITORS=$(id -u)
  

Build this image

• Install hashicorp/packer && docker
• get the code
• run ./bin/build.sh

Image provision notes

• See .ansible, the image is (re)-configured using ansible.

A note on file rights

• Docker file rights are a nightmare for developers

• We provide a very way to use, specially when you are on localhost,
  activly developping your app to edit the files of the container,
  thanks to POSIX ACLS.

• You need two things to configure your app (normally good by dedfault):

  • cops_postgresql_supereditors_paths Tell which paths will be “opened” to the outside user(s) if default does not suit your need
  • cops_postgresql_supereditors Tell which user(s), (attention UIDS).
    The aforementioned command to launch container includes the SUPEREDITORS env var configured with the loggued in user

• Those settings can be overriden via /setup/reconfigure.yml

• File rights are enforced upon container (re-)start

• If file rights are messed up too much, you can try this to enforce them

  er exec -e SUPEREDITORS="$(id -u)" -ti <mycontainer> bash
  /projects/<myproject>/fixperms.sh
  

ansible

• Docker uses the postgresql role underthehood which is generic and is not docker specific.
• You may use directly this role to provision postgresql on another host type.
• This code the raw corpusops.roles/postgresql role

Steps to create cops docker compliant images

• We use via bin/build.sh which launch docker_build_chain (doc)