CODEWITHSUNDEEP
postgresql
EamonnMcElroy
EamonnMcElroy

Reputation: 607

Postgres sql file insert hanging

I am attempting to insert data from an sql file which is about 1.6GB in size. I used the command:

\i filename.sql

however it had been running for well over ten minutes. Initially when I ran it I got output such as below:

psql:xxx.sql:1102: ERROR:  role "xxx" does not exist
psql:xxx.sql:1124: ERROR:  relation "xxx" already exists
psql:xxx.sql:1127: ERROR:  role "xxx" does not exist
psql:xxx.sql:1138: ERROR:  relation "xxx" already exists
psql:xxx.sql:1141: ERROR:  role "xxx" does not exist
psql:xxx.sql:1177: ERROR:  relation "xxx" already exists
psql:xxx.sql:1180: ERROR:  role "xxx" does not exist
psql:xxx.sql:1191: ERROR:  relation "xxx" already exists
psql:xxx.sql:1194: ERROR:  role "xxx" does not exist
psql:xxx.sql:1223: ERROR:  relation "xxx" already exists
psql:xxx.sql:1226: ERROR:  role "xxx" does not exist
psql:xxx.sql:1237: ERROR:  relation "xxx" already exists
psql:xxx.sql:1240: ERROR:  role "xxx" does not exist

This continues until the final output was:

psql:xxx.sql:1183266: ERROR:  duplicate key value violates unique constraint "xxx"
DETAIL:  Key (id)=(2) already exists.
CONTEXT:  COPY mo_logs, line 1: "2  +6421558671     +15759151518    LolWithPlus     unable to deliver, dropping : undefined method `bytesize' for..."

Then the console shows nothing new since then. On my first attempt I thought it was hanging so exited but got the following output:

Cancel request sent

This leads me to believe the script is still running but I have no way of telling. Can anyone advise how I can either diagnose what is going on or give me an indication of whether the insert is failing as shown by it hanging.

Upvotes: 1

Views: 820

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248255

It seems like the SQL script contains several COPY statements.
One of them (COPY TO mo_logs) failed, and another of them (COPY TO midn_protections) was running when you interrupted processing.

Normally psql continues working after an error and will just execute the next statement. To avoid that, start it with the option --set ON_ERROR_STOP=on.

Upvotes: 1

Related Questions