Fwd: upgrade to PG11 on secondary fails (no initdb was launched)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Fwd: upgrade to PG11 on secondary fails (no initdb was launched)

Mariel Cherkassky


Hey,
I have 2 nodes that are configured with streaming replication (PG 9.6, repmgr 4.3).
I was trying to upgrade the nodes to PG11 with the doc - https://www.postgresql.org/docs/11/pgupgrade.html
Everything goes well until I try to start the secondary and then it fails on the next error : 

2019-05-23 04:17:02 EDT  23593  FATAL:  database files are incompatible with server
2019-05-23 04:17:02 EDT  23593  DETAIL:  The database cluster was initialized with PG_CONTROL_VERSION 960, but the server was compiled with PG_CONTROL_VERSION 1100.
2019-05-23 04:17:02 EDT  23593  HINT:  It looks like you need to initdb.
2019-05-23 04:17:02 EDT  23593  LOG:  database system is shut down

I upgraded the primary, then I run the rsync command in the primary : 
rsync --archive --delete --hard-links --size-only --no-inc-recursive /var/lib/pgsql/data /var/lib/pgsql/11/data/ secondary_ip:/var/lib/pgsql/data/

in the secondary I checked the version file and it was 11 : 
[secondary]# cat PG_VERSION
11

Any idea how to handle it ? I'm sending it to the performance mail list because no one answered it in the admin list ..
Reply | Threaded
Open this post in threaded view
|

RE: upgrade to PG11 on secondary fails (no initdb was launched)

Greg Clough-6
> 2019-05-23 04:17:02 EDT  23593  DETAIL:  The database cluster was initialized with PG_CONTROL_VERSION 960, but the server was compiled with PG_CONTROL_VERSION 1100.

It appears that you have not upgraded the standby server, so either use "rsync" or simply destroy and rebuild it from scratch "repmgr standby clone..."

      https://www.postgresql.org/docs/11/pgupgrade.html

      Upgrade Streaming Replication and Log-Shipping standby servers

      If you used link mode and have Streaming Replication (see Section 26.2.5) or Log-Shipping (see Section 26.2) standby servers, you can follow these steps to quickly upgrade them. You will not be running pg_upgrade on the standby servers, but rather rsync on the primary. Do not start any servers yet.

      If you did not use link mode, do not have or do not want to use rsync, or want an easier solution, skip the instructions in this section and simply recreate the standby servers once pg_upgrade completes and the new primary is running.

Greg.





________________________________

This e-mail, including accompanying communications and attachments, is strictly confidential and only for the intended recipient. Any retention, use or disclosure not expressly authorised by IHSMarkit is prohibited. This email is subject to all waivers and other terms at the following link: https://ihsmarkit.com/Legal/EmailDisclaimer.html

Please visit www.ihsmarkit.com/about/contact-us.html for contact information on our offices worldwide.
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: upgrade to PG11 on secondary fails (no initdb was launched)

Fabio Pardi
In reply to this post by Mariel Cherkassky
Hi Mariel,


On 5/23/19 1:07 PM, Mariel Cherkassky wrote:
>
>
> Hey,
>
> I upgraded the primary, then I run the rsync command in the primary : 
> rsync --archive --delete --hard-links --size-only --no-inc-recursive
> /var/lib/pgsql/data /var/lib/pgsql/11/data/
> secondary_ip:/var/lib/pgsql/data/

rsync needs only 2 arguments, not 3.

You are here passing /var/lib/pgsql/data /var/lib/pgsql/11/data/
secondary_ip:/var/lib/pgsql/data/

and if you try to do that, you will end up copying the content of the
first folder into the third.

Therefore your secondary database will contain what on the primary is in
/var/lib/pgsql/data/ (guess, 9.6.0)

Also, I do not think it best practice (or perhaps not correct at all) to
use '--size-only'

>
> in the secondary I checked the version file and it was 11 : 
> [secondary]# cat PG_VERSION
> 11
>

from which folder are you running that? And what is the PGDATA of your
standby server?



regards,

fabio pardi


Reply | Threaded
Open this post in threaded view
|

Re: Fwd: upgrade to PG11 on secondary fails (no initdb was launched)

Stephen Frost
In reply to this post by Mariel Cherkassky
Greetings,

Please don't post these kinds of questions to this list, it's not the
right list.

Pick the correct list to use in the future, and don't cross-post to
multiple lists.

This list is specifically for performance issues and questions regarding
PostgreSQL, not about how to upgrade.  For that, I would suggest either
-general OR -admin (not both).

> Any idea how to handle it ? I'm sending it to the performance mail list
> because no one answered it in the admin list ..

This isn't an acceptable reason to forward it to another list.  These
lists have specific purposes and should be used for those purposes.
Further, no one is under any obligation to respond to questions posed to
these lists and any help provided is entirely at the discretion of those
on the list as to if they wish to, and have time to, help, or not.

Thanks,

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: upgrade to PG11 on secondary fails (no initdb was launched)

Bruce Momjian
In reply to this post by Fabio Pardi
On Thu, May 23, 2019 at 01:31:01PM +0200, Fabio Pardi wrote:

> Hi Mariel,
>
>
> On 5/23/19 1:07 PM, Mariel Cherkassky wrote:
> >
> >
> > Hey,
> >
> > I upgraded the primary, then I run the rsync command in the primary : 
> > rsync --archive --delete --hard-links --size-only --no-inc-recursive
> > /var/lib/pgsql/data /var/lib/pgsql/11/data/
> > secondary_ip:/var/lib/pgsql/data/
>
> rsync needs only 2 arguments, not 3.
>
> You are here passing /var/lib/pgsql/data /var/lib/pgsql/11/data/
> secondary_ip:/var/lib/pgsql/data/
>
> and if you try to do that, you will end up copying the content of the
> first folder into the third.
>
> Therefore your secondary database will contain what on the primary is in
> /var/lib/pgsql/data/ (guess, 9.6.0)
>
> Also, I do not think it best practice (or perhaps not correct at all) to
> use '--size-only'

--size-only is correct, as far as I know.

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +


Reply | Threaded
Open this post in threaded view
|

Re: Fwd: upgrade to PG11 on secondary fails (no initdb was launched)

Fabio Pardi
Hi Bruce,


On 6/14/19 5:30 AM, Bruce Momjian wrote:

>> Also, I do not think it best practice (or perhaps not correct at all) to
>> use '--size-only'
>
> --size-only is correct, as far as I know.
>


Maybe I am missing something, but I am of the opinion that --size-only
should not be used when syncing database content (and probably in many
other use cases where content can change over time).

The reason is that db allocates blocks, 8K by default regardless from
the content.

Using --size-only, tells rsync to only check the size of the blocks.
That is: if the block is present on the destination, and is the same
size as the origin, then skip.

I understand that in this thread we are contextualizing in a step by
step procedure to create a new standby, but I have anyway a few remarks
about it (and the documentation where it has been copied from) and I
would be glad if you or somebody else could shed some light on it.


*) It might happen in some corner cases that when syncing the standby,
rsync dies and the DBA does not realize it. It will then start the
master and some data gets modified. At the time the DBA realizes the
issue on the standby, he will stop master and resume the sync.
Changes happened on the master will then not be propagated to the
standby if they happened on files already present on the standby.


*) It might be a long shot because I do not have time now to reproduce
the situation of the standby at that exact point in time, but I think
that  --size-only option is there probably to speed up operations. In
that case I do not see a reason for it since the data folder on the
standby is assumed to be empty



regards,

fabio pardi


Reply | Threaded
Open this post in threaded view
|

Re: Fwd: upgrade to PG11 on secondary fails (no initdb was launched)

Bruce Momjian
On Fri, Jun 14, 2019 at 03:12:29PM +0200, Fabio Pardi wrote:

> Hi Bruce,
>
>
> On 6/14/19 5:30 AM, Bruce Momjian wrote:
>
> >> Also, I do not think it best practice (or perhaps not correct at all) to
> >> use '--size-only'
> >
> > --size-only is correct, as far as I know.
> >
>
>
> Maybe I am missing something, but I am of the opinion that --size-only
> should not be used when syncing database content (and probably in many
> other use cases where content can change over time).
>
> The reason is that db allocates blocks, 8K by default regardless from
> the content.
>
> Using --size-only, tells rsync to only check the size of the blocks.
> That is: if the block is present on the destination, and is the same
> size as the origin, then skip.

The files are _exactly_ the same on primary and standby, so we don't
need to check anything.  Frankly, it is really only doing hard linking
of the files.

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +


Reply | Threaded
Open this post in threaded view
|

Re: Fwd: upgrade to PG11 on secondary fails (no initdb was launched)

Bruce Momjian
On Fri, Jun 14, 2019 at 10:39:40AM -0400, Bruce Momjian wrote:
> On Fri, Jun 14, 2019 at 03:12:29PM +0200, Fabio Pardi wrote:
> > Using --size-only, tells rsync to only check the size of the blocks.
> > That is: if the block is present on the destination, and is the same
> > size as the origin, then skip.
>
> The files are _exactly_ the same on primary and standby, so we don't
> need to check anything.  Frankly, it is really only doing hard linking
> of the files.

Here is the description from our docs:

       What this does is to record the links created by pg_upgrade's
       link mode that connect files in the old and new clusters on the
       primary server.  It then finds matching files in the standby's old
       cluster and creates links for them in the standby's new cluster.
       Files that were not linked on the primary are copied from the
       primary to the standby.  (They are usually small.)  This provides
       rapid standby upgrades.  Unfortunately, rsync needlessly copies
       files associated with temporary and unlogged tables because these
       files don't normally exist on standby servers.

The primary and standby have to be binary the same or WAL replay would
not work on the standby.  (Yes, I sometimes forgot how this worked so I
wrote it down in the docs.)  :-)

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +


Reply | Threaded
Open this post in threaded view
|

Re: Fwd: upgrade to PG11 on secondary fails (no initdb was launched)

Stephen Frost
Greetings,

* Bruce Momjian ([hidden email]) wrote:

> On Fri, Jun 14, 2019 at 10:39:40AM -0400, Bruce Momjian wrote:
> > On Fri, Jun 14, 2019 at 03:12:29PM +0200, Fabio Pardi wrote:
> > > Using --size-only, tells rsync to only check the size of the blocks.
> > > That is: if the block is present on the destination, and is the same
> > > size as the origin, then skip.
> >
> > The files are _exactly_ the same on primary and standby, so we don't
> > need to check anything.  Frankly, it is really only doing hard linking
> > of the files.
>
> Here is the description from our docs:
>
>        What this does is to record the links created by pg_upgrade's
>        link mode that connect files in the old and new clusters on the
>        primary server.  It then finds matching files in the standby's old
>        cluster and creates links for them in the standby's new cluster.
>        Files that were not linked on the primary are copied from the
>        primary to the standby.  (They are usually small.)  This provides
>        rapid standby upgrades.  Unfortunately, rsync needlessly copies
>        files associated with temporary and unlogged tables because these
>        files don't normally exist on standby servers.
>
> The primary and standby have to be binary the same or WAL replay would
> not work on the standby.  (Yes, I sometimes forgot how this worked so I
> wrote it down in the docs.)  :-)
Right- this is *not* a general process for building a replica, this is
specifically *only* for when doing a pg_upgrade and *everything* is shut
down when it runs, and every step is checked to ensure that there are no
errors during the process.

Thanks!

Stephen

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: upgrade to PG11 on secondary fails (no initdb was launched)

Stephen Frost
In reply to this post by Fabio Pardi
Greetings,

* Fabio Pardi ([hidden email]) wrote:
> I understand that in this thread we are contextualizing in a step by
> step procedure to create a new standby, but I have anyway a few remarks
> about it (and the documentation where it has been copied from) and I
> would be glad if you or somebody else could shed some light on it.

This is not a procedure for creating a new standby.

To create a *new* standby, with the primary online, use pg_basebackup or
another tool that knows how to issue the appropriate start/stop backup
and takes care of the WAL.

Thanks!

Stephen

signature.asc (836 bytes) Download Attachment