Vous n'êtes pas identifié(e).
Pages : 1
Bonjour,
merci pour la réponse.
Cela semble être lié à la machine et certainement lié à des versions de librairie, ou de modules, voir du kernel.
car j'ai essayé sur une autre machine virtuelle from scratch avec la même version de postgres (13.5) et postgis 3.1.5, et le pg_restore fonctionne sans problème.
Je crois que je suis quitte d'aller faire un tour dans le coredump une fois qu'il sera généré.
Bonjour,
je vous explique mon problème.
Je dois migrer une application de postgres 11/postgis 2.5 vers postgres 13/postgis 3.1.5.
Pour ce faire, j'ai une machine intermédiaire en postgres 11/postgis 2.5.5 sur laquelle je vais upgrader uniquement postgis de 2.5.5 vers 3.1.5.
Les étapes de migration sont :
- machine source (CentOS):
pg_dump -Fc -v -f xxx.dump xxx - machine intermediare (RH7.9):
pg_restore -cCv -d postgres xxx.dump
select name,default_version,installed_version from pg_available_extensions where name like 'postgis%';
name | default_version | installed_version
------------------------+-----------------+-------------------
postgis | 3.1.5 | 2.5.5
postgis_raster | 3.1.5 |
postgis_sfcgal | 3.1.5 |
postgis_tiger_geocoder | 3.1.5 |
postgis_topology | 3.1.5 |J'installe postgis 3.1.5 et fais l'upgrade.
alter extension postgis update;
SELECT postgis_extensions_upgrade();
select name,default_version,installed_version from pg_available_extensions where name like 'postgis%';
name | default_version | installed_version
------------------------+-----------------+-------------------
postgis | 3.1.5 | 3.1.5
postgis_raster | 3.1.5 | 3.1.5
postgis_sfcgal | 3.1.5 |
postgis_tiger_geocoder | 3.1.5 |
postgis_topology | 3.1.5 |De là, je peux faire le dump pour aller sur la machine finale postgres 13/ postgis 3.1.5
pg_dump -Fc -v -f xxx.dump xxx- machine target finale (RH8):
une fois le transfert effectué du dump, je peux faire le pg_restore :
pg_restore -cCv -d postgres xxx.dumpEt c'est là, qu'une erreur se produit :
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
fatal: connection to server was lostDans le log postgres, je vois bien que le serveur s'est mechamment crashé :
2022-05-12 10:12:33.976 CEST 922335 idle LOG: statement: ALTER TABLE ONLY xxx_sch.pag_nq_qe
ADD CONSTRAINT pag_nq_qe_pk PRIMARY KEY (id, campaign_year);
2022-05-12 10:12:33.976 CEST 922335 idle LOG: statement: ALTER TABLE ONLY xxx_sch.pag_nq_qe_partition_2021
ADD CONSTRAINT pag_nq_qe_partition_2021_pkey PRIMARY KEY (id, campaign_year);
2022-05-12 10:12:38.791 CEST 922098 LOG: background worker "parallel worker" (PID 922336) was terminated by signal 11: Segmentation fault
2022-05-12 10:12:38.791 CEST 922098 LOG: terminating any other active server processes
2022-05-12 10:12:38.791 CEST 922335 ALTER TABLE WARNING: terminating connection because of crash of another server process
2022-05-12 10:12:38.791 CEST 922335 ALTER TABLE DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.L'objet en question n'a rien d'exotique :
\d+ pag_nq_qe
Partitioned table "xxx_sch.pag_nq_qe"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------+------------------------+-----------+----------+---------------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('pag_nq_qe_id_seq'::regclass) | plain | |
geometry | geometry | | not null | | main | |
campaign_year | integer | | not null | | plain | |
category | character varying(255) | | not null | | extended | |
municipality | character varying(255) | | not null | | extended | |
cus | double precision | | | | plain | |
qe_name | character varying | | | | extended | |
section_id | integer | | | | plain | |
Partition key: LIST (campaign_year)
Indexes:
"pag_nq_qe_pk" PRIMARY KEY, btree (id, campaign_year) INVALID
Check constraints:
"pag_nq_qe_srid_geometry" CHECK (st_srid(geometry) = 2169)
Partitions: pag_nq_qe_partition_2021 FOR VALUES IN (2021),
pag_nq_qe_partition_2022 FOR VALUES IN (2022),
pag_nq_qe_partition_2023 FOR VALUES IN (2023),
pag_nq_qe_partition_2024 FOR VALUES IN (2024),
pag_nq_qe_partition_2025 FOR VALUES IN (2025),
pag_nq_qe_partition_2026 FOR VALUES IN (2026),
pag_nq_qe_partition_2027 FOR VALUES IN (2027),
pag_nq_qe_partition_2028 FOR VALUES IN (2028),
pag_nq_qe_partition_2029 FOR VALUES IN (2029),
pag_nq_qe_partition_2030 FOR VALUES IN (2030)
\d+ pag_nq_qe
Partitioned table "xxx_sch.pag_nq_qe"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------+------------------------+-----------+----------+---------------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('pag_nq_qe_id_seq'::regclass) | plain | |
geometry | geometry | | not null | | main | |
campaign_year | integer | | not null | | plain | |
category | character varying(255) | | not null | | extended | |
municipality | character varying(255) | | not null | | extended | |
cus | double precision | | | | plain | |
qe_name | character varying | | | | extended | |
section_id | integer | | | | plain | |
Partition key: LIST (campaign_year)
Indexes:
"pag_nq_qe_pk" PRIMARY KEY, btree (id, campaign_year) INVALID
Check constraints:
"pag_nq_qe_srid_geometry" CHECK (st_srid(geometry) = 2169)
Partitions: pag_nq_qe_partition_2021 FOR VALUES IN (2021),
pag_nq_qe_partition_2022 FOR VALUES IN (2022),
pag_nq_qe_partition_2023 FOR VALUES IN (2023),
pag_nq_qe_partition_2024 FOR VALUES IN (2024),
pag_nq_qe_partition_2025 FOR VALUES IN (2025),
pag_nq_qe_partition_2026 FOR VALUES IN (2026),
pag_nq_qe_partition_2027 FOR VALUES IN (2027),
pag_nq_qe_partition_2028 FOR VALUES IN (2028),
pag_nq_qe_partition_2029 FOR VALUES IN (2029),
pag_nq_qe_partition_2030 FOR VALUES IN (2030)Si j'essaie le alter table à la main, cela fonctionne.
Pouvez-vous m'aider à trouver une solution, car avec cette erreur, la migration est bloquée.
au niveau de la config Postgres,
j'ai :
shared_buffers à 4GBs
work_mem à 4MBs
maintenance_work_mem 64MBsMerci d'avance pour votre aide.
Pages : 1