Обсуждение: How to do failover in pglogical replication?
Hi, I am currently exploring pglogical replication for my db servers. I would like to know how can I automatically failover from Provider Node to Subscriber Node, if the Provider node goes down for some reasons. How can I redirect all the traffic to SubscriberNode automatically ? In the normal replication, we use recovery_file and triggers to get this job done. Do we have any similar alternative for pglogical replications as well? Regards, Roshan -- View this message in context: http://postgresql.nabble.com/How-to-do-failover-in-pglogical-replication-tp5916769.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On 17 August 2016 at 18:21, roshan_myrepublic <roshan@myrepublic.net> wrote:
Hi,
I am currently exploring pglogical replication for my db servers. I would
like to know how can I automatically failover from Provider Node to
Subscriber Node, if the Provider node goes down for some reasons. How can I
redirect all the traffic to SubscriberNode automatically ? In the normal
replication, we use recovery_file and triggers to get this job done. Do we
have any similar alternative for pglogical replications as well?
There is not, as yet, any integegration into tooling like repmgr. You'll want some fairly simple scripts to manage failover, likely:
* Update pgbouncer / haproxy / whatever to redirect connections
* Drop the subscription on the replica
* STONITH to make sure the master is really down
* Clone and start a new master
There's some work on automating this through repmgr, but at this time pglogical isn't really focused on failover deployments as its main use case. The limitations in PostgreSQL's logical decoding and replication when it comes to handling of big xacts, sequences, etc mean it's still better suited to data movement/integration etc than HA.
Hi Craig,
I am trying to set up pglogical replication. I have a table which has around 4 rows in the provider server.
=========
employee_id | visitor_email | vistor_id | date | message
-------------+-----------------------+-----------+---------------------+--------------------------
1 | [hidden email] | 1 | 2016-08-24 00:00:00 | This is the first test.
2 | [hidden email] | 2 | 2016-08-24 00:00:00 | This is the second test.
3 | [hidden email] | 3 | 2016-08-24 00:00:00 | This is the third test.
4 | [hidden email] | 4 | 2016-08-24 00:00:00 | This is the fourth test.
===============================
After creating the above mentioned table. I have created a replication set in the provider . Then I configured the subscriber node and the subscription. Ideally, I should see the 4 rows from the provider table in my subscriber, but I am only seeing the table structure in subscriber, not the data. If I add a new row in the provider table as follows
INSERT INTO employees (employee_id, visitor_email, date, message) VALUES (4, '[hidden email]', current_date, 'This is the fourth test.');
Now, I am able to see the last added row in my subscriber table. All the other 4 rows which were added in the beginning are still missing. What am I doing wrong here?
Does it mean that only the data which was created after the creation of replication_sets will be considered? How to add the whole content of a table to a replication set.?
Any help would be much appreciated.?
Regards,
Muhammed Roshan
On Thu, Aug 18, 2016 at 1:01 PM, Craig Ringer-3 [via PostgreSQL] <[hidden email]> wrote:
On 17 August 2016 at 18:21, roshan_myrepublic <[hidden email]> wrote:Hi,
I am currently exploring pglogical replication for my db servers. I would
like to know how can I automatically failover from Provider Node to
Subscriber Node, if the Provider node goes down for some reasons. How can I
redirect all the traffic to SubscriberNode automatically ? In the normal
replication, we use recovery_file and triggers to get this job done. Do we
have any similar alternative for pglogical replications as well?There is not, as yet, any integegration into tooling like repmgr. You'll want some fairly simple scripts to manage failover, likely:* Update pgbouncer / haproxy / whatever to redirect connections* Drop the subscription on the replica* STONITH to make sure the master is really down* Clone and start a new masterThere's some work on automating this through repmgr, but at this time pglogical isn't really focused on failover deployments as its main use case. The limitations in PostgreSQL's logical decoding and replication when it comes to handling of big xacts, sequences, etc mean it's still better suited to data movement/integration etc than HA.--If you reply to this email, your message will be added to the discussion below:http://postgresql.nabble.com/How-to-do-failover-in- pglogical-replication- tp5916769p5916916.html
View this message in context: Re: How to do failover in pglogical replication?
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On 24 August 2016 at 19:42, roshan_myrepublic <roshan@myrepublic.net> wrote: > Now, I am able to see the last added row in my subscriber table. All the > other 4 rows which were added in the beginning are still missing. What am I > doing wrong here? Hi. This isn't really on topic for the pgsql-hackers mailing list. We're adding a pglogical mailing list now that it's clear it's not going into core, but in the mean time feel free to raise this on the github tracker for the project. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
Hi Craig,
Could you please let me know how to access the github tracker?
Regards,
Muhammed Roshan
Muhammed Roshan
On Thu, Aug 25, 2016 at 9:25 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
On 24 August 2016 at 19:42, roshan_myrepublic <roshan@myrepublic.net> wrote:
> Now, I am able to see the last added row in my subscriber table. All the
> other 4 rows which were added in the beginning are still missing. What am I
> doing wrong here?
Hi. This isn't really on topic for the pgsql-hackers mailing list.
We're adding a pglogical mailing list now that it's clear it's not
going into core, but in the mean time feel free to raise this on the
github tracker for the project.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Github tracker: https://github.com/2ndQuadrant/pglogical/issues
You can also send an email to pglogical-list@2ndquadrant.com.
- Umair
On Thu, Aug 25, 2016 at 7:01 AM, Muhammed Roshan <roshan@myrepublic.net> wrote:
Hi Craig,Could you please let me know how to access the github tracker?Regards,
Muhammed RoshanOn Thu, Aug 25, 2016 at 9:25 AM, Craig Ringer <craig@2ndquadrant.com> wrote:On 24 August 2016 at 19:42, roshan_myrepublic <roshan@myrepublic.net> wrote:
> Now, I am able to see the last added row in my subscriber table. All the
> other 4 rows which were added in the beginning are still missing. What am I
> doing wrong here?
Hi. This isn't really on topic for the pgsql-hackers mailing list.
We're adding a pglogical mailing list now that it's clear it's not
going into core, but in the mean time feel free to raise this on the
github tracker for the project.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services