对于基于文本的 PG 恢复需要调整哪些参数?

发布于 2024-11-10 12:04:46 字数 960 浏览 7 评论 0原文

每天晚上,我们使用以下方法转储和恢复 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

凉月流沐 2024-11-17 12:04:46

您读过吗?特别参见14.4.9

Did you read this ? See specially sec 14.4.9

尤怨 2024-11-17 12:04:46

为了恢复数据库,请更改:

# I don't think PostgreSQL 8.3 supports synchronous_commit
synchronous_commit = off
# only change fsync = off if your version of PG is too old to support synchronous_commit. If you do support synchronous_commit, don't ever change fsync to anything but on. Ever.
#fsync = off
checkpoint_segments = 25

关于 checkpoint_segments,将该值设置为磁盘控制器写入缓冲区的大小。 25 = 400MB

另外,请确保您的 psql 在单个事务中加载所有内容:

some-irrelevant-pipe | psql -1 -d DATNAME

For the purposes of restoring a database, change:

# I don't think PostgreSQL 8.3 supports synchronous_commit
synchronous_commit = off
# only change fsync = off if your version of PG is too old to support synchronous_commit. If you do support synchronous_commit, don't ever change fsync to anything but on. Ever.
#fsync = off
checkpoint_segments = 25

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:

some-irrelevant-pipe | psql -1 -d DATNAME
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文