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.

The next step is to get PITR running...

recovery_target_time='2018-01-06 16:22:00'

This 2019, not 2018. If you look in the server log file, you will probably find the recovery stopped as soon as it could (as soon as it reached a consistent state), because it could not stop as soon as you told it to, being a year late for that.