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
Date: Wed, 22 Dec 2021 13:28:28 -0500
Message-ID: <288243.1640197708@sss.pgh.pa.us> (raw)
In-Reply-To: <17342-4af1dddb4500065b@postgresql.org>
References: <17342-4af1dddb4500065b@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, gparc@online.fr, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17342: pg_restore with jobs > 1 errors out : a worker process died unexpectedly
In-Reply-To: <288243.1640197708@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox