Re: [GENERAL] Understanding streaming replication
Philippe Amelant wrote: So i was thinking it was just a reconnect to the sender (and I can see the standby trying to reconnect in the log) Hmmm. I think I was too quick when I said no. If you ship the WAL archives including the history file to the standby, then the standby should be able to recover across the timeline change from the archives (if you have recovery_target_timeline set to latest in recovery.conf) and then reestablish streaming replication. I never tried that though. (The patch I quoted above would allow the timeline change via streaming replication.) You're right I added recovery_target_timeline='latest' in the recovery.conf then I promoted the standby. The replication on the second standby stopped with a message complaining about timeline. Then I copied the archived wal from the new master to the (stopped) standby (in pg_xlog) The standby restarted on the new timeline and the datas seem to be ok. I also tried to just copy the last 00X.history in pg_xlog and it work too. I suppose this could fail if max_wal_keep_segment is too low Thanks you very much for your help. Could you just point me where you found this information in the doc ? I didn't consult the documentation, I used what I know of how WAL recovery and streaming replication work... However, I find the following in http://www.postgresql.org/docs/current/static/recovery-target-settings.html recovery_target_timeline [...] Setting this to latest recovers to the latest timeline found in the archive, which is useful in a standby server. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding streaming replication
On 11/13/2012 02:40 AM, Albe Laurenz wrote: The only thing I have seen is RedHat's Cluster Suite, which is commercial. I would recommend to have at least three nodes though, because the two node cluster we had was subject to spurious failovers on short quorum disk hiccups. There's also the Pacemaker + Corosync stack. There are plenty of tutorials on how it works over at Cluster Labs: http://www.clusterlabs.org/ It's totally free and we've been using it for a couple years now to replace Lifekeeper, a commercial cluster offering from SIOS. So there's two more right there. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding streaming replication
Hi, On Tue, 2012-11-13 at 09:40 +0100, Albe Laurenz wrote: The only thing I have seen is RedHat's Cluster Suite, which is commercial. Depends. It is open source, and all components are also available in CentOS and Scientific Linux, and there are companies out there who support clusters on these two. Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Understanding streaming replication
Philippe Amelant wrote: I'm setting up a 3 nodes cluster and after some tests I just discover that the cascading slave does not recover. Right, switching timeline over streaming replication is not supported yet. There's a patch by Heikki in the pipeline for this, so it will probably work in 9.3. As far as I can see in the 9.2 documentation it should work after an automatic reconnect to the new master. Where did you see that? Is there any chance to get this fixed in 9.2.x ? No. It is a new feature, and those aren't backpatched. In case of disaster on master and on standby, can I just restart the cascading slave after removing recovery.conf ? The correct way it to pg_ctl promote. Would it be better to copy all archives log from the master in pg_xlog on the third node and then restart it ? What is the best way to get back this node with minimal loss? You can copy the archives, then wait until replication has caught up, then promote the standby. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding streaming replication
Pawel Veselov wrote: QUESTION: If multiple pgpools are running, and if there are no network problems, and configuration files are identical, is there any guarantee that the same stand-by node will be selected for promotion? Concern here is that with configuration of (M-SB0-SB1) one pgpool decides to promote SB0 and another - SB1, causing both of them to enter master mode, and splitting the cluster. It does look that pgpool will always select next alive node for promotion, but I couldn't find a definitive statement on that. I don't know about pgpool and its abilities to handle cluster failover, but I wouldn't go this way at all. Even if the answer were that in the circumstances you describe things would work, you can depend on it that things will go wrong in ways different from what you expect, e.g. a broken network card. The consequences would be worse than I'd like to imagine. I would imagine this situation will happen in any case, I don't logically see how it's avoidable. If you only have one agent that has power to promote a node to be a new master, you have SPF. If you have multiple agents that can do the promotion, there is always a risk that they fall out of sync. Cluster software usually has the cluster nodes communicate regularly, and if anything fails, the nodes try to form groups where everybody can reach everybody else. The group that is bigger than half of the original nodes wins, turns off the others and takes over their services. If you want reliable automatic failover, consider cluster software. Anything you could please recommend? The only thing I have seen is RedHat's Cluster Suite, which is commercial. I would recommend to have at least three nodes though, because the two node cluster we had was subject to spurious failovers on short quorum disk hiccups. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding streaming replication
Pawel Veselov wrote: From your later comments, it's also apparent that these archived WALs will be useless after failover (for the purpose of recovery), so there is no reason to send them to all the nodes after all. I obviously lost it here. The archives do need to be synchronized, for the purpose of recovering slaves. If a slave dies, and I want to recover it, it may need the archived WALs, and for this, the archives should be available on the node. So, rsync (or something like that) is necessary. But it's a bad idea to run the rsync from the archive command itself. Right, that's exactly what I tried to say. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding streaming replication
Hello, Thank for all this informations Le 13/11/2012 09:31, Albe Laurenz a écrit : Philippe Amelant wrote: I'm setting up a 3 nodes cluster and after some tests I just discover that the cascading slave does not recover. Right, switching timeline over streaming replication is not supported yet. There's a patch by Heikki in the pipeline for this, so it will probably work in 9.3. So if I understand it, I need to rebuild the cascading slave if I promote the first standby. Is there a way to follow the new master without rebuild ? As far as I can see in the 9.2 documentation it should work after an automatic reconnect to the new master. Where did you see that? I found this http://www.postgresql.org/docs/9.2/static/warm-standby.html 25.2.6. Cascading Replication Promoting a cascading standby terminates the immediate downstream replication connections which it serves. This is because the timeline becomes different between standbys, and they can no longer continue replication. The affected standby(s) may reconnect to reestablish streaming replication. So i was thinking it was just a reconnect to the sender (and I can see the standby trying to reconnect in the log) Is there any chance to get this fixed in 9.2.x ? No. It is a new feature, and those aren't backpatched. In case of disaster on master and on standby, can I just restart the cascading slave after removing recovery.conf ? The correct way it to pg_ctl promote. Would it be better to copy all archives log from the master in pg_xlog on the third node and then restart it ? What is the best way to get back this node with minimal loss? You can copy the archives, then wait until replication has caught up, then promote the standby. Ok thanks, I will work on this. Regards -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding streaming replication
Philippe Amelant wrote: I'm setting up a 3 nodes cluster and after some tests I just discover that the cascading slave does not recover. Right, switching timeline over streaming replication is not supported yet. There's a patch by Heikki in the pipeline for this, so it will probably work in 9.3. So if I understand it, I need to rebuild the cascading slave if I promote the first standby. Is there a way to follow the new master without rebuild ? As far as I can see in the 9.2 documentation it should work after an automatic reconnect to the new master. Where did you see that? I found this http://www.postgresql.org/docs/9.2/static/warm-standby.html 25.2.6. Cascading Replication Promoting a cascading standby terminates the immediate downstream replication connections which it serves. This is because the timeline becomes different between standbys, and they can no longer continue replication. The affected standby(s) may reconnect to reestablish streaming replication. So i was thinking it was just a reconnect to the sender (and I can see the standby trying to reconnect in the log) Hmmm. I think I was too quick when I said no. If you ship the WAL archives including the history file to the standby, then the standby should be able to recover across the timeline change from the archives (if you have recovery_target_timeline set to latest in recovery.conf) and then reestablish streaming replication. I never tried that though. (The patch I quoted above would allow the timeline change via streaming replication.) Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding streaming replication
Le 13/11/2012 14:57, Albe Laurenz a écrit : Philippe Amelant wrote: So i was thinking it was just a reconnect to the sender (and I can see the standby trying to reconnect in the log) Hmmm. I think I was too quick when I said no. If you ship the WAL archives including the history file to the standby, then the standby should be able to recover across the timeline change from the archives (if you have recovery_target_timeline set to latest in recovery.conf) and then reestablish streaming replication. I never tried that though. (The patch I quoted above would allow the timeline change via streaming replication.) Yours, Laurenz Albe You're right I added recovery_target_timeline='latest' in the recovery.conf then I promoted the standby. The replication on the second standby stopped with a message complaining about timeline. Then I copied the archived wal from the new master to the (stopped) standby (in pg_xlog) The standby restarted on the new timeline and the datas seem to be ok. I also tried to just copy the last 00X.history in pg_xlog and it work too. I suppose this could fail if max_wal_keep_segment is too low Thanks you very much for your help. Could you just point me where you found this information in the doc ? Regards -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding streaming replication
I'll try to answer the questions I can. Pawel Veselov wrote: I've been struggling with understanding all the necessary pieces for streaming replication. So I put down the pieces as I did understand them, and would appreciate if you guys could point out any of the stuff I understood or have done wrong. The set up is pgpool + streaming replication + hot stand by. No load balancing, stand-by nodes will not receive any application queries (I don't have that big of a query load, and I don't want to risk inconsistent reads). There are no shared file systems, but there is a way to rsync/scp files between nodes. Fail-over is automatic, and should kick in within reasonably small period after master failure. 1. Archiving. Should be turned on on all the nodes. The archive command should copy the archive file to the local archive directory, and rsync archive directory between all the nodes. My understanding is that archiving is necessary if a stand-by node ever missed enough WAL updates to need an old enough WAL that might have been removed from pg_xlog. You don't give details about how the rsync is triggered, but I'd advise against having rsync as part of archive_command. First, it is slow and if there is a lot of activity, the archiver will not be able to keep up. Second, if rsync fails, the WAL file will not be considered archived. Both these things will keep the WAL files from being deleted from pg_xlog. I'd schedule rsync as a cron job or similar. QUESTION: After the failover, the new master will start archiving its WAL files. These archived WALs will not collide in any way with the archived WALs generated by previous master(s)? They will not, because the standby starts a new time line when it is promoted to primary, which will result in new WAL file names. QUESTION: What is a good policy for archive clean up? From the perspective to only remove archive files that are guaranteed to never be required by any nodes. You cannot tell from the primary's side. Since you also need the archives to restore an online backup, I'd keep them a long as your backup policy dictates. I hope you don't rely on standby databases for backup (just imagine an accidental DROP TABLE that gets propagated to all standbys withing seconds). 2. Failover. On master failure, pgpool will automatically select a new master, and degenerate all other nodes. The cluster is now in the emergency state and requires manual intervention for reconfiguration and recovery. pgpool executes a script to promote a node, that script will create a trigger file on a newly selected master node, and postgres will exist stand-by mode. QUESTION: If multiple pgpools are running, and if there are no network problems, and configuration files are identical, is there any guarantee that the same stand-by node will be selected for promotion? Concern here is that with configuration of (M-SB0-SB1) one pgpool decides to promote SB0 and another - SB1, causing both of them to enter master mode, and splitting the cluster. It does look that pgpool will always select next alive node for promotion, but I couldn't find a definitive statement on that. I don't know about pgpool and its abilities to handle cluster failover, but I wouldn't go this way at all. Even if the answer were that in the circumstances you describe things would work, you can depend on it that things will go wrong in ways different from what you expect, e.g. a broken network card. The consequences would be worse than I'd like to imagine. If you want reliable automatic failover, consider cluster software. 3. Recovery. That part is a bit confusing. The majority of the documentation says that in this case, the node should be re-loaded from the base backup, obtained from the master. I'm not sure why this is necessary, if there are enough archived WALs. Because of the new time line; streaming replication cannot (yet) recover across a time line change. QUESTION: Is there any metric to understand whether hauling base will be slower/faster than replaying missed WALs? Anyway, pgpool only has one recovery mechanism, and it does invoke a base restore from whatever current master is. PROBLEM: This I see as a problem. The only way that I see to re-attach a node to the pgpool, short of restarting it, is to call pcp_recovery_node. This will make the master take a base back up, push it to the stand-by that needs recovery, and re-start the stand-by node. I am not sure if there is a good way to check if that node has already been recovered. That because if there are more than 2 pgpools, they both will attempt to recover the same stand-by, and this will probably get ugly. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding streaming replication
Hello all, I read this thread with interest but I still have some questions about cascading replication as you describe it. Le 12/11/2012 10:36, Albe Laurenz a écrit : I'll try to answer the questions I can. 3. Recovery. That part is a bit confusing. The majority of the documentation says that in this case, the node should be re-loaded from the base backup, obtained from the master. I'm not sure why this is necessary, if there are enough archived WALs. Because of the new time line; streaming replication cannot (yet) recover across a time line change. I'm setting up a 3 nodes cluster and after some tests I just discover that the cascading slave does not recover. As far as I can see in the 9.2 documentation it should work after an automatic reconnect to the new master. Is there any chance to get this fixed in 9.2.x ? In case of disaster on master and on standby, can I just restart the cascading slave after removing recovery.conf ? Would it be better to copy all archives log from the master in pg_xlog on the third node and then restart it ? What is the best way to get back this node with minimal loss? Thanks for your advice Regards -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Understanding streaming replication
On Mon, Nov 12, 2012 at 1:36 AM, Albe Laurenz laurenz.a...@wien.gv.atwrote: I'll try to answer the questions I can. Thank you! Pawel Veselov wrote: I've been struggling with understanding all the necessary pieces for streaming replication. So I put down the pieces as I did understand them, and would appreciate if you guys could point out any of the stuff I understood or have done wrong. The set up is pgpool + streaming replication + hot stand by. No load balancing, stand-by nodes will not receive any application queries (I don't have that big of a query load, and I don't want to risk inconsistent reads). There are no shared file systems, but there is a way to rsync/scp files between nodes. Fail-over is automatic, and should kick in within reasonably small period after master failure. 1. Archiving. Should be turned on on all the nodes. The archive command should copy the archive file to the local archive directory, and rsync archive directory between all the nodes. My understanding is that archiving is necessary if a stand-by node ever missed enough WAL updates to need an old enough WAL that might have been removed from pg_xlog. You don't give details about how the rsync is triggered, but I'd advise against having rsync as part of archive_command. First, it is slow and if there is a lot of activity, the archiver will not be able to keep up. Second, if rsync fails, the WAL file will not be considered archived. Both these things will keep the WAL files from being deleted from pg_xlog. I'd schedule rsync as a cron job or similar. From your later comments, it's also apparent that these archived WALs will be useless after failover (for the purpose of recovery), so there is no reason to send them to all the nodes after all. QUESTION: After the failover, the new master will start archiving its WAL files. These archived WALs will not collide in any way with the archived WALs generated by previous master(s)? They will not, because the standby starts a new time line when it is promoted to primary, which will result in new WAL file names. QUESTION: What is a good policy for archive clean up? From the perspective to only remove archive files that are guaranteed to never be required by any nodes. You cannot tell from the primary's side. Since you also need the archives to restore an online backup, I'd keep them a long as your backup policy dictates. I hope you don't rely on standby databases for backup (just imagine an accidental DROP TABLE that gets propagated to all standbys withing seconds). I don't relay on stand-by's for back up. But that timeline establishment business is a key piece that I didn't realize. 2. Failover. On master failure, pgpool will automatically select a new master, and degenerate all other nodes. The cluster is now in the emergency state and requires manual intervention for reconfiguration and recovery. pgpool executes a script to promote a node, that script will create a trigger file on a newly selected master node, and postgres will exist stand-by mode. QUESTION: If multiple pgpools are running, and if there are no network problems, and configuration files are identical, is there any guarantee that the same stand-by node will be selected for promotion? Concern here is that with configuration of (M-SB0-SB1) one pgpool decides to promote SB0 and another - SB1, causing both of them to enter master mode, and splitting the cluster. It does look that pgpool will always select next alive node for promotion, but I couldn't find a definitive statement on that. I don't know about pgpool and its abilities to handle cluster failover, but I wouldn't go this way at all. Even if the answer were that in the circumstances you describe things would work, you can depend on it that things will go wrong in ways different from what you expect, e.g. a broken network card. The consequences would be worse than I'd like to imagine. I would imagine this situation will happen in any case, I don't logically see how it's avoidable. If you only have one agent that has power to promote a node to be a new master, you have SPF. If you have multiple agents that can do the promotion, there is always a risk that they fall out of sync. If you want reliable automatic failover, consider cluster software. Anything you could please recommend? [skipped] Yours, Laurenz Albe
Re: [GENERAL] Understanding streaming replication
On Mon, Nov 12, 2012 at 10:11 AM, Pawel Veselov pawel.vese...@gmail.comwrote: On Mon, Nov 12, 2012 at 1:36 AM, Albe Laurenz laurenz.a...@wien.gv.atwrote: I'll try to answer the questions I can. Thank you! Pawel Veselov wrote: I've been struggling with understanding all the necessary pieces for streaming replication. So I put down the pieces as I did understand them, and would appreciate if you guys could point out any of the stuff I understood or have done wrong. The set up is pgpool + streaming replication + hot stand by. No load balancing, stand-by nodes will not receive any application queries (I don't have that big of a query load, and I don't want to risk inconsistent reads). There are no shared file systems, but there is a way to rsync/scp files between nodes. Fail-over is automatic, and should kick in within reasonably small period after master failure. 1. Archiving. Should be turned on on all the nodes. The archive command should copy the archive file to the local archive directory, and rsync archive directory between all the nodes. My understanding is that archiving is necessary if a stand-by node ever missed enough WAL updates to need an old enough WAL that might have been removed from pg_xlog. You don't give details about how the rsync is triggered, but I'd advise against having rsync as part of archive_command. First, it is slow and if there is a lot of activity, the archiver will not be able to keep up. Second, if rsync fails, the WAL file will not be considered archived. Both these things will keep the WAL files from being deleted from pg_xlog. I'd schedule rsync as a cron job or similar. From your later comments, it's also apparent that these archived WALs will be useless after failover (for the purpose of recovery), so there is no reason to send them to all the nodes after all. I obviously lost it here. The archives do need to be synchronized, for the purpose of recovering slaves. If a slave dies, and I want to recover it, it may need the archived WALs, and for this, the archives should be available on the node. So, rsync (or something like that) is necessary. But it's a bad idea to run the rsync from the archive command itself.
[GENERAL] Understanding streaming replication
Hi. I've been struggling with understanding all the necessary pieces for streaming replication. So I put down the pieces as I did understand them, and would appreciate if you guys could point out any of the stuff I understood or have done wrong. The set up is pgpool + streaming replication + hot stand by. No load balancing, stand-by nodes will not receive any application queries (I don't have that big of a query load, and I don't want to risk inconsistent reads). There are no shared file systems, but there is a way to rsync/scp files between nodes. Fail-over is automatic, and should kick in within reasonably small period after master failure. 1. Archiving. Should be turned on on all the nodes. The archive command should copy the archive file to the local archive directory, and rsync archive directory between all the nodes. My understanding is that archiving is necessary if a stand-by node ever missed enough WAL updates to need an old enough WAL that might have been removed from pg_xlog. QUESTION: After the failover, the new master will start archiving its WAL files. These archived WALs will not collide in any way with the archived WALs generated by previous master(s)? QUESTION: What is a good policy for archive clean up? From the perspective to only remove archive files that are guaranteed to never be required by any nodes. 2. Failover. On master failure, pgpool will automatically select a new master, and degenerate all other nodes. The cluster is now in the emergency state and requires manual intervention for reconfiguration and recovery. pgpool executes a script to promote a node, that script will create a trigger file on a newly selected master node, and postgres will exist stand-by mode. QUESTION: If multiple pgpools are running, and if there are no network problems, and configuration files are identical, is there any guarantee that the same stand-by node will be selected for promotion? Concern here is that with configuration of (M-SB0-SB1) one pgpool decides to promote SB0 and another - SB1, causing both of them to enter master mode, and splitting the cluster. It does look that pgpool will always select next alive node for promotion, but I couldn't find a definitive statement on that. 3. Recovery. That part is a bit confusing. The majority of the documentation says that in this case, the node should be re-loaded from the base backup, obtained from the master. I'm not sure why this is necessary, if there are enough archived WALs. QUESTION: Is there any metric to understand whether hauling base will be slower/faster than replaying missed WALs? Anyway, pgpool only has one recovery mechanism, and it does invoke a base restore from whatever current master is. PROBLEM: This I see as a problem. The only way that I see to re-attach a node to the pgpool, short of restarting it, is to call pcp_recovery_node. This will make the master take a base back up, push it to the stand-by that needs recovery, and re-start the stand-by node. I am not sure if there is a good way to check if that node has already been recovered. That because if there are more than 2 pgpools, they both will attempt to recover the same stand-by, and this will probably get ugly. Thank you, Pawel.
