A question about possible recovery inconsistency
;
pg_create_restore_point
-
0/790
(1 row)
+ cat 00010006.0028.backup
START WAL LOCATION: 0/628 (file 00010006)
STOP WAL LOCATION: 0/6000100 (file 00010006)
CHECKPOINT LOCATION: 0/660
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2023-10-10 15:07:37 UTC
LABEL: 2023-10-10 15:07:37.844015 + UTC m=+0.057006433
START TIMELINE: 1
STOP TIME: 2023-10-10 15:07:38 UTC
STOP TIMELINE: 1
2023-10-10 15:07:43.451 UTC [128] LOG: starting point-in-time
recovery to "2023-10-10 15:07:37"
2023-10-10 15:07:43.525 UTC [128] LOG: restored log file
"00010006" from archive
2023-10-10 15:07:43.540 UTC [128] LOG: redo starts at 0/628
2023-10-10 15:07:43.677 UTC [128] LOG: consistent recovery state
reached at 0/6000100
2023-10-10 15:07:43.677 UTC [128] LOG: recovery stopping at restore
point "2023-10-10 15:07:37", time 2023-10-10 15:07:38.846288+00
2023-10-10 15:07:43.677 UTC [128] LOG: redo done at 0/728 system
usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.13 s
2023-10-10 15:07:43.677 UTC [125] LOG: database system is ready to
accept read-only connections
1. Why here (in experiment2.txt) redo done at 0/728 when recovery
target name "2023-10-10 15:07:37" is at 0/790?
I suppose 0/790 should be included, because
`recovery_target_inclusive=true` by default.
2. Is there any way to include a label into a base backup which I can
use as `recoverty_target_name`?
This is not clear from documentation
https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP
Is 'label' the name for `recovery_target_name` /
`pg_create_restore_point` is called by `pg_backup_start`?
3. Is there any way to get the latest time from a base backup which is
reachable and could be used as the value for `recovery_target_time`?
As a workaround for XX000 error I inserted one additional record into
the database, so a new WAL file is generated. Then I can use the t3
value for `recovery_target_time`.
This only works when archive_command/restore_command was configured.
But without them it seems I can not use the `recovery_target_time`
option. Is this true?
Thank you.
--
Eugen Konkov
DevOps Engineer, Planitar Inc.
+ (( i++ ))
+ (( i<=15 ))
+ '[' -n '' -o '!' -f /dbdata/postgres/recovery.conf ']'
+ pg_isready -h localhost
localhost:5432 - no response
+ sleep 1s
+ (( i++ ))
+ (( i<=15 ))
+ '[' -n '' -o '!' -f /dbdata/postgres/recovery.conf ']'
+ pg_isready -h localhost
localhost:5432 - no response
+ sleep 1s
+ (( i++ ))
+ (( i<=15 ))
+ '[' -n '' -o '!' -f /dbdata/postgres/recovery.conf ']'
+ pg_isready -h localhost
localhost:5432 - no response
+ sleep 1s
+ (( i++ ))
+ (( i<=15 ))
+ '[' -n '' -o '!' -f /dbdata/postgres/recovery.conf ']'
+ pg_isready -h localhost
localhost:5432 - no response
+ sleep 1s
+ (( i++ ))
+ (( i<=15 ))
+ '[' -n '' -o '!' -f /dbdata/postgres/recovery.conf ']'
+ pg_isready -h localhost
localhost:5432 - accepting connections
+ exit 0
+ test-table-create test-userdb-restore
+ local id=test-userdb-restore
+ docker exec test-userdb-restore psql -d userdb -U user_api -c 'CREATE TABLE
test (num SMALLINT);'
CREATE TABLE
+ test-table-write-seq test-userdb-restore 1 5
+ local id=test-userdb-restore
+ seq 1 5
+ xargs -n1 '-I{}' docker exec test-userdb-restore psql -d userdb -U user_api
-c 'INSERT INTO test (num) VALUES ({});'
xargs: warning: options --max-args and --replace/-I/-i are mutually exclusive,
ignoring previous --max-args value
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
++ docker exec test-userdb-restore date '+%Y-%m-%d %T'
+ t1='2023-10-10 14:48:40'
+ backup test-userdb-restore
+ local id=test-userdb-restore
+ docker exec test-userdb-restore backup
+ historylength=10
+ case "${CONTINOUS_BACKUP}" in
+ is_ready
+ '[' '' == -r ']'
+ WAIT_TIME=30
+ (( i=1 ))
+ (( i<=30 ))
+ '[' -n '' -o '!' -f /dbdata/postgres/recovery.conf ']'
+ pg_isready -h localhost
localhost:5432 - accepting connections
+ exit 0
+ echo '[backup]: Starting wal-g backup-push base backup...'
+ wal-g backup-push /dbdata/postgres
[backup]: Starting wal-g backup-push base backup...
INFO: 2023/10/10 14:48:41.133449 Calling pg_start_backup()
INFO: 2023/10/10 14:48:41.227876 Starting a new tar bundle
INFO: 2023/10/10 14:48:41.227935 Walking ...
INFO: 2023/10/10 14:48:41.229261 Starting part 1 ...
INFO: 2023/10/10 14:48:41.883465 Packing ...
INFO: 2023/10/10 14:48:41.886932 Finished writing part 1.
INFO: 2023/10/10 14:48:41.948913 Starting part 2 ...
INFO: 2023/10/10 14:48:41.94900
Re: A question about possible recovery inconsistency
>But why do you want to do that, if all that you have to do is specify "recovery_target = 'immediate'" to recover to the end of the backup? Because automation scripts do not know if transactions are available after some point in time or not. But automation scripts know that backup was completed successfully at that point. For example: We want to provide time to recover the database. 1. Base backup restored, wal files are applied successfully if there is a transaction. 2. Base backup restored, wal files are not applied successfully, even if we have the correct wal file after target time. eg. this wal file was created with help: pg_create_restore_point / pg_switch_wal It looks inconsistent, because we can restore save archive by name, but we can not restore it by time, even if this time is less when the named point was created. As workaround we just insert a fake record into database, but this looks very questionable: Why do we need to insert more records into database after successful backup?? On Wed, Oct 11, 2023 at 3:45 AM Laurenz Albe wrote: > > On Tue, 2023-10-10 at 11:46 -0400, Eugen Konkov wrote: > > [wants to avoid > > FATAL: recovery ended before configured recovery target was reached > > that is issued in v13 and later] > > > > 1. Why here (in experiment2.txt) redo done at 0/728 when recovery > > target name "2023-10-10 15:07:37" is at 0/790? > > I suppose 0/790 should be included, because > > `recovery_target_inclusive=true` by default. > > Because there was no transaction at 0/790. > > > 2. Is there any way to include a label into a base backup which I can > > use as `recoverty_target_name`? > > This is not clear from documentation > > https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP > > Is 'label' the name for `recovery_target_name` / > > `pg_create_restore_point` is called by `pg_backup_start`? > > No. > > > 3. Is there any way to get the latest time from a base backup which is > > reachable and could be used as the value for `recovery_target_time`? > > As a workaround for XX000 error I inserted one additional record into > > the database, so a new WAL file is generated. Then I can use the t3 > > value for `recovery_target_time`. > > This only works when archive_command/restore_command was configured. > > But without them it seems I can not use the `recovery_target_time` > > option. Is this true? > > Perhaps you could use the time from the "backup" file in the WAL archive, > not sure. > > But why do you want to do that, if all that you have to do is specify > "recovery_target = 'immediate'" to recover to the end of the backup? > > Yours, > Laurenz Albe -- Eugen Konkov DevOps Engineer, Planitar Inc. M. 416-276-1715 ekonk...@planitar.com | goiguide.com 560 Parkside Drive, Unit 401 Waterloo, ON, Canada N2L 5Z4
