Re: [ADMIN] Recovery of corrupted database
| От | Bear Giles |
|---|---|
| Тема | Re: [ADMIN] Recovery of corrupted database |
| Дата | |
| Msg-id | CALBNtw5t-ox78fv3uU4hT++_Mt9udxzBkzDe6wR6fM7EBam_Tg@mail.gmail.com обсуждение исходный текст |
| Ответ на | [ADMIN] Recovery of corrupted database (Rikardo Tinauer <rikardo.tinauer@eba.si>) |
| Список | pgsql-admin |
Take this with a huge grain of salt but you can't back up the database files from a running server - too much is cached in memory. You must shut it down first. That's one reason the usual advice is to explicitly exclude the database directory from backups and perform backups using pg_dump and pg_restore.
There's a big difference between restoring from backups and recovering from a crash. The latter is abrupt - everything is caught in a moment in time. Backups take time so files will be backed up at a slightly different times. That means transactions may be caught midway, e.g., between writing to the data file and updating the log. That makes it much harder to recover.
The encryption probably had the same effect as backing up a running database. It didn't capture anything cached in memory and it wasn't a clean snapshot at a single instance. The results aren't a valid image.
This seems especially likely since you're seeing a problem in block 0. I don't know about PostgreSQL but it's common to store the index to other information in the first block and it's always kept in memory with only periodic flushes to disk.
I'm sure others will be able to give you better advice on what, if anything, you can do. I'm not very hopeful though - I hope they did regular and frequent backups.
(Aside: would this be a use-case for a replicated read-only server? Or would the primary database crash in a way that would also corrupt the secondary server?)
On Wed, Jul 19, 2017 at 7:27 AM, Rikardo Tinauer <rikardo.tinauer@eba.si> wrote:
Our client got virus on database server encrypting the database. They paid and got files decrypted but database wasn’t able to start.We got the following error:2017-07-19 10:31:11 CEST LOG: database system was shut down in recovery at 2017-07-19 10:29:00 CEST2017-07-19 10:31:11 CEST LOG: invalid magic number B549 in log segment 000000010000002E00000015, offset 02017-07-19 10:31:11 CEST LOG: invalid primary checkpoint record2017-07-19 10:31:11 CEST LOG: invalid magic number B549 in log segment 000000010000002E00000015, offset 02017-07-19 10:31:11 CEST LOG: invalid secondary checkpoint record2017-07-19 10:31:11 CEST PANIC: could not locate a valid checkpoint record2017-07-19 10:31:11 CEST LOG: shutdown at recovery target2017-07-19 10:31:11 CEST LOG: shutting down2017-07-19 10:31:11 CEST LOG: database system is shut downWe got past that with executing the “pg_resetxlog -f DATADIR” command.Now database starts but we cannot connect to it, we get the following error:2017-07-19 14:11:05 CEST ERROR: invalid page in block 0 of relation global/12369Can anyone help?Lp,Rikardo Tinauer----------------------------------------------------------
EBA, d.o.o., Ljubljana
Litostrojska 40, SI-1000 Ljubljana
Tel: +386 (0)590 98 890
Email: rikardo.tinauer@eba.si
Web: http://www.ebadms.com
-----------------------------------------------------------
В списке pgsql-admin по дате отправления: