pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRecovering standby from primary archived WAL segments
6+ messages / 3 participants
[nested] [flat]
* Recovering standby from primary archived WAL segments
@ 2023-11-04 12:46 Ebubekir Büyüktosun <ebubekir.buyuktosun@primeit.com.tr>
0 siblings, 1 reply; 6+ messages in thread
From: Ebubekir Büyüktosun @ 2023-11-04 12:46 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; +Cc: pg <pg@primeit.com.tr>
<div>Hello community,</div><div> </div><div>I want to ask how we should configure primary and/or standby servers in case of need to recover the standby server from primary archived WAL segments.</div><div> </div><div>In the scenario<span style="background-color:transparent">, standby server has down because of some server issues for 3 days while primary server has been working well and generating WAL segments and archiving them as configured. (You can consider primary server is configured to archive WAL segments setting the parameters archive_mode, archive_command and even restore_command.) After 3 days in this </span>scenario, the problem in the <span style="background-color:transparent">standby server has been solved and we want to recover the standby from primary archived wal segments and continue the streaming replication as well <strong>instead of reinitializing the standby</strong>. My expectation in this case, postgres must manage itself to restore and send the archived WAL segments in the primary site which is required from standby server for recovery itself without any manuel intervention by DBA. If it is possible, how primary and/or standby servers must be configured?</span></div><div> </div><div>Thanks for your kind investigations.</div><div> </div><div><span style="color:#000000;font-size:14px;line-height:normal"><strong><span style="font-family:'courier new' , monospace"><span style="background-color:#ffffff">İyi çalışmalar </span></span></strong></span></div><div><span style="color:#000000;font-size:14px;line-height:normal"><strong><span style="font-family:'courier new' , monospace"><span style="background-color:#ffffff">Best Regards</span></span></strong></span></div><div> </div><div><img src="https://avatars.mds.yandex.net/get-mail-signature/200369/b5cfbc69fec7ba407ad439c916819b51/orig"; /></div><div> </div>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Recovering standby from primary archived WAL segments
@ 2023-11-04 13:42 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Ebubekir Büyüktosun <ebubekir.buyuktosun@primeit.com.tr>
0 siblings, 2 replies; 6+ messages in thread
From: Laurenz Albe @ 2023-11-04 13:42 UTC (permalink / raw)
To: Ebubekir Büyüktosun <ebubekir.buyuktosun@primeit.com.tr>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; +Cc: pg <pg@primeit.com.tr>
On Sat, 2023-11-04 at 15:46 +0300, Ebubekir Büyüktosun wrote:
> I want to ask how we should configure primary and/or standby servers in case of need
> to recover the standby server from primary archived WAL segments.
>
> If it is possible, how primary and/or standby servers must be configured?
Set "restore_command" on the standby.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Recovering standby from primary archived WAL segments
@ 2023-11-04 14:06 Ebubekir Büyüktosun <ebubekir.buyuktosun@primeit.com.tr>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 6+ messages in thread
From: Ebubekir Büyüktosun @ 2023-11-04 14:06 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; +Cc: pg <pg@primeit.com.tr>
<div>Hi Laurenz,</div><div> </div><div>Thanks for your response.</div><div> </div><div>I have already set it on standby site but i do not want to use any remote file copy utility such as "scp" in this command to restore archived wal in primary site. Instead, I expect postgres to send it whenever standby requires it if it is possible.</div><div> </div><div>But i will reconsider the parameter in detail for standby site.</div><div> </div><div>04.11.2023, 16:43, "Laurenz Albe" <laurenz.albe@cybertec.at>:</div><blockquote><p>On Sat, 2023-11-04 at 15:46 +0300, Ebubekir Büyüktosun wrote:</p><blockquote> I want to ask how we should configure primary and/or standby servers in case of need<br /> to recover the standby server from primary archived WAL segments.<br /> <br /> If it is possible, how primary and/or standby servers must be configured?</blockquote><p><br />Set "restore_command" on the standby.<br /><br />Yours,<br />Laurenz Albe</p></blockquote><div> </div><div> </div><div> </div><div><span style="color:#000000;font-size:14px;line-height:normal"><strong><span style="font-family:'courier new' , monospace"><span style="background-color:#ffffff">İyi çalışmalar </span></span></strong></span></div><div><span style="color:#000000;font-size:14px;line-height:normal"><strong><span style="font-family:'courier new' , monospace"><span style="background-color:#ffffff">Best Regards</span></span></strong></span></div><div> </div><div><img src="https://avatars.mds.yandex.net/get-mail-signature/200369/b5cfbc69fec7ba407ad439c916819b51/orig"; /></div><div> </div>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Recovering standby from primary archived WAL segments
@ 2023-11-04 14:16 Ron <ronljohnsonjr@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 6+ messages in thread
From: Ron @ 2023-11-04 14:16 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 11/4/23 08:42, Laurenz Albe wrote:
> On Sat, 2023-11-04 at 15:46 +0300, Ebubekir Büyüktosun wrote:
>> I want to ask how we should configure primary and/or standby servers in case of need
>> to recover the standby server from primary archived WAL segments.
>>
>> If it is possible, how primary and/or standby servers must be configured?
> Set "restore_command" on the standby.
When using physical replication and a replication slot still exists in
pg_replication_slots, won't the WAL files "just" accumulate in pg_wal/ until
Pg on the replica is restarted? (I just tested that, and it seems to work.)
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Recovering standby from primary archived WAL segments
@ 2023-11-04 14:41 Ebubekir Büyüktosun <ebubekir.buyuktosun@primeit.com.tr>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ebubekir Büyüktosun @ 2023-11-04 14:41 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
<div>Hi Ron,</div><div> </div><div>I test it using repmgr. So, when i close the postgres and repmgr services at the standby site, as i understand, repmgr drops it's replication slot so there is no wal accumulation in pg_wal at primary site but archiving is working well because it is enabled.</div><div> </div><div>Thanks for response,</div><div> </div><div>04.11.2023, 17:16, "Ron" <ronljohnsonjr@gmail.com>:</div><blockquote><p>On 11/4/23 08:42, Laurenz Albe wrote:</p><blockquote> On Sat, 2023-11-04 at 15:46 +0300, Ebubekir Büyüktosun wrote:<blockquote> I want to ask how we should configure primary and/or standby servers in case of need<br /> to recover the standby server from primary archived WAL segments.<br /> <br /> If it is possible, how primary and/or standby servers must be configured?</blockquote> Set "restore_command" on the standby.</blockquote><p><br />When using physical replication and a replication slot still exists in<br />pg_replication_slots, won't the WAL files "just" accumulate in pg_wal/ until<br />Pg on the replica is restarted? (I just tested that, and it seems to work.)<br /> </p>--<br />Born in Arizona, moved to Babylonia.<br /><br /> </blockquote><div> </div><div> </div><div> </div><div><span style="color:#000000;font-size:14px;line-height:normal"><strong><span style="font-family:'courier new' , monospace"><span style="background-color:#ffffff">İyi çalışmalar </span></span></strong></span></div><div><span style="color:#000000;font-size:14px;line-height:normal"><strong><span style="font-family:'courier new' , monospace"><span style="background-color:#ffffff">Best Regards</span></span></strong></span></div><div> </div><div><img src="https://avatars.mds.yandex.net/get-mail-signature/200369/b5cfbc69fec7ba407ad439c916819b51/orig"; /></div><div> </div>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Recovering standby from primary archived WAL segments
@ 2023-11-04 14:47 Ron <ronljohnsonjr@gmail.com>
parent: Ebubekir Büyüktosun <ebubekir.buyuktosun@primeit.com.tr>
0 siblings, 0 replies; 6+ messages in thread
From: Ron @ 2023-11-04 14:47 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Well that stinks. And seems a pretty fatal flaw in repmgr.
On 11/4/23 09:41, Ebubekir Büyüktosun wrote:
> Hi Ron,
> I test it using repmgr. So, when i close the postgres and repmgr services
> at the standby site, as i understand, repmgr drops it's replication slot
> so there is no wal accumulation in pg_wal at primary site but archiving is
> working well because it is enabled.
> Thanks for response,
> 04.11.2023, 17:16, "Ron" <ronljohnsonjr@gmail.com>:
>
> On 11/4/23 08:42, Laurenz Albe wrote:
>
> On Sat, 2023-11-04 at 15:46 +0300, Ebubekir Büyüktosun wrote:
>
> I want to ask how we should configure primary and/or standby
> servers in case of need
> to recover the standby server from primary archived WAL segments.
>
> If it is possible, how primary and/or standby servers must be
> configured?
>
> Set "restore_command" on the standby.
>
>
> When using physical replication and a replication slot still exists in
> pg_replication_slots, won't the WAL files "just" accumulate in pg_wal/
> until
> Pg on the replica is restarted? (I just tested that, and it seems to
> work.)
>
> --
> Born in Arizona, moved to Babylonia.
>
> *İyi çalışmalar *
> *Best Regards*
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-11-04 14:47 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-04 12:46 Recovering standby from primary archived WAL segments Ebubekir Büyüktosun <ebubekir.buyuktosun@primeit.com.tr>
2023-11-04 13:42 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-11-04 14:06 ` Ebubekir Büyüktosun <ebubekir.buyuktosun@primeit.com.tr>
2023-11-04 14:16 ` Ron <ronljohnsonjr@gmail.com>
2023-11-04 14:41 ` Ebubekir Büyüktosun <ebubekir.buyuktosun@primeit.com.tr>
2023-11-04 14:47 ` Ron <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox