大网数据广告活动
服务器运维问答

Streaming PostgreSQL pg_dump to S3

· 52CE 我爱测

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 ba…

问题原文

原帖链接 · 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).

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2017-12-04)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。