问题原文
原帖链接 · kapso · CC BY-SA 3.0
Is it possible or advisable to stream/pipe pg_dump output to S3?
We are dumping large datasets to our instance and the database size is big. So trying to optimize for local disk space (avoid temp space for dump) and create the backup straight on S3.
We have a PostgreSQL v9.6.3 on Ubuntu 16.04.
社区高分解答
原帖链接 · JaakL · CC BY-SA 3.0
pg_dump streaming directly to S3 seems to work fine. I have 350GB database and do not want to create temp additional drives. You need to make sure that the multipart chunk size is big enough, otherwise I ran into 'too many segments' issue. With AWS CLI the commands:
aws configure set default.s3.multipart_chunksize 200MB
time sudo -u postgres pg_dump -Z 9 -v DB_NAME |aws s3 cp - s3://BUCKET/DB_NAME.dump.gz
With my db it took about 8 hours and result was 130GB file in S3. Now restoring has to be done with psql, as pg_restore does not like plain sql dumps what the command above creates. I could not use custom format there, as this wants to create directories which cannot (probably?) be piped away.
Finally restoring same way, without intermediate file saving. Note that I had to uncompress the data before psql using zcat:
wget -O - 'https://S3-URL/BUCKET/DB_NAME.dump.gz' |zcat |sudo -u postgres psql DB_NAME
The restoring seems to take about same time (~8 hours) as dumping, probably depends where and how big is your server (AWS or somewhere else, mine is outside AWS).
摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。
