AW: Insert 1 million data - Mailing list pgsql-admin

We have increased backup volume and run the Job Again . When I reach 900 thousand records,  getting almost similar error again. 

-          Do I need to turn off auto vaccum ?

-          Shall increase maintance work mem ?

2020-12-23 01:16:14.335 +04 [18450] DETAIL: Failed process was running: autovacuum: ANALYZE public.lt_encounter 2020-12-23 01:16:14.343 +04 [18450] LOG: terminating any other active server processes 2020-12-23 01:16:14.344 +04 [18455] WARNING: terminating connection because of crash of another server process 2020-12-23 01:16:14.344 +04 [18455] 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. 

2020-12-23 01:16:14.344 +04 [18455] HINT: In a moment you should be able to reconnect to the database and repeat your command. 

2020-12-23 01:16:14.356 +04 [31155] rptDBA@Ast-ReportDB WARNING: terminating connection because of crash of another server process 

2020-12-23 01:16:14.356 +04 [31155] rptDBA@Ast-ReportDB 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. 

2020-12-23 01:16:14.356 +04 [31155] rptDBA@Ast-ReportDB HINT: In a moment you should be able to reconnect to the database and repeat your command.

2020-12-23 01:16:14.356 +04 [31155] rptDBA@Ast-ReportDB CONTEXT: SQL statement "INSERT INTO lt_encounter( encounter_id, customer_id, customer_name, site_id, site_territory, site_cluster, site_emirates, site_long_name, site_short_name, encounter_date, encounter_created_on, enounter_no, patient_mpi, patient_name, pateint_nationality, patient_nationality_id, patient_gender, pateint_age, encounter_visit_purpose, encounter_payer_type, encounter_end_date, is_parent_encounter, parent_encounter_no, encounter_type_id, encounter_status_id, parent_encounter_id, primary_encounter_id,patient_email, patient_passport,patient_address,mobile_number ) SELECT e.encounter_id, e.customer_id, e.customer_name, e.site_id, e.site_territory, e.site_cluster, e.site_emirates, e.site_name, e.site_alias, e.encounter_date, e.encounter_created_on, e.encounter_no, e.mpi, TRIM(REGEXP_REPLACE(e.patient_name, '\s+', ' ', 'g')), e.nationality, e.nationality_id, e.patient_gender, e.patient_age, e.visit_purpose, e.payer_type, e.encounter_end_date, e.is_parent_encounter, e.parent_encounter_no, e.encounter_type_id, e.encounter_status_id, e.parent_encounter_id, e.primary_encounter_id,e.email, e.passport,e.address,e.mobile_no FROM JSON_TO_RECORDSET(_encounters) AS e ( "encounter_id" BIGINT, "customer_id" INT, "customer_name" VARCHAR, "site_id" INT, "site_territory" VARCHAR, "site_cluster" VARCHAR, "site_emirates" VARCHAR, "site_name" VARCHAR, "site_alias" VARCHAR, "encounter_date" TIMESTAMP(0), "encounter_created_on" TIMESTAMP(0), "encounter_no" VARCHAR, "mpi" VARCHAR, "patient_name" VARCHAR, "nationality" VARCHAR, "nationality_id" VARCHAR, "patient_gender" VARCHAR, "patient_age" VARCHAR, "visit_purpose" VARCHAR, "payer_type" VARCHAR, "encounter_end_date" TIMESTAMP(0), "is_parent_encounter" BOOLEAN, "parent_encounter_no" VARCHAR, "encounter_type_id" INT, "encounter_status_id" INT, "parent_encounter_id" BIGINT, "primary_encounter_id" BIGINT,"email" varchar, "passport" varchar,"address" varchar,"mobile_no" varchar ) ON CONFLICT ON CONSTRAINT lt_encounter_pkey DO UPDATE SET ( last_updated_on, customer_id, customer_name, site_id, site_territory, site_cluster, site_emirates, site_long_name, site_short_name, encounter_date, encounter_created_on, enounter_no, patient_mpi, patient_name, pateint_nationality, patient_nationality_id, patient_gender, pateint_age, encounter_visit_purpose, encounter_payer_type, encounter_end_date, is_parent_encounter, parent_encounter_no, encounter_type_id, encounter_status_id, parent_encounter_id, primary_encounter_id,patient_email, patient_passport,patient_address,mobile_number ) = ( clock_timestamp()::TIMESTAMP(0), EXCLUDED.customer_id, EXCLUDED.customer_name, EXCLUDED.site_id, EXCLUDED.site_territory, EXCLUDED.site_cluster, EXCLUDED.site_emirates, EXCLUDED.site_long_name, EXCLUDED.site_short_name, EXCLUDED.encounter_date, EXCLUDED.encounter_created_on, EXCLUDED.enounter_no, EXCLUDED.patient_mpi, EXCLUDED.patient_name, EXCLUDED.pateint_nationality, EXCLUDED.patient_nationality_id, EXCLUDED.patient_gender, EXCLUDED.pateint_age, EXCLUDED.encounter_visit_purpose, EXCLUDED.encounter_payer_type, EXCLUDED.encounter_end_date, EXCLUDED.is_parent_encounter, EXCLUDED.parent_encounter_no, EXCLUDED.encounter_type_id, EXCLUDED.encounter_status_id, EXCLUDED.parent_encounter_id, EXCLUDED.primary_encounter_id,EXCLUDED.patient_email, EXCLUDED.patient_passport,EXCLUDED.patient_address,EXCLUDED.mobile_number )" PL/pgSQL function lt_insert_update_encounters(json) line 21 at SQL statement 

2020-12-23 01:16:14.570 +04 [18450] LOG: all server processes terminated; reinitializing 2020-12-23 01:16:14.826 +04 [31201] LOG: database system was interrupted; last known up at 2020-12-23 01:12:30 +04 2020-12-23 01:16:14.932 +04 [31201] LOG: database system was not properly shut down; automatic recovery in progress 2020-12-23 01:16:14.937 +04 [31201] LOG: redo starts at 5C/2DC41798 2020-12-23 01:16:22.302 +04 [31201] LOG: invalid record length at 5C/6FD96D10: wanted 24, got 0 2020-12-23 01:16:22.302 +04 [31201] LOG: redo done at 5C/6FD96CD8 2020-12-23 01:16:22.302 +04 [31201] LOG: last completed transaction was at log time 2020-12-23 01:15:14.852692+04 2020-12-23 01:16:32.813 +04 [18450] LOG: database system is ready to accept connections 2020-12-23 01:26:00.119 +04 [31209] rptDBA@Ast-ReportDB LOG: could not receive data from client: Connection reset by peer 2020-12-24 01:14:27.047 +04 [18450] LOG: server process (PID 8936) was terminated by signal 9: Killed 2020-12-24 01:14:27.047 +04 [18450] DETAIL: Failed process was running: autovacuum: ANALYZE public.lt_encounter 2020-12-24 01:14:27.148 +04 [18450] LOG: terminating any other active server processes 2020-12-24 01:14:27.149 +04 [31206] WARNING: terminating connection because of crash of another server process 2020-12-24 01:14:27.149 +04 [31206] 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. 2020-12-24 01:14:27.149 +04 [31206] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2020-12-24 01:14:27.162 +04 [8929] rptDBA@Ast-ReportDB WARNING: terminating connection because of crash of another server process 2020-12-24 01:14:27.162 +04 [8929] rptDBA@Ast-ReportDB 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. 2020-12-24 01:14:27.162 +04 [8929] rptDBA@Ast-ReportDB HINT: In a moment you should be able to reconnect to the database and repeat your command.

From: Rohit Rajput <rht.rajput@yahoo.com>
Date: Tuesday, 29 December 2020 at 1:06 PM
To: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>, Sreejith P <sreejith@lifetrenz.com>
Subject: Re: Insert 1 million data

Solutions for Care Anywhere