# 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 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;
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
- both client and server firewalls must allow TCP connections from each other on port 5432
- you have to alter
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
server.key are already there as symlinks to a self-signed certificate. Seems like you still have to copy
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.