Thread: How to choose new master from slaves.?

On 8/4/22 02:43, Sacheen Birhade wrote:

@font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0;}@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; font-size:11.0pt; font-family:"Calibri",sans-serif;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri",sans-serif; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri",sans-serif;}div.WordSection1 {page:WordSection1;}

Hi All,

 

I have a very basic question about streaming replication feature of Postgres.

Let’s assume I have servers A, B, C, D, & E with postgres installed with streaming replication as follow:

A  à  B ( sync replication )

A  à  C ( async replication )

A  à  D ( async replication )

A  à  E ( async replication )

A is master & rest are slaves.

 

Now my question is:

Assume replication link between A & B may/may not stable.  If A goes down, how will I choose my new master from slaves? What are criteria to choose new master?

By "link between A & B may/may not stable", do you mean that B might not actually be synchronized?

On 2022-08-04 07:43:28 +0000, Sacheen Birhade wrote:
> I have a very basic question about streaming replication feature of Postgres.
> Let’s assume I have servers A, B, C, D, & E with postgres installed with
> streaming replication as follow:
> A  à  B ( sync replication )
> A  à  C ( async replication )
> A  à  D ( async replication )
> A  à  E ( async replication )
> A is master & rest are slaves.
>
> Now my question is:
> Assume replication link between A & B may/may not stable.

Using sync replication on an unstable link is probably not a good idea.
Every time the link goes down, A freezes. Is this what you want?

> If A goes down, how will I choose my new master from slaves? What are
> criteria to choose new master?

I would prefer B over the others because it is in sync with A, so you
don't lose data. All the other ones might.

Otherwise: For the same reason I would prefer B, I would prefer the one
with the most up-to-date data. But there might be other considerations,
e.g. the network connections (bandwidth and delays) between the
surviving members and the clients.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 8/6/22 03:40, Peter J. Holzer wrote:
> On 2022-08-04 07:43:28 +0000, Sacheen Birhade wrote:
>> I have a very basic question about streaming replication feature of Postgres.
>> Let’s assume I have servers A, B, C, D, & E with postgres installed with
>> streaming replication as follow:
>> A  à  B ( sync replication )
>> A  à  C ( async replication )
>> A  à  D ( async replication )
>> A  à  E ( async replication )
>> A is master & rest are slaves.
>>   
>> Now my question is:
>> Assume replication link between A & B may/may not stable.
> Using sync replication on an unstable link is probably not a good idea.
> Every time the link goes down, A freezes. Is this what you want?

I had to fight my end users about how to replicate a SQL Server database 
across a WAN.  They of course wanted it synchronously replicated, but I 
insisted that was a horrible idea, for the exact reason you mentioned 
(though it "only" freezes when the untransmitted transmitted transaction 
logs fill of the primary database's disk).


-- 
Angular momentum makes the world go 'round.

On 2022-08-06 15:06:06 -0500, Ron wrote:
> On 8/6/22 03:40, Peter J. Holzer wrote:
> > Using sync replication on an unstable link is probably not a good idea.
> > Every time the link goes down, A freezes. Is this what you want?
>
> I had to fight my end users about how to replicate a SQL Server database
> across a WAN.  They of course wanted it synchronously replicated, but I
> insisted that was a horrible idea, for the exact reason you mentioned
> (though it "only" freezes when the untransmitted transmitted transaction
> logs fill of the primary database's disk).

Doesn't synchronous mean that a commit only returns when the transaction
has been replicated?

(The database can work on other transactions in the meantime, but that
client is blocked.)

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 8/6/22 15:45, Peter J. Holzer wrote:
> On 2022-08-06 15:06:06 -0500, Ron wrote:
>> On 8/6/22 03:40, Peter J. Holzer wrote:
>>> Using sync replication on an unstable link is probably not a good idea.
>>> Every time the link goes down, A freezes. Is this what you want?
>> I had to fight my end users about how to replicate a SQL Server database
>> across a WAN.  They of course wanted it synchronously replicated, but I
>> insisted that was a horrible idea, for the exact reason you mentioned
>> (though it "only" freezes when the untransmitted transmitted transaction
>> logs fill of the primary database's disk).
> Doesn't synchronous mean that a commit only returns when the transaction
> has been replicated?

Correct.  I don't know what I was thinking when I wrote that.

Probably that transaction log data gets queued up and not deleted if it 
can't send the data across.

> (The database can work on other transactions in the meantime, but that
> client is blocked.)

Eventually any client doing writes blocks.  Readers might block if a writer 
took an exclusive lock,

-- 
Angular momentum makes the world go 'round.