pg_restore fails when psql succeeds

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.klaver@aklaver.com

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.klaver@aklaver.com>
wrote:

On 12/12/20 12:39 PM, Cherio wrote:

I install PostgreSQL from "apt.postgresql.org
<http://apt.postgresql.org&gt;&quot; 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.

What is the exact command you are using to do the restore?

--
Adrian Klaver
adrian.klaver@aklaver.com

The exact command is nothing fancy really. The complete exact command is
below

export PGPASSWORD=xxxxxxxxxxxxxxxxxxx
pg_restore --verbose --no-password --clean --if-exists -h 111.111.111.111
-p 5432 -U user -d blankdbfromstandardtemplate0 /PATH/EXPORTEDDIR 2>&1 |
tee logfile

I was able to import with pg_restore eventually after I disabled huge_pages
on the OS level. Huge pages were initially configured exactly as suggested
here:
https://www.postgresql.org/docs/13/kernel-resources.html#LINUX-HUGE-PAGES.
This is a dedicated postgres DB server machine but I always verify memory
settings to leave a little wiggle room; postgresqltuner.pl report was clear.

On Sun, Dec 13, 2020 at 1:00 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote: