Postgres Handbook

Categories

Setup using Ansible

pg_user=user
pg_pass=password
pg_db=db
docker run --name postgres \
    -d --restart=always \
    -v $HOME/Dropbox/data/postgres:/var/lib/postgresql/data \
    -e POSTGRES_USER=$pg_user \
    -e POSTGRES_PASSWORD=$pg_pass \
    -e POSTGRES_DB=$pg_db \
    postgres:11.5

Backup

docker run --rm --network=network \
    -e PGPASSWORD=password \
    --entrypoint '/usr/bin/pg_dump' \
    postgres:9.6 \
    -h host -p 5432 \
    -U user -d database | gzip > backup.sql.gz

Restore

zcat backup.sql.gz | docker exec -i postgres_contaienr_name psql -U user -d database

Postgres Maintenance Commands

ALTER USER username WITH PASSWORD 'new_password';

Postgresql Client

psql

# https://unix.stackexchange.com/questions/249494/install-postgresql-client-psql-only-on-centos
# https://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file
yum install postgresql
psql -h host -p 5432 -U user -d database

\copy (Select * From foo) To '/tmp/test.csv' With CSV

python

PostgreSQL HA

Kubernetes

https://github.com/zalando/patroni

Reference