... | ... | @@ -773,11 +773,10 @@ a script from there to copy the WAL files from backup. |
|
|
|
|
|
touch /var/lib/postgresql/13/main/recovery.signal
|
|
|
|
|
|
7. Then you need to create a
|
|
|
`/etc/postgresql/13/main/conf.d/recovery.conf` file that will
|
|
|
tell postgres where to find the WAL files. At least the
|
|
|
`restore_command` need to be specified. Something like this
|
|
|
should work:
|
|
|
8. Then you need to create add a configuration parameter in
|
|
|
`/etc/postgresql/13/main/postgresql.conf` that will tell postgres where to
|
|
|
find the WAL files. At least the `restore_command` need to be
|
|
|
specified. Something like this should work:
|
|
|
|
|
|
restore_command = '/usr/local/bin/pg-receive-file-from-backup $OLDSERVER $CLUSTERNAME.WAL.%f %p'
|
|
|
|
... | ... | @@ -788,7 +787,7 @@ a script from there to copy the WAL files from backup. |
|
|
* `$CLUSTERNAME` should be replaced by the previous cluster name
|
|
|
(e.g. `main`, generally)
|
|
|
|
|
|
You can specify a specific recovery point in the `recovery.conf`,
|
|
|
You can specify a specific recovery point in the `postgresql.conf`,
|
|
|
see the [upstream documentation](https://www.postgresql.org/docs/13/runtime-config-wal.html#RUNTIME-CONFIG-WAL-RECOVERY-TARGET) for more information. This,
|
|
|
for example, will recover `meronense` from backups of the `main`
|
|
|
cluster up to October 1st, and then start accepting connections
|
... | ... | @@ -799,18 +798,7 @@ a script from there to copy the WAL files from backup. |
|
|
recovery_target_time = '2022-10-01T00:00:00+0000'
|
|
|
recovery_target_action = 'promote'
|
|
|
|
|
|
Also make sure the file is owned by postgres:
|
|
|
|
|
|
$EDITOR /etc/postgresql/13/main/conf.d/recovery.conf
|
|
|
chown postgres /etc/postgresql/13/main/conf.d/recovery.conf
|
|
|
|
|
|
NOTE: a previous version of this guide was suggesting using
|
|
|
`/var/lib/postgresql/9.6/main/recovery.conf` instead, but newer
|
|
|
upstream documentation suggest editing the `postgresql.conf` file
|
|
|
directly. The [upstream guide](https://www.postgresql.org/docs/11/recovery-config.html) wasn't quite clear on where the
|
|
|
`recovery.conf` file went in the first place...
|
|
|
|
|
|
8. Then start the server and look at the logs to follow the recovery
|
|
|
9. Then start the server and look at the logs to follow the recovery
|
|
|
process:
|
|
|
|
|
|
service postgresql start
|
... | ... | @@ -856,13 +844,13 @@ a script from there to copy the WAL files from backup. |
|
|
|
|
|
The server is now ready for use.
|
|
|
|
|
|
9. Remove the temporary SSH access on the backup server, either by
|
|
|
10. Remove the temporary SSH access on the backup server, either by
|
|
|
removing the `.more` key file or restoring the previous key
|
|
|
configuration:
|
|
|
|
|
|
rm /etc/ssh/userkeys/torbackup.more
|
|
|
|
|
|
10. re-enable Puppet:
|
|
|
11. re-enable Puppet:
|
|
|
|
|
|
puppet agent -t
|
|
|
|
... | ... | @@ -1080,31 +1068,34 @@ You should more likely use the [direct backup recovery](#direct-backup-recovery) |
|
|
|
|
|
8. Make sure the `pg_xlog` directory doesn't contain any files.
|
|
|
|
|
|
9. Then you need to create a `recovery.conf` file in
|
|
|
`/var/lib/postgresql/9.6/main` that will tell postgres where to
|
|
|
9. Then you need to create add a configuration parameter in
|
|
|
`/etc/postgresql/13/main/postgresql.conf` that will tell postgres where to
|
|
|
find the WAL files. At least the `restore_command` need to be
|
|
|
specified. Something like this should work:
|
|
|
|
|
|
restore_command = 'cp /var/lib/postgresql/restore/subnotabile/main.WAL.%f %p'
|
|
|
|
|
|
You can specify a specific recovery point in the `recovery.conf`,
|
|
|
see the [upstream documentation](https://www.postgresql.org/docs/11/recovery-target-settings.html) for more information.
|
|
|
see the [upstream documentation](https://www.postgresql.org/docs/13/runtime-config-wal.html#RUNTIME-CONFIG-WAL-RECOVERY-TARGET) for more information.
|
|
|
|
|
|
10. Tell the database it is okay to restore from backups:
|
|
|
|
|
|
touch /var/lib/postgresql/13/main/recovery.signal
|
|
|
|
|
|
10. Then start the server and look at the logs to follow the recovery
|
|
|
11. Then start the server and look at the logs to follow the recovery
|
|
|
process:
|
|
|
|
|
|
service postgresql start
|
|
|
tail -f /var/log/postgresql/*
|
|
|
|
|
|
11. re-enable Puppet, which should clean up the extra SSH key and
|
|
|
12. re-enable Puppet, which should clean up the extra SSH key and
|
|
|
firewall rules:
|
|
|
|
|
|
puppet agent -t
|
|
|
|
|
|
make sure it's okay in `/etc/ssh/userkeys/root` and `iptables -L`.
|
|
|
|
|
|
When the restore succeeds, the `recovery.conf` file will be renamed to
|
|
|
`recovery.done` and you will see something like:
|
|
|
When the restore succeeds, in the logs you will see something like:
|
|
|
|
|
|
2019-08-12 21:36:53.453 UTC [16901] LOG: selected new timeline ID: 2
|
|
|
2019-08-12 21:36:53.470 UTC [16901] LOG: archive recovery complete
|
... | ... | @@ -1124,8 +1115,6 @@ If you find the following error in the logs: |
|
|
It's because postgres cannot find the WAL logs to restore from. There
|
|
|
could be many causes for this, but the ones I stumbled upon were:
|
|
|
|
|
|
* wrong location for `recovery.conf` (it's in `/var`, not `/etc`),
|
|
|
probably the case if you don't see an error from `cp`
|
|
|
* wrong permissions on the archive (put the WAL files in `~postgres`,
|
|
|
not `~root`)
|
|
|
* wrong path or pattern for `restore_command` (double-check the path
|
... | ... | |