Hypatia PostgreSQL Restoration

This is not a technical restoration document -- for details on this please see HypatiaPostgresInstallation -- but is an important prerequisite for performing any restoration!


If restoration is need for reasons of sudden hardware failure, your best choice of backup is probably the HypatiaPostgresReplication slave as this data will be most up-to-date. However you should consider carefully the implications of restoring any data onto the (or an) infdb server.

It is almost always OK to restore a ''read-only'' infdb server. Indeed the HypatiaPostgresReplication slave drinfdb is a permanently-available infdb clone which provides a safe and up-to-date copy of the database. The danger is in allowing write access to a new copy, at which point future reconciliation (effectively conflict resolution) will become very difficult indeed.

The most critical decision is the one to enable writes, at which point the new server and its data should be declared the master and treated with the same deference as the original. This might be impossible or inadvisable if any of the normal infrastructure is missing:

  • availability (RAID1)
  • integrity (split os/data/log/backup partitions)
  • replication (at least one slave)
  • backups (hourly cron, nightly mirror)

if any of this is missing, you should consider carefully the risk/benefit balance.


Assuming '''uncorrupted data''', the preferred data sources are of course in order of recency, so:

  1. the infdb server disk itself
  2. data from ''synchronous'' replication slaves
  3. data from asynchronous replication slaves
  4. data from the most recent ''hourly'' backup
  5. data from the most recently ''hourly'' backup as taken by the replication slave
  6. as 4-5 but for previous ''hourly'' or ancestor backups.
  7. as 4 or 6 but from the mirror server.

It is ''very'' unlikely given our usage patterns for the school database that an asynchronous replicated slave will be more than a few seconds out of date, and then only if heavy writes are taking place when power is removed from the master. So in most circumstances, if ''read-write'' access must be restored ''urgently'', 1-3 above can be assumed to be equivalent.


In either case, data consistency and recency is the most important consideration. If promoting a slave, you should ''take steps to assure yourself that replication was working'' prior to failure of the master. If promoting a backup (dump) on a new server, think carefully about whether you're content to lose, in the worst case, all changes since said backup.

To promote a slave

Follow the Promotion procedure, then the Moving the Main Infdb server steps where appropriate.

To promote from backup

Simply follow the Moving the Main Infdb server procedure, taking the contents from the appropriate backup .sql.gz file / mirror partition rather than (necessarily) from the old server.

== Corruption & Reconciliation

Assuming '''corrupted data''', obviously the most desirable data is the most recent uncorrupted copy. Determining this is the tricky part. in general, PostgreSQL does not arbitrarily corrupt data on disk: experience has proven its atomicity to be very reliable. It's far more more likely a rogue finger or application causes systematic damage (but leaves data consistent). This is likely to require recovery from an hourly backup and manual line-by-line "replay" using the query audit logs. Unfortunately, careful diffs with knowledge of the data model are likely to be the only sensible way to achieve this.

Of course hardware failures unknown could cause data corruption on disk and, again, consensus of data (and logs) is almost certainly the only way to achieve some level of confidence of any backup.

-- TimColles - 27 Nov 2018

Topic revision: r1 - 27 Nov 2018 - 13:55:57 - TimColles
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
This Wiki uses Cookies