Skip to content

Postgresql on Kubernetes

Manually change the postgres user password

Upgrade postgres major version

Dump & restore

Preferred solution: Dump and restore

Issues / considerations:

  • Don't use text file format, but custom file format (-Fc), otherwise it could lead to text file issues like dos line endings and \N issues.
  • Also, avoid piping from pod to local filesystem because of the above issue.

Manual dumps

Option 0: Use pg_dumpall like the automated bitname postgresql backup cronjob: From inside the postgres container ()

export PGDUMP_DIR=/var/lib/postgresql/data
pg_dumpall --clean --if-exists --load-via-partition-root \
  --quote-all-identifiers --no-password \
  --file=${PGDUMP_DIR}/pg_dumpall-$(date '+%Y-%m-%d-%H-%M').pgdump"

Option 1: Dump directly on pg host:

export PG_PASSWORD=$(kubectl get secret -n plausible plausible-postgresql \
  -o jsonpath="{.data.postgres-password}" | base64 --decode)
echo $PG_PASSWORD
kubectl -n plausible exec -ti plausible-postgresql-0 -- pg_dump -Fc --host \
  plausible-postgresql -U postgres -d plausible -f /tmp/plausible.dump
kubectl -n plausible cp plausible-postgresql-0:/tmp/plausible.dump /tmp/plausible.dump

Option 2: Dump from dedicated pg client pod (WIP, still not working since the pod terminates after pg_dump)

export PG_PASSWORD=$(kubectl get secret -n plausible plausible-postgresql \
  -o jsonpath="{.data.postgres-password}" | base64 --decode)
export PG_IMAGE=$(kubectl -n plausible get pod plausible-postgresql-0 \
  -o jsonpath="{.spec.containers[0].image}")
kubectl run db-postgresql-client --rm --tty -i --restart='Never' \
  --namespace plausible --image $PG_IMAGE --env="PGPASSWORD=$PG_PASSWORD" \
  --command -- \
  pg_dump -Fc --host plausible-postgresql -U postgres -d plausible -f /tmp/plausible.dump

kubectl -n plausible cp plausible-postgresql-0:/tmp/plausible.dump /tmp/plausible.dump
  • Remove Helmrelease
  • Remove Postgres PVC
  • Upgrade helmrelease to new Postgres major version
  • Scale down deployments/statefulsets (i.e. Plausible, Plausible-clickhouse )

Restore

kubectl -n plausible cp /tmp/plausible.dump plausible-postgresql-0:/tmp/plausible.dump

kubectl -n plausible exec -ti plausible-postgresql-0 -- \
  dropdb -U postgres plausible
kubectl -n plausible exec -ti plausible-postgresql-0 -- \
  createdb -U postgres plausible
kubectl -n plausible exec -ti plausible-postgresql-0 -- \
  pg_restore -U postgres -d plausible /tmp/plausible.dump

pg_upgrade