quick link
create postgres user
kill process
Postgres Home
Postgres Online Manual
Postgres Wiki
Postgres FAQ
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
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;
Table Maitenance
analyze table_name;
vaccum tabl_name;
EXPLAIN ANALYZE query;
EXPLAIN query;
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
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
Postgres db creation
$PG_HOME/bin/initdb -D $PGDATA
createdb -U login_username -O new_db_owner MY_NEW_DB
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
Postgres Extension
1): CREATE LANGUAGE plpgsql;
2):
drop extension pgcrypto;
create extension pgcrypto;
CREATE EXTENSION pgcrypto;
table and text file conversion
COPY weather FROM ./home/user/weather.txt.;
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;
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
Kill Process
select * from pg_stat_activity;
select pg_cancel_backend(9999); --- safe way to kill a process
Fuente:
http://quickdbasupport.com/most_used_postgres.php
