Skip to content

Instantly share code, notes, and snippets.

@oofnikj oofnikj/pg_manage.sh
Last active Oct 14, 2019

Embed
What would you like to do?
PostgreSQL dump / restore to Google Cloud Storage
#!/bin/bash
PGUSER=${PGUSER:-"postgres"}
CATALOG_BUCKET="normalized-catalog"
_get_dbs() {
psql -U $PGUSER -c "COPY (
SELECT datname FROM pg_database
WHERE datname NOT IN (
'postgres',
'template0',
'template1'
)
) TO STDOUT;"
}
_pg_dump() {
echo "dumping database $1..."
pg_dump -U $PGUSER -d $1 \
--format=c \
--compress=1 \
| gsutil cp - gs://$CATALOG_BUCKET/$1.pgdump
}
_cat() {
if [[ $1 == "-" ]] ; then
echo "restoring from stdin..." >&2
cat
else
echo "restoring database $1 from GCS..." >&2
gsutil cat gs://$CATALOG_BUCKET/$1.pgdump
fi
}
_pg_restore() {
_cat $1 \
| pg_restore -d postgres -U $PGUSER \
--create \
--no-tablespaces \
--clean \
--if-exists
}
_usage() {
cat <<EOF
Usage:
${BASH_SOURCE[0]} [ dump_all | dump | restore ] [database]
Commands 'dump' and 'restore' require user to provide the database name.
If database name is '-', database will be restored from standard input.
'dump_all' dumps all the databases in compressed pg_dump custom format.
EOF
exit 1
}
case $1 in
dump_all)
for db in $(_get_dbs); do
_pg_dump $db
done
;;
dump)
( shift; _pg_dump $1 ) || ( echo 'must provide database name'; exit 1 )
;;
restore)
( shift; _pg_restore $1 ) || ( echo 'must provide database name'; exit 1 )
;;
*)
_usage
;;
esac
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.