Restoring & pg_restore -j

[Krysztophe]

Both extensions (omp-core & wh_nagios) create their tables during the CREATE EXTENSION, including foreign keys. So we had a pg_restore -j8 --section=data of an OPM database that failed, because it was parallelized, the extensions tables data were not loaded sequentially, and the foreign keys were already there.

The doc should state an official upgrade procedure, eg:

• separate pg_restore for extension tables (not parallelized) and other ones (parallelized), with pg_restore -l and -L
• pg_restore --disable-triggers 😱
• an official script to drop and recreate manually the constraints
• something else that I didn't think of.

[rjuju]

I'm not sure I understand everything here. What do you mean by "extension tables"? Do you have some log examples of the problem?

If you already managed to properly dump/restore an opm database, do you have some recommended procedure that allows you to achieve it?

[Krysztophe]

How to reproduce:

$ createdb opm_clone

$ pg_restore -d opm_clone --section=pre-data opm_2020-02-10_09-00-01.dump

$ pg_restore -d opm_clone -j8 -e --section=data opm_2020-02-10_09-00-01.dump
pg_restore: [programme d'archivage (db)] Erreur pendant le traitement de la TOC (« PROCESSING TOC ») :
pg_restore: [programme d'archivage (db)] Erreur à partir de l'entrée TOC 11198 ; 0 42343 TABLE DATA metrics postgres
pg_restore: [programme d'archivage (db)] COPY échoué pour la table « metrics » : ERROR:  insert or update on table "metrics" violates foreign key constraint "metrics_id_service_fkey"
DÉTAIL : Key (id_service)=(1) is not present in table "services".
pg_restore: [archiveur en parallèle] un processus worker a subi un arrêt brutal inattendu

The problem comes from the --section=pre-data, that contains all CREATE EXTENSIONS. These extensions create immediately tables, who have foreign keys:

$ createdb opm_clone

# create extension wh_nagios cascade;
NOTICE:  installing required extension "opm_core"
NOTICE:  installing required extension "hstore"

opm_clone=# \d
                    Liste des relations
 Schéma |           Nom           |   Type   | Propriétaire 
--------+-------------------------+----------+--------------
 public | api                     | table    | postgres
 public | graphs                  | table    | postgres
 public | graphs_id_seq           | séquence | postgres
 public | graphs_templates        | table    | postgres
 public | graphs_templates_id_seq | séquence | postgres
 public | members                 | table    | postgres
 public | metrics                 | table    | postgres
 public | metrics_id_seq          | séquence | postgres
 public | roles                   | table    | postgres
 public | roles_id_seq            | séquence | postgres
 public | series                  | table    | postgres
 public | servers                 | table    | postgres
 public | servers_id_seq          | séquence | postgres
 public | services                | table    | postgres
 public | services_id_seq         | séquence | postgres

# \d+ wh_nagios.metrics
….
Index :
    "metrics_pkey" PRIMARY KEY, btree (id)
Contraintes de clés étrangères :
    "metrics_id_service_fkey" FOREIGN KEY (id_service) REFERENCES wh_nagios.services(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
Référencé par :
    TABLE "wh_nagios.series" CONSTRAINT "series_id_metric_fkey" FOREIGN KEY (id_metric) REFERENCES wh_nagios.metrics(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
Hérite de: metrics

pg_restore -j8 -e --section=data does not care about the table order, hence the error. Of course it works wihout -j8.

We've solved it by loading separately everything except service_counters tables (the big ones, without constraints) and later the service_counters tables .

$ pg_restore --section=data -l opm_2020-02-10_09-00-01.dump |grep -v 'service_counters_' > restore.1.list
$ pg_restore --section=data -l opm_2020-02-10_09-00-01.dump |grep  'service_counters_' > restore.2.list
$ pg_restore -d opm_clone      -L restore.1.list opm_2020-02-10_09-00-01.dump
$ pg_restore -d opm_clone -j8 -L restore.2.list opm_2020-02-10_09-00-01.dump

pg_restore --disable-triggers works too but I don't like it, this could hide problems elsewhere.

[rjuju]

I see, thanks for the extra details. So this is a limitation due to how extensions work internally. This could be fixed by either moving out of extensions or something like a new function to call after initial setup or restore, but this would be too much work or adding even more complexity to installation, for the only benefit of simplifying logical dump/parallel restore, so I'm not willing to go that way.

I'm assuming that you used pg_dump/pg_restore to do a major upgrade? Was pg_upgrade considered, or is there any issue with opm?

It seems that your approach is the simplest way to go if you want to use parallel pg_restore. Do you want to submit a documentation patch for it?

[Krysztophe]

OK,I'll do the patch.

pg_upgrade was not considered as it was on a different server (I've just tested, it fails, this will be for another issue)