对于基于文本的 PG 恢复需要调整哪些参数?
每天晚上,我们使用以下方法转储和恢复 200 GB 数据库:
# Production, PG 9:
pg_dump DATNAME | some-irrelevant-pipe
# QA, PG 8.3:
some-irrelevant-pipe | psql -d DATNAME
我必须进行基于文本的备份,才能从 9 恢复到 8.3 的转储。
恢复过程非常痛苦且缓慢。我注意到我的日志充满了这些:
2011-05-22 08:02:47 CDT LOG: checkpoints are occurring too frequently (9 seconds apart)
2011-05-22 08:02:47 CDT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2011-05-22 08:02:54 CDT LOG: checkpoints are occurring too frequently (7 seconds apart)
2011-05-22 08:02:54 CDT HINT: Consider increasing the configuration parameter "checkpoint_segments".
我的问题是: checkpoint_segments
的设置是否可能是瓶颈?我还可以调整哪些其他参数来加快该过程?
该机器有 4 GB RAM。 postgresql.conf 中其他可能相关的设置是:
shared_buffers = 1000MB
work_mem = 200MB
maintenance_work_mem = 200MB
effective_cache_size = 2000MB
# fsync and checkpoint settings are default
Every night we dump and restore a 200 GB database using:
# Production, PG 9:
pg_dump DATNAME | some-irrelevant-pipe
# QA, PG 8.3:
some-irrelevant-pipe | psql -d DATNAME
I had to go for text-based backups in order to restore a dump from 9 on 8.3.
The restore is painfully and unreasonably slow. I noticed my log is full of these:
2011-05-22 08:02:47 CDT LOG: checkpoints are occurring too frequently (9 seconds apart)
2011-05-22 08:02:47 CDT HINT: Consider increasing the configuration parameter "checkpoint_segments".
2011-05-22 08:02:54 CDT LOG: checkpoints are occurring too frequently (7 seconds apart)
2011-05-22 08:02:54 CDT HINT: Consider increasing the configuration parameter "checkpoint_segments".
My question is: Is it possible that the setting of checkpoint_segments
is the bottleneck? What other parameters can I tweak to speed up the process?
That machine has 4 GB RAM. Other possibly relevant settings in postgresql.conf are:
shared_buffers = 1000MB
work_mem = 200MB
maintenance_work_mem = 200MB
effective_cache_size = 2000MB
# fsync and checkpoint settings are default
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您读过此吗?特别参见14.4.9
Did you read this ? See specially sec 14.4.9
为了恢复数据库,请更改:
关于 checkpoint_segments,将该值设置为磁盘控制器写入缓冲区的大小。 25 = 400MB
另外,请确保您的
psql
在单个事务中加载所有内容:For the purposes of restoring a database, change:
Regarding checkpoint_segments, set that value to the size of your disk controller's write buffer. 25 = 400MB
Also, make sure your
psql
is loading everything in a single transaction: