postgres cheatsheet


# 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"


View more about stats tables at

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


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

Connecting multiple servers 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 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.