Invalid connection! (PG::Error)

[ashawley]

I get the error Invalid connection! (PG::Error) from the Postgres source database with a large table that is about 33GB. After about 40 minutes, it gives the following error:

$ postgres_to_redshift
INFO:  Relation "submissions" already exists and will be skipped
Downloading submissions
Uploading submissions.1
Uploading submissions.2
Uploading submissions.3
Uploading submissions.4
Uploading submissions.5
Uploading submissions.6
Uploading submissions.7
Uploading submissions.8
Uploading submissions.9
/usr/local/share/ruby/gems/2.2/gems/pg-0.17.1/lib/pg/connection.rb:140:in `cancel': Invalid connection! (PG::Error)
        from /usr/local/share/ruby/gems/2.2/gems/pg-0.17.1/lib/pg/connection.rb:140:in `rescue in copy_data'
        from /usr/local/share/ruby/gems/2.2/gems/pg-0.17.1/lib/pg/connection.rb:137:in `copy_data'
        from ~/.gem/ruby/2.2/gems/postgres_to_redshift-0.1.1/lib/postgres_to_redshift.rb:97:in `copy_table'
        from ~/.gem/ruby/2.2/gems/postgres_to_redshift-0.1.1/lib/postgres_to_redshift.rb:27:in `block in update_tables'
        from ~/.gem/ruby/2.2/gems/postgres_to_redshift-0.1.1/lib/postgres_to_redshift.rb:24:in `each'
        from ~/.gem/ruby/2.2/gems/postgres_to_redshift-0.1.1/lib/postgres_to_redshift.rb:24:in `update_tables'
        from ~/.gem/ruby/2.2/gems/postgres_to_redshift-0.1.1/bin/postgres_to_redshift:5:in `<top (required)>'
        from ~/bin/postgres_to_redshift:23:in `load'
        from ~/bin/postgres_to_redshift:23:in `<main>'

This happens for a 16GB table, as well.

NOTE: That I am using the code from #15 to split files up and the code from #14 to avoid out-of memory.

Is the database connection getting lost? I've modified the following settings on the Postgres 9.3 RDS instance at AWS:

tcp_keepalives_count      10
tcp_keepalives_idle     1200
tcp_keepalives_interval 1200

Seems like it should allow 20 minutes of idle and check up to 10 times, but error keeps happening.

[toothrot]

Hey @ashawley,

It looks like those parameters are ignored on Unix sockets, so I'm not sure if they help: https://www.postgresql.org/docs/9.5/static/runtime-config-connection.html

I was going to add some connection pooling and retry logic. PG connections are cheap to open, so we could just open a fresh one for every query, tbh.

[ashawley]

I actually tried reopening fresh connections in b4848f8. Unfortunately the same error kept happening. Although, the script did seem to get further along...