5

Postgres version 9.3, hosted on Amazon RDS. I have a messages table with 10G of data. I connected the database using psql on the EC2. Then used

\COPY (select * from messages) TO '/tmp/messages.csv' WITH (FORMAT CSV, FORCE_QUOTE *)

to copy all messages into a file on the EC2.

Then I try to copy the file back to the table:

\COPY messages FROM '/tmp/messages.csv' WITH (FORMAT CSV)

I always got this error after around 5 minutes. This messages.csv file is around 10G.

connection not open
The connection to the server was lost. Attempting reset: Succeeded.

I tried a dumb solution, to reduce the input data size, by spliting the file into multiple smaller trunks. ie 400MB each.

split -l 1000000 messages.csv messages_

This creates multiple smaller files with 400MB each. This works fine.

But is there any configurations I can change to keep the connections alive for large file?

I tried to set tcp_keepalives_idle=7200 and tcp_keepalives_interval=7200 and tcp_keepalives_count=5, but the connection still lost after 10-15 minutes.

  • This shouldn't happen. I'm wondering if there's an issue with your networking infrastructure. Have a look at the logs from the RDS instance to see what the server side saw when the connection broke. BTW, Amazon recommend that you COPY from S3 instead where possible. – Craig Ringer Jun 3 '15 at 0:49
  • Thank you for the reply, the connection is between EC2 and RDS, I ssh to EC2, then on EC2 I use psql to connection to RDS. And I can only use the client version \COPY only, since I cannot connect to RDS directly to use the server version COPY. – Zhaohan Weng Jun 9 '15 at 19:52
  • psql db_url < data.sql Currently I am using this command, I modified the schema to include the trigger, then start restoring data seems fine, it did not disconnect at all. – Zhaohan Weng Jun 9 '15 at 19:55
  • Could you give us the result of select name,setting from pg_settings where name like '%timeout%' order by name ? – Rodolphe Nov 16 '17 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.