Re: pg_dump crashes - Mailing list pgsql-general

On 5/22/20 8:17 AM, Nico De Ranter wrote:
> 
> 
> On Fri, May 22, 2020 at 5:14 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 5/22/20 8:05 AM, Nico De Ranter wrote:
>      >
> 
>      >
>      >     Assuming the above matches:
>      >
>      >     COPY public.file (fileid, fileindex, jobid, pathid, filenameid,
>      >     deltaseq, markid, lstat, md5)
>      >
>      >     the '????????????????????' would be for the md5 field. I'm
>     going to say
>      >     that is important.
>      >
>      >
>      > But that would be content of the database only. The should matter
>     for
>      > the application but not for a dump of the database, right?
> 
>     Also what does:
> 
>     \d public.file
> 
>     show?
> 
>     In particular are there any triggers on the table?
> 
> 
> bacula=# \d public.file
>                                   Table "public.file"
>     Column   |   Type   | Collation | Nullable |               Default
> ------------+----------+-----------+----------+--------------------------------------
>   fileid     | bigint   |           | not null | 
> nextval('file_fileid_seq'::regclass)
>   fileindex  | integer  |           | not null | 0
>   jobid      | integer  |           | not null |
>   pathid     | integer  |           | not null |
>   filenameid | integer  |           | not null |
>   deltaseq   | smallint |           | not null | 0
>   markid     | integer  |           | not null | 0
>   lstat      | text     |           | not null |
>   md5        | text     |           | not null |
> Indexes:
>      "file_pkey" PRIMARY KEY, btree (fileid)
>      "file_jobid_idx" btree (jobid)
>      "file_jpfid_idx" btree (jobid, pathid, filenameid)
> 
> 
> 
> Following up on the max(bigint), I tried
> 
>     SELECT md5 FROM public.file where fileid >2087994666;
> 
> and got
> 
>     ERROR:  compressed data is corrupted
> 
> So it does look like those entries are killing it.  Now for the 
> million-dollar question: how do I get them out?

This is a Bacula table and I'm guessing you are using:

https://www.bacula.org/9.6.x-manuals/en/main/Configuring_Director.html#11585

signature=MD5
     An MD5 signature will be computed for all files saved. Adding this 
option generates about 5% extra overhead for each file saved. In 
addition to the additional CPU time, the MD5 signature adds 16 more 
bytes per file to your catalog. We strongly recommend that this option 
or the SHA1 option be specified as a default for all files.

Maybe you can ask the Bacula folks how that is actually calculated and 
test that on row, to see if you can overwrite the bad data.

> 
> Nico
> 
> -- 
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com