Let's sum up the process so far. We have adjusted the postgresql.conf file (wal_level, max_wal_senders, archive_mode, and archive_command) and we have allowed for the pg_basebackup command in the pg_hba.conf file. Then, the database was restarted and a base backup was successfully produced.
Keep in mind that base backups can only happen while the database is fully operational—only a brief restart to change the max_wal_sender and wal_level variables is needed.
Now that the system is working properly, we might face a crash that we will want to recover from. Therefore, we can perform PITR to restore as much data as possible. The first thing we've got to do is take the base backup and put it in the desired location.
Given the preceding filesystem layout, we might want to do something like the following:
cd /some_target_dir
cp -Rv * /data
We're assuming that the new database server will be located in the /data directory. Make sure that the directory is empty before you copy the base backup over.
In PostgreSQL 12, some things have changed: in older versions, we had to configure recovery.conf to control the behavior of a replica or PITR in general. All config settings to control those things have been moved to the main configuration file, postgresql.conf. If you are running old setups it is, therefore, time to change to the new interfaces to make sure that your automation is not broken.
So, let's see how to configure the replay process. Try to put restore_command and recovery_target_time into postgresql.conf:
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2019-11-05 11:43:12'
After fixing the postgresql.conf file, we can simply start up our server. The output might look as follows:
waiting for server to start....2019-11-05 10:35:17.130 CET [106353] LOG: starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.2.1 20190827 (Red Hat 9.2.1-1), 64-bit
2019-11-05 10:35:17.130 CET [106353] LOG: listening on IPv6 address "::1", port 5433
2019-11-05 10:35:17.130 CET [106353] LOG: listening on IPv4 address "127.0.0.1", port 5433
2019-11-05 10:35:17.130 CET [106353] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433"
2019-11-05 10:35:17.141 CET [106361] LOG: database system was interrupted; last known up at 2019-11-05 10:25:16 CET
cp: cannot stat '/tmp/archive/00000002.history': No such file or directory
2019-11-05 10:35:17.154 CET [106361] LOG: entering standby mode
2019-11-05 10:35:17.168 CET [106361] LOG: restored log file "000000010000000000000006" from archive
2019-11-05 10:35:17.170 CET [106361] LOG: redo starts at 0/60000D8
2019-11-05 10:35:17.170 CET [106361] LOG: consistent recovery state reached at 0/60001B0
2019-11-05 10:35:17.171 CET [106353] LOG: database system is ready to accept read only connections
cp: cannot stat '/tmp/archive/000000010000000000000007': No such file or directory
2019-11-05 10:35:17.175 CET [106394] LOG: started streaming WAL from primary at 0/7000000 on timeline 1
done
server started
When the server is started, there are a couple of messages to look for to ensure that our recovery works perfectly. consistent_state_reached is the most important one to look for. Once you have reached this point, you can be sure that your database is consistent and is not corrupted. Depending on the timestamp you have chosen, you might have lost some transactions at the end (if desired) but in general, your database is consistent (there are no key violations and so on).
If you have used a timestamp in the future, PostgreSQL will complain that it could not find the next WAL file and terminate the replay process. If you are using a timestamp between the end of the base backup and somewhere before the crash, you will, of course, not see this kind of message.
Once the process is done, the server will successfully start up.