Re: pg_restore fails when psql succeeds

I install PostgreSQL from "apt.postgresql.org" repository:
$ /usr/lib/postgresql/13/bin/postgres --version
postgres (PostgreSQL) 13.1 (Ubuntu 13.1-1.pgdg20.04+1)

It runs in a VM, the most recent & updated version of Ubuntu LTS 20.04
$ uname -a
Linux dbwfprod8-20 5.4.0-56-generic #62-Ubuntu SMP Mon Nov 23 19:20:19 UTC
2020 x86_64 x86_64 x86_64 GNU/Linux
RAM: 48GB
CPU: 8
Storage: 800GB (plenty of free space left)

I attached customizations to postgresql.conf and a few relevant errors from
the PostgreSQL server log; there were no relevant messages in journalctl.

On Sat, Dec 12, 2020 at 3:18 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 12/12/20 12:10 PM, Cherio wrote:
> > I am facing a consistent issue with pg_restore when moving databases
> > with large tables from PostgreSQL 10 to 13. pg_restore fails to restore
> > indexes on some large tables (anything over 20 million records).
> >
> > pg_restore: error: could not execute query: ERROR: out of memory
> > DETAIL: Failed on request of size 214728704 in memory context
> > "TupleSort sort".
> > CONTEXT: parallel worker
> > Command was: CREATE INDEX some_index_idx ON schema1.table1 USING btree
> > (some_field);
> >
> > This happens when a database is exported with formats "custom" or
> > "directory". No errors occur when the same databases are exported as
> > plain text and imported with psql.
> >
> > Initially I was importing with --jobs in several threads, but reducing
> > threads to 1 made no difference. I tried exporting with pg_dump versions
> > 13 and 10. It made no difference either - restore succeeds with plain
> > text + psql and fails with the other formats + pg_restore.
> >
> > The same doesn't happen when I import from 10 into 12. I am a bit lost
> > and concerned at this point about moving on with conversion to version
> 13.
> >
> > Any guidance would be greatly appreciated!
>
> Exact Postgres 13 version?
>
> Hardware specifications for machine?
>
> Changes in this
> section(https://www.postgresql.org/docs/13/runtime-config-resource.html)
> of postgresql.conf?
>
> Relevant information from system logs?
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>