Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.