Postgres Most Used Commands

I am in the process of putting my notes into web pages, please keep checking in later for more contents
 

 

 

 

quick link

create postgres user   kill process  


Postgres Home 
Postgres Online Manual 
Postgres Wiki 
Postgres FAQ 

top   prev   next  

Postgres Commands

pg_ctl stop 
pg_ctl stop -m immediate  # master db
pg_ctl stop -m fast    # standby db

pg_ctl start 
pg_ctl start -D /db/d/edb/v3/table/EDBD1
pg_ctl start -l /.../mydb.log -w -o -i
pg_ctl restart -w -o -i

pg_ctl status

g_ctl reload   cf: pg_reload_conf()  --> reload server configuration file;


PGDATA=/.../data
PGPORT=5436
PG_HOME=/.../9.2.0

PGUSER=dbpsvn
PGLOG="/db/p/svn/v1/admin/SVNP1/log/svnp1.log"

psql -d postgres -U postgres
psql -U my_username -d my_database_name -h myhostname -p 5436
psql --help

psql -d mydb -U my_login_user -e -L mylog.log -f my_script.sql 2>my_error.err

psql -d mydb -U my_login_user -e -f my_script.sql>mylog.log 2>&1

view owner:
\dt *.*
\dv *.*
alter table xxxx owner to yyyy;
alter view  xxxx owner to yyyy;

\l  --> list databases  ===> select datname from pg_database;

\c database
\c database username

\p  --> print buffer
\e  --> edit buffer

\o xxx -> spool to a file or spool off
\o

\i my_postgres_01.sql

\df *cancel*.* --> find a function

\g ---> postgrees vertical output
select datname,pg_database_size(datname) from pg_database;

\h
\set VERBOSITY verbose

*** set schema search path
SET search_path = new_schema
search_path = '$user,public,sys,dbo'    # schema names 


top   prev   next  

Postgres Data Dictionary

select * from pg_stat_activity;
select pg_cancel_backend(9999);   --- safe way to kill a process

-- --------------------------------------------------------------------------

\du
\du+

create user my_username with password 'my_username_pass';

grant select on cred_status to snacreadonly;
revoke insert,update,delete on cred_status from snacreadonly;
grant insert,update,delete on cred_status to snacreadwrite;

grant select               on all tables in schema public to snacreadonly;
grant insert,update,delete on all tables in schema public to snacreadwrite;

grant snacreadonly to my_username;
grant snacreadwrite to my_username;
grant snacreports to my_username;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLES FROM webuser;
ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
create or replace function function kill_pid(procpid integer)
 returns void
security definer
as
$$
  declare
    var1 text;
  begin
    select pg_cancel_backend(procpid) into var1;
  end;
$$ language plpgsql;

revoke execute on function kill_pid(procpid integer) from public;
grant execute on function kill_pid(procpid integer) to eubo017;
-- ---------------------------------------------------------------------------

select procpid, query_start, current_query from pg_stat_activity where current_query <> '' order by query_start desc ;

select procpid, client_addr, client_port, usename, query_start, current_query from pg_stat_activity where current_query <> '';

select datid, datname, procpid, usesysid, usename, waiting, xact_start, query_start, backend_start, client_addr, client_port from pg_stat_activity;

select datid, datname, procpid, usesysid, usename, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_port from pg_stat_activity;

select query_start,procpid, datname, usename, client_addr,  current_query from pg_stat_activity where current_query!='' order by query_start;

select
pg_stat_activity.datname,
pg_class.relname,
pg_locks.mode,
pg_locks.granted,
pg_stat_activity.usename,
-- substr(pg_stat_activity.current_query,1,30),
pg_stat_activity.query_start,
age(now(),
pg_stat_activity.query_start) as "age",
pg_stat_activity.procpid
from
pg_stat_activity,
pg_locks left outer join pg_class
on (pg_locks.relation = pg_class.oid)
where
pg_locks.pid=pg_stat_activity.procpid
and  pg_stat_activity.usename <> 'dbpsnac'
order by query_start;

select
 procpid, usename,
 query_start,
 current_query
from
pg_stat_activity
where usename in ( 'jewettdx' )
order by query_start desc ;


select
pg_locks.pid, pg_class.relname,pg_locks.locktype,
pg_locks.transactionid
from pg_locks, pg_class
where pg_locks.relation=pg_class.oid
order by 2,1;

select pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid,
pg_locks.mode, pg_locks.granted,pg_stat_activity.usename,
substr(pg_stat_activity.current_query,1,30),
pg_stat_activity.query_start, age(now(),
pg_stat_activity.query_start) as "age",
pg_stat_activity.procpid
from
pg_stat_activity,pg_locks left outer join pg_class
on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid
order by query_start;


select * from pg_locks;

select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted 
from 
pg_locks l, pg_stat_all_tables t 
where l.relation=t.relid order by relation asc;

select * from pg_class limit 1;
select * from pg_locks limit 1;

select * from pg_stat_activity;

select * from session_waits;

select * from pg_listener;


top   prev   next  

Table Maitenance

analyze table_name;
vaccum tabl_name;

EXPLAIN ANALYZE query;
EXPLAIN query;

top   prev   next  

Postgres Setting: postgresql.conf

show all;

select name,setting from pg_settings;
select name,setting from pg_settings where name like '%log%' order by 1;

# $log_directory/$log_filename
select a.setting || '/' || b.setting cmd from pg_settings a, pg_settings b where a.name='log_directory' and b.name = 'log_filename';

psql -A -t -q -c "select a.setting || '/' || b.setting cmd from pg_settings a, pg_settings b where a.name='log_directory' and b.name = 'log_filename'"
-e: echo commands
-a: all to output
-q: quite mode
-c: command
-P tuples_only=on,footer=off,border=0 --> don't work
-P tuples_only=on --> working
-L logfile.txt --> -L logfile
-t, --tuples-only        print rows only


select name,setting from pg_settings where name like '%redirect%' order by 1;
select name,setting from pg_settings where name like '%stderr%' order by 1;

shared_buffers = 2GB                  # min 128kB
work_mem = 2MB                                # min 64kB
maintenance_work_mem = 2GB            # min 1MB
max_stack_depth = 8MB                 # min 100kB
effective_io_concurrency = 8          # 1-1000. 0 disables prefetching
seq_page_cost = 1.0                   # measured on an arbitrary scale
random_page_cost = 1.0                        # same scale as above
cpu_tuple_cost = 0.5  # same scale as abov
cpu_index_tuple_cost = 0.005          # same scale as above
cpu_operator_cost = 0.05              # same scale as above
effective_cache_size = 4GB

work_mem                        | 2MB
temp_buffers                    | 1024
shared_buffers                  | 1GB
effective_cache_size            | 128MB
autovacuum                      | on
                                        
shared_buffers = 2048MB                 # min 128kB or max_connections*16kB
                                        # (change requires restart)

temp_buffers = 256MB                    # min 800kB
temp_buffers                    | 32768

effective_cache_size            | 40GB       | Sets the planner's assumption about
maintenance_work_mem            | 512MB


top   prev   next  

Postgres function

select pg_rotate_logfile();
select pg_reload_conf();
select pg_cancel_backend(9999);

select name, setting from pg_settings where name like '%log%';
select pg_current_xlog_location() ;

select pg_switch_xlog(); # switch a logfile

hotstandby monitoring:
check if recovery is running:

SELECT pg_is_in_recovery();
SELECT txid_current_snapshot();

select pg_current_xlog_location(); --> on primary
select pg_last_xlog_receive_location(); --> on standby


top   prev   next  

Postgres db creation

$PG_HOME/bin/initdb -D $PGDATA
createdb -U login_username -O new_db_owner MY_NEW_DB 

top   prev   next  

hot backup

step 1): begin backup
#!/bin/bash
tag="HOT_BACKUP_`date +%Y%m%d_%H:%M:%S`"
psql -p 5444 -v 'ON_ERROR_STOP=on' <\c template1
PSQL>checkpoint;
PSQL>pg_startup_backup();
PSQL> SELECT pg_startup_backup();

step 2): backup datafile

step 3): end backup
#!/bin/bash
psql -p 5444 -v 'ON_ERROR_STOP=on' < roles.sql
pg_dumpall -h $SOCKET -p 5432 --tablespaces-only > tablespaces.sql 

pg_restore -h $SOCKET -p 5432 -C -Fc -d postgres startingDump.dump # only work for pg_dump -Fc


top   prev   next  

Postgres Extension

1): CREATE LANGUAGE plpgsql;

2):
drop extension pgcrypto;
create extension pgcrypto;
CREATE EXTENSION pgcrypto;


top   prev   next  

table and text file conversion

COPY weather FROM ./home/user/weather.txt.;

top   prev   next  

other notes


UNIX: 
dtrace -ln transaction-start


EXPLAIN select * from pg_stat_activity;



postgres:
SELECT relname,relnamespace,reltype,pg_relation_filepath(oid), relpages FROM pg_class order by relpages;

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;


http://pgedit.com/node/20


-- Revoke all privileges from user manuel on view kinds:
REVOKE ALL PRIVILEGES ON kinds FROM manuel;

http://pgedit.com/public/sql/acl_admin/acl_admin.html

http://pgedit.com/public/sql/acl_admin/acl_admin.txt

http://pgedit.com/public/sql/acl_admin/index.html


-- ----------
-- Turn off headers:
\t
-- Use SQL to build SQL:
SELECT 'REVOKE ALL ON public.' || table_name || ' FROM PUBLIC;'
FROM information_schema.tables
  WHERE table_type = 'BASE TABLE' AND table_schema='public';
-- If the output looks good, write it to a file and run it:
\g out.tmp
\i out.tmp 
-- ------------


http://www.ntchosting.com/postgresql/create-user.html

create user george with password 'password';
DROP USER cashier;

grant all privileges on database money to cashier;
REVOKE ALL PRIVILEGES ON money FROM cashier;

create role newuser with login nosuperuser nocreatedb nocreaterole noinherit password 'newpassword';

revoke all privileges on database database1, database3 from newuser;
REVOKE CONNECT ON DATABASE your_db FROM user;

top   prev   next  

Create Postgres User

\l   --> list db
\du  --> list user --> default user name is lower case, has some permision info
\du+ -->

\c my_database ---> set permission on default db
\c database username

\set VERBOSITY verbose

create user my_username with password 'my_username_pass';
drop user my_username;

grant  select on cred_status               to   snacreadonly;
revoke insert,update,delete on cred_status from snacreadonly;
grant  insert,update,delete on cred_status to   snacreadwrite;

grant all privileges on database money to   my_username;
REVOKE ALL PRIVILEGES ON money         FROM my_username;

grant select               on all tables in schema public to my_username_or_my_role;
grant insert,update,delete on all tables in schema public to my_usenrame_or_my_role;

grant snacreadonly  to my_username;
grant snacreadwrite to my_username;
grant snacreports   to my_username;

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;

verify connection:
psql --help
psql -U my_username -d my_database_name -h myhostname -p 5436
PostgreSQL grants 
PostgreSQL grant Default Privileges 
top   prev   next  

Kill Process

select * from pg_stat_activity;
select pg_cancel_backend(9999);   --- safe way to kill a process