SQLS:

postgres=# show port;

port
-----5431
(1 row)
edbsto
postgres=# show data_directory;
data_directory
-------------------------/opt/PostgreSQL/9.6/data
(1 row)
postgres=# select version();
version
--------------------------------------------------------------------------------------------------------PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (
Red Hat 4.4.7-16), 64-bit
(1 row)
select oid,datname from pg_database;
(ls -l $PGDATA/base)
create table testtbl(rollno numeric, name varchar(50));
select now();
select pg_relation_filepath('table_name');
(ls -l $PGDATA/base/<oid_of_postgres_dataaase>/)
#how to find log file if pg_log is not set
select pg_backend_pid();
then log file can be found at /proc/<above_pid/fd/2
#set run-time parameter
postgresql -c log_statement='all' -D $PGDATA start
#set parameter thru psql
SELECT set_config('log_statement', 'all', true);
ALTER SYSTEM SET log_statement='all';
f gets updated

#$PGDATA/[Link]

CREATE CLUSTER:
initdb -D edbstore
mkdir -p /home/postgres/PostgreSQL/9.6/edbdata
./initdb -D /home/postgres/PostgreSQL/9.6/edbdata -W
view /home/postgres/PostgreSQL/9.6/edbdata/[Link]
pg_ctl -D /home/postgres/PostgreSQL/9.6/edbdata start
psql -D /home/postgres/PostgreSQL/9.6/edbdata
#create user
create user user1 password 'abc123' [superuser];
#create database
creeate database db1;

#connect to the databagse

\c db1
#connect to database db1
\c db1 user1; #connect to db1 as user user1
#other
tml
\?
\a
\c

META (\) commands

[Link]

#help info about the backslash/meta commands

#aligned/unaligned the table output format
#connect to a db [dbname username host port conn eg.
=> \c mydb myuser [Link] 6432
=> \c service=foo
=> \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=
disable"
=> \c postgresql://tom@localhost/mydb?application_name=myapp
\cd
#change director
\!pwd
\conninfo
\copy <table> from|to <filename>
\d <table>
#describe table
\dg
#list of roles
\db
#list of tablespaces
\df
#list of funtions
\du
#list of roles
\dp
#list of access privileges
\l
#list of databases
\ef
#edut the function
\o <file_name> #output to a filename
\s <file_name> #lists the command line history
\set #without any arguments displays the names and values of all currently-set
psql variables
\w
#outputs the current query buffer to the file
\z
#lists tables, views and sequences with their associated access privilege
s
\set PROMPT1 '%n@%m %~%R%# '
\pset border 2
\timing
EG:
postgres=> \pset border 1
Border style is 1.
postgres=> \pset format unaligned
Output format is unaligned.
postgres=> \pset fieldsep ","
Field separator is ",".
postgres=> \pset tuples_only
Showing only tuples.
postgres=> SELECT second, first FROM my_table;
one,1
two,2
three,3
four,4
postgres=> \a \t \x
Output format is aligned.
Tuples only is off.
Expanded display is on.
* handy to have a .psqlrc file ([Link]/[Link])
\set PSQL_EDITOR "/usr/bin/vim -c ':set ft=sql'"

\set PROMPT1 '%:USER:@%/:%>%R%# '

\set ON_ERROR_STOP
\timing
#change settings/parameters
alter system set work_mem=10240;
show work_mem; #needs to reload the config settings
select pg_reload_conf();
<or>
pg_ctl reload
set work_mem=1024;
alter user user1 set work_mem=2048;
alter database <db_name> set work_mem=4096;
alter system set work_mem=40960;

#session level
#user level
#database level
#cluster level

-- show variable/parameter setting

select name, setting,short_desc,unit from pg_settings where name like 'log%';
-- show running queries (9.2)
SELECT pid, age(query_start, clock_timestamp()), usename, query FROM pg_stat_act
ivity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
-- kill running query
SELECT pg_cancel_backend(procpid);
-- kill idle query
SELECT pg_terminate_backend(procpid);
-- vacuum command
VACUUM (VERBOSE, ANALYZE);
-- all database users
select * from pg_stat_activity where current_query not like '<%';
-- all databases and their sizes
select * from pg_user;
-- all tables and their size, with/without indexes
select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc;
-- cache hit rates (should not be less than 0.99)
SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, (sum(h
eap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
FROM pg_statio_user_tables;
-- table index usage rates (should not be less than 0.99)
SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_us
ed, n_live_tup rows_in_table
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
-- how many indexes are in cache
SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_b

lks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio

FROM pg_statio_user_indexes;
-- Dump database on remote host to file
$ pg_dump -U username -h hostname databasename > [Link]
-- Import dump into existing database
$ psql -d newdb -f [Link]
--get row count
SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tu
p DESC;
<<OR>>
SELECT nspname AS schemaname,relname,reltuples FROM pg_class C
LEFT JOIN pg_namespace N ON ([Link] = [Link])
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND relkind='r'
ORDER BY reltuples DESC;
>> order of precedence for settings is SESSION -> USER -> DATABASE -> CLUSTER
HBA - host based authentication
#connection settings
listen_addresses=ip address of db host, vip, nic IPs
max_connections
port
superuser_reserved_Connections (defaiult 3)
unxi_socket_directory (default /tmp)
unix_socket_permissions (default 0777)
#security and authentication settings
authentication_timeout (default 1min)
ssl (default: off)
ssl_ca_file
ssl_key_file
ssl_ciphers
#memory settings
shared_buffers (default 128mb)
temp_buffers (default 8mb)
##per session
work_mem (default 4mb)
##per session, used for sorting/hashing
maintenance_work_mem (default 64mb) ##for index build, vaccum, copy, analyze etc
temp_file_limit (default 1)
##disk space that is used to perform sorting
etc when memory is not enough
#query planner settings
random_page_cost (4.0)
## >= seq_page_cost
seq_page_cost (1.0)
## < random_page_cost
effective_cache_size (4g)
##used to estimate the cost of an index scan
, ROT is 75% of system memory
many enable_* parameters to influence the planner to choose an optimal plan
enable_indexscan
enable_seqscan
enable_hashjoin
enable_mergejoin
#write ahead log settings
wal_level (minimal)
##archive, logical, hot_standby
fsync (on)
##flush from wal buffers to wal segments at
each commit
wal_buffers (-1, autotune)

min_wal_size (80mb)
max_wal_size (1gb)
checkpoint_timeout (5min)
wal_compression (off)
#where to log
log_destination
##valid values are combination of stderr, cs
vlog, syslog, and eventlog
logging_collector
log_directory
log_filename
##postgresql-%Y-%M-%[Link]
log_file_mode
log_rotation_age
log_rotation_size
#when to log
client_min_messages (NOTICE)
log_min_messages (WARNING)
log_min_error_stagement (ERROR)
log_min_duration_statement (-1)
##500ms meaning any sql taking more than 500
milli sec is written to the server log
#what to log
log_connections (off)
log_disconnections (off)
log_error_verbosity (default)
##terse or verbose
log_duration (off)
log_line_prefix
log_statement
log_temp_files (-1)
log_checkpoints (off)
#background writer settings (for tuning purpose, down the sleeping time and inc
rease the lru_messages)
bgwriter_default (200ms)
bgwriter_lru_maxpages (100)
bgwriter_lru_multiplier (2.0)
#statement behavior
search_path ("$user",public)
default_tablespace
temp_tablespaces
statement_timeout
#vacuum cost settings
vaccum_cost_delay - 0ms
vaccum_cost_page_hit - 1
vaccum_cost_page_miss - 10
vaccum_cost_page_dirty - 20
vaccum_cost_limit - 200
#autovacuum settings
autovacuum - on
log_autovacuum_min_duration - -1
autovacuum_max_workers - 3
autovacuum_work_mem - -1
Configuration File Includes:
The "include" directive Allows config file to be deivided in separate files.
Put all common settings of dozens of clusters that may be maintained and include
that config file in [Link] file.
config file is : $PGDATA/[Link]
include 'filename'
include_dir 'directory_name'
Exercise-1

==========
Keep a backup of [Link]
Make necessary changes in the server parameter file for the following settings:
- server allows 200 connected users
max_connections=200
- server should reserve 10 connection slots for DBA
superuser_reserved_connections=10
- max time to complete client authentication will be 10sec
authentication_timeout=10
cd $PGDATA
cp -p [Link] [Link].201610081143
vi [Link]
max_connections=200
superuser_reserved_connections=10
authentication_timeout=10
Exercise-2
==========
Track down certain activities on the database server logging has to be
ed. Go through the server parameters that control loggin and implement
wing:
- save all the error message in a file inside the pg_log folder in the
ata directory ($PGDATA)
cd $PGDATA
mkdir pg_log
psql
alter system set logging_collector=on;
../bin/pg_ctl restart
psql
show logging_collector
ls -l $PGDATA/pg_log
- log all queries which are taking more than 5 seconds to execute, and
e
log_min_duration_statement=500
log_connections=on

implement
the follo
cluster d

their tim

Exercise-3
==========
Perform the recormmended changes and verify.
- Shared buffer to 256MB
- Effective cache for indexes to 512MB
- Maintenance memory to 64MB
- Temporary memory to 8MB
shared_buffers=256MB
effective_cache_size=512MB
maintenance_work_mem=8MB
temp_buffers=8096
postgres=# select name, setting,short_desc,unit from pg_settings where name in (
'shared_buffers','effective_cache_size','maintenance_work_mem','temp_buffers');
name
| setting |
short_desc
| unit
----------------------+---------+-------------------------------------------------------------------+-----effective_cache_size | 524288 | Sets the planner's assumption about the size o
f the disk cache.
| 8kB

maintenance_work_mem | 65536
ance operations.
| kB
shared_buffers
| 16384
by the server.
| 8kB
temp_buffers
| 1024
sed by each session. | 8kB
(4 rows)

| Sets the maximum memory to be used for mainten

| Sets the number of shared memory buffers used
| Sets the maximum number of temporary buffers u

postgres=#
(make the above changes in $PGDATA/[Link] and restart as shared_buffers
need db bounce)
$ pg_ctl -D /home/postgres/PostgreSQL/9.6/edbdata/ restart

postgres=# select name, setting,short_desc,unit from pg_settings where name in (

'shared_buffers','effective_cache_size','maintenance_work_mem','temp_buffers');
name
| setting |
short_desc
| unit
----------------------+---------+-------------------------------------------------------------------+-----effective_cache_size | 65536 | Sets the planner's assumption about the size o
f the disk cache.
| 8kB
maintenance_work_mem | 65536 | Sets the maximum memory to be used for mainten
ance operations.
| kB
shared_buffers
| 32768 | Sets the number of shared memory buffers used
by the server.
| 8kB
temp_buffers
| 1024
| Sets the maximum number of temporary buffers u
sed by each session. | 8kB
(4 rows)

Exercise-4
==========
autovacuum_max_workers=6 #needs db bounce
autovacuum_vacuum_threshold=100
autovacuum_vacuum_scale_factor=0.3
autovacuum_analyze_threshold=100
autovacuum_vacuum_cost_limit=100
#long running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state F
ROM pg_stat_activity WHERE now() - pg_stat_activity.query_start > interval '5 mi
nutes';
echo "select procpid,datname,current_query,now() - pg_stat_activity.query_start
as duration from pg_stat_activity where pg_stat_activity.current_query <> ''::t
ext and now() - pg_stat_activity.query_start > interval '5 minutes'" | sudo -u p
ostgres psql
#cancel the query
SELECT pg_cancel_backend(__pid__);
#kill the session
SELECT pg_terminate_backend(__pid__);
#OBJECT HIERARCHY:
DATABASE CLUSTER -> USERS/GROUP (ROLE) : DATABASE : TABLESPACE -> CATALOGS : SCH

EMA : EXTENSIONS -> TABLE : VIEW : SEQUENCE : FUNCTIONS : EVENT TRIGGERS

CREATE DATABASE:
createdb dbname
create database dbname OWNER user1;
revoke connect on database dbname from public;
postgres=# select usename,passwd from pg_shadow;
usename |
passwd
----------+------------------------------------postgres | md526bfb123c73584ad0a4e2f1427e26070
user1
| md57d1b5a4329b6478e976508ab9a49ee3d
user2
| user2
(3 rows)
CREATE USER:
create user user2 unencrypted password 'user2';
PRIVILIEGES:
cluster level
object level
SCHEMA:
create user u1 password '1';
create user u2 password '2';
create database udb;
revoke connect on database udb from public;
\c udb
revoke all on schema public from public;
grant usage on schema public to public;
create schema s1 authorization u1;
create schema s2 authorization u2;
grant connect on database udb to u1,u2;
SEARCH PATH:
search_path="$user", public
Exercise 1:
- create a db user "edbuser" in the existing store
- create a db "edbstore" with owership of "edbuser"
- login to edbstore db using edbuser and create a schema "edbuser"
\du
create user edbuser password 'edbuser';
\du
\l
create database edbstore owner edbuser;
\l
\c edbstore edbuser <OR> psql -U edbuser
\dn
create schema edbuser;
\dn
Exercise 2:
- create an "ebuy" user with password "lion"
- create an "ebuy" schema which can be used by ebuy user
- login as ebuy user, create a table "sample1" and check whether this table belo
ngs to ebuy schema or not

create user ebuy password 'lion';

create schema ebuy authorization ebuy;
grant connect on database edbstore to ebuy;
\c edbstore ebuy
create table sample1 (trans_id integer, trans_details varchar(20));
\dt
Exercise 3: