Re: pg_dump crashes

FYI: I tried setting the md5 field to '' in the whole table but that didn't fix the pg_dump issue. In the end I decided to drop the database and revert to my last successful backup. I'm now still reading in all tapes to reconstruct the latest database state. Thanks for the help anyway. Nico On

Re: pg_dump crashes

On 5/24/20 10:30 PM, Nico De Ranter wrote: Unfortunately not. I discovered the issue rather late. The last working backup is about 2 months old. Well first it is entirely possible this is not the only corruption in the database. Second you are probably going to have to reach out to the Bacul

Re: pg_dump crashes

Unfortunately not. I discovered the issue rather late. The last working backup is about 2 months old. On Fri, May 22, 2020 at 5:36 PM Adrian Klaver wrote: > On 5/22/20 8:17 AM, Nico De Ranter wrote: > > > > > > On Fri, May 22, 2020 at 5:14 PM Adrian Klaver > >

Re: pg_dump crashes

On 5/22/20 8:17 AM, Nico De Ranter wrote: On Fri, May 22, 2020 at 5:14 PM Adrian Klaver > wrote: On 5/22/20 8:05 AM, Nico De Ranter wrote: > > >     Assuming the above matches: > >     COPY public.file (fileid, fileindex, jobid,

Re: pg_dump crashes

On 5/22/20 8:17 AM, Nico De Ranter wrote: On Fri, May 22, 2020 at 5:14 PM Adrian Klaver > wrote: On 5/22/20 8:05 AM, Nico De Ranter wrote: > > >     Assuming the above matches: > >     COPY public.file (fileid, fileindex, jobid,

Re: pg_dump crashes

On 5/22/20 8:13 AM, Nico De Ranter wrote: bacula=# SELECT md5 FROM public.file where fileid = 4557430888798830399;  md5 - (0 rows) So that fileid is bogus too (max(bigint) I assume) No: select 4557430888798830399::bigint; int8 - 4557430888798830399 (1 r

Re: pg_dump crashes

On Fri, May 22, 2020 at 5:14 PM Adrian Klaver 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 '' w

Re: pg_dump crashes

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 woul

Re: pg_dump crashes

On Fri, May 22, 2020 at 5:09 PM Adrian Klaver wrote: > On 5/22/20 8:05 AM, Nico De Ranter wrote: > > > > On Fri, May 22, 2020 at 5:02 PM Adrian Klaver > > wrote: > > > > On 5/22/20 7:55 AM, Nico De Ranter wrote: > > > Correct. > > > > > > If I

Re: pg_dump crashes

On Fri, May 22, 2020 at 5:07 PM Adrian Klaver wrote: > On 5/22/20 7:48 AM, Nico De Ranter wrote: > > The original server was running 9.5.14 > > The system I am currently testing on is 11.8 > > > > 2 fields are marked as 'extended'. However if I understand correctly > > the table isn't actually

Re: pg_dump crashes

On 5/22/20 8:05 AM, Nico De Ranter wrote: On Fri, May 22, 2020 at 5:02 PM Adrian Klaver > wrote: On 5/22/20 7:55 AM, Nico De Ranter wrote: > Correct. > > If I run 'pg_dumpall --cluster 11/main --file=dump.sql'   the end of the > fil

Re: pg_dump crashes

On 5/22/20 7:48 AM, Nico De Ranter wrote: The original server was running 9.5.14 The system I am currently testing on is  11.8 2 fields are marked as 'extended'.   However if I understand correctly the table isn't actually toasted:   oid  |    table_schema    |       table_name        | tot

Re: pg_dump crashes

On Fri, May 22, 2020 at 5:02 PM Adrian Klaver wrote: > On 5/22/20 7:55 AM, Nico De Ranter wrote: > > Correct. > > > > If I run 'pg_dumpall --cluster 11/main --file=dump.sql' the end of the > > file looks like: > > > > ## cut here > > 4557430888798830399 1061109567 1061109567 1061109567 1061

Re: pg_dump crashes

On 5/22/20 7:55 AM, Nico De Ranter wrote: Correct. If I run 'pg_dumpall --cluster 11/main --file=dump.sql'   the end of the file looks like: ## cut here 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N \N ?? 4557430888798830399 10611095

Re: pg_dump crashes

Correct. If I run 'pg_dumpall --cluster 11/main --file=dump.sql' the end of the file looks like: ## cut here 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N \N ?? 4557430888798830399 1061109567 1061109567 1061109567 1061109567 16191 \N \N

Re: pg_dump crashes

The original server was running 9.5.14 The system I am currently testing on is 11.8 2 fields are marked as 'extended'. However if I understand correctly the table isn't actually toasted: oid |table_schema| table_name| total_bytes | total| index| toast

Re: pg_dump crashes

On 5/22/20 6:40 AM, Nico De Ranter wrote: I was just trying that.  It's always the same (huge) table that crashes the pg_dump.   Running a dump excluding that one table goes fine, running a dump of only that one table crashes. In the system logs I always see a segfault May 22 15:22:14 core4 ke

Re: pg_dump crashes

> pg_dump: The command was: COPY public.file (fileid, fileindex, jobid, pathid, filenameid, deltaseq, markid, lstat, md5) TO stdout; What happens when you run that COPY ... TO stdout; command (but redirecting it to /dev/null)? On 5/22/20 8:40 AM, Nico De Ranter wrote: I was just trying that. 

Re: pg_dump crashes

Am 22.05.20 um 14:37 schrieb Nico De Ranter: Postgres version: 9.5 which minor-version? Can you check if the table has TOAST-Tables? Can you try to select all columns but not TOASTed columns? Maybe there is data-corruption only in toast-tables. Regards, Andreas -- 2ndQuadrant - The Pos

Re: pg_dump crashes

/usr/lib/postgresql/11/bin/pg_upgrade --old-datadir /data/postgresql/9.5/main/ --new-datadir /var/lib/postgresql/11/main/ -b /usr/lib/postgresql/9.5/bin -B /usr/lib/postgresql/11/bin -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf'

Re: pg_dump crashes

I was just trying that. It's always the same (huge) table that crashes the pg_dump. Running a dump excluding that one table goes fine, running a dump of only that one table crashes. In the system logs I always see a segfault May 22 15:22:14 core4 kernel: [337837.874618] postgres[1311]: segfault

Re: pg_dump crashes

On 5/22/20 5:37 AM, Nico De Ranter wrote: Hi all, Postgres version: 9.5 OS: Ubuntu 18.04.4 I have a 144GB Bacula database that crashes the postgres daemon when I try to do a pg_dump. At some point the server ran out of diskspace for the database storage. I expanded the lvm and rebooted the s

Re: pg_dump crashes

On 5/22/20 5:37 AM, Nico De Ranter wrote: Hi all, Postgres version: 9.5 OS: Ubuntu 18.04.4 I have a 144GB Bacula database that crashes the postgres daemon when I try to do a pg_dump. At some point the server ran out of diskspace for the database storage. I expanded the lvm and rebooted the s

pg_dump crashes

Hi all, Postgres version: 9.5 OS: Ubuntu 18.04.4 I have a 144GB Bacula database that crashes the postgres daemon when I try to do a pg_dump. At some point the server ran out of diskspace for the database storage. I expanded the lvm and rebooted the server. It seemed to work fine, however when I