postgres/psql commands

postgres.md

Postgres Commands

current activity

SELECT datname,usename,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity;

shared buffer in OS X

sudo sysctl -w kern.sysv.shmmax=209715200
sudo sysctl -w kern.sysv.shmall=209715200

// To read:
sysctl kern.sysv.shmmax
sysctl kern.sysv.shmall

kill connections

psql -U postgres -h localhost -d db -c "SELECT pg_terminate_backend( procpid ) FROM pg_stat_activity WHERE procpid <> pg_backend_pid() AND datname = current_database();"

copy database

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

execute sql file

psql -Upostgres -d db -f file.sql