BUG #17023: wal_log_hints not configured even if it on

On Wed, May 19, 2021 at 09:33:57AM +0000, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 17023
Logged by: Mohan nagandlla
Email address: nagandllamohan@gmail.com
PostgreSQL version: 13.2
Operating system: Alpine
Description:

Hi team,
I am doing streaming replication and everything is going good but at fail
over case while coversion of old master to slave I am using pg_rewind it is
showing error like wal_log_hints is in on but I have enabled that and
restart the server also if I do it on 10 times 3 times it's passing 7 times
it's failing may I know what am I missing?

I think you have to have had wal_log_hints for the entire time that WAL
was generated for your rewind timespan. Does that help?

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.

Yes but the wal_log_hints will be in on from the begining if the server.

On Wed, 19 May, 2021, 8:12 pm Bruce Momjian, <bruce@momjian.us> wrote:

Yes but the wal_log_hints will be in on from the begining of the server.
Which means it is in on when I am starting the server.

On Wed, 19 May, 2021, 9:21 pm Mohan Nagandlla, <nagandllamohan@gmail.com>
wrote:

On Wed, May 19, 2021 at 09:22:45PM +0530, Mohan Nagandlla wrote:

Yes but the wal_log_hints will be in on from the begining of the server.
Which means it is in on when I am starting the server.

Please describe more precisely the flow of what you are doing here.
It looks like what you are missing is that wal_log_hints needs to be
enabled on the target server, not the source. Please note that this
needs to be reflected in the control file of the target cluster (fetch
for "wal_log_hints setting" in the output generated by
pg_controldata).
--
Michael

Yes
I just enabled the streaming replication among 2 postgres instance and I
just enabled the option wal_log_hints at the initial level of the streaming
replication flow. So both master and stand by are configured with
wal_log_hints as on. The streaming is going good . All the DB data
replication is happening successfully.

These are the config options I have enabled in master before connecting to
the slave

wal_log_hints = on
restart_command ='cp /path to the directory/%f %p'
archive_command='.......some command'
promote_trigger_file='/tmp/switch.txt'
recovery_target_timeline='latest'

After enabled the slave the same options are enabled in slave also.
Slave also running with wal_log_hints = on and all options....

Now coming to the manual fail over case I have converted the slave as
master by using promote_trigger_file it was done successfully. The slave is
accepting the read and write also. And now I am coverting the old master as
slave for that I have done everything in new master and now by using
pg_rewind I am syncing the data time lines but that command is giving the
error like wal_log_hints should be in on . But I have enabled this option
from beginning onwards still it is in on but in target server and source
server.

On Thu, 20 May, 2021, 5:45 am Michael Paquier, <michael@paquier.xyz> wrote:

On Thu, May 20, 2021 at 07:13:26AM +0530, Mohan Nagandlla wrote:

Now coming to the manual fail over case I have converted the slave as
master by using promote_trigger_file it was done successfully. The slave is
accepting the read and write also. And now I am coverting the old master as
slave for that I have done everything in new master and now by using
pg_rewind I am syncing the data time lines but that command is giving the
error like wal_log_hints should be in on . But I have enabled this option
from beginning onwards still it is in on but in target server and source
server.

What does pg_controldata tell you when run on both clusters? That's
what pg_rewind looks after in its sanity checks.
--
Michael

This is the pg_controldata on both

On current master

pg_control version number: 1300
Catalog version number: 202007201
Database system identifier: 6963982442470461472
Database cluster state: in production
pg_control last modified: Thu May 20 02:04:33 2021
Latest checkpoint location: 0/C000110
Latest checkpoint's REDO location: 0/C0000D8
Latest checkpoint's REDO WAL file: 00000002000000000000000C
Latest checkpoint's TimeLineID: 2
Latest checkpoint's PrevTimeLineID: 2
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:505
Latest checkpoint's NextOID: 24584
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 477
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 505
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Thu May 20 02:04:33 2021
Fake LSN counter for unlogged rels: 0/3E8
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: on
max_connections setting: 100
max_worker_processes setting: 8
max_wal_senders setting: 10
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float8 argument passing: by value
Data page checksum version: 0
Mock authentication nonce:
a6265e4a2c8ebb6ecaa69ef02b9a927c4e6dd2a8066f19068e541fd6d9dabef8

On slave

Time of latest checkpoint: Wed May 19 12:59:24 2021
Fake LSN counter for unlogged rels: 0/3E8
Minimum recovery ending location: 0/801AC68
Min recovery ending loc's timeline: 2
Backup start location: 0/5000060
Backup end location: 0/801AC68
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: on
max_connections setting: 100
max_worker_processes setting: 8
max_wal_senders setting: 10
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float8 argument passing: by value
Data page checksum version: 0
Mock authentication nonce:
a6265e4a2c8ebb6ecaa69ef02b9a927c4e6dd2a8066f19068e541fd6d9dabef8

On Thu, 20 May, 2021, 7:49 am Michael Paquier, <michael@paquier.xyz> wrote: