pg_restore fails when psql succeeds
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!
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 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.kla...@aklaver.com > var-log-postgresql-postgresql-13-main.log Description: Binary data postgresql-44G.conf Description: Binary data
Re: pg_restore fails when psql succeeds
The exact command is nothing fancy really. The complete exact command is below export PGPASSWORD=xxx 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 wrote: > On 12/12/20 12:39 PM, Cherio wrote: > > I install PostgreSQL from "apt.postgresql.org > > <http://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. > > > > > > What is the exact command you are using to do the restore? > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: count(*) vs count(id)
I just ran a few practical tests on large (~14mil rows) tables that have multiple indexes. SELECT COUNT(id) forces PostgreSQL to use the primary key index. SELECT COUNT(*) allows PostgreSQL to chose an index to use and it seems to be choosing one of smaller size which leads to less IO and hence returns the result faster. On Tue, Feb 2, 2021 at 3:45 PM Hellmuth Vargas wrote: > >
