Reload
|
1 |
pg_ctl -D . reload |
Stop – soft
|
1 |
pg_ctl -D . stop -m i |
Stop – force
|
1 |
pg_ctl -D . stop -m f |
Start
|
1 |
pg_ctl -D . start |
|
1 |
/usr/bin/postmaster -p 5432 -D /dev/postgres/pgsql/data > /dev/postgres/pgsql/postmaster_logfile 2>&1& |
Restart
|
1 |
pg_ctl -D . restart |
Restart to particular port
|
1 |
pg_ctl -D . restart -m f -o '-p 5435' |
PG_DUMP EXPORT
|
1 |
pg_dump -p 5432 --format=c --inserts --encoding=UTF8 mydatabase > mydatabase-21-11-2012-1232.sql |
better
|
1 2 |
screen pg_dump -p 5432 -U postgres --format=c --inserts --encoding=UTF8 mydatabase > mydatabase-06-12.sql |
PG_RESTORE IMPORT
|
1 |
pg_restore -p 5432 --format=c -v -d mydatabase --table=movie_fts_index mydatabase-21-11-2012-1232.sql |
|
1 |
/usr/bin/pg_restore -p 5432 --format=c -v -d mydatabase-21-01-2012-13001.sql >> /data/dump/import.log 2>&1& |
|
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
|
1 |
psql -p 5432 -v -d mydatabase -f mydatabase-21-11-2012-1232.sql |
List Locks
|
1 |
select * from pg_locks where pid=24359 limit 10; |
check if vacuum is running
|
1 |
ps -ef|grep VACUUM |
#15872
Quick Database Vacuum Analyze
|
1 |
vacuumdb -p 5432 -d mydatabase --verbose --analyze |
Full Import Schedule Restart in another port
|
1 2 3 |
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
|
1 |
SELECT * FROM pg_stat_activity order by client_addr; |
IO Reads and Writes
|
1 2 3 4 5 |
select * from pg_statio_user_tables where relname in ('mytable1', 'mytable2', 'mytable3') order by schemaname; stats_block_level = on; |
Exec Statements Stepwise – bash
|
1 2 3 4 5 6 7 8 9 |
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
|
1 |
createuser -p 5432 -D -A -e myuser; |
Grant ALL
|
1 |
GRANT ALL ON mydatabse TO myuser; |
Database Privileges Change Password
|
1 |
ALTER USER myuser WITH PASSWORD 'mypwd'; |
CREATE A NEW DATABASE
|
1 |
CREATE DATABASE mydatabase WITH ENCODING 'UTF8' OWNER myuser; |
Set Postgres System Variables and Properties
|
1 |
set statement_timeout to 10; -- in millisecs |
Show Postgres System Variables and Properties
|
1 2 |
show statement_timeout; show default_tablespace; |
Show Path and Environment
|
1 |
env |
|
1 2 3 4 5 6 |
time psql -p 5432 mydatabase << _SEPARATOR_ select * from mytable where id = 910300; SELECT pg_sleep(2); select * from mytable where id = 910301; _SEPARATOR_ |
