postgresql 如何在 postgres 中设置同步流复制？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10981701/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set up synchronous streaming replication in postgres?
提问by Julio Garcia
I am trying to set up synchronous replication with Postgres 9.1, but I cannot get it to work. I was able to configure streaming replication, but not synchronous. I hope I have not missed anything obvious. I have read carefully many sections of ch 17, 18, 14, 25, 26 and 29 in the admin guide.
我正在尝试使用 Postgres 9.1 设置同步复制，但我无法让它工作。我能够配置流复制，但不能同步。我希望我没有遗漏任何明显的东西。我已经仔细阅读了管理指南中第 17、18、14、25、26 和 29 章的许多部分。
I am running ubuntu 12.04 and my master postgresql.conf has these, among all the other standard settings:
我正在运行 ubuntu 12.04 并且我的主 postgresql.conf 具有这些，以及所有其他标准设置：
listen_addresses = '*' # what IP address(es) to listen on;
wal_level = archive # minimal, archive, or hot_standby
archive_mode = on # allows archiving to be done
archive_command = 'test ! -f /data/pgWalArchive/%f && cp %p /data/pgWalArchive/%f'
wal_keep_segments = 100 # in logfile segments, 16MB each; 0 disables ??? What should this be ????
max_wal_senders = 3 # max number of walsender processes
My pg_hba.conf has this, in addition to the standard stuff:
我的 pg_hba.conf 有这个，除了标准的东西：
host all all XX.6.35.0/24 md5
host replication postgres XX.6.35.0/24 md5
My master db has just one sequence, so it is tiny. I successfully created a backup of the master in the primary and restored it:
我的主数据库只有一个序列，所以它很小。我成功地在主服务器中创建了主服务器的备份并恢复了它：
sudo -u postgres pg_basebackup -D ~/backup -F tar -x -z -l ~/backup/base1 -v -h XX.6.35.51 -U postgres
I also copied the WAL archive files to the standby. My standby recovery.conf file has this:
我还将 WAL 存档文件复制到备用数据库。我的备用 recovery.conf 文件是这样的：
restore_command = '/usr/lib/postgresql/9.1/bin/pg_standby /data/pgWalArchive %f %p %r'
archive_cleanup_command = '/usr/lib/postgresql/9.1/bin/pg_archivecleanup /data/pgWalArchive %r'
standby_mode = on
primary_conninfo = 'host=XX.6.35.51 port=5432' # e.g. 'host=masterIpAddressOrName port=5432'
Both servers start up with no problems and the logs seem ok. My standby has this:
两台服务器启动时都没有问题，日志看起来也不错。我的待机有这个：
2012-06-08 10:23:51 MDT LOG: shutting down
2012-06-08 10:23:51 MDT LOG: database system is shut down
2012-06-08 10:23:53 MDT LOG: database system was shut down in recovery at 2012-06-08 10:23:51 MDT
2012-06-08 10:23:53 MDT LOG: entering standby mode
2012-06-08 10:23:53 MDT LOG: consistent recovery state reached at 0/1D000078
2012-06-08 10:23:53 MDT LOG: record with zero length at 0/1D000078
2012-06-08 10:23:53 MDT LOG: streaming replication successfully connected to primary
2012-06-08 10:23:53 MDT LOG: incomplete startup packet
2012-06-08 10:23:54 MDT FATAL: the database system is starting up
2012-06-08 10:23:54 MDT FATAL: the database system is starting up
2012-06-08 10:23:55 MDT FATAL: the database system is starting up
2012-06-08 10:23:55 MDT FATAL: the database system is starting up
2012-06-08 10:23:56 MDT FATAL: the database system is starting up
2012-06-08 10:23:56 MDT FATAL: the database system is starting up
2012-06-08 10:23:57 MDT FATAL: the database system is starting up
2012-06-08 10:23:57 MDT FATAL: the database system is starting up
2012-06-08 10:23:58 MDT FATAL: the database system is starting up
2012-06-08 10:23:58 MDT FATAL: the database system is starting up
2012-06-08 10:23:59 MDT FATAL: the database system is starting up
2012-06-08 10:23:59 MDT LOG: incomplete startup packet
2012-06-08 10:24:40 MDT LOG: redo starts at 0/1D000078
The problem is that when I issue statements against the master, they hang forever. Am I missing something?
问题是当我对主人发表声明时，他们永远挂起。我错过了什么吗？
回答by Richard T
I was the first person to put Streaming Replication (AKA "Binary Replication") into production when 9.0 came out two years ago, but skipped over 9.1 because Postgres' replication feature was in a state of flux. Now, as of about the second week of September or thereabouts, 9.2 is out - you WANT to know about it!
两年前 9.0 出现时，我是第一个将流式复制（又名“二进制复制”）投入生产的人，但跳过了 9.1，因为 Postgres 的复制功能处于不断变化的状态。现在，大约在 9 月的第二周左右，9.2 已经发布——你想知道它！
With 9.2, replication has been simplified and GREATLY improved!
在 9.2 中，复制得到了简化和极大的改进！
Now you can have replication cascade! Previously, you had to have the master serve all the slaves directly. Now you can off-load the master by streaming to just ONE slave, and then have that slave stream to as many as you like! This lets you set up your first-level slave as a potential hot-stand-by system that will take over in the event your master fails.
现在您可以进行复制级联了！以前，您必须让 master 直接为所有 slave 服务。现在，您可以通过将主流传输到一个从流来卸载主流，然后将该从流传输到任意数量的从流！这使您可以将第一级从站设置为潜在的热备用系统，以便在您的主站出现故障时接管。
The whole strategy for configuring this has been revamped and I found your question because I myself was looking for a quick-setup-guide type of thing because I already know all the basics. However, you can get started with the new 9.2 stuff here:
配置它的整个策略已经修改，我发现了你的问题，因为我自己正在寻找一种快速设置指南类型的东西，因为我已经知道所有的基础知识。但是，您可以在此处开始使用新的 9.2 内容：
Postgres 9.2 High Availability, Load Balancing, and Replication
Meanwhile, at least one vendor has come out with something to help mere mortals make good use of this: Science Tools announced "Dual Mode" has been added to their PolyglotSQL product. PolyglotSQL lets an application operate against most any SQL database and ignore dialect differences. Similarly, the "dual mode" feature lets you have one read-only connection and another connection for writes, without having to re-write your application (!!), so you can take advantage of the Postgres Synchronous Replication, offloading the master of all writes, and putting that load on whatever readers you have configured.
与此同时，至少有一家供应商推出了一些东西来帮助普通人充分利用这一点：Science Tools 宣布“双模式”已添加到他们的 PolyglotSQL 产品中。PolyglotSQL 允许应用程序对大多数 SQL 数据库进行操作并忽略方言差异。同样，“双模式”功能让您拥有一个只读连接和另一个写入连接，而无需重新编写您的应用程序（！！），因此您可以利用 Postgres 同步复制，卸载 master所有写入，并将负载置于您配置的任何读取器上。
I highly recommend you move to Postgres v 9.2.
我强烈建议您迁移到 Postgres v 9.2。
回答by Ajeet Khan
回答by Richard Huxton
I don't see anything about synchronous_standby_names - you need to tell it which servers it the master will wait on.
我没有看到任何关于 synchronous_standby_names 的信息 - 您需要告诉它主服务器将等待哪些服务器。
http://www.postgresql.org/docs/9.1/static/warm-standby.html#SYNCHRONOUS-REPLICATION
http://www.postgresql.org/docs/9.1/static/warm-standby.html#SYNCHRONOUS-REPLICATION
Oh - don't forget to tweak your wal_levelsetting if you want to run queries on the slave server.
哦 -wal_level如果您想在从服务器上运行查询，请不要忘记调整您的设置。
回答by depthfirstdesigner
I had similar issues, -- for Postgres 9.3, I had to add hot_standby = onto my Standby database server's postgresql.conffile before my standby server could accept read queries/clients.
我遇到了类似的问题——对于 Postgres 9.3，在我的备用服务器可以接受读取查询/客户端之前，我必须添加hot_standby = on到我的备用数据库服务器的postgresql.conf文件中。
