Comandos más usados en PostgreSQL

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