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" |
# 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; |
-- 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
- both client and server firewalls must allow TCP connections from each other on port 5432
- 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 |
# 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.