Skip to content

Instantly share code, notes, and snippets.

@bjuretko

bjuretko/pg_csv_export.sh

Last active April 1, 2020 14:00
Show Gist options
  • Save bjuretko/11f5a1c59d7feea888f88f8c42fbbbea to your computer and use it in GitHub Desktop.
Save bjuretko/11f5a1c59d7feea888f88f8c42fbbbea to your computer and use it in GitHub Desktop.
Download ZIP
Azure PostgreSQL dump script *Disclaimer* : be careful when operating on databases and try out database clone before. I am not responsible for any data loss or corruption etc.
Raw
pg_csv_export.sh
#!/usr/bin/env bash
# provide a env file content like
# POSTGRES_HOST=xxxx.postgres.database.azure.com
# POSTGRES_USERNAME=postgres@xxxx.postgres.database.azure.com
# POSTGRES_PORT=5432
# POSTGRES_PASSWORD=xxxxxxx
# POSTGRES_DB=xxxx_production
# you need the cert from in pwd from https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem
if [ "$1" ]; then
while IFS='=' read -r n v
do
# trim input
n=$(echo -e $n | sed -e 's/^[[:space:]]*//' -e 's/^export *//')
[[ $n != "#"* ]] && eval export "'$n=$v'"
done<$1
elif [ -z "$POSTGRES_HOST" ]; then
echo You need to provide POSTGRES_HOST, POSTGRES_USERNAME, POSTGRES_PORT, POSTGRES_PASSWORD, POSTGRES_DB
echo or call this script with an env file as paramater.
exit 1
fi
if [ ! -r BaltimoreCyberTrustRoot.crt.pem ]; then
echo Downloading BaltimoreCyberTrustRoot.crt.pem ...
wget https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem
fi
function _psql() {
docker run --entrypoint "" -it --rm -e "PGPASSWORD=$POSTGRES_PASSWORD" -v $(pwd):/tmp/cwd postgres:alpine psql "sslmode=verify-full sslrootcert=/tmp/cwd/BaltimoreCyberTrustRoot.crt.pem host=$POSTGRES_HOST dbname=$POSTGRES_DB user=$POSTGRES_USERNAME" "$@"
}
tables=($(_psql -Atc "COPY (select table_name from information_schema.tables where table_schema='public') TO STDOUT ENCODING 'UTF-8';"))
for t in ${tables[@]}; do
echo "Creating ${POSTGRES_DB}_""${t}"".csv ..."
_psql -Atc "COPY (select * from $t) TO STDOUT WITH CSV delimiter ',' HEADER ENCODING 'UTF-8';" > "${POSTGRES_DB}_${t}.csv"
done
docker run --entrypoint "" -it --rm -e "PGPASSWORD=$POSTGRES_PASSWORD" -v $(pwd):/tmp/cwd postgres:alpine \
pg_dump "sslmode=verify-full sslrootcert=/tmp/cwd/BaltimoreCyberTrustRoot.crt.pem host=$POSTGRES_HOST dbname=$POSTGRES_DB user=$POSTGRES_USERNAME" -Fc -C -c > dump_`date +%d-%m-%Y"_"%H_%M_%S`.dump
Raw
postgres_backup_restore.md

Backup

NOTE: Do not use -t parameter for pseudo-tty handling with docker. This can cause errornous handling of piped (binary) output

The parameter -C -c will force recreation of database

docker exec your-db-container pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql

Custom format dump file for use with pg_restore with recreation of database (-C -c)

docker exec postgres pg_dump -Fc -C -c -U postgres my-database > dump_`date +%d-%m-%Y"_"%H_%M_%S`.dump

Restore

NOTE: Do not use -i parameter for stdin handling with docker. This can cause errornous handling of piped (binary) input

cat latestdump.sql | docker exec -i your-db-containe psql -U postgres

Create database (-C)

docker exec -t your-db-container pg_restore -C -U postgres < dump_xxxx-xx-xx_xx_xx_xx.sql

Custom format restore with recreation (drop+create) of database (-C -c)

docker exec -t your-db-container pg_restore -Fc -C -c -U postgres < dump_xxxx-xx-xx_xx_xx_xx.dump
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment