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

> 2019-05-23 04:17:02 EDT  23593  DETAIL:  The database cluster was initialized with PG_CONTROL_VERSION 960, but the
serverwas 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)
standbyservers, 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
instructionsin 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
toall 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.

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

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

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  <bruce@momjian.us>        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 +

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

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  <bruce@momjian.us>        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 +

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  <bruce@momjian.us>        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 +

Greetings,

* Bruce Momjian (bruce@momjian.us) 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

Greetings,

* Fabio Pardi (f.pardi@portavita.eu) 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