Monday 21 November 2011

Certificate based authentication on Linux

You can create your own certificate and use it in order to authenticate on Linux machines while connecting through SSH. But be aware that SSHD (SSH daemon is the one that accepts SSH connections, and can be perceived as SSH server) is very sensitive to file permissions. If you set wrong permissions on .ssh or .ssh/authorized_keys, your certificate won't work. Here are the correct file permissions (considering that you're in home folder of remote machine target user):

$ chmod 0700 .ssh
$ chmod 0600 .ssh/authorized_keys

Wednesday 16 November 2011

Retrieving large resultsets from postgres using psql

Consider that you have a large table in database and you want to export it to a CSV file. What's your first idea? I started using PgAdmin - wrote a simple SELECT and ran "Execute to file". But it gave me out "out of memory for query result". It turns out that PgAdmin tries to load the resultset into memory (client side), but fails since memory limitations. Then I tried psql with same results. Then I found really good variable named FETCH_COUNT in http://www.postgresql.org/docs/9.1/static/app-psql.html and here's how I used it:

statistics=#\a
statistics=#\f ,
statistics=#\set FETCH_COUNT 500000
statistics=#select * from MY_HUGE_TABLE
statistics-#\g /home/arvids/statistics.csv
  1. Disable aligned mode, because we don't want extra whitespaces in CSV file
  2. Then set field separator to comma (it's a CSV file after all)
  3. Then set fetch count to 500000 so that client (my computer) could load the partial resultset into memory
  4. Now execute query
  5. ... and route it to a file