I'm testing and trying to figure out how the backup and recovery strategy in PostgreSQL 11 works but it does not work as expected. When I recover the backup, I don't get the correct state. I get any state after a given timestamp. In detail:
I have this setup in database:
wal_level = replica
max_wal_senders = 10
archive_mode = on
I have executed the following commands:
postgres=# select pg_create_physical_replication_slot('slot1');
pg_basebackup -D /media/extern/postgresql_basebackup/
pg_receivewal -D /media/extern/postgresql_archive -S slot1 -v
pg_receivewal is writing into postgresql_archive, this seems to work.
- then I insert something into the database, let's say at 16:21.
- I wait some minutes, let's say until 16:27 and delete all the insert.
Then I want to recover the inserts by doing...
service postgresql stop
(do I need to stop pg_receivewal as well)?
# mv main main.before_recovery
# cp -rp /media/extern/postgresql_basebackup main
nano /var/lib/postgresql/11/main/recovery.conf
I add the following content
restore_command = 'cp -r /media/extern/postgresql_archive/%f %p'
recovery_target_time='2018-01-06 16:22:00'
Then I execute
# chown postgres:postgres recovery.conf
# chmod 700 recovery.conf
service postgresql start
After startup, the file changes to recovery.done, but the state in database is wrong. It didn't restore the data. The database is still empty, so the PITR didn't work, why?
What should I do In case if everything was successful after recovery?. I'm unsure about these questions:
- Creating a new slot2 by executing pg_create_physical_replication_slot('slot2'); and streaming from this slot by pg_receivewal -D /media/extern/postgresql_archive -S slot2 -v?
- Doing a pg_receivewal -D /media/extern/postgresql_archive -S slot1 -v again?
- Do I have to delete anywhen any data in postgresql_archive? (-> Probably not)
- When do I have to create another base backup?
- When and why do I execute select pg_switch_wal(); in this recovery concept?
- How do I deal with new 'timelines' after recovery? I understand the concept of the timelines, but I'm unsure how I should proceed with it after recovery.
[UPDATE]
jjanes pointed out, that the date is wrong. I corrected it, but I still get an empty database after recovery. I tried these steps after I recovered the database, which is now empty:
2019-01-08, 18:50: create a new base backup from this empty database to have a good starting point.
2019-01-08, 18:55 created slot1
pg_receivewal -D /media/extern/postgresql_archive -S slot1 -v
2019-01-08, 18:57 insert
2019-01-08, 19:38 delete
service postgresql stop
# mv main main.before_recovery
# cp -rp /media/extern/postgresql_basebackup main
# chmod 700 main
# nano /var/lib/postgresql/11/main/recovery.conf
restore_command = 'cp -r /media/extern/postgresql_archive/%f %p'
recovery_target_time='2019-01-08 19:20:00'
# chown postgres:postgres recovery.conf
# chmod 700 recovery.conf
# service postgresql start
(btw: I saw that it starts by itself new timelimes after recovery.)
[UPDATE]
I activated in postgresql.conf
checkpoint_flush_after = 256kB
checkpoint_timeout = 30s
I tried to restore the database by using the previous method but without recovery_target_time and without any DELETE statement. I just wanted to look if the current full state can be rebuilt at all. Additionally I renamed the current wal file 00000007000000000000002D.partial to 00000007000000000000002D after stopping the database and before recovering the data.
I'm currently not sure which of these things worked, but at least, the non-PITR recovery seem to work, because I was able to restore the latest data by installing the (empty) base backup and the wal files. At least a step into the right direction, but this only works when you don't miss any shipped wal files. Here, question 2 comes into play: What is the correct workflow to handle the "next shipping iteration".
At first, you can't start pg_receivewal -D /media/extern/postgresql_archive -S slot1 -v
again without deleting the shipped WALs in the external folder. Otherwise it would not start shipping the logs and it would abort with connection errors. This means that you can throw away your old base backup, because when you try the recover again with the old base backup, you would miss all the wal logs between base backup and last recovery point (because you deleted them). That also means that you have to create a new base backup immediately from the recovery point state. When you start log shipping again, you have no "gap" then between the new base backup and the wal files.
[UPDATE]
I got PITR running with the same approach, so I was able to "undo" a delete now. After recovery and service postgresql start
, the database remains in read only mode. I had to execute select pg_wal_replay_resume();
to make it RW again. As already mentioned, I can't do WAL shipping again. When I don't modify the archive folder and try to restart the WAL to ship inside the same folder, I get the following exception:
pg_receivewal: could not send replication command "START_REPLICATION": ERROR: requested starting point 0/34000000 on timeline 11 is not in this server's history DETAIL: This server's history forked from timeline 11 at 0/3310ADA8.
Sure, I could do log shipping to a new archive folder or something, but I guess this is not the professional way to do that. What is the common used strategy to resume log shipping after recovery?