Thread: Re: Please help (backup)

The dumped file is just a plain-text file (Depending on the data types

within your database)

You should be able to open the backup you made with pg_dump in your

favourite editor such as vi and search through for the dodgy data. Clear out

the bad stuff, write it back to disk and you should be good to go.

Obviously this won't "save" the damaged portion of the data, and it may have

unpredictable effects when you take in to account any foreign keys etc you

may have in the database that depends on this data.

Cheers,

~p

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to

the addressee.  If you are not the addressee indicated in this message or

responsible for delivery of the message to such person, you may not copy

or deliver this message to anyone, and you should destroy it and kindly

notify the sender by reply email.

Information in this message that does not relate to the official business

of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.

Weatherbeeta, its employees, contractors or associates shall not be liable

for direct, indirect or consequential loss arising from transmission of this

message or any attachments

e-mail.

---------------------------(end of broadcast)---------------------------

TIP 1: if posting/reading through Usenet, please send an appropriate

       subscribe-nomail command to majordomo@postgresql.org so that your

       message can get through to the mailing list cleanly

THINK BEFORE YOU PRINT - Save paper if you don't really need to print this e-mail.

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.

Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments

<font size="-1"><font face="Verdana">Really all file content after object definitions looks like "Encoded/Encrypted"
andwhen I do a restore all table data are restored successfully (except one, that is in the "corrupted sector"). So,
maybethe pg_dump do a encrypt or something like that over the DATA content.<br /><br /><br /> Thanks.<br /><br /><br
/></font></font><br/> Phillip Smith escribió: <blockquote cite="mid:024701c8147c$55cc9430$9b0014ac@wbaus090"
type="cite"><style>
<!--/* Font Definitions */@font-face{font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face{font-family:Verdana;panose-1:2 11 6 4 3 5 4 4 2 4;}/* Style Definitions */p.MsoNormal, li.MsoNormal,
div.MsoNormal{margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;font-family:"TimesNew Roman";color:black;} 
a:link, span.MsoHyperlink{color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed{color:blue;text-decoration:underline;}
pre{margin:0cm;margin-bottom:.0001pt;font-size:10.0pt;font-family:"Courier New";color:black;}
span.EmailStyle18{font-family:Arial;color:navy;}
@page Section1{size:595.3pt 841.9pt;margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1{page:Section1;}
--> </style><div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size:
10pt;font-family: Arial; color: navy;">(Please reply to the list so everyone can keep track of the
thread)</span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10pt;
font-family:Arial; color: navy;"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:10pt; font-family: Arial; color: navy;">You’ll need to look at the table definitions to work out what
thedata should look like, and if it doesn’t look like that – then it’s probably part of the corrupt
data…</span></font><pclass="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10pt;
font-family:Arial; color: navy;"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:10pt; font-family: Arial; color: navy;"> </span></font><div style="border-style: none none none solid;
border-color:-moz-use-text-color -moz-use-text-color -moz-use-text-color blue; border-width: medium medium medium
1.5pt;padding: 0cm 0cm 0cm 4pt;"><p class="MsoNormal"><font color="black" face="Tahoma" size="2"><span lang="EN-US"
style="font-size:10pt; font-family: Tahoma; color: windowtext;">-----Original Message-----<br /><b><span
style="font-weight:bold;">From:</span></b> Anibal David Acosta [<a class="moz-txt-link-freetext"
href="mailto:aa@personal.net.py">mailto:aa@personal.net.py</a>]<br /><b><span style="font-weight:
bold;">Sent:</span></b>Monday, 22 October 2007 15:33<br /><b><span style="font-weight: bold;">To:</span></b> Phillip
Smith<br/><b><span style="font-weight: bold;">Subject:</span></b> Re: [ADMIN] Please help (backup)</span></font><p
class="MsoNormal"><fontcolor="black" face="Times New Roman" size="3"><span style="font-size: 12pt;"> </span></font><p
class="MsoNormal"><fontcolor="black" face="Verdana" size="2"><span style="font-size: 10pt; font-family: Verdana;">I
openwith word pad & notepad, but table, functions and other object declarations looks like "plain text" but the
DATAnope.<br /><br /> This is the beginnig of something that looks as DB object<br />
---------------------------------------------------------------------<br/> ALTER TABLE ONLY threads<br />     ADD
CONSTRAINTthreads_fk1 FOREIGN KEY (forum_id) REFERENCES forums(id) ON DELETE RESTRICT;<br />  =   ALTER TABLE ONLY
public.threadsDROP CONSTRAINT threads_fk1;<br />        public       postgres    false <hr align="left" size="1" />  
1488<hr align="left" size="1" />   1869 <hr align="left" size="1" />   1464      <br />
---------------------------------------------------------------------</span></font><br/><font face="Verdana"
size="2"><spanstyle="font-size: 10pt; font-family: Verdana;"><br /><br /> This is the beginnig of something that looks
asDB object<br /> ---------------------------------------------------------------------</span></font><br /><font
face="Verdana"size="2"><span style="font-size: 10pt; font-family: Verdana;"> xœeYÝzÚ¸½Þ}<br />
Ï͹˜ïk¶1†»!</span></font><fontface="Verdana" size="2"><span style="font-size: 10pt; font-family:
Verdana;"></span></font><fontface="Verdana" size="2"><span style="font-size: 10pt; font-family: Verdana;">¤¤$á MÛ3gÊ'Œ
Ùbd;=É74Wç]æQæ<br/> Î’Im·?!^–-ikk­µE—†CæjgJš½‰ÜÕ…È•VÂÒÅ›Ð]_ÊBZ<br />
---------------------------------------------------------------------</span></font><br/><font face="Verdana"
size="2"><spanstyle="font-size: 10pt; font-family: Verdana;"><br /> BTW, I used the "PGAdmin" application to make the
backup,I don't have any BLOB column, just normal data type (integer, varchar, datetime)<br /><br /><br /> As you can
see,the "data" section is not human readable, so I cant  modify it.<br /><br /><br /> Thanks for your time Phillip, I
hope to find a solution.<br /><br /> regards.<br /><br /> Anibal<br /></span></font><br /> Phillip Smith escribió: <pre
wrap=""><fontcolor="black" face="Courier New" size="2"><span style="font-size: 10pt;">The dumped file is just a
plain-textfile (Depending on the data types</span></font></pre> <pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size: 10pt;">within your database)</span></font></pre> <pre><font color="black" face="Courier
New"size="2"><span style="font-size: 10pt;"> </span></font></pre> <pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size: 10pt;">You should be able to open the backup you made with pg_dump in
your</span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size: 10pt;">favourite
editorsuch as vi and search through for the dodgy data. Clear out</span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">the bad stuff, write it back to disk and you should be good
togo.</span></font></pre> <pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;"> </span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;">Obviouslythis won't "save" the damaged portion of the data, and it may have</span></font></pre> <pre><font
color="black"face="Courier New" size="2"><span style="font-size: 10pt;">unpredictable effects when you take in to
accountany foreign keys etc you</span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;">may have in the database that depends on this data.</span></font></pre> <pre><font
color="black"face="Courier New" size="2"><span style="font-size: 10pt;"> </span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">Cheers,</span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">~p</span></font></pre> <pre><font color="black" face="Courier
New"size="2"><span style="font-size: 10pt;"> </span></font></pre> <pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size: 10pt;">  </span></font></pre><blockquote style="margin-top: 5pt; margin-bottom: 5pt;"
type="cite"><prewrap=""><font color="black" face="Courier New" size="2"><span style="font-size: 10pt;">-----Original
Message-----</span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;">From:<a href="mailto:pgsql-admin-owner@postgresql.org"
moz-do-not-send="true">pgsql-admin-owner@postgresql.org</a>[<a href="mailto:pgsql-admin"
moz-do-not-send="true">mailto:pgsql-admin</a>-</span></font></pre><pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size: 10pt;"><a href="mailto:owner@postgresql.org"
moz-do-not-send="true">owner@postgresql.org</a>]On Behalf Of Anibal David Acosta</span></font></pre> <pre><font
color="black"face="Courier New" size="2"><span style="font-size: 10pt;">Sent: Monday, 22 October 2007
11:16</span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size: 10pt;">To: <a
href="mailto:pgsql-admin@postgresql.org"moz-do-not-send="true">pgsql-admin@postgresql.org</a></span></font></pre>
<pre><fontcolor="black" face="Courier New" size="2"><span style="font-size: 10pt;">Subject: [ADMIN] Please help
(backup)</span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;"> </span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size: 10pt;">I have
abackup done with "pg_dump" utility.</span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;"> </span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;">For some reason the backup file has 8 phisical bad sectors (17 KB of data)</span></font></pre>
<pre><fontcolor="black" face="Courier New" size="2"><span style="font-size: 10pt;"> </span></font></pre> <pre><font
color="black"face="Courier New" size="2"><span style="font-size: 10pt;">Now when I try to restore the backup all tables
arecreated successfully</span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;">and data of almost all tables restores successfully too, but one table</span></font></pre>
<pre><fontcolor="black" face="Courier New" size="2"><span style="font-size: 10pt;">(the most important) appear
empty.</span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;"> </span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size: 10pt;">When I
doa pg_restore <backup_file> its show all sql instructions and</span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">data until arrive to the "damaged sector" and throws a "out
ofmemory"</span></font></pre> <pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;">exception.</span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;"> </span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size: 10pt;">I want
toknow if is possible to fix the backup file removing the blocks</span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">in the "bad sectors" in order to restore all other
information.</span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;"> </span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;">somethinglike file dump surgery :)</span></font></pre> <pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size: 10pt;"> </span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;">Thanks!</span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;"> </span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;">Anibal</span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;"> </span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;"> </span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;">---------------------------(end of broadcast)---------------------------</span></font></pre>
<pre><fontcolor="black" face="Courier New" size="2"><span style="font-size: 10pt;">TIP 4: Have you searched our list
archives?</span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;"> </span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;">              <a href="http://archives.postgresql.org"
moz-do-not-send="true">http://archives.postgresql.org</a></span></font></pre><pre><font color="black" face="Courier
New"size="2"><span style="font-size: 10pt;">    </span></font></pre></blockquote><pre wrap=""><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;"> </span></font></pre> <pre><font color="black" face="Courier
New"size="2"><span style="font-size: 10pt;"> </span></font></pre> <pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size: 10pt;">THINK BEFORE YOU PRINT - Save paper if you don't really need to print
this</span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;"> </span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;">*******************Confidentialityand Privilege Notice*******************</span></font></pre> <pre><font
color="black"face="Courier New" size="2"><span style="font-size: 10pt;"> </span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">The material contained in this message is privileged and
confidentialto</span></font></pre> <pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;">theaddressee.  If you are not the addressee indicated in this message or</span></font></pre> <pre><font
color="black"face="Courier New" size="2"><span style="font-size: 10pt;">responsible for delivery of the message to such
person,you may not copy</span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;">or deliver this message to anyone, and you should destroy it and kindly</span></font></pre>
<pre><fontcolor="black" face="Courier New" size="2"><span style="font-size: 10pt;">notify the sender by reply
email.</span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;"> </span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size:
10pt;">Informationin this message that does not relate to the official business</span></font></pre> <pre><font
color="black"face="Courier New" size="2"><span style="font-size: 10pt;">of Weatherbeeta must be treated as neither
givennor endorsed by Weatherbeeta.</span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;">Weatherbeeta, its employees, contractors or associates shall not be liable</span></font></pre>
<pre><fontcolor="black" face="Courier New" size="2"><span style="font-size: 10pt;">for direct, indirect or
consequentialloss arising from transmission of this</span></font></pre> <pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size: 10pt;">message or any attachments</span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;">e-mail.</span></font></pre> <pre><font color="black"
face="CourierNew" size="2"><span style="font-size: 10pt;"> </span></font></pre> <pre><font color="black" face="Courier
New"size="2"><span style="font-size: 10pt;">---------------------------(end of
broadcast)---------------------------</span></font></pre><pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;">TIP 1: if posting/reading through Usenet, please send an appropriate</span></font></pre>
<pre><fontcolor="black" face="Courier New" size="2"><span style="font-size: 10pt;">       subscribe-nomail command to
<ahref="mailto:majordomo@postgresql.org" moz-do-not-send="true">majordomo@postgresql.org</a> so that
your</span></font></pre><pre><font color="black" face="Courier New" size="2"><span style="font-size: 10pt;">      
messagecan get through to the mailing list cleanly</span></font></pre> <pre><font color="black" face="Courier New"
size="2"><spanstyle="font-size: 10pt;"> </span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;"> </span></font></pre> <pre><font color="black" face="Courier New" size="2"><span
style="font-size:10pt;">  </span></font></pre></div></div><br /><p>THINK BEFORE YOU PRINT - Save paper if you don't
reallyneed to print this e-mail. <p><b>*******************Confidentiality and Privilege
Notice*******************</b><p>Thematerial contained in this message is privileged and confidential to the addressee.
Ifyou are not the addressee indicated in this message or responsible for delivery of the message to such person, you
maynot copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.
<p>Informationin this message that does not relate to the official business of Weatherbeeta must be treated as neither
givennor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for
direct,indirect or consequential loss arising from transmission of this message or any attachments <br /><pre wrap=""> 
<hr size="4" width="90%" />
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.15.3/1082 - Release Date: 20/10/2007 14:59 </pre></blockquote>

On 10/22/07, Anibal David Acosta <aa@personal.net.py> wrote:
>
>  Really all file content after object definitions looks like
> "Encoded/Encrypted" and when I do a restore all table data are restored
> successfully (except one, that is in the "corrupted sector"). So, maybe the
> pg_dump do a encrypt or something like that over the DATA content.

No. What likely happened is that due to a bug in your OS or harddrive
controller, sectors from the middle of a binary (like an executable)
got put in place of some of your blocks in your postgresql backup.

You need to use a robust editor like vi to chop those parts out and
see what you get.

But the real answer is to use a known good backup.