Thread: Asynchronous streaming replication in postgresql
Hi,
I am new to postgreSql and evaluating the streaming replication for my use case. My use case is:
1. Need to replicate data from primary database (master) to secondary database (slave) asynchronously.
2. If master goes down, the slave should automatically be promoted to master.
3. Later, when the original primary server (original master) is brought up again, it should obtain back its master role and the new master should assume the slave again as it was with original setup.
For #1, the streaming replication of postgresql is good enough.
For #2, we need to create the trigger file in slave. This can be done manually. How can we do this automatically?
For #3, this seems to be quite complicated. Is this possible using streaming replication and if yes then how can this be achieved?
Your help is greatly appreciated in this regard.
Thank you very much..
Le 12 mars 2015 06:57, "Deole, Pushkar (Pushkar)" <pdeole@avaya.com> a écrit :
>
> Hi,
>
>
>
> I am new to postgreSql and evaluating the streaming replication for my use case. My use case is:
>
>
>
> 1. Need to replicate data from primary database (master) to secondary database (slave) asynchronously.
>
> 2. If master goes down, the slave should automatically be promoted to master.
>
> 3. Later, when the original primary server (original master) is brought up again, it should obtain back its master role and the new master should assume the slave again as it was with original setup.
>
>
>
> For #1, the streaming replication of postgresql is good enough.
>
> For #2, we need to create the trigger file in slave. This can be done manually. How can we do this automatically?
>
> For #3, this seems to be quite complicated. Is this possible using streaming replication and if yes then how can this be achieved?
>
This is doable with pacemaker or something alike. Wether this is a smart move or not is definitely arguable :-) I know for sure that I don't want that kind of automatic actions on my servers.
BTW, this isn't the list you should use for that kind of questions. Pgsql - general would be better.
On 12 March 2015 at 05:56, Deole, Pushkar (Pushkar) <pdeole@avaya.com> wrote: > I am new to postgreSql and evaluating the streaming replication for my use > case. My use case is: > > > > 1. Need to replicate data from primary database (master) to secondary > database (slave) asynchronously. > > 2. If master goes down, the slave should automatically be promoted to > master. > > 3. Later, when the original primary server (original master) is > brought up again, it should obtain back its master role and the new master > should assume the slave again as it was with original setup. > > > > For #1, the streaming replication of postgresql is good enough. > > For #2, we need to create the trigger file in slave. This can be done > manually. How can we do this automatically? Look at repmgr, it does what you need. > For #3, this seems to be quite complicated. Is this possible using streaming > replication and if yes then how can this be achieved? Bad idea to do that automatically. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services
Can you elaborate on " Bad idea to do that automatically." In production, that has to happen automatically, isn't it? There won't be an administrator available all the time to do afailover in case primary goes down.. -----Original Message----- From: Simon Riggs [mailto:simon@2ndQuadrant.com] Sent: Friday, March 13, 2015 3:23 PM To: Deole, Pushkar (Pushkar) Cc: pgsql-cluster-hackers@postgresql.org Subject: Re: [pgsql-cluster-hackers] Asynchronous streaming replication in postgresql On 12 March 2015 at 05:56, Deole, Pushkar (Pushkar) <pdeole@avaya.com> wrote: > I am new to postgreSql and evaluating the streaming replication for my > use case. My use case is: > > > > 1. Need to replicate data from primary database (master) to secondary > database (slave) asynchronously. > > 2. If master goes down, the slave should automatically be promoted to > master. > > 3. Later, when the original primary server (original master) is > brought up again, it should obtain back its master role and the new > master should assume the slave again as it was with original setup. > > > > For #1, the streaming replication of postgresql is good enough. > > For #2, we need to create the trigger file in slave. This can be done > manually. How can we do this automatically? Look at repmgr, it does what you need. > For #3, this seems to be quite complicated. Is this possible using > streaming replication and if yes then how can this be achieved? Bad idea to do that automatically. -- Simon Riggs https://urldefense.proofpoint.com/v2/url?u=http-3A__www.2ndQuadrant.com_&d=AwIBaQ&c=BFpWQw8bsuKpl1SgiZH64Q&r=9JxdEvGq7qy3NnL3UxN47w&m=40Wa40JcHBzcpMHkes_PqVNhpVzn4pK5rDc4JEb1XcI&s=BTCgqd3RbpTPwz5yzUOwfvuP0p1OkgnFGfKcfF6BeDU&e= PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services
On 13 March 2015 at 10:28, Deole, Pushkar (Pushkar) <pdeole@avaya.com> wrote: > Can you elaborate on " Bad idea to do that automatically." > > In production, that has to happen automatically, isn't it? There won't be an administrator available all the time to doa failover in case primary goes down.. Automatic failover is OK. Automatic failback is not OK. >> For #3, this seems to be quite complicated. Is this possible using >> streaming replication and if yes then how can this be achieved? > > Bad idea to do that automatically. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services