pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRecovery of corrupted database
3+ messages / 3 participants
[nested] [flat]
* Recovery of corrupted database
@ 2017-07-19 13:27 Rikardo Tinauer <rikardo.tinauer@eba.si>
2017-07-19 16:01 ` Re: Recovery of corrupted database Samed YILDIRIM <samed@reddoc.net>
2017-07-19 16:01 ` Re: Recovery of corrupted database Bear Giles <bgiles@coyotesong.com>
0 siblings, 2 replies; 3+ messages in thread
From: Rikardo Tinauer @ 2017-07-19 13:27 UTC (permalink / raw)
To: pgsql-admin
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 CEST
2017-07-19 10:31:11 CEST LOG: invalid magic number B549 in log segment 000000010000002E00000015, offset 0
2017-07-19 10:31:11 CEST LOG: invalid primary checkpoint record
2017-07-19 10:31:11 CEST LOG: invalid magic number B549 in log segment 000000010000002E00000015, offset 0
2017-07-19 10:31:11 CEST LOG: invalid secondary checkpoint record
2017-07-19 10:31:11 CEST PANIC: could not locate a valid checkpoint record
2017-07-19 10:31:11 CEST LOG: shutdown at recovery target
2017-07-19 10:31:11 CEST LOG: shutting down
2017-07-19 10:31:11 CEST LOG: database system is shut down
We 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/12369
Can 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
-----------------------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Recovery of corrupted database
2017-07-19 13:27 Recovery of corrupted database Rikardo Tinauer <rikardo.tinauer@eba.si>
@ 2017-07-19 16:01 ` Samed YILDIRIM <samed@reddoc.net>
1 sibling, 0 replies; 3+ messages in thread
From: Samed YILDIRIM @ 2017-07-19 16:01 UTC (permalink / raw)
To: Rikardo Tinauer <rikardo.tinauer@eba.si>; pgsql-admin
<div>Hi Rikardo,</div><div> </div><div>I assume that you do not have any base backup, archive or replicated Postgres.</div><div> </div><div>Which Postgres version do you use? What is your operating system? Is It x64?</div><div> </div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>19.07.2017, 18:20, "Rikardo Tinauer" <rikardo.tinauer@eba.si>:</div><blockquote type="cite"><div>Our client got virus on database server encrypting the database. They paid and got files decrypted but database wasn’t able to start.<div> </div><div>We got the following error:</div><div><div><span>2017-07-19 10</span>:31:11 CEST LOG: database system was shut down in recovery at <span>2017-07-19 10</span>:29:00 CEST</div><div><span>2017-07-19 10</span>:31:11 CEST LOG: invalid magic number B549 in log segment <span>000000010000002</span>E<span>00000015</span>, offset 0</div><div><span>2017-07-19 10</span>:31:11 CEST LOG: invalid primary checkpoint record</div><div><span>2017-07-19 10</span>:31:11 CEST LOG: invalid magic number B549 in log segment <span>000000010000002</span>E<span>00000015</span>, offset 0</div><div><span>2017-07-19 10</span>:31:11 CEST LOG: invalid secondary checkpoint record</div><div><span>2017-07-19 10</span>:31:11 CEST PANIC: could not locate a valid checkpoint record</div><div><span>2017-07-19 10</span>:31:11 CEST LOG: shutdown at recovery target</div><div><span>2017-07-19 10</span>:31:11 CEST LOG: shutting down</div><div><span>2017-07-19 10</span>:31:11 CEST LOG: database system is shut down</div></div><div> <div> </div><div>We got past that with executing the “pg_resetxlog -f DATADIR” command.</div><div> </div><div>Now database starts but we cannot connect to it, we get the following error: </div><div><span>2017-07-19 14</span>:11:05 CEST ERROR: invalid page in block 0 of relation global/12369</div><div> </div><div>Can anyone help?</div><div> <div><div style="color:rgb(0,0,0);text-align:start;text-transform:none;white-space:normal;"><div><div style="color:rgb(0,0,0);font-family:"Arial Unicode MS";font-size:12px;font-style:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;">Lp, </div><div style="color:rgb(0,0,0);font-family:"Arial Unicode MS";font-size:12px;font-style:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;">Rikardo Tinauer</div><div style="color:rgb(0,0,0);font-family:"Arial Unicode MS";font-size:12px;font-style:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;">----------------------------------------------------------<br />EBA, d.o.o., Ljubljana<br />Litostrojska 40, SI-1000 Ljubljana<br />Tel: <span>+386 (0)590 98 890</span><br />Email: <a href="mailto:rikardo.tinauer@eba.si">rikardo.tinauer@eba.si</a><br />Web: <a href="http://www.ebadms.com/">http://www.ebadms.com</a><br />-----------------------------------------------------------</div></div></div></div></div></div></div></blockquote>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Recovery of corrupted database
2017-07-19 13:27 Recovery of corrupted database Rikardo Tinauer <rikardo.tinauer@eba.si>
@ 2017-07-19 16:01 ` Bear Giles <bgiles@coyotesong.com>
1 sibling, 0 replies; 3+ messages in thread
From: Bear Giles @ 2017-07-19 16:01 UTC (permalink / raw)
To: Rikardo Tinauer <rikardo.tinauer@eba.si>; +Cc: 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 CEST
> 2017-07-19 10:31:11 CEST LOG: invalid magic number B549 in log segment
> 000000010000002E00000015, offset 0
> 2017-07-19 10:31:11 CEST LOG: invalid primary checkpoint record
> 2017-07-19 10:31:11 CEST LOG: invalid magic number B549 in log segment
> 000000010000002E00000015, offset 0
> 2017-07-19 10:31:11 CEST LOG: invalid secondary checkpoint record
> 2017-07-19 10:31:11 CEST PANIC: could not locate a valid checkpoint record
> 2017-07-19 10:31:11 CEST LOG: shutdown at recovery target
> 2017-07-19 10:31:11 CEST LOG: shutting down
> 2017-07-19 10:31:11 CEST LOG: database system is shut down
>
>
> We 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/12369
>
> Can 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
> -----------------------------------------------------------
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-07-19 16:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-07-19 13:27 Recovery of corrupted database Rikardo Tinauer <rikardo.tinauer@eba.si>
2017-07-19 16:01 ` Samed YILDIRIM <samed@reddoc.net>
2017-07-19 16:01 ` Bear Giles <bgiles@coyotesong.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox