Re: BUG #17342: pg_restore with jobs > 1 errors out : a worker process died unexpectedly

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(at)sss(dot)pgh(dot)pa(dot)us>
À: gparc(at)online(dot)fr
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)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(at)postgresql(dot)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