ssl handshake failure with COPY on PostgreSQL

Question

I'm restoring a CSV file to database with COPY command. The csv file is bziped. I use this command:

bunzip2 -c -d online-20110923000001.csv.bz2 | psql -U user -h 127.0.0.1 -d testdb -c "COPY data FROM STDIN WITH CSV HEADER;"

After some time it stops with:

SSL error: ssl handshake failure
connection was lost to database

Log file shows:

2012-03-06 10:01:10 IRST STATEMENT:  COPY data FROM STDIN WITH CSV HEADER;
2012-03-06 10:01:10 IRST LOG:  SSL error: ssl handshake failure
2012-03-06 10:01:10 IRST CONTEXT:  COPY data, line 8349702
2012-03-06 10:01:10 IRST STATEMENT:  COPY data FROM STDIN WITH CSV HEADER;
2012-03-06 10:01:10 IRST LOG:  could not receive data from client: Connection reset by peer
2012-03-06 10:01:10 IRST CONTEXT:  COPY data, line 8349702

Could anyone help? Does psql use SSL? How can I disable it? I restarted the command above and now it is working fine again. I haven't changed any PostgreSQL configuration. I'm running PostgreSQL 8.4.10 on Debian 6.

Tom · Accepted Answer · 2012-03-06T07:40:20.030

Some of the docs suggest that you can disable ssl connections using the option sslmode like so;

$ psql "service=myservice sslmode=disable"

this also seems to be the standard way including passing args explicitly, of which there are a few examples on google;

$ bunzip2 -c -d online-20110923000001.csv.bz2 |  \
psql "host=127.0.0.1 user=joe password=foo dbname=testdb sslmode=disable" \
-c "COPY data FROM STDIN WITH CSV HEADER;"

THere is also the conf file option so you could put these commands in /etc/postgresql-8.4/pg_service.conf

[myservice]
dbname=testdb
user=user
host=127.0.0.1
password=password.here

and that might work like so;

bunzip2 -c -d online-20110923000001.csv.bz2 | psql "service=myservice sslmode=disable"  -c "COPY data FROM STDIN WITH CSV HEADER;"

but its rejecting that invocation for me, but it is along those lines, and I dont have an postgresql server at hand, but I can update the answer later when I get on a work box.

the example page from the manual is;
http://www.postgresql.org/docs/8.4/static/app-psql.html

(hmm. I should have really left this question to someone who uses postgresql day-to-day... ;-)

ssl handshake failure with COPY on PostgreSQL

1 Answers1