Home / server / Questions / 366731
Accepted
Majid Azimi
Asked: 2012-03-06 23:01:02 +0800 CST

ssl handshake failure with COPY on PostgreSQL

  • 772

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.

ssl postgresql copy psql

1 Answers

  1. Best Answer

    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... ;-)

    • 2