Thread: Re: BUG #17342: pg_restore with jobs > 1 errors out : a worker process died unexpectedly
Re: BUG #17342: pg_restore with jobs > 1 errors out : a worker process died unexpectedly
From
gparc@free.fr
Date:
Hello Tom, thanks for your answer. There is no direct access between source and target server so I will restore without job (-j) option. By chance, the schema size to restore is not so huge. Regards ----- Mail original ----- De: "Tom Lane" <tgl@sss.pgh.pa.us> À: gparc@online.fr Cc: pgsql-bugs@lists.postgresql.org Envoyé: Mercredi 22 Décembre 2021 19:28:28 Objet: Re: BUG #17342: pg_restore with jobs > 1 errors out : a worker process died unexpectedly PG Bug reporting form <noreply@postgresql.org> writes: > I'm migrating from PostgreSQL 9.1.24 to PostgreSQL 12.7 > On the source machine (9.1.24), the export process is done like this : > pg_dump --schema=$SCHEMA --no-owner --format=custom $BASE --no-password > --verbose -f $DUMPFILE > On the target machine (12.7), the import process is done like this : > export PGOPTIONS="-c maintenance_work_mem=512MB" > pg_restore --no-owner --no-tablespaces --dbname=$BASE --username=postgres > --role=$ROLE --schema=$SCHEMA --no-password --verbose --jobs=4 > --exit-on-error <dumpfile_from_9.1.24> > And I get the following messages : > pg_restore: error: could not execute query: ERROR: there is no unique > constraint matching given keys for referenced table "<table_name>" > Command was: ALTER TABLE ONLY <table_name> > ADD CONSTRAINT <constraint_name> FOREIGN KEY (<column_name>) REFERENCES > <table_name>(column_name); > pg_restore: error: a worker process died unexpectedly > ==> if I omit --jobs option or use --jobs=1, i get no errors > I saw this type of error presumably fixed in 12.4 but I'm on 12.7 12.5, actually. But the point is that you need to use 12.5 or later pg_dump to get the benefit of the fix. 9.1's pg_dump will produce an archive with insufficient dependency links, allowing the parallel restore to execute steps in the wrong order. You could use the 12.7 installation's pg_dump to extract data from the old server. However, doing that across the network might slow things down enough to outweigh the benefit of being able to restore in parallel later. regards, tom lane