Thread: Streaming Replication between PostGreSQL 9.2.2 on Red Hat andPostGreSQL 9.2.24 on Debian

Streaming Replication between PostGreSQL 9.2.2 on Red Hat andPostGreSQL 9.2.24 on Debian

From

Jonatan Evald Buus

Date:

21 May 2018, 16:18:00

Hi there,

I'm trying to configure streaming replication between a Red Hat server running PostGreSQL 9.2.2 and a Debian server running PostGreSQL 9.2.24 with Hot Standby enabled.

While the base backup works fine using pg_basebackup, I get the following errors with hot standby enabled on the Debian slave when starting PostGreSQL:

WARNING: WAL was generated with wal_level=minimal, data may be missing

HINT: This happens if you temporarily set wal_level=minimal without taking a new base backup.

FATAL: hot standby is not possible because wal_level was not set to "hot_standby" on the master server

HINT: Either set wal_level to "hot_standby" on the master, or turn off hot_standby here

If I turn Hot Standby off on the Debian Slave I get the following errors:

FATAL: database system identifier differs between the primary and standby

DETAIL: The primary's identifier is 5940475598986796885, the standby's identifier is 6557962695089036503.

Configuring streaming replication with Hot Standby enabled locally on the Red Hat server works like a charm, and I've thus concluded that the issues must be one of the following:

- PostGreSQL's minor version differs between the 2 servers

- The two servers are running different operating systems

Any insight or guidance as to achieve this scenario would be greatly appreciated.

Cheers

Jona

Re: Streaming Replication between PostGreSQL 9.2.2 on Red Hat andPostGreSQL 9.2.24 on Debian

From

Ian Barwick

Date:

21 May 2018, 17:27:30

On 05/21/2018 07:18 PM, Jonatan Evald Buus wrote:
 > Hi there,
 >
 > I'm trying to configure streaming replication between a Red Hat server
 > running PostGreSQL 9.2.2 and a Debian server running PostGreSQL 9.2.24
 > with Hot Standby enabled.
 >
 > While the base backup works fine using /pg_basebackup/, I get the following
 > errors with hot standby enabled on the Debian slave when starting PostGreSQL:

 > /WARNING:  WAL was generated with wal_level=minimal, data may be missing/
 > /HINT:  This happens if you temporarily set wal_level=minimal without taking a new base backup./
 > /FATAL:  hot standby is not possible because wal_level was not set to "hot_standby" on the master server/
 > /HINT:  Either set wal_level to "hot_standby" on the master, or turn off hot_standby here/
 >
 > If I turn Hot Standby off on the Debian Slave I get the following errors:
 > /FATAL:  database system identifier differs between the primary and standby/
 > /DETAIL:  The primary's identifier is 5940475598986796885, the standby's identifier is 6557962695089036503./

The standby clearly hasn't been cloned from the primary, otherwise the
identifiers would be the same. Are you sure the PostgreSQL instance
running on the standby is the one you backed up with pg_basebackup?


Regards

Ian Barwick

-- 
  Ian Barwick                   http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services

Re: Streaming Replication between PostGreSQL 9.2.2 on Red Hat andPostGreSQL 9.2.24 on Debian

From

Jonatan Evald Buus

Date:

22 May 2018, 14:13:27

Thanks Ian, thank you for pointing out the obvious.

It appears that Debian's pg_dropcluster command had unexpected consequences.... you live, you pull out your hair in frustration and you learn.

I now have streaming replication working as we expected, can you confirm that my (somewhat unusual scenario?) shouldn't cause any problems?

Specifically:

- Using Streaming Replication between two PostGreSQL instances with different minor versions will work (9.2.2 on RHEL and 9.2.24 on Debian)

- Using Streaming Replication between two servers running different operating systems (RHEL 5.5 and Debian 8 / 9) will work

Greatly appreciate your insight

Cheers

Jona

On 21 May 2018 at 13:27, Ian Barwick <ian.barwick@2ndquadrant.com> wrote:

On 05/21/2018 07:18 PM, Jonatan Evald Buus wrote:
> Hi there,
>
> I'm trying to configure streaming replication between a Red Hat server
> running PostGreSQL 9.2.2 and a Debian server running PostGreSQL 9.2.24
> with Hot Standby enabled.
>
> While the base backup works fine using /pg_basebackup/, I get the following
> errors with hot standby enabled on the Debian slave when starting PostGreSQL:

> /WARNING: WAL was generated with wal_level=minimal, data may be missing/
> /HINT: This happens if you temporarily set wal_level=minimal without taking a new base backup./
> /FATAL: hot standby is not possible because wal_level was not set to "hot_standby" on the master server/
> /HINT: Either set wal_level to "hot_standby" on the master, or turn off hot_standby here/
>
> If I turn Hot Standby off on the Debian Slave I get the following errors:
> /FATAL: database system identifier differs between the primary and standby/
> /DETAIL: The primary's identifier is 5940475598986796885, the standby's identifier is 6557962695089036503./

The standby clearly hasn't been cloned from the primary, otherwise the
identifiers would be the same. Are you sure the PostgreSQL instance
running on the standby is the one you backed up with pg_basebackup?

Regards

Ian Barwick

--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: Streaming Replication between PostGreSQL 9.2.2 on Red Hat andPostGreSQL 9.2.24 on Debian

From

talk to ben

Date:

24 May 2018, 14:21:33

Hi,

Doing Streaming Replication between different minor version of PG is possible but not recommended [2]

Doing Streaming Replication between different OSes is not recommended pre ICU (pg10), please check you glibc versions. [1]

[1] https://www.postgresql.org/message-id/BA6132ED-1F6B-4A0B-AC22-81278F5AB81E@tripadvisor.com
[2] https://www.postgresql.org/docs/9.2/static/warm-standby.html (planning section)

Hoep this helps.

Cheers

Ben

2018-05-22 10:13 GMT+02:00 Jonatan Evald Buus <jonatan.buus@cellpointmobile.com>:

Thanks Ian, thank you for pointing out the obvious.
It appears that Debian's pg_dropcluster command had unexpected consequences.... you live, you pull out your hair in frustration and you learn.

I now have streaming replication working as we expected, can you confirm that my (somewhat unusual scenario?) shouldn't cause any problems?
Specifically:
- Using Streaming Replication between two PostGreSQL instances with different minor versions will work (9.2.2 on RHEL and 9.2.24 on Debian)
- Using Streaming Replication between two servers running different operating systems (RHEL 5.5 and Debian 8 / 9) will work

Greatly appreciate your insight

Cheers
Jona

On 21 May 2018 at 13:27, Ian Barwick <ian.barwick@2ndquadrant.com> wrote:
On 05/21/2018 07:18 PM, Jonatan Evald Buus wrote:
> Hi there,
>
> I'm trying to configure streaming replication between a Red Hat server
> running PostGreSQL 9.2.2 and a Debian server running PostGreSQL 9.2.24
> with Hot Standby enabled.
>
> While the base backup works fine using /pg_basebackup/, I get the following
> errors with hot standby enabled on the Debian slave when starting PostGreSQL:

> /WARNING: WAL was generated with wal_level=minimal, data may be missing/
> /HINT: This happens if you temporarily set wal_level=minimal without taking a new base backup./
> /FATAL: hot standby is not possible because wal_level was not set to "hot_standby" on the master server/
> /HINT: Either set wal_level to "hot_standby" on the master, or turn off hot_standby here/
>
> If I turn Hot Standby off on the Debian Slave I get the following errors:
> /FATAL: database system identifier differs between the primary and standby/
> /DETAIL: The primary's identifier is 5940475598986796885, the standby's identifier is 6557962695089036503./

The standby clearly hasn't been cloned from the primary, otherwise the
identifiers would be the same. Are you sure the PostgreSQL instance
running on the standby is the one you backed up with pg_basebackup?

Regards

Ian Barwick

--
Ian Barwick http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Jonatan Evald Buus
CTO, CellPoint Mobile
www.cellpointmobile.com
WE MAKE TRAVEL EASIER™

O: +45 70211512 | M: +45 28882861
E: jonatan.buus@cellpointmobile.com
Copenhagen | Dubai | London | Miami | Pune | Singapore

Re: Streaming Replication between PostGreSQL 9.2.2 on Red Hat andPostGreSQL 9.2.24 on Debian

From

Michael Paquier

Date:

25 May 2018, 07:15:17

On Thu, May 24, 2018 at 10:21:33AM +0200, talk to ben wrote:
> - Doing Streaming Replication between different minor version of PG is
>   possible but not recommended [2]

Standbys need to be updated first, hence be careful that the primary is
not updated before the standbys or WAL generated on the primary may not
be able to replay on its standbys.  Note however that you won't get
support for such configurations on the community lists, so just make
sure that all nodes in a cluster are on the same version and that it is
the latest one.
--
Michael

Attachment

signature.asc