pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL replication failover
7+ messages / 5 participants
[nested] [flat]
* PostgreSQL replication failover
@ 2021-01-13 08:27 Jan Peters <haseningo@gmx.de>
0 siblings, 1 reply; 7+ messages in thread
From: Jan Peters @ 2021-01-13 08:27 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello,
we are running postgresqlserver on s390 zLinux machines. The distribution is RedHat 7 and RedHat 8, so we do not have the many x86 tools available.
We always run 2 instances with a replication (streaming) async mode, the replica is in hot_standby and we use it for read-only accesses. About the setup we have the following question:
How is an orderly failover accomplished? Our current procedure is.
1. primary stop
2. promote replica to primary
3. create standby.signal on old primary
4. change primary_conninfo on old primary
5. start old primary as new replica
Is this processing correct? Are there any other steps that simplify a failover? How can we be sure that all changes have been transferred from the old master to the replica?
thanks in advance
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL replication failover
@ 2021-01-13 11:25 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Jan Peters <haseningo@gmx.de>
0 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2021-01-13 11:25 UTC (permalink / raw)
To: Jan Peters <haseningo@gmx.de>; pgsql-admin@lists.postgresql.org
On Wed, 2021-01-13 at 09:27 +0100, Jan Peters wrote:
> we are running postgresqlserver on s390 zLinux machines. The distribution
> is RedHat 7 and RedHat 8, so we do not have the many x86 tools available.
>
> We always run 2 instances with a replication (streaming) async mode, the replica
> is in hot_standby and we use it for read-only accesses. About the setup we have the following question:
>
> How is an orderly failover accomplished? Our current procedure is.
>
> 1. primary stop
> 2. promote replica to primary
> 3. create standby.signal on old primary
> 4. change primary_conninfo on old primary
> 5. start old primary as new replica
>
> Is this processing correct? Are there any other steps that simplify a failover?
> How can we be sure that all changes have been transferred from the old master to the replica?
What you describe is not a failover, but a switchover.
If you shut down the primary server cleanly, all changes will be replicated,
so you should be good.
During a failover, that is, if the primary suddenly fails, there is always
the possibility that you lose some transactions, unless you use synchronous
replication.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL replication failover
@ 2021-01-13 13:24 Pepe TD Vo <pepevo@yahoo.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 7+ messages in thread
From: Pepe TD Vo @ 2021-01-13 13:24 UTC (permalink / raw)
To: Jan Peters <haseningo@gmx.de>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>
>>If you shut down the primary server cleanly, all changes will be replicated,so you should be good.
>>During a failover, that is, if the primary suddenly fails, there is always
the possibility that you lose some transactions, unless you use synchronous
you said above which I don't need to run promote to make it failover as long as I set synchronous on? The last couple of weeks I have a failure on the primary server and can't run on a slave. It picks up as reading mode only.
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love, and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success
On Wednesday, January 13, 2021, 06:25:53 AM EST, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2021-01-13 at 09:27 +0100, Jan Peters wrote:
> we are running postgresqlserver on s390 zLinux machines. The distribution
> is RedHat 7 and RedHat 8, so we do not have the many x86 tools available.
>
> We always run 2 instances with a replication (streaming) async mode, the replica
> is in hot_standby and we use it for read-only accesses. About the setup we have the following question:
>
> How is an orderly failover accomplished? Our current procedure is.
>
> 1. primary stop
> 2. promote replica to primary
> 3. create standby.signal on old primary
> 4. change primary_conninfo on old primary
> 5. start old primary as new replica
>
> Is this processing correct? Are there any other steps that simplify a failover?
> How can we be sure that all changes have been transferred from the old master to the replica?
What you describe is not a failover, but a switchover.
If you shut down the primary server cleanly, all changes will be replicated,
so you should be good.
During a failover, that is, if the primary suddenly fails, there is always
the possibility that you lose some transactions, unless you use synchronous
replication.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL replication failover
@ 2021-01-13 16:57 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Pepe TD Vo <pepevo@yahoo.com>
1 sibling, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2021-01-13 16:57 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; Jan Peters <haseningo@gmx.de>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Wed, 2021-01-13 at 13:24 +0000, Pepe TD Vo wrote:
> you said above which I don't need to run promote to make it failover as long as I set synchronous on?
That's not what I meant. Of course you have to promote the standby after shutting down
the old primary server.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL replication failover
@ 2021-01-13 18:46 Ganesh Korde <ganeshakorde@gmail.com>
parent: Pepe TD Vo <pepevo@yahoo.com>
1 sibling, 1 reply; 7+ messages in thread
From: Ganesh Korde @ 2021-01-13 18:46 UTC (permalink / raw)
To: Pepe TD Vo <pepevo@yahoo.com>; +Cc: Jan Peters <haseningo@gmx.de>; pgsql-admin@lists.postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
You can use different tools which detects if primary fails and
automatically promotes standby.
To assure all data on standby you should use synchronous replication.
On Wed, 13 Jan 2021, 6:54 pm Pepe TD Vo, <pepevo@yahoo.com> wrote:
> >>If you shut down the primary server cleanly, all changes will be
> replicated,so you should be good.
>
> >>During a failover, that is, if the primary suddenly fails, there is
> always
> the possibility that you lose some transactions, unless you use synchronous
>
> you said above which I don't need to run promote to make it failover as
> long as I set synchronous on? The last couple of weeks I have a failure
> on the primary server and can't run on a slave. It picks up as reading mode
> only.
>
>
>
> *Bach-Nga*No one in this world is pure and perfect. If you avoid people
> for their mistakes you will be alone. So judge less, love, and forgive more.[image:
> Emoji][image: Emoji][image: Emoji]
> To call him a dog hardly seems to do him justice though in as much as he
> had four legs, a tail, and barked, I admit he was, to all outward
> appearances. But to those who knew him well, he was a perfect gentleman
> (Hermione Gingold)
>
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
>
>
>
>
> On Wednesday, January 13, 2021, 06:25:53 AM EST, Laurenz Albe <
> laurenz.albe@cybertec.at> wrote:
>
>
> On Wed, 2021-01-13 at 09:27 +0100, Jan Peters wrote:
> > we are running postgresqlserver on s390 zLinux machines. The distribution
> > is RedHat 7 and RedHat 8, so we do not have the many x86 tools
> available.
> >
> > We always run 2 instances with a replication (streaming) async mode, the
> replica
> > is in hot_standby and we use it for read-only accesses. About the setup
> we have the following question:
> >
> > How is an orderly failover accomplished? Our current procedure is.
> >
> > 1. primary stop
> > 2. promote replica to primary
> > 3. create standby.signal on old primary
> > 4. change primary_conninfo on old primary
> > 5. start old primary as new replica
> >
> > Is this processing correct? Are there any other steps that simplify a
> failover?
> > How can we be sure that all changes have been transferred from the old
> master to the replica?
>
> What you describe is not a failover, but a switchover.
>
> If you shut down the primary server cleanly, all changes will be
> replicated,
> so you should be good.
>
> During a failover, that is, if the primary suddenly fails, there is always
> the possibility that you lose some transactions, unless you use synchronous
>
> replication.
>
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PostgreSQL replication failover
@ 2021-01-14 15:03 John Wiencek <jwiencek3@comcast.net>
parent: Ganesh Korde <ganeshakorde@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: John Wiencek @ 2021-01-14 15:03 UTC (permalink / raw)
To: Jan Peters <haseningo@gmx.de>; +Cc: Ganesh Korde <ganeshakorde@gmail.com>; Pepe TD Vo <pepevo@yahoo.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>
Hi
You can use repmgr, which is free, or EFM which requires a subscription.
John
> On Jan 14, 2021, at 2:16 AM, Jan Peters <haseningo@gmx.de> wrote:
>
> Hello,
>
> thank you very much for the answers.
> Can you tell me some tools, but they must be available for s390 ZLinux.
> For our purposes in redhat linux
>
>
> Gesendet: Mittwoch, 13. Januar 2021 um 19:46 Uhr
> Von: "Ganesh Korde" <ganeshakorde@gmail.com>
> An: "Pepe TD Vo" <pepevo@yahoo.com>
> Cc: "Jan Peters" <haseningo@gmx.de>, pgsql-admin@lists.postgresql.org, "Laurenz Albe" <laurenz.albe@cybertec.at>
> Betreff: Re: PostgreSQL replication failover
>
> You can use different tools which detects if primary fails and automatically promotes standby.
>
> To assure all data on standby you should use synchronous replication.
>
> On Wed, 13 Jan 2021, 6:54 pm Pepe TD Vo, <pepevo@yahoo.com[mailto:pepevo@yahoo.com]> wrote:
>
>>> If you shut down the primary server cleanly, all changes will be replicated,so you should be good.
>
>>> During a failover, that is, if the primary suddenly fails, there is always
> the possibility that you lose some transactions, unless you use synchronous
> you said above which I don't need to run promote to make it failover as long as I set synchronous on? The last couple of weeks I have a failure on the primary server and can't run on a slave. It picks up as reading mode only.
>
>
> Bach-Nga
>
> No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love, and forgive more.
> To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
>
>
>
>
>
> On Wednesday, January 13, 2021, 06:25:53 AM EST, Laurenz Albe <laurenz.albe@cybertec.at[mailto:laurenz.albe@cybertec.at]> wrote:
>
>
>
> On Wed, 2021-01-13 at 09:27 +0100, Jan Peters wrote:
>> we are running postgresqlserver on s390 zLinux machines. The distribution
>> is RedHat 7 and RedHat 8, so we do not have the many x86 tools available.
>>
>> We always run 2 instances with a replication (streaming) async mode, the replica
>> is in hot_standby and we use it for read-only accesses. About the setup we have the following question:
>>
>> How is an orderly failover accomplished? Our current procedure is.
>>
>> 1. primary stop
>> 2. promote replica to primary
>> 3. create standby.signal on old primary
>> 4. change primary_conninfo on old primary
>> 5. start old primary as new replica
>>
>> Is this processing correct? Are there any other steps that simplify a failover?
>> How can we be sure that all changes have been transferred from the old master to the replica?
>
> What you describe is not a failover, but a switchover.
>
> If you shut down the primary server cleanly, all changes will be replicated,
> so you should be good.
>
> During a failover, that is, if the primary suddenly fails, there is always
> the possibility that you lose some transactions, unless you use synchronous
> replication.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com[https://www.cybertec-postgresql.com]
>
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Re: PostgreSQL replication failover
@ 2021-01-19 09:09 Ganesh Korde <ganeshakorde@gmail.com>
parent: John Wiencek <jwiencek3@comcast.net>
0 siblings, 0 replies; 7+ messages in thread
From: Ganesh Korde @ 2021-01-19 09:09 UTC (permalink / raw)
To: Jan Peters <haseningo@gmx.de>; +Cc: John Wiencek <jwiencek3@comcast.net>; Pepe TD Vo <pepevo@yahoo.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>
Hi Jan,
You can use pgpool. Found something on internet.
http://www.rpm-find.net/linux/RPM/archive.fedoraproject.org/fedora-secondary/releases/28/Everything/...
Hope this helps.
Regards,
Ganesh Korde.
On Tue, 19 Jan 2021, 1:42 pm Jan Peters, <haseningo@gmx.de> wrote:
> Hi John,
>
> I can't find any s390 packages for repmgr :-(
>
>
>
> > Gesendet: Donnerstag, 14. Januar 2021 um 16:03 Uhr
> > Von: "John Wiencek" <jwiencek3@comcast.net>
> > An: "Jan Peters" <haseningo@gmx.de>
> > Cc: "Ganesh Korde" <ganeshakorde@gmail.com>, "Pepe TD Vo" <
> pepevo@yahoo.com>, "Pgsql-admin" <pgsql-admin@lists.postgresql.org>,
> "Laurenz Albe" <laurenz.albe@cybertec.at>
> > Betreff: Re: PostgreSQL replication failover
> >
> > Hi
> >
> > You can use repmgr, which is free, or EFM which requires a subscription.
> >
> > John
> >
> > > On Jan 14, 2021, at 2:16 AM, Jan Peters <haseningo@gmx.de> wrote:
> > >
> > > Hello,
> > >
> > > thank you very much for the answers.
> > > Can you tell me some tools, but they must be available for s390 ZLinux.
> > > For our purposes in redhat linux
> > >
> > >
> > > Gesendet: Mittwoch, 13. Januar 2021 um 19:46 Uhr
> > > Von: "Ganesh Korde" <ganeshakorde@gmail.com>
> > > An: "Pepe TD Vo" <pepevo@yahoo.com>
> > > Cc: "Jan Peters" <haseningo@gmx.de>, pgsql-admin@lists.postgresql.org,
> "Laurenz Albe" <laurenz.albe@cybertec.at>
> > > Betreff: Re: PostgreSQL replication failover
> > >
> > > You can use different tools which detects if primary fails and
> automatically promotes standby.
> > >
> > > To assure all data on standby you should use synchronous replication.
> > >
> > > On Wed, 13 Jan 2021, 6:54 pm Pepe TD Vo, <pepevo@yahoo.com[mailto:
> pepevo@yahoo.com]> wrote:
> > >
> > >>> If you shut down the primary server cleanly, all changes will be
> replicated,so you should be good.
> > >
> > >>> During a failover, that is, if the primary suddenly fails, there is
> always
> > > the possibility that you lose some transactions, unless you use
> synchronous
> > > you said above which I don't need to run promote to make it failover
> as long as I set synchronous on? The last couple of weeks I have a
> failure on the primary server and can't run on a slave. It picks up as
> reading mode only.
> > >
> > >
> > > Bach-Nga
> > >
> > > No one in this world is pure and perfect. If you avoid people for
> their mistakes you will be alone. So judge less, love, and forgive more.
> > > To call him a dog hardly seems to do him justice though in as much as
> he had four legs, a tail, and barked, I admit he was, to all outward
> appearances. But to those who knew him well, he was a perfect gentleman
> (Hermione Gingold)
> > > **Live simply **Love generously **Care deeply **Speak kindly.
> > > *** Genuinely rich *** Faithful talent *** Sharing success
> > >
> > >
> > >
> > >
> > >
> > > On Wednesday, January 13, 2021, 06:25:53 AM EST, Laurenz Albe <
> laurenz.albe@cybertec.at[mailto:laurenz.albe@cybertec.at]> wrote:
> > >
> > >
> > >
> > > On Wed, 2021-01-13 at 09:27 +0100, Jan Peters wrote:
> > >> we are running postgresqlserver on s390 zLinux machines. The
> distribution
> > >> is RedHat 7 and RedHat 8, so we do not have the many x86 tools
> available.
> > >>
> > >> We always run 2 instances with a replication (streaming) async mode,
> the replica
> > >> is in hot_standby and we use it for read-only accesses. About the
> setup we have the following question:
> > >>
> > >> How is an orderly failover accomplished? Our current procedure is.
> > >>
> > >> 1. primary stop
> > >> 2. promote replica to primary
> > >> 3. create standby.signal on old primary
> > >> 4. change primary_conninfo on old primary
> > >> 5. start old primary as new replica
> > >>
> > >> Is this processing correct? Are there any other steps that simplify a
> failover?
> > >> How can we be sure that all changes have been transferred from the
> old master to the replica?
> > >
> > > What you describe is not a failover, but a switchover.
> > >
> > > If you shut down the primary server cleanly, all changes will be
> replicated,
> > > so you should be good.
> > >
> > > During a failover, that is, if the primary suddenly fails, there is
> always
> > > the possibility that you lose some transactions, unless you use
> synchronous
> > > replication.
> > >
> > > Yours,
> > > Laurenz Albe
> > > --
> > > Cybertec | https://www.cybertec-postgresql.com[
> https://www.cybertec-postgresql.com]
> > >
> > >
> > >
> > >
> > >
> >
> >
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-01-19 09:09 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-13 08:27 PostgreSQL replication failover Jan Peters <haseningo@gmx.de>
2021-01-13 11:25 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-01-13 13:24 ` Pepe TD Vo <pepevo@yahoo.com>
2021-01-13 16:57 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-01-13 18:46 ` Ganesh Korde <ganeshakorde@gmail.com>
2021-01-14 15:03 ` John Wiencek <jwiencek3@comcast.net>
2021-01-19 09:09 ` Ganesh Korde <ganeshakorde@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