From: Sergey Burladyan <eshkinkot@gmail.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: Vladimir Borodin <root@simply.name>
Cc: Amit Kapila <amit.kapila16@gmail.com>
Cc: Dmitriy Sarafannikov <dsarafannikov@yandex.ru>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Broken hint bits (freeze)
Date: Fri, 16 Jun 2017 04:33:16 +0300
Message-ID: <87mv98af8j.fsf@seb.koffice.internal> (raw)
In-Reply-To: <20170613193124.GQ13873@momjian.us>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: eshkinkot@gmail.com, bruce@momjian.us, root@simply.name, amit.kapila16@gmail.com, dsarafannikov@yandex.ru
Subject: Re: Broken hint bits (freeze)
In-Reply-To: <87mv98af8j.fsf@seb.koffice.internal>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox