Re: Broken hint bits (freeze)

Bruce Momjian <bruce(at)momjian(dot)us> writes:

> On Fri, Jun 16, 2017 at 04:33:16AM +0300, Sergey Burladyan wrote:
> > Bruce Momjian <bruce(at)momjian(dot)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