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

downloading a big database (postgres) to a local copy

· 52CE 我爱测

I have a pretty big postgres DB on Amazon RDS (about 9GB when zipped), and sometimes we need to copy it and do some tests on it on our local machines. Doing a DB dump (pg_dump) and downloading it is simply too slow and honestly b…

问题原文

原帖链接 · Ronen Ness · CC BY-SA 3.0

I have a pretty big postgres DB on Amazon RDS (about 9GB when zipped), and sometimes we need to copy it and do some tests on it on our local machines.

Doing a DB dump (pg_dump) and downloading it is simply too slow and honestly been just getting stuck last few times we tried.

Is there a simple way to get parts of the DB in a smart way? for example, get only changes from last 10 days, and then we can merge them with the local DB we have, or maybe getting DB in chunks etc?

I'm sure I'm not the first one with that need, but couldn't find a decent method or tutorial to explain the best ways to do it.

Thanks!

已采纳解答

原帖链接 · Tometzky · CC BY-SA 3.0

9GB compressed dump isn't really that large. You just need to make do it right:

  • dump from EC2 instance in the same AWS account and region that RDS database is running — not over the internet;
  • use directory dump format (--format=directory or -Fd) — it's automatically compressed;
  • use parallel dump (for example --jobs=16 or -j16) and parallel restore;
  • use sslmode=disable in connection string or env PGSSLMODE=disable pg_dump … to disable SSL — some versions of AWS RDS have 64GB limit of SSL data over single connection;
  • remember that RDS database restored from snapshot is terribly slow until all the data is read at least once - first read is over 10 times slower than normal, as the data is pulled from archive storage (S3 maybe) on demand — don't do this dump from a db restored recently from snapshot;
  • move it out of AWS only after successfull dump in compressed state.

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

资料来源与适用范围

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

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

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