postgres database recovery - version 9.2

Question

I get hacked by several tables dropping for the postgres database.

It is on a cloud ECS environment. During the hack, I realized the attack and created a snapshot but unfortunately I still have several important tables dropped.

But I have the database XLOG file not be deleted. And besides I have the dump.sql for the data several months ago.

Please help me find the solutions that recover the data if possible. Thanks in advance.

Postgres 9.2 does not have xlogdump tool, I tried to build it https://github.com/snaga/xlogdump However it does not have very detailed document, I did not quite understand the steps.

If I upgrade the postgres server from 9.2 to 9.5 using the tool like pg_upgrade, will the xlog content be compatible with 9.5 — Ruiqing Xu
– Ruiqing Xu, Commented Jan 25, 2024 at 16:28

Richard Huxton · Accepted Answer · 2024-01-23 07:09:06Z

1

Bad news I'm afraid. You have just learned why it is important to have regular, tested backups.

The transaction logs are not going to be much use to you without a valid filesystem-level backup that they overlap. Once you manage to get the tool to compile you will only be able to come up with changes to a copy of the tables you don't have. You will also have to try and untangle possible other changes by the hackers. Dropping tables might just have been the last thing they did.

You can spend a lot of time or money (or both) on this and get what is probably not a valid database back. If it is that important to you I would join the mailing lists and start a conversation there. Or hire one of the PostgreSQL consultancies listed on the website.

You may also be required (depending on where in the world you are and what information this database held) to report this as a breach of data protection laws.

answered Jan 23, 2024 at 7:09

Richard Huxton

23.6k5 gold badges43 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurenz Albe Over a year ago

Well, if the data were important, they wouldn't have used 9.2 in 2024.

Ruiqing Xu Over a year ago

Hi Richard...Thanks for the message. I do need some help either the advice or any support. I do appreciate that.

Collectives™ on Stack Overflow

postgres database recovery - version 9.2

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related