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.
\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_count=5, but the connection still lost after 10-15 minutes.