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: 
 <CAA4eK1L2n8stKZvOp3yrOCZJi4Z5eDuzmvhdPdbZUAc9qacRPg@mail.gmail.com>
	<2E86EB5F-50ED-4DE2-B14E-98EF8F62B2F2@simply.name>
	<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>
Date: Fri, 16 Jun 2017 04:33:16 +0300
In-Reply-To: <20170613193124.GQ13873@momjian.us> (Bruce Momjian's message of
	"Tue, 13 Jun 2017 15:31:24 -0400")
Message-ID: <87mv98af8j.fsf@seb.koffice.internal>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/24.3 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

Bruce Momjian <bruce@momjian.us> writes:

> !      against the old primary and standby clusters.  Verify that the
> !      <quote>Latest checkpoint location</> values match in all clusters.

For "Log-Shipping only" standby server this cannot be satisfied, because
last WAL from master (with shutdown checkpoint) never archived. For
example (git master):
==== postgresql.conf ===
port = 5430
shared_buffers = 32MB
wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f "$ARH/%f" && ( echo "arch %p"; cp %p "$ARH/%f"; )'
max_wal_senders = 5
hot_standby = on
log_line_prefix = '%t '
log_checkpoints = on
lc_messages = C
========================

==== pg_control ====
pg_control version number:            1002
Catalog version number:               201705301
Database system identifier:           6432034080221219745
Database cluster state:               shut down
pg_control last modified:             Fri Jun 16 03:57:22 2017
Latest checkpoint location:           0/D000028
Prior checkpoint location:            0/1604878
Latest checkpoint's REDO location:    0/D000028
Latest checkpoint's REDO WAL file:    00000001000000000000000D
====================

==== WALs archive ====
-rw------- 1 sergey users 16777216 Jun 16 03:57 000000010000000000000003
-rw------- 1 sergey users 16777216 Jun 16 03:57 000000010000000000000004
-rw------- 1 sergey users 16777216 Jun 16 03:57 000000010000000000000005
-rw------- 1 sergey users 16777216 Jun 16 03:57 000000010000000000000006
-rw------- 1 sergey users 16777216 Jun 16 03:57 000000010000000000000007
-rw------- 1 sergey users 16777216 Jun 16 03:57 000000010000000000000008
-rw------- 1 sergey users 16777216 Jun 16 03:57 000000010000000000000009
-rw------- 1 sergey users 16777216 Jun 16 03:57 00000001000000000000000A
-rw------- 1 sergey users 16777216 Jun 16 03:57 00000001000000000000000B
-rw------- 1 sergey users 16777216 Jun 16 03:57 00000001000000000000000C
======================

==== logfile ====
arch pg_wal/00000001000000000000000A
arch pg_wal/00000001000000000000000B
2017-06-16 00:57:21 GMT LOG:  received fast shutdown request
2017-06-16 00:57:21 GMT LOG:  aborting any active transactions
2017-06-16 00:57:21 GMT LOG:  shutting down
arch pg_wal/00000001000000000000000C
2017-06-16 00:57:21 GMT LOG:  checkpoint starting: shutdown immediate
2017-06-16 00:57:22 GMT LOG:  checkpoint complete: wrote 4058 buffers (99.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.033 s, sync=0.949 s, total=1.144 s; sync files=32, longest=0.598 s, average=0.029 s; distance=190445 kB, estimate=190445 kB
2017-06-16 00:57:22 GMT LOG:  database system is shut down
=================

There is no 00000001000000000000000D in archive and after shutdown,
standby can only be at it previous restartpoint (0/1604878) because it
does not receive latest checkpoint (0/D000028) from master.

So, after shutdown master and "Log-Shipping only" standby, it always "one
checkpoint early" then master and "Latest checkpoint location" never
match for it.

I think this must be mentioned somehow in documentation. 


> !     <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
================

If you rsync standby now (without start/stop new master after pg_upgrade)
you will send pg_control with wal_level=minimal into it and after that
standby abort on startup:
==== standby logfile ====
2017-06-16 01:22:14 GMT LOG:  entering standby mode
2017-06-16 01:22:14 GMT WARNING:  WAL was generated with wal_level=minimal, data may be missing
2017-06-16 01:22:14 GMT HINT:  This happens if you temporarily set wal_level=minimal without taking a new base backup.
2017-06-16 01:22:14 GMT FATAL:  hot standby is not possible because wal_level was not set to "replica" or higher on the master server
2017-06-16 01:22:14 GMT HINT:  Either set wal_level to "replica" on the master, or turn off hot_standby here.
2017-06-16 01:22:14 GMT LOG:  startup process (PID 27916) exited with exit code 1
=================

PS: Thank you for answer, Bruce!

-- 
Sergey Burladyan


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