I just made a huge mistake by doing a rails command on my production web server which reset the postgres database – I believe the database is dropped and reset with just the schema
I have a backup from a day ago, but there's still a lot of critical data I'll be missing from the last 24 hours including transaction data. I'm really confused on what to do. I can't recover everything by just going through the HTTP logs as there are some background jobs that ran as well.
I'm running postgres 9.6, I've looked at past stackoverflow posts, but I don't know if the data is still there somewhere.
I stopped postgres as soon as I found out(maybe 10 mins after I issued the command)
There are some WAL files in the "pg_xlog" directory, is it possible to recover from them?
I also saw this post: https://dba.stackexchange.com/a/49118 that says there's a way to inspecting the pageinspect module – is that worth looking into?
Is there any way to get a sql transaction history or something from the last 24 hours?
I wanted to ask this question first before restarting postgres and maybe killing whatever chance I had.
I already asked the question here https://stackoverflow.com/questions/42962303/dropped-database-with-rake-dbschemaload-any-way-to-recover-data and suggested to post here.
There is no pg_base_backup. Simply a pg_dump from 24 hours ago.
Best Answer
Hire a professional.
If this is important production data that needs to be recovered quickly, your best strategy is to get an expert in as soon as possible to get the work done.
The work you will be focusing on is creating processes and procedures to make sure this doesn't happen in the future. And convincing management that the likely hood of it happen again based on those processes and procedures are as low as possible.