From: Sergey Burladyan <eshkinkot@gmail.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: Vladimir Borodin <root@simply.name>,
  Amit Kapila <amit.kapila16@gmail.com>,
  Dmitriy Sarafannikov <dsarafannikov@yandex.ru>,
  pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Broken hint bits (freeze)
References: 
 <CAA4eK1KtEDAKbq_40hSYBTgvTf88Jio4Q_DxiEK0dkjjXdg4_g@mail.gmail.com>
	<4C1127FC-74AE-4544-9B22-6A32FD0C0607@yandex.ru>
	<CAA4eK1L4WKBs5LQ52hZ_whrR=A-cQwsXvGwbaz7k6O3NYUjhKQ@mail.gmail.com>
	<948C9927-CC63-47F7-87B9-1FA5921E6D4A@yandex.ru>
	<CAA4eK1LqGRcSQJ0SF_JDECjbc=YTMxQLuQzq7-dm006TDN5J=A@mail.gmail.com>
	<0DFC2046-3025-465E-BBB8-1E55479A9B7D@simply.name>
	<CAA4eK1Lyk6_B5oV7VDw7vMmroubVoKXy4Fw6eJ6W-mfwwPpXdQ@mail.gmail.com>
	<C4ADB713-CF88-4A75-A3C3-10E4E0A750DB@simply.name>
	<20170613193124.GQ13873@momjian.us> <87mv98af8j.fsf@seb.koffice.internal>
	<20170616164600.GO11450@momjian.us>
Date: Fri, 16 Jun 2017 22:57:33 +0300
Message-ID: <871sqj9042.fsf@seb.koffice.internal>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.3 (gnu/linux)
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=-=-="
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

--=-=-=
Content-Type: text/plain

Bruce Momjian <bruce@momjian.us> writes:

> On Fri, Jun 16, 2017 at 04:33:16AM +0300, Sergey Burladyan wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> > > !     <para>
> > > !      Also, if upgrading standby servers, change <varname>wal_level</>
> > > !      to <literal>replica</> in the <filename>postgresql.conf</> file on
> > > !      the new cluster.
> > >       </para>
> > >      </step>
> > 
> > I am not sure how this help.
> > 
> > wal_level is reset by pg_resetxlog during pg_upgrade, so it does not
> > depend on postgresql.conf. After pg_upgrade wal_level always is
> > 'minimal', that is why you must start and stop new master before rsync:
> > 
> > ==== output ====
> > $ "$bin"/pg_controldata "$ver" | grep wal_level
> > wal_level setting:                    replica
> > 
> > $ "$bin"/pg_resetwal "$ver"
> > Write-ahead log reset
> > 
> > $ "$bin"/pg_controldata "$ver" | grep wal_level
> > wal_level setting:                    minimal
> > ================
>
> Yes, I see that, but pg_resetxlog is run _before_ the _new_ cluster is
> started for the last time, so in my testing the wal_level at the end of
> pg_upgrade matches the value in postgresql.conf, e.g. "replica".  For
> example:
>
> 	Upgrade Complete
> 	----------------
> 	Optimizer statistics are not transferred by pg_upgrade so,
> 	once you start the new server, consider running:
> 	    ./analyze_new_cluster.sh
> 	
> 	Running this script will delete the old cluster's data files:
> 	    ./delete_old_cluster.sh
>
> 	$ pg_controldata /u/pg/data/ | grep wal_level
> 	wal_level setting:                    replica
>
> The way pg_upgrade uses rsync, the standby never needs to replay the WAL
> when it starts up because we already copied the changed system tables
> and hard linked the user data files.

Oh, it is my fail, I was not run test script completely for current git
master. In git master it work as expected. But not in previous versions.
I used this test script and got this result:
9.2 -> master: wal_level setting:                    replica
9.2 -> 9.6: wal_level setting:                    minimal
9.2 -> 9.5: wal_level setting:                    minimal
9.2 -> 9.4: Current wal_level setting:            minimal

I also save strace for pg_upgrade:
=== 9.6 ===
pg_resetxlog", ["/home/sergey/inst/pg9.6/bin/pg_resetxlog", "-l", "000000010000000000000002", "9.6"],
pg_ctl", ["/home/sergey/inst/pg9.6/bin/pg_ctl", "-w", "-l", "pg_upgrade_server.log", "-D", "9.6",
pg_ctl", ["/home/sergey/inst/pg9.6/bin/pg_ctl", "-w", "-D", "9.6", "-o", "", "-m", "smart", "stop"],
pg_resetxlog", ["/home/sergey/inst/pg9.6/bin/pg_resetxlog", "-o", "16393", "9.6"], [/* 68 vars */]) = 0
===========

It is exec pg_resetxlog last for set next OID,
it is from src/bin/pg_upgrade/pg_upgrade.c:149

=== master ===
pg_resetwal", ["/home/sergey/inst/pg-master/bin/pg_resetwal", "-l", "000000010000000000000002", "master"],
pg_ctl", ["/home/sergey/inst/pg-master/bin/pg_ctl", "-w", "-l", "pg_upgrade_server.log", "-D", "master",
pg_ctl", ["/home/sergey/inst/pg-master/bin/pg_ctl", "-w", "-D", "master", "-o", "", "-m", "smart", "stop"],
pg_resetwal", ["/home/sergey/inst/pg-master/bin/pg_resetwal", "-o", "16393", "master"], [/* 70 vars */]) = 0
pg_ctl", ["/home/sergey/inst/pg-master/bin/pg_ctl", "-w", "-l", "pg_upgrade_server.log", "-D", "master",
pg_ctl", ["/home/sergey/inst/pg-master/bin/pg_ctl", "-w", "-D", "master", "-o", "", "-m", "smart", "stop"],
==============

From git master pg_upgrade is restart new master again after
pg_resetwal -o, as you said.

It is from src/bin/pg_upgrade/check.c:176
void
issue_warnings(void)
{
    /* Create dummy large object permissions for old < PG 9.0? */
    if (GET_MAJOR_VERSION(old_cluster.major_version) <= 804)
    {
        start_postmaster(&new_cluster, true);
        new_9_0_populate_pg_largeobject_metadata(&new_cluster, false);
        stop_postmaster(false);
    }

    /* Reindex hash indexes for old < 10.0 */
    if (GET_MAJOR_VERSION(old_cluster.major_version) <= 906)
    {
        start_postmaster(&new_cluster, true);
        old_9_6_invalidate_hash_indexes(&new_cluster, false);
        stop_postmaster(false);
    }
}

-- 
Sergey Burladyan


--=-=-=
Content-Type: application/x-sh
Content-Disposition: attachment
Content-Transfer-Encoding: base64

IyEvYmluL2Jhc2gKIwoKIyBzZXQgLXgKCldEPS4vdGVzdApkZWNsYXJlIC1B
IFZFUlM9KAojICBbJzkuMiddPX4vImluc3QvcGc5LjIvYmluIgogIFsnOS40
J109fi8iaW5zdC9wZzkuNC9iaW4iCiAgWyc5LjUnXT1+LyJpbnN0L3BnOS41
L2JpbiIKICBbJzkuNiddPX4vImluc3QvcGc5LjYvYmluIgogIFsnbWFzdGVy
J109fi8iaW5zdC9wZy1tYXN0ZXIvYmluIgopCgpleHBvcnQgTENfQUxMPUMK
ZXhwb3J0IFBHREFUQUJBU0U9dGVzdAoKY2QgIiRXRCIgfHwgZXhpdCAxCgpy
dW4gKCkKewogICAgbG9jYWwgdmVyPSQxIGJpbj0kMiBvbGRiaW49fi8iaW5z
dC9wZzkuMi9iaW4iCgogICAgZWNobyAiaW5pdCIKCiAgICAiJG9sZGJpbiIv
aW5pdGRiIC1BIHRydXN0IC1FIFVURi04IC0tbG9jYWxlPUMgc3JjIHx8IGV4
aXQgMQoKICAgICIkYmluIi9pbml0ZGIgLUEgdHJ1c3QgLUUgVVRGLTggLS1s
b2NhbGU9QyAiJHZlciIgfHwgZXhpdCAxCgogICAgY2F0IDw8RU9GID4gIiR2
ZXIiL3Bvc3RncmVzcWwuY29uZgpwb3J0ID0gNTQzMApzaGFyZWRfYnVmZmVy
cyA9IDMyTUIKd2FsX2xldmVsID0gaG90X3N0YW5kYnkKI2FyY2hpdmVfbW9k
ZSA9IG9uCiNhcmNoaXZlX2NvbW1hbmQgPSAndGVzdCAhIC1mICIkQVJILyVm
IiAmJiAoIGVjaG8gImFyY2ggJXAiOyBjcCAlcCAiJEFSSC8lZiI7ICknCm1h
eF93YWxfc2VuZGVycyA9IDUKaG90X3N0YW5kYnkgPSBvbgpsb2dfbGluZV9w
cmVmaXggPSAnJXQgJwojbG9nX2NoZWNrcG9pbnRzID0gb24KbGNfbWVzc2Fn
ZXMgPSBDCkVPRgoKICAgIGNwICIkdmVyIi9wb3N0Z3Jlc3FsLmNvbmYgc3Jj
LwoKICAgIGVjaG8gIndhbCBsZXZlbCBmb3IgJyR2ZXInIgoKICAgICIkYmlu
Ii9wZ19jdGwgLXcgLUQgIiR2ZXIiIC1sIGxvZ2ZpbGUuIiR2ZXIiIHN0YXJ0
IHx8IGV4aXQgMQogICAgIiRiaW4iL3BzcWwgLVggLXEgLXAgNTQzMCAtZCBw
b3N0Z3JlcyAtYyAnc2hvdyB3YWxfbGV2ZWwnCiAgICAiJGJpbiIvcGdfY3Rs
IC13IC1EICIkdmVyIiBzdG9wIHx8IGV4aXQgMQoKICAgIGVjaG8gInN0YXJ0
IDkuMiIKCiAgICAiJG9sZGJpbiIvcGdfY3RsIC13IC1EIHNyYyAtbCBsb2dm
aWxlLjkyIHN0YXJ0IHx8IGV4aXQgMQoKICAgIGVjaG8gIndhbCBsZXZlbCBm
b3IgOS4yIgogICAgIiRvbGRiaW4iL3BzcWwgLVggLXEgLXAgNTQzMCAtZCBw
b3N0Z3JlcyAtYyAnc2hvdyB3YWxfbGV2ZWwnCgogICAgZWNobyAiZ2VuZXJh
dGUgc29tZSBXQUxzIC4uLiIKCiAgICAiJG9sZGJpbiIvcHNxbCAtWCAtcSAt
cCA1NDMwIC1kIHBvc3RncmVzIC1jICdjcmVhdGUgZGF0YWJhc2UgdGVzdCcK
ICAgICIkb2xkYmluIi9wc3FsIC1YIC1xIC1wIDU0MzAgLWYtIDw8RU9GCmNy
ZWF0ZSB0YWJsZSB0ZXN0IChpZCBpbnQgcHJpbWFyeSBrZXksIHYgdGV4dCk7
Cmluc2VydCBpbnRvIHRlc3Qgc2VsZWN0IG4sICd0aGlzIGlzIGxvbmcgbGlu
ZSBmb3IgZmlsbCB0YWJsZSwgbGluZSBudW1iZXIgJyB8fCBuIGZyb20gZ2Vu
ZXJhdGVfc2VyaWVzKDEsIDEwMDAwKSBuOwotLSB2YWN1dW0gYW5hbHl6ZSB0
ZXN0OwotLSBzZWxlY3QgcGdfY3VycmVudF94bG9nX2luc2VydF9sb2NhdGlv
bigpOwpFT0YKCiAgICBlY2hvICJzdG9wIDkuMiIKCiAgICAiJG9sZGJpbiIv
cGdfY3RsIC13IC1EIHNyYyBzdG9wIHx8IGV4aXQgMQoKICAgIGVjaG8gInJ1
biB1cGdyYWRlIGZyb20gOS4yIHRvICckdmVyJyIKCiAgICAiJGJpbiIvcGdf
dXBncmFkZSAtYiAiJG9sZGJpbiIgLUIgIiRiaW4iIC1kIHNyYyAtRCAiJHZl
ciIgLWsgLWMgfHwgZXhpdCAxCgogICAgc3RyYWNlIC1mIC1zIDI1NiAtZSB0
cmFjZT1wcm9jZXNzIC1vIHN0cmFjZS4iJHZlciIgIiRiaW4iL3BnX3VwZ3Jh
ZGUgLWIgIiRvbGRiaW4iIC1CICIkYmluIiAtZCBzcmMgLUQgIiR2ZXIiIC1r
IHx8IGV4aXQgMQoKICAgICMgY29tcGFyZSB3YWxfbGV2ZWwKICAgIGVjaG8g
LW4gIjkuMiAtPiAkdmVyOiAiID4+IHJlc3VsdAogICAgIiRiaW4iL3BnX2Nv
bnRyb2xkYXRhICIkdmVyIiB8IGdyZXAgd2FsX2xldmVsID4+IHJlc3VsdAp9
Cgpmb3IgdmVyIGluICIkeyFWRVJTW0BdfSI7IGRvCiAgICBiaW49JHtWRVJT
WyR2ZXJdfQogICAgZWNobyAidmVyOiAnJHZlciciCgogICAgaWYgW1sgLWQg
IiR2ZXIiIF1dOyB0aGVuCiAgICAgICAgZWNobyAiY2xlYW51cCBjbHVzdGVy
IGRpciAnJHZlcicgLi4uIgogICAgICAgIHJtIC1yICIkdmVyIgogICAgZmkK
CiAgICBydW4gIiR2ZXIiICIkYmluIgogICAgcm0gLXIgc3JjCmRvbmUKCg==

--=-=-=
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--=-=-=--