Reload
pg_ctl -D . reload
Stop – soft
pg_ctl -D . stop -m i
Stop – force
pg_ctl -D . stop -m f
Start
pg_ctl -D . start
/usr/bin/postmaster -p 5432 -D /dev/postgres/pgsql/data > /dev/postgres/pgsql/postmaster_logfile 2>&1&
Restart
pg_ctl -D . restart
Restart to particular port
pg_ctl -D . restart -m f -o '-p 5435'
PG_DUMP EXPORT
pg_dump -p 5432 --format=c --inserts --encoding=UTF8 mydatabase > mydatabase-21-11-2012-1232.sql
better
screen pg_dump -p 5432 -U postgres --format=c --inserts --encoding=UTF8 mydatabase > mydatabase-06-12.sql
PG_RESTORE IMPORT
pg_restore -p 5432 --format=c -v -d mydatabase --table=movie_fts_index mydatabase-21-11-2012-1232.sql
/usr/bin/pg_restore -p 5432 --format=c -v -d mydatabase-21-01-2012-13001.sql >> /data/dump/import.log 2>&1&
nohup /usr/bin/pg_restore -p 5432 --format=c -v -d mydatabase /data/dump/mydatabase-21-01-2012-1300.sql >> /data/dump/import.log 2>&1&
PG_RESTORE via psql
psql -p 5432 -v -d mydatabase -f mydatabase-21-11-2012-1232.sql
List Locks
select * from pg_locks where pid=24359 limit 10;
check if vacuum is running
ps -ef|grep VACUUM
#15872
Quick Database Vacuum Analyze
vacuumdb -p 5432 -d mydatabase --verbose --analyze
Full Import Schedule Restart in another port
cd ~/pgsql/data pg_ctl -D . restart -m f -o '-p 5433' pg_dump -p 5433 mydatabase --format=c --inserts --encoding=UTF8 > /data/dumps/mydatabase.sql
Postgres Database – Who is Currently Connected
SELECT * FROM pg_stat_activity order by client_addr;
IO Reads and Writes
select * from pg_statio_user_tables where relname in ('mytable1', 'mytable2', 'mytable3') order by schemaname; stats_block_level = on;
Exec Statements Stepwise – bash
screen t=122000; tstep=1000; while [[ t -lt 4000000 ]] ; do echo doing between $t and $((t + tstep - 1)); echo 'set statement_timeout to 0; update metadata set m_number = 7 where id between ' $t and $((t + tstep - 1))';' | time /usr/bin/psql -p 5432 -x -d mydatabase -U postgres -e -f - ; t=$((t + tstep)) ; sleep 1; done
Create a new User
createuser -p 5432 -D -A -e myuser;
Grant ALL
GRANT ALL ON mydatabse TO myuser;
Database Privileges Change Password
ALTER USER myuser WITH PASSWORD 'mypwd';
CREATE A NEW DATABASE
CREATE DATABASE mydatabase WITH ENCODING 'UTF8' OWNER myuser;
Set Postgres System Variables and Properties
set statement_timeout to 10; -- in millisecs
Show Postgres System Variables and Properties
show statement_timeout; show default_tablespace;
Show Path and Environment
env
time psql -p 5432 mydatabase << _SEPARATOR_ select * from mytable where id = 910300; SELECT pg_sleep(2); select * from mytable where id = 910301; _SEPARATOR_