Insert 1 million data

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Insert 1 million data

Sreejith P

Getting below error while running a background job for inserting 1 Million records.

 

Can I get some help ?

 

cp: error writing '/BackupVolume/hisDbBackup/WALs/0000000200000AE6000000B9': No space left on device

2020-12-17 21:55:26 +04 [55822]: user=,db=,app=,client= LOG:  archive command failed with exit code 1

2020-12-17 21:55:26 +04 [55822]: user=,db=,app=,client= DETAIL:  The failed archive command was: test ! -f /BackupVolume/dbWALBack/WALs/0000000200000AE6000000B9 && cp pg_wal/0000000200000AE6000000B9 /BackupVolume/hisDbBackup/WALs/0000000200000AE6000000B9

 

2020-12-17 21:53:56 +04 [84225]: user=AstDBA,db=AST-PROD,app=[unknown],client=172.18.200.100 HINT:  In a moment you should be able to reconnect to the database and repeat your command.

2020-12-17 21:53:56 +04 [51102]: user=AstDBA,db=AST-PROD,app=[unknown],client=172.18.200.100 WARNING:  terminating connection because of crash of another server process

2020-12-17 21:53:56 +04 [51102]: user=AstDBA,db=AST-PROD,app=[unknown],client=172.18.200.100 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-17 21:55:20 +04 [55808]: user=replicator,db=[unknown],app=[unknown],client=172.18.200.144 FATAL:  the database system is in recovery mode

 

 

2020-12-17 21:55:25 +04 [55828]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.148 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55827]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.147 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55829]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.145 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55830]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.146 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55831]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.144 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

 

Thanks

Sreejith

 

 


 

Solutions for Care Anywhere


dWise HealthCare IT Solutions Pvt. Ltd. | www.lifetrenz.com
Disclaimer: The information and attachments contained in this email are intended for exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender immediately and destroy all copies of this message and any attachments. The views expressed in this email are, unless otherwise stated, those of the author and not those of dWise HealthCare IT Solutions or its management.
Reply | Threaded
Open this post in threaded view
|

Re: Insert 1 million data

Rohit Rajput
If you look at error, it clearly says that your WAL directory is full when you are loading your data. You should get more space added to your disk where you have placed your WAL files.

On Tuesday, 29 December, 2020, 12:55:01 pm IST, Sreejith P <[hidden email]> wrote:


Getting below error while running a background job for inserting 1 Million records.

 

Can I get some help ?

 

cp: error writing '/BackupVolume/hisDbBackup/WALs/0000000200000AE6000000B9': No space left on device

2020-12-17 21:55:26 +04 [55822]: user=,db=,app=,client= LOG:  archive command failed with exit code 1

2020-12-17 21:55:26 +04 [55822]: user=,db=,app=,client= DETAIL:  The failed archive command was: test ! -f /BackupVolume/dbWALBack/WALs/0000000200000AE6000000B9 && cp pg_wal/0000000200000AE6000000B9 /BackupVolume/hisDbBackup/WALs/0000000200000AE6000000B9

 

2020-12-17 21:53:56 +04 [84225]: user=AstDBA,db=AST-PROD,app=[unknown],client=172.18.200.100 HINT:  In a moment you should be able to reconnect to the database and repeat your command.

2020-12-17 21:53:56 +04 [51102]: user=AstDBA,db=AST-PROD,app=[unknown],client=172.18.200.100 WARNING:  terminating connection because of crash of another server process

2020-12-17 21:53:56 +04 [51102]: user=AstDBA,db=AST-PROD,app=[unknown],client=172.18.200.100 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-17 21:55:20 +04 [55808]: user=replicator,db=[unknown],app=[unknown],client=172.18.200.144 FATAL:  the database system is in recovery mode

 

 

2020-12-17 21:55:25 +04 [55828]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.148 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55827]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.147 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55829]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.145 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55830]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.146 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55831]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.144 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

 

Thanks

Sreejith

 

 


 

Solutions for Care Anywhere


dWise HealthCare IT Solutions Pvt. Ltd. | www.lifetrenz.com
Disclaimer: The information and attachments contained in this email are intended for exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender immediately and destroy all copies of this message and any attachments. The views expressed in this email are, unless otherwise stated, those of the author and not those of dWise HealthCare IT Solutions or its management.
Reply | Threaded
Open this post in threaded view
|

Re: Insert 1 million data

Sreejith P

Thanks Rohit.

 

After upgrading volume getting following error. Almost same as previous one.

 

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 <[hidden email]>
Date: Tuesday, 29 December 2020 at 1:06 PM
To: "[hidden email]" <[hidden email]>, Sreejith P <[hidden email]>
Subject: Re: Insert 1 million data

 

If you look at error, it clearly says that your WAL directory is full when you are loading your data. You should get more space added to your disk where you have placed your WAL files.

 

On Tuesday, 29 December, 2020, 12:55:01 pm IST, Sreejith P <[hidden email]> wrote:

 

 

Getting below error while running a background job for inserting 1 Million records.

 

Can I get some help ?

 

cp: error writing '/BackupVolume/hisDbBackup/WALs/0000000200000AE6000000B9': No space left on device

2020-12-17 21:55:26 +04 [55822]: user=,db=,app=,client= LOG:  archive command failed with exit code 1

2020-12-17 21:55:26 +04 [55822]: user=,db=,app=,client= DETAIL:  The failed archive command was: test ! -f /BackupVolume/dbWALBack/WALs/0000000200000AE6000000B9 && cp pg_wal/0000000200000AE6000000B9 /BackupVolume/hisDbBackup/WALs/0000000200000AE6000000B9

 

2020-12-17 21:53:56 +04 [84225]: user=AstDBA,db=AST-PROD,app=[unknown],client=172.18.200.100 HINT:  In a moment you should be able to reconnect to the database and repeat your command.

2020-12-17 21:53:56 +04 [51102]: user=AstDBA,db=AST-PROD,app=[unknown],client=172.18.200.100 WARNING:  terminating connection because of crash of another server process

2020-12-17 21:53:56 +04 [51102]: user=AstDBA,db=AST-PROD,app=[unknown],client=172.18.200.100 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-17 21:55:20 +04 [55808]: user=replicator,db=[unknown],app=[unknown],client=172.18.200.144 FATAL:  the database system is in recovery mode

 

 

2020-12-17 21:55:25 +04 [55828]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.148 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55827]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.147 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55829]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.145 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55830]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.146 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55831]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.144 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

 

Thanks

Sreejith

 

 

 

Error! Filename not specified.

 

Solutions for Care Anywhere


dWise HealthCare IT Solutions Pvt. Ltd. | www.lifetrenz.com
Disclaimer: The information and attachments contained in this email are intended for exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender immediately and destroy all copies of this message and any attachments. The views expressed in this email are, unless otherwise stated, those of the author and not those of dWise HealthCare IT Solutions or its management.


 

Solutions for Care Anywhere


dWise HealthCare IT Solutions Pvt. Ltd. | www.lifetrenz.com
Disclaimer: The information and attachments contained in this email are intended for exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender immediately and destroy all copies of this message and any attachments. The views expressed in this email are, unless otherwise stated, those of the author and not those of dWise HealthCare IT Solutions or its management.
Reply | Threaded
Open this post in threaded view
|

AW: Insert 1 million data

Dischner, Anton

Hi,

 

try a

 

watch df -h

 

while you are running your job to see how filesystem space decreases over time

 

regards,

 

Anton

 

Von: Sreejith P <[hidden email]>
Gesendet: Dienstag, 29. Dezember 2020 10:56
An: Rohit Rajput <[hidden email]>; [hidden email]
Betreff: Re: Insert 1 million data

 

Thanks Rohit.

 

After upgrading volume getting following error. Almost same as previous one.

 

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 <[hidden email]>
Date: Tuesday, 29 December 2020 at 1:06 PM
To: "[hidden email]" <[hidden email]>, Sreejith P <[hidden email]>
Subject: Re: Insert 1 million data

 

If you look at error, it clearly says that your WAL directory is full when you are loading your data. You should get more space added to your disk where you have placed your WAL files.

 

On Tuesday, 29 December, 2020, 12:55:01 pm IST, Sreejith P <[hidden email]> wrote:

 

 

Getting below error while running a background job for inserting 1 Million records.

 

Can I get some help ?

 

cp: error writing '/BackupVolume/hisDbBackup/WALs/0000000200000AE6000000B9': No space left on device

2020-12-17 21:55:26 +04 [55822]: user=,db=,app=,client= LOG:  archive command failed with exit code 1

2020-12-17 21:55:26 +04 [55822]: user=,db=,app=,client= DETAIL:  The failed archive command was: test ! -f /BackupVolume/dbWALBack/WALs/0000000200000AE6000000B9 && cp pg_wal/0000000200000AE6000000B9 /BackupVolume/hisDbBackup/WALs/0000000200000AE6000000B9

 

2020-12-17 21:53:56 +04 [84225]: user=AstDBA,db=AST-PROD,app=[unknown],client=172.18.200.100 HINT:  In a moment you should be able to reconnect to the database and repeat your command.

2020-12-17 21:53:56 +04 [51102]: user=AstDBA,db=AST-PROD,app=[unknown],client=172.18.200.100 WARNING:  terminating connection because of crash of another server process

2020-12-17 21:53:56 +04 [51102]: user=AstDBA,db=AST-PROD,app=[unknown],client=172.18.200.100 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-17 21:55:20 +04 [55808]: user=replicator,db=[unknown],app=[unknown],client=172.18.200.144 FATAL:  the database system is in recovery mode

 

 

2020-12-17 21:55:25 +04 [55828]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.148 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55827]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.147 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55829]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.145 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55830]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.146 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

2020-12-17 21:55:25 +04 [55831]: user=replicator,db=[unknown],app=walreceiver,client=172.18.200.144 ERROR:  requested starting point AEA/A2000000 is ahead of the WAL flush position of this server AEA/A1FFFA50

 

Thanks

Sreejith

 

 

 

Error! Filename not specified.

 

Solutions for Care Anywhere


dWise HealthCare IT Solutions Pvt. Ltd. | www.lifetrenz.com
Disclaimer: The information and attachments contained in this email are intended for exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender immediately and destroy all copies of this message and any attachments. The views expressed in this email are, unless otherwise stated, those of the author and not those of dWise HealthCare IT Solutions or its management.

 

 

Solutions for Care Anywhere


dWise HealthCare IT Solutions Pvt. Ltd. | www.lifetrenz.com
Disclaimer: The information and attachments contained in this email are intended for exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender immediately and destroy all copies of this message and any attachments. The views expressed in this email are, unless otherwise stated, those of the author and not those of dWise HealthCare IT Solutions or its management.

Reply | Threaded
Open this post in threaded view
|

Re: Insert 1 million data

Olivier Gautherot-2
In reply to this post by Sreejith P
Hi Sreejit,

On Tue, Dec 29, 2020 at 10:56 AM Sreejith P <[hidden email]> wrote:

Thanks Rohit.

 

After upgrading volume getting following error. Almost same as previous one.

 

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 ? 

If you're tight on space, my recommendation would be to run the inserts in small batches (say 10,000 at a time). Don't turn off autovaccum, ever :-)

That being said, if you're suffering this way when creating your database, my inclination would be to move it with its logs to a disk with more space. Your server has no scalability and you'll suffer more dramatic crashes very quickly.

My cent worth...
--
Olivier Gautherot