Postgres – Tips and Tricks

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_