Lubberly & Unnavylike

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. Note that if it says it's "closed" that probably means 
# postgres is not allowing connections, not that you have a firewall issue. If iptables was blocking it, 
# nmap would say the port was "filtered"
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.