Обсуждение: pg_dump query failed

Hi all,

last week backup of my one database stops with error like below:

"pg_dump: [archiver (db)] query failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_dump: [archiver (db)] query was: SELECT l.oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = l.lomowner) AS rolname, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(l.lomacl,pg_catalog.acldefault('L',l.lomowner))) WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('L',l.lomowner))) AS init(init_acl) WHERE acl = init_acl)) as foo) AS lomacl, (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('L',l.lomowner))) WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(l.lomacl,pg_catalog.acldefault('L',l.lomowner))) AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rlomacl, NULL AS initlomacl, NULL AS initrlomacl FROM pg_largeobject_metadata l LEFT JOIN pg_init_privs pip ON (l.oid = pip.objoid AND pip.classoid = 'pg_largeobject'::regclass AND pip.objsubid = 0) "

Additional info:

PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit

database size 220GB

System CentOS 7

I am not sure how to recognize the problem.

Additional info:

PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit

database size 220GB

System CentOS 7

I am not sure how to recognize the problem.

On 7/28/22 05:08, karol.malinowski@trustedeyes.pl wrote:
>
> Hi all,
>
> last week backup of my one database stops with error like below:
>
> "pg_dump: [archiver (db)] query failed: server closed the connection 
> unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> pg_dump: [archiver (db)] query was: SELECT l.oid, (SELECT rolname FROM 
> pg_catalog.pg_roles WHERE oid = l.lomowner) AS rolname, (SELECT 
> pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM 
> pg_catalog.unnest(coalesce(l.lomacl,pg_catalog.acldefault('L',l.lomowner))) 
> WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM 
> pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('L',l.lomowner))) 
> AS init(init_acl) WHERE acl = init_acl)) as foo) AS lomacl, (SELECT 
> pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM 
> pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('L',l.lomowner))) 
> WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM 
> pg_catalog.unnest(coalesce(l.lomacl,pg_catalog.acldefault('L',l.lomowner))) 
> AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rlomacl, NULL AS 
> initlomacl, NULL AS initrlomacl FROM pg_largeobject_metadata l LEFT JOIN 
> pg_init_privs pip ON (l.oid = pip.objoid AND pip.classoid = 
> 'pg_largeobject'::regclass AND pip.objsubid = 0) "
>
>
> Additional info:
>
> PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
> 20150623 (Red Hat 4.8.5-16), 64-bit
>

You will of course patch that to 9.6.24, right?

> database size 220GB
>
> System CentOS 7
>
> I am not sure how to recognize the problem.
>

I got these when there's a network hiccup while dumping a remote database.

-- 
Angular momentum makes the world go 'round.

karol.malinowski@trustedeyes.pl writes:
> last week backup of my one database stops with error like below: 

> "pg_dump: [archiver (db)] query failed: server closed the connection
> unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.

> Additional info: 
> PostgreSQL 9.6.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (Red Hat 4.8.5-16), 64-bit 

The first thing I'd suggest is updating to the latest release
in that branch (9.6.24).  You're missing four years' worth of
bug fixes, and it's not improbable that the underlying issue
here is included in that.

If that doesn't help, you'll need to start debugging.  The
symptom looks a bit like catalog corruption; if you are lucky
then REINDEX'ing the catalogs used in that query would help.

            regards, tom lane