Postgres Most Used Commands

quick link

Posgresql On Amazon AWS RDS

RDS_Docs  User_Guide  DBA_Tasks  roles  install_client_software 

SHOW rds.restrict_password_commands;

SHOW rds.allowed_extensions;
SHOW rds.extensions; 
-- CREATE EXTENSION rds_tools;
SELECT rds_tools.rds_version();
SELECT * FROM rds_tools.role_password_encryption_type();

-- ALTER EXTENSION PostgreSQL-extension UPDATE TO 'new-version';
SELECT * FROM pg_extension;
SELECT * FROM pg_available_extension_versions;

SELECT name,setting FROM pg_settings WHERE name IN ('wal_level','rds.logical_replication');

show temp_tablespaces;
-- SET temp_tablespaces TO 'pg_default';

SELECT
    spcname AS "Name",
    pg_catalog.pg_size_pretty(pg_catalog.pg_tablespace_size(oid)) AS "size"
FROM
    pg_catalog.pg_tablespace
WHERE
    spcname IN ('rds_temp_tablespace');


SELECT name, setting, boot_val, reset_val, unit  FROM pg_settings  ORDER BY name;



Parameter_Settings  Setting_For_Importing  Events 

Tuning  Tuning2  The wal_level parameter determines how much information is written to the WAL. 


video_1  video_2  video_3 

top   prev   next  

Start And Stop Postgresql Database Instance

sudo service postgresql start
sudo service postgresql stop
sudo service postgresql restart

systemctl start postgresql
systemctl stop postgresql
systemctl restart postgresql

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

pg_ctl reload   cf: pg_reload_conf()  --> reload server configuration file: postgresql.conf;

About the psql Command

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

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

export PGOPTIONS="-P"

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

-e: echo sql statement with output data 

psql command reference 

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 version();

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

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;

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;

Postgres Setting: postgresql.conf

sudo -iu postgres psql -U postgres -c 'SHOW config_file'
# postgresql.conf

sudo -iu postgres psql -U postgres -c 'SHOW hba_file'
# pg_hba.conf

1):
# -----------------------------------------------------------------------------
By default PostgreSQL will only accept local connections. 
pg-primary:/etc/postgresql/12/demo/postgresql.conf Set listen_addresses

listen_addresses = '*'
# listen_addresses = '10.10.10.1'
#-------------------------------------------------------------------------------

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

listen_addresses = '*'
# listen_addresses = '10.10.10.1'
password_encryption='scram-sha-256'
archive_mode = on

other postgresql.conf parameters 

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

sudo -u postgres psql -c "select *, current_timestamp from pg_switch_wal()";
sudo -u postgres psql -c "select *, current_timestamp from pg_switch_xlog()";

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

# parameter for postgres standby 
postgresql.conf
archive_mode = on
archive_command = 'cp %p /archive/%f'  # %p=path of file to archive %f = file name only

#wal_level = minial 
#wal_level = archive 
wal_level = hot_standby
max_wal_senders = 5

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' <<EOF
SELECT pg_stop_backup();
\q
EOF

Postgres pg_dump pg_dumpall

psql
\list  # list all databases;
pg_dump -U myusername -W -F t myDb > myBackup_file.tar

-W:  forces pg_dump to prompt for the password before connecting to the PostgreSQL database server. 
After you hit enter, pg_dump will prompt for the password of postgres user.

-F : specifies the output file format that can be one of the following:

c: custom-format archive file format
d: directory-format archive
t: tar
p: plain-text SQL script file).

pg_dumpall -U postgres > pg_backup\all.sql

pg_dumpall --schema-only > pg_definitiononly.sql

pg_dumpall --roles-only > pg_allroles.sql

pg_dumpall --tablespaces-only > pg_allroles.sql

pg_dump  -h ${SOCKET} -p ${PORT} -F c -f /backup/exp/xxxx.dump my_pg_db 

pg_dump -f $BACKUP_NAME -Fc -O -U ${SNAC_DB_USERNAME} -h ${MY_DB_HOST} ${MY_DB_SCHEMA}

-- postgresql default port: psql 5432

pg_dump -h $SOCKET -p 5432 -Fc -f /db/p/mydb/v3/backup/mydb_0118.dmp SNAC

pg_dumpall -h $SOCKET -p 5432 --roles-only > roles.sql
pg_dumpall -h $SOCKET -p 5432 --tablespaces-only > tablespaces.sql 

# restore from the .sql file:
psql -p 6000 -h /tmp -v ON_ERROR_STOP=1 -1 -b -f dump.sql 

# restore from the .pgdmp file:
pg_restore -p 6000 -h /tmp -d mydb -C -1 -f dump.pgdmp
pg_restore -h $SOCKET -p 5432 -C -Fc -d postgres startingDump.dump # only work for pg_dump -Fc

Postgres pgBackRest

User Guide 
Command 
Configuration 

1): Installation
yum install pgbackrest

2): configuration file
/etc/pgbackrest.conf 
# ----------------------------------------
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=7  # Configure retention to 7 full backups
start-fast=y
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO
repo1-cipher-type=aes-256-cbc

[global:archive-push]
compress-level=3
#[main]
#pg1-path=/var/lib/pgsql/10/data
[testing]
pg1-path=/var/lib/pgsql/11/data

# ---------------------------------------


3):  postgresql.conf 

# ----------------------------------------------------------------
archive_mode = on
archive_command = 'pgbackrest --stanza=testing archive-push %p'
max_wal_senders = 3
wal_level = logical

# Verify

SELECT name,setting,context,source FROM pg_settings 
WHERE NAME IN ('listen_addresses','archive_mode','password_encryption'); 

Viewing the current settings
Look at the configuration files. This is generally not definitive!
SHOW ALL, SHOW  will show you the current value of the setting. Watch out for session specific changes
SELECT * FROM pg_settings will label session specific changes as locally modified

1. Shared_buffers (integer)

2. Work_mem (integer)
pg_ctl reload, or SELECT pg_reload_conf();

3. Maintenance_work_mem (integer)

4. Effective_cache_size (integer)

#shared_buffers = 128MB
#maintenance_work_mem = 64MB
#work_mem = 4MB
#effective_cache_size 


# ---------------------------------------------------------------

step 1): create stanza
# sudo -u postgres pgbackrest --stanza=testing --log-level-console=info stanza-create

step 2): validation
sudo -u postgres pgbackrest --stanza=testing --log-level-console=info check

step 3a): backup full
sudo -u postgres pgbackrest --stanza=testing --type=full --log-level-stderr=info backup

step 3b): backup diff
sudo -u postgres pgbackrest --stanza=testing --type=diff --log-level-console=info backup

step 3c): backup incremental
sudo -u postgres pgbackrest --stanza=testing --type=incr backup

step 4): get the information about backup
sudo -u postgres pgbackrest info

test restore
# stop running instance:  service postgresql-11 stop
# rm -rf /var/lib/pgsql/11/data/*
# pgbackrest --stanza=testing --log-level-stderr=info restore
# start db instance:  service postgresql-11 start

Using pgBackRest to setup hotstandby

Postgres Extension

1): CREATE LANGUAGE plpgsql;

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

create extension adminpack schema pg_catalog version "1.0";

table and text file conversion

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

other notes

UNIX: 
dtrace -ln transaction-start

Create Postgres User

PostgreSQL grants 


PostgreSQL grant Default Privileges 

\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

eg 1:
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;

-- Grant all privileges on view myview1 to user myuser1 ;
GRANT ALL PRIVILEGES ON myview1 to myuser1;
-- Revoke all privileges from user myuser1 on view myview1:
REVOKE ALL PRIVILEGES ON myview1 FROM myuser1;

eg 2:
create user my_username with password 'my_username_pass';
drop user my_username;

# create role newuser with login nosuperuser nocreatedb nocreaterole noinherit password 'newpassword';
# grant myRole  to myUsername;
default role 

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 database database1, database3 from newuser;

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;
revoke all privileges on money         from my_username;

revoke connect on database yourDb from user1;

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 GRANT  EXECUTE ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

verify connection:
psql --help
psql -U my_username -d my_database_name -h myhostname -p 5436

-- ----------
-- 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://pgedit.com/node/20
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

Kill Process

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

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;

Installation And Create Database

yum install postgresql-server
systemctl enable postgresql

# Installation and Create Databases
./configure
make
su
make install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres

export PATH=/usr/local/pgsql/bin:$PATH
export MANPATH=/usr/local/pgsql/share/man:$MANPATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH

or login as root: /sbin/ldconfig -m /usr/local/pgsql/lib

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

Postgres DB Creation

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

Table Maitenance

analyze table_name;


vaccum tabl_name;

explain analyze query;

explain query;

reindex
-- reindex concurrently
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]
REINDEX INDEX my_index;
REINDEX TABLE my_table;
REINDEX DATABASE my_db;

--
alter table:
SET CONSTRAINTS ... DEFERRED
WITH CHECK OPTION

Tuning And Explain Analyze

Tuning 
EXPLAIN
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
BUFFERS [ boolean ]
TIMING [ boolean ]  
SUMMARY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }


EXPLAIN SELECT * FROM tenk1;

# EXPLAIN ANALYZE: actually executes the query
EXPLAIN ANALYZE SELECT * FROM tenk1;

EXPLAIN ANALYZE update ... ...
ROLLBACK;

eg 1:
EXPLAIN select * from pg_stat_activity;


# setup hints
SET enable_seqscan TO off;
other query configuration parameters 

Setup hot standby

On master server
1. create replication user and grant replication permission
sudo -u postgres createuser -U postgres repuser -P -c 5 --replication

2. pg_hba.conf
# Allow replication connections
# host     replication     repuser         [standbyServerExternalIpAddress]/32        md5
host    replication     repuser         10.10.10.102/32  md5

3. postgresql.conf
listen_address = '*'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
[replication]
max_wal_senders = 3

# -------------------------------------------------
On standby Server
1): postgresql.conf
[replication]
hot_standby = on  # when on: user can run queries  default: off. user cannot run queries

2): recovery.conf
standby_mode = on # standby_mode indicates whether to start the PostgreSQL server as a backup server.
#primary_conninfo = 'host=[primaryServerExternalIpAddress] port=5432 user=repuser password=[password]'
primary_conninfo = 'host=10.10.10.101 port=5432 user=repuser password=myPassword'
trigger_file = '/tmp/postgresql.trigger.5432'   # Optional

postgresql stream replication

hot standby wiki

Postgres External Reference

Postgres Home 
Postgres Online Manual 
Postgres Wiki 
Postgres FAQ 



pgadmin 
pgBackRest 
pgDash