Re: pg_upgrade from Postgresql-12 to Postgresql-13 fails with "Creating dump of database schemas postgres *failure*"

Hi, Laurenz,

I tried out both `pg_dumpall` and `pg_dump`.

`pg_dumpall` went by alphbetical order when dumping the database. It always
succeeds the first, but fails at the second database (`ankara` is the
second) onwards with the following thread:

```
postgres(at)ual:~$ pg_dumpall -U postgres | gzip > /bnas/pgdumps/$(date
+"%Y_%m_%d")_pg_bck.gz
pg_dump: WARNING: terminating connection because of crash of another
server process
DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
pg_dump: error: query failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_dump: error: query was: SELECT at.attname, (SELECT
pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n FROM
pg_catalog.unnest(coalesce(at.attacl,pg_catalog.acldefault('c',c.relowner)))
WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM
pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('c',c.relowner)))
AS init(init_acl) WHERE acl = init_acl)) as foo) AS attacl, (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('c',c.relowner)))
WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM
pg_catalog.unnest(coalesce(at.attacl,pg_catalog.acldefault('c',c.relowner)))
AS permp(orig_acl) WHERE acl = orig_acl)) as foo) AS rattacl, NULL AS
initattacl, NULL AS initrattacl FROM pg_catalog.pg_attribute at JOIN
pg_catalog.pg_class c ON (at.attrelid = c.oid) LEFT JOIN
pg_catalog.pg_init_privs pip ON (at.attrelid = pip.objoid AND pip.classoid
= 'pg_catalog.pg_class'::pg_catalog.regclass AND at.attnum = pip.objsubid)
WHERE at.attrelid = '119531955'::pg_catalog.oid AND NOT at.attisdropped AND
((SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM (SELECT acl, row_n
FROM
pg_catalog.unnest(coalesce(at.attacl,pg_catalog.acldefault('c',c.relowner)))
WITH ORDINALITY AS perm(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM
pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('c',c.relowner)))
AS init(init_acl) WHERE acl = init_acl)) as foo) IS NOT NULL OR (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('c',c.relowner)))
WITH ORDINALITY AS initp(acl,row_n) WHERE NOT EXISTS ( SELECT 1 FROM
pg_catalog.unnest(coalesce(at.attacl,pg_catalog.acldefault('c',c.relowner)))
AS permp(orig_acl) WHERE acl = orig_acl)) as foo) IS NOT NULL OR NULL IS
NOT NULL OR NULL IS NOT NULL)ORDER BY at.attnum
pg_dumpall: error: pg_dump failed on database "ankara", exiting

```
When tested the failing query on a DB Client, it returns zero rows but
without error. The postgresql log attached.

I then wrote a simple bash to `pg_dump` the databases one by one.
```
#!/bin/bash

declare -a databases=('lugano' 'kinta' 'quito' 'zagreb')
for db in "${databases[(at)]}"; do
echo "Starting pg_dump for ${db}."
dt=`date +%Y%m%d-%T`
cmd="pg_dump -Fc -b -v ${db} > /bnas/pgdumps/${db}_${dt}.tar"
echo "${cmd}"
eval "${cmd}"
echo "pg_dump for ${db} completed."
done
```
Similarly, the first dump always succeeds, but the second one onwards
fails. The postgresql log is also attached below. That said, triggering
pg_dump manually one by one works, though painful.

The server is a Dell T430 tower, with 12 core and 128 RAM. My
`postresql.conf` is attached as well. I suspect the checkpoint settings
might have screw things up, cause I used to be able to continuously dump
the databases one by one from bash script.

For the reproducer, I just need to list out the environment and steps? What
else should I include to be helpful?

Thanks alot for your help.

Chow.

On Fri, Feb 18, 2022 at 11:06 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Fri, 2022-02-18 at 13:17 +0800, Yoong S. Chow wrote:
> > I was trying to perform `pg_upgrade` from Postgresql-12 to Postgresql-13
> > (after being advised that upgrading from Postgresql-12 to Postgresql-14
> > is biting off much more than I could chew... ).
>
> That was bad advice.
>
> > The `pg_upgrade --check` went through OK. But the actual `pg_upgrade`
> > always get error at the step of Creating dump of database schemas for
> > `postgres` database.
>
> Buried in your logs I find
>
> free(): invalid pointer
>
> Perhaps you have found a PostgreSQL bug. Then it would be gread if you
> could provide a reproducer.
>
> But perhaps there is an extension installed that causes that error?
>
> Does a plain pg_dumpall succeed?
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>