hi,
i run a webapp where lots of accounts are managing something. I do a nightly
backup of my database. Sometime some users want to have their account restored
from a backup days, weeks or months ago.
At the moment i use (multi-column) natural keys. So each and every table has
at least a column "account_name" (sometimes part of a multi-column primary
key). If i want to restore i use pg_restore and grep to filter out the lines
i need. It is not very sophisticated but it works.
Our new Databse design removes natural keys in favor of surrogate keys.
Now i still want to easily restore an account from a given dump file.
I can't use pg_restore and grep anymore as the hierarchical structure of the
tables can't be easily greped as i dont have the account_name in every table
anymore.
I came across the following ideas:
1. Scripting pg_restore output
- use a perl script which greps for the base table (accounts)
- grep the member table for foreign keys to account_id
- remember all member_ids
- grep the "entries" table for alle memorized member_ids
- and so on for each hierarchical level.
2. PITR
I could use PITR using a backup database replying it to the timestamp where i
want to restore an account.
Then i can select all entries with regular sql and replay them in the original
database.
Nice side effect: more backups are made
3. Install all backup databases
I could install my dumps on a backup server and name the databases according
to their backup date.
advantage: i could connect my app directly to the backup database to get a
historical view.
disadvantage: This needs to much disk space.
Are their other solutions?
What are you doing if you want to restore only some specific and hierarchical
data from a backup?
kind regards,
Janning
PS: i recently used oracle for the first time in a project. Postgresql is so
much better! I do not want to start a discussion about this, just wanted to
cheer all those pg developers. Great job! I love it even more after using
oracle.