Postgres sql file insert hanging

Question

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.

Laurenz Albe · Accepted Answer · 2016-07-29 11:15:31Z

1

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.

answered Jul 29, 2016 at 11:15

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

EamonnMcElroy Over a year ago

Thanks for the advice. Was thinking it was still running but it would be nice to see some logging other than errors to show that progress is being made. Is this possible? Tried finding a command but no luck other than basic logging being sent to a file of my choosing.

Laurenz Albe Over a year ago

You can use the -a option of psql to have it echo the current statement.

EamonnMcElroy Over a year ago

So would that be \i filename.sql -a

Laurenz Albe Over a year ago

No, psql -a. Inside psql you'd have to \set ECHO all.

Collectives™ on Stack Overflow

Postgres sql file insert hanging

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related