From: Tom Lane <tgl@sss.pgh.pa.us>
To: gparc@online.fr
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17342: pg_restore with jobs > 1 errors out : a worker
 process died unexpectedly
In-reply-to: <17342-4af1dddb4500065b@postgresql.org>
References: <17342-4af1dddb4500065b@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Tue, 21 Dec 2021 17:22:57 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <288242.1640197708.1@sss.pgh.pa.us>
Date: Wed, 22 Dec 2021 13:28:28 -0500
Message-ID: <288243.1640197708@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/288243.1640197708%40sss.pgh.pa.us>
Precedence: bulk

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