Обсуждение: [GENERAL] Recover corrupted data

1) restore from backup

2) fix whatever configuration you made to let windows (or your hardware) destroy your data on crash. is there some RAID cache that is not backed up by a battery?

IMHO If there's no hurry, it'd be better to start with point 2, because if your filesystem (or hardware) is somehow badly broken, it will happen again...
If you have more than one database in your cluster, you can find what's the database that's been corrupted and restore just that one, instead of the whole cluster.
Cheers
Moreno

On Wed, Apr 19, 2017 at 10:18 AM, Alexandre <psybox@gmail.com> wrote:

Hello,

The computer had a unexpected shutdown, it is a Windows machine.

Now some data appears to be corrupted, I am receiving exceptions like this:

ERROR: could not read block 0 in file "base/16393/16485": read only

0 of 8192 bytes

There is some way to correct this?

On Wed, 19 Apr 2017 13:25:41 -0300, Alexandre <psybox@gmail.com>
wrote:

>  :
>But there is no solution for [file corruption]?


The only solutions are to guard against it: make frequent backups and
make use of safety mechanisms in Postgresql and in the OS.

Postgresql logs (WAL) intended changes to the database before it makes
them.  NTFS *can* do similar change logging for files - but its
logging may or may not be turned on by default.


If you are using NTFS on a hard disk, then for maximum crash
resistance make sure that both journaling (usn) and self-healing
(repair) are turned on.

If the hard disk was formatted by a (relatively) recent version of
Windows, then it is likely that journaling is on already.  But best to
check because prior to Vista the default was OFF, and a number of
internet "tweak" sites advise to turn off journaling deliberately to
enhance write performance.  Disabling journaling is [maybe] acceptable
for a personal workstation, but not for a server.


If you are using SSD, then OS journaling will be off by default.  If
the SSD is battery backed, then journaling *probably* is not necessary
and you can choose whether to trade enhanced crash resistance against
increased SSD wear and (slightly) reduced write performance.


See:

fsutil usn ...
https://technet.microsoft.com/en-us/library/cc788042(v=ws.11).aspx

fsutil repair ...
https://technet.microsoft.com/en-us/library/ff621565(v=ws.11).aspx


George

On 4/19/2017 9:24 AM, Alexandre wrote:
> 2) We dont use RAID.

so just a direct attached single disk drive?  is it perchance a
'desktop' type disk?  those often have dodgy write buffering and lie
about writes (saying they are complete when they are just in a volatile
ram buffer).   sometimes you can turn this behavior off, depending on
the brand and model drive, but do note it will slow your system down a
fair bit.


--
john r pierce, recycling bits in santa cruz