PDF cheatsheet

Some common PDF-related commands I find myself doing and forgetting.

# Convert a bunch of images to an OCR'd PDF
# Relies on my own img2pdf script:
#  http://kueda.net/blog/2015/06/20/converting-images-of-text-to-searchable-pdfs/
#  https://gist.github.com/kueda/c02b9f3f5a0f03f41524
img2pdf *.jpg
 
# Make a PDF of scanned page images searchable
# pdfimages comes with Poppler, which you'll need to get img2pdf working
pdfimages PDF_NAME IMAGE_NAME_ROOT
mogrify -negate *.pbm # pdfimages seems to invert colors for some reason
img2pdf *.pbm

postgres cheatsheet

psql

# copy data to csv
psql yourdb -c "COPY (SELECT * FROM tablename) TO STDOUT WITH CSV HEADER" > file.csv
 
# copy data out, load it back in
psql yourdb -c "COPY (SELECT * FROM tablename) TO STDOUT WITH CSV" > file.csv
psql yourdb -c "\copy tablename FROM 'file.csv' WITH CSV"

Stats

View more about stats tables at http://www.postgresql.org/docs/9.2/static/monitoring-stats.html

-- view currently running queries
SELECT client_addr, query_start, current_query FROM pg_stat_activity;

Admin

The first step is always to have a relaxing beverage in hand…

Connecting multiple servers

http://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html is a good guide. The most salient points in addition to editing pg_hba.conf are

  1. both client and server firewalls must allow TCP connections from each other on port 5432
  2. you have to alter listen_addresses in postgresql.conf

Some useful debugging commands:

# Check that the port is open from the client. Make sure your firewalls are
# configured correctly on both ends and that SSL has been set up and
# configured
nmap -v -sV remote_host -p 5432
 
# Both of these will tell you if pg is up and listening on the server machine
netstat -plnt on localhost
lsof -i:5432

Setting Up SSL

http://www.howtoforge.com/postgresql-ssl-certificates is a good step-by-step. Note that in ubuntu pg’s data dir seems to live somewhere like /var/lib/postgresql/9.1/main/, and that sever.crt and server.key are already there as symlinks to a self-signed certificate. Seems like you still have to copy server.crt to root.crt, though, and of course you need to make your client certs. Setting the -subj flag to something simple like '/CN=inaturalist' seemed to work for me.