One of my favorite differences about OmniTI compared to other companies I have seen is that we aren't afraid to use advanced technology to service our clients needs just because it isn't mainstream. I recently needed to restore a table a client inadvertently truncated (aka deleted all data) before generating a critical report.
The table in question resides in a Postgres database that is about 4TB which restore testing have shown takes up to 48 hours to restore. As a workaround, we have continuous archiving of WAL enabled to a remote server with online backups taken with pg_start/stop_backup and daily ZFS snapshots alongside our normal PITR setup.
Putting Everything Together
So, with a customer crises at hand, and no one looking forward to a 48 hour restore process, I identified the tools at my disposal and mapped out the plan to restore:
- 1. Restore data directory and xlog directory backup using ZFS clone (which automatically creates the mount-point). The clone command was used here because a copy of the previous night’s backup existed on the server.
- ·
zfs clone data/set/pgdata@lastfull_20170523001500 - ·
data/set/pgdata_lastfull_20170523001500_restore - ·
zfs clone rpool/xlog@lastfull_20170523001500 rpool/xlog_lastfull_20170523001500_restore
- ·
- 2. Restore the necessary archived wal logs to the local server in a separate directory.
- 3. Create the recovery.conf file like so :
- ·
standby_mode = 'on' - ·
restore_command = '/data/path_to_wal/%f "%p"' - ·
recovery_target_action = pause - ·
recovery_target_time = '2017-05-23 11:00:00' - ·
recovery_target_inclusive = false
- ·
- 4. Edit postgresql.conf - changing port and archive details like so:
- ·
port = 5434 - ·
shared_buffers = 64MB - ·
archive_mode = off - ·
max_wal_senders = 3 - ·
#archive_command = '/omnipitr-archive "%p"'
- ·
- 5. Changed pg_xlog symlink to point to the restored xlog directory - this was
crucial as I was restoring the backup on the prod server and did not want to
cause a data corruption issue. Note: ZFS snapshots are file system snapshots, so
when restoring a zfs backup the snapshot will be identical to the original
file system that was backed up.
- ·
rm -rf /wal/symlink/in/restored/backup/cause_it_is/pointing/to/prod - ·
ln -s /point/to/restored/wal/backup/directory
- ·
Sometimes it’s never that simple
With my plan in place, I was ready to save the day. I restored the database and checked the table, but it was empty! What happened? I had gone past the truncate ..but how could that be? The client said it was truncated a few minutes after my recovery_target_time.
So where is my data? Hmm.
Logging is your friend.
I started to review the pg_logs to identify when the truncate occurred. To my surprise, the client was not 100% accurate in the timing of when the truncate was issued, the logs told the true story. Well, thanks! Now I have to start over.
Tools of the Trade.
But wait, I am using ZFS snapshots; so I am not going to be here all night -- re-creating a clone takes but a few seconds. Once the correct timing of the truncate was identified, I was able to destroy the clone and recreate it, pause recovery and voilà - there was my table with the data I needed. I pg_dumped it out and restored it into the prod database with a new name, and just like that the client was pleased that he was able to create the report sooner than expected.
Conclusion
Of course, we could have solved this without ZFS, but I shudder to think about two day restore times, and can you imagine if after two days you found out that the recovery time you were given was incorrect, and then having to wait another two days to try again? Instead I was able to solve this problem in just a few hours; sure still a solid time investment, but much better than most of the alternatives. You can also imagine how happy the client was to find out they weren't going to have to wait days to receive their data. While I know a lot of companies don't run ZFS, but I am thankful we have the option to use quality tools to help save everyone both time and stress.
