Dump PostgreSQL to S3 and Restore from S3 to Local

#! /bin/sh -x
pg_dump -Fc --host= postgres://role:password@host:port/dbname -c > $data
aws s3 cp $data s3://host/path/filename --acl public-read
rm $data

aws s3 cp s3://host/path/filename /tmp/
pg_restore -c -C --no-privileges --no-owner /tmp/filename
OR pg_restore -c -h localhost -U username -p 5432 --no-privileges --no-owner /tmp/filename
########################

pg_restore -C creates the same name database as one in an archive file ignoring with -d option.
according to https://www.postgresql.jp/document/9.3/html/app-pgrestore.html

-c, --clean option drop a table in your destination database if only it has the same name as one in an archive file. It could print warning when trying to drop non-existing table.

For option meaning,
-no-privileges avoids an error on "role "role" does not exist"
-no-owner avoids an error on "ALTER OWNER"

When you dump with "pg_dump" or "pg_dumpall", you restore with "psql".
When you dump with "pg_dump -Fc", you restore with "pg_restore".


pg_dump
https://www.postgresql.org/docs/9.3/static/app-pgdump.html
pg_restore
https://www.postgresql.org/docs/9.2/static/app-pgrestore.html

コメント

人気の投稿