i was wondering if there is a way to manually manipulate the wal location
from where a pglogical slave resumes the replication process.
Let's imagine the following scenario. There is a pglogical slave running on
an operating system that allows hourly snapshots. If at 08:00 we decide we
need to go back in time to 04:00 we can simply shut down the database and
restore the 04:00 snapshot. When this happens, this database will be back to
04:00 meaning all data etc will be as they were at that time. However, when
querying the pg_replication_slots of the master, the wal position on
replication slot used for this pglogical slave will stay at where it was at
08:00 just before this database was shut down (for the restore to happen).
So, when this database gets back online with the 04:00 data, it will still
try to connect to the master to resume the replication. Based on the fact
that the replication slot will have the location of 08:00, the slave will
succesfully reconnect to the master but will start applying data from 08:00
onwards, basically losing 4h of transactions.
Is there any way, that the wal position can be manipulated so that this does
not happen and the replication slot can be 'put behind' in time, to match
the 04:00 timestamp?
Thank you in advance for any clarification
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
