-- Below commands can be used to create database

postgres=# create database DBATEST;
CREATE DATABASE

postgres=# create database DBATEST with tablespace ts_postgres;
CREATE DATABASE

postgres#CREATE DATABASE "DBATEST"
WITH TABLESPACE ts_postgres
OWNER "postgres"
ENCODING 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TEMPLATE template0;

-- View database information:

postgres=# \l

postgres# select * from pg_database;

<< Note - alternatively database can be created using pgadmin GUI tool also >>>

How to connect to postgres db:

set PATH if not done:

postgres$ export PATH=/Library/PostgreSQL/10/bin:$PATH
postgres$ which psql
/Library/PostgreSQL/10/bin/psql

connect to db using SYNTAX - psql -d -U

postgres$ psql -d edb -U postgres
Password for user postgres:
psql (10.13)
Type "help" for help.

postgres=#

Find current connection info:

postgres=# \conninfo
You are connected to database "edb" as user "postgres" via socket in "/tmp" at port "5432".

postgres=# select current_schema,current_user,session_user,current_database();
current_schema  | current_user | session_user | current_database
----------------+--------------+--------------+------------------
public         | postgres.     | postgres     | edb

Switch to another database:

postgres-# \c dbaclass
You are now connected to database "dbaclass" as user "postgres".

- Drop database from psql
Note - while dropping a database, you need to connect to a database other than the db you are trying to drop.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" at port "5432".

postgres#drop database "DBACLASS";

-- Drop database using dropdb os utility

postgres$ pwd
/Library/PostgreSQL/10/bin

postgres$ ./dropdb -e "DBACLASS"
Password:
SELECT pg_catalog.set_config('search_path', '', false)
DROP DATABASE "DBACLASS";

For complete article visit -  > https://dev.dbaclass.com/article/drop-database-postgres/

postgres=# \list+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
dbaclass | postgres | UTF8 | C | C | | 2268 MB | pg_default |
postgres | postgres | UTF8 | C | C | | 4132 MB | pg_default | default administrative connection database
template0 | postgres | UTF8 | C | C | =c/postgres +| 7601 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | C | C | =c/postgres +| 7601 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)

postgres=# select datname from pg_database;
datname
-----------
postgres
template1
template0
dbaclass
(4 rows)

How to get postgres db size:

postgres=# SELECT pg_database.datname as "database_name", pg_size_pretty(pg_database_size(pg_database.datname)) AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;

 database_name | size_in_mb
---------------+------------
 DBACLASS      | 7767 kB
 postgres      | 7735 kB
 template1     | 7735 kB
 template0     | 7601 kB
(4 rows)

(or)

postgres=# \l+
                                                               List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
 DBACLASS  | postgres | UTF8     | C       | C     |                       | 7767 kB | pg_default | TESTING DB
 postgres  | postgres | UTF8     | C       | C     |                       | 7735 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +| 7601 kB | pg_default | unmodifiable empty database
           |          |          |         |       | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +| 7735 kB | pg_default | default template for new databases
           |          |          |         |       | postgres=CTc/postgres |         |            |
(4 rows)

Commands to find timezone information

dbaclass=# show timezone
TimeZone
--------------
Asia/Kolkata
(1 row)

dbaclass=# SELECT current_setting('TIMEZONE');
current_setting
-----------------
Asia/Kolkata

dbaclass=# select name,setting,short_desc,boot_val from pg_settings where name='TimeZone';
name     | setting      | short_desc                                                      | boot_val
----------+--------------+-----------------------------------------------------------------+----------
TimeZone | Asia/Kolkata | Sets the time zone for displaying and interpreting time stamps. | GMT
(1 row)

Below commands can be used to find postgres version:

dbaclass=# show server_version;
server_version
----------------
10.13
(1 row)

dbaclass=# select version ();
version
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.13 on x86_64-apple-darwin, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2336.11.00), 64-bit
(1 row)

postgres$ cd /Library/PostgreSQL/10/bin

postgres$ ./postgres -V
postgres (PostgreSQL) 10.13

-- PG_VERSION file is under data directory
postgres$ cd /Library/PostgreSQL/10/data

postgres$ cat PG_VERSION
10

STEPS FOR enabling archiving:

1. Create directory for archiving:

mkdir -p /Library/PostgreSQL/10/data/archive/

2. Update the postgres.conf file with below values

wal_level = replica
archive_mode = on
max_wal_senders=1
archive_command= 'test ! -f /Library/PostgreSQL/10/data/archive/%f && cp %p /Library/PostgreSQL/10/data/archive/%f'

3. Restart the postgres servers

export PGDATA=/Library/PostgreSQL/10/data
pg_ctl stop
pg_ctl start

3. Check archive status:
postgres=# select name,setting from pg_settings where name like 'archive%';
name             | setting
-----------------+--------------------------------------------------------------------------------------------------
archive_command  | test ! -f /Library/PostgreSQL/10/data/archive/%f && cp %p /Library/PostgreSQL/10/data/archive/%f
archive_mode     | on
archive_timeout  |  0

Below command signals the log-file manager to switch to a new output file immediately.it is just like an alert log

postgres=# select pg_rotate_logfile() ;
pg_rotate_logfile
-------------------
t

-- Monitor query execution time

select substr(query,1,100) query,calls,min_time/1000 "min_time(in sec)" , max_time/1000 "max_time(in sec)", mean_time/1000 "avg_time(in sec)", rows from pg_stat_statements order by mean_time desc;

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

NOTE:

If pg_stat_statements is not available in your database, then activate using below:
-- Add below parameters in postgres.conf file and restart the postgres cluster

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

sudo service postgresql restart

-- Now create extension:

dbaclass=# create extension pg_stat_statements;
CREATE EXTENSION

HOW TO FIND DATA DIRECTORY LOCATION 

DATA_DIRECTORY - > Specifies the directory to use for data storage.

dbaclass=# show data_directory;

data_directory
-----------------------------
/Library/PostgreSQL/10/data
(1 row)

dbaclass=# select setting from pg_settings where name = 'data_directory';
setting
-----------------------------
/Library/PostgreSQL/10/data
(1 row)

-- This will show location of important files in postgres

dbaclass=# SELECT name, setting FROM pg_settings WHERE category = 'File Locations';
name                     | setting
-------------------+---------------------------------------------
config_file              | /Library/PostgreSQL/10/data/postgresql.conf
data_directory           | /Library/PostgreSQL/10/data
external_pid_file        |
hba_file                 | /Library/PostgreSQL/10/data/pg_hba.conf
ident_file               | /Library/PostgreSQL/10/data/pg_ident.conf
(5 rows)

Find sessions in the postgres:

select pid as process_id,
usename as username,
datname as database_name,
client_addr as client_address,
application_name,
backend_start,
state,
state_change,query
from pg_stat_activity;

-- For specific database:

select pid as process_id,
usename as username,
datname as database_name,
client_addr as client_address,
application_name,
backend_start,
state,
state_change,query 
from pg_stat_activity where datname='dbaclass';

process_id | username | database_name | client_address | application_name |          backend_start           | state  |           state_change           ------------+----------+---------------+----------------+------------------+----------------------------------+--------+----------------------------------

      18970 | postgres | dbaclass      |                | psql             | 2020-07-03 20:27:42.225987+05:30 | active | 2020-07-03 23:19:12.023416+05:30

(1 row)

-- First find the pid of the session:

dbaclass#SELECT datname as database, pid as pid, usename as username, application_name , client_addr , query FROM pg_stat_activity;

<< Lets say the pid=1124, in the below query pass the pid value to kill that particular session..>>

dbaclass#select pg_terminate_backend(pid) from pg_stat_activity where pid='1123';

-- Here we want to kill all session of the user postgres

-- List all the session of that user.
dbaclass#select datname as database, pid as pid, usename as username, application_name , client_addr, query FROM pg_stat_activity where username='postgres';

-- Kill all the session of user postgres.

dbaclass#select pg_terminate_backend(pid) from pg_stat_activity where usename='postgres';

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;

-- QUERY TO FIND BLOCKING SESSION DETAILS

dbaclass#select pid as blocked_pid, usename, pg_blocking_pids(pid) as "blocked_by(pid)", query as blocked_query from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0;

output:

 blocked_pid | usename  | blocked_by(pid) |        blocked_query
-------------+----------+-------------+------------------------------
        4206 | postgres | {3673}      | alter table test drop query;

Find location of postgres related conf files

dbaclass=# SELECT name, setting FROM pg_settings WHERE category = 'File Locations';
name                     | setting
-------------------+---------------------------------------------
config_file              | /Library/PostgreSQL/10/data/postgresql.conf
data_directory           | /Library/PostgreSQL/10/data
external_pid_file        |
hba_file                 | /Library/PostgreSQL/10/data/pg_hba.conf
ident_file               | /Library/PostgreSQL/10/data/pg_ident.conf
(5 rows)

alternatively:

postgres=# show config_file;

                 config_file
--------------------------------------------

/Library/PostgreSQL/10/data/postgresql.conf

(1 row)

postgres=# show hba_file;

                hba_file
-----------------------------------------
/Library/PostgreSQL/10/data/pg_hba.conf

(1 row)

postgres=# show ident_file;
                ident_file
-------------------------------------------

/Library/PostgreSQL/10/data/pg_ident.conf

(1 row)

-- Below commands can be used to find postgres db date/timestamp

postgres=# SELECT CURRENT_TIMESTAMP;
current_timestamp
----------------------------------
2020-07-06 17:45:24.929293+05:30
(1 row)

postgres=# select current_date;
current_date
--------------
2020-07-06
(1 row)

postgres=# select statement_timestamp() ;
statement_timestamp
----------------------------------
2020-07-06 17:46:16.825492+05:30
(1 row)

postgres=# select timeofday() ;
timeofday
-------------------------------------
Mon Jul 06 17:46:23.861551 2020 IST
(1 row)

postgres=# select localtime(0);
localtime
-----------
17:52:38
(1 row)

postgres=# select localtimestamp(0);
localtimestamp
---------------------
2020-07-06 17:52:48
(1 row)

postgres=# \des+
List of foreign servers
Name  | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
------+-------+----------------------+-------------------+------+---------+-------------+-------------
(0 rows)

postgres=# select srvname,srvowner,srvoptions,fdwname,srvversion,srvtype from pg_foreign_server join pg_foreign_data_wrapper b on b.oid=srvfdw;
srvname  | srvowner | srvoptions | fdwname | srvversion | srvtype
---------+----------+------------+---------+------------+---------
(0 rows)

-- Find list of installed extension:

psql# \dx

(or)

psql#\dx+

(or)

psql#SELECT * FROM pg_extension;

-- For finding available extension in server:

psql# SELECT * FROM pg_available_extensions;

-- Uptime of server

postgres# SELECT now() - pg_postmaster_start_time() "uptime";
uptime
------------------------
9 days 04:54:56.774981
(1 row)

-- Server startup time:

postgres# SELECT pg_postmaster_start_time();
pg_postmaster_start_time
----------------------------------
26-SEP-20 11:13:08.283105 +03:00
(1 row)

postgres# select * from pg_stat_archiver;
-[ RECORD 1 ]------+---------------------------------
archived_count     | 0
last_archived_wal  |
last_archived_time |
failed_count.      | 0
last_failed_wal.   |
last_failed_time   |
stats_reset        | 26-SEP-20 11:13:08.540237 +03:00

-- Last pg config reload time

postgres=# select pg_conf_load_time() ;
pg_conf_load_time
----------------------------------
2020-07-06 13:20:18.048689+05:30
(1 row)

-- Reload again and see whether reload time changed or not

postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

postgres=# select pg_conf_load_time() ;

pg_conf_load_time
----------------------------------
2020-07-06 17:46:59.958056+05:30
(1 row)

-- Create a simple table
postgres=# Create table member_table ( mem_id integer, member_name varchar(100) , mobile integer not null);
CREATE TABLE

-- Create table with primary key
postgres=# Create table member_table ( mem_id integer primary key, member_name varchar(100) , mobile integer not null);
CREATE TABLE
-- Create table under particular tablespace
postgres=# Create table member_table ( mem_id integer primary key, member_name varchar(100) , mobile integer not null) tablespace pg_production_ts;
CREATE TABLE

-- Create table with unique constraint
postgres=# Create table member_table ( mem_id integer, member_name varchar(100) , mobile integer not null , constraint mem_id_cons unique(mem_id));
CREATE TABLE

-- Create temporary table:

postgres=# Create temporary table member_table ( mem_id integer primary key, member_name varchar(100) , mobile integer not null) tablespace pg_default;
CREATE TABLE

-- Drop table

postgres=# drop table member_table;

-- Below of any commands can be used to find the schema details:

postgres=# select schema_name,schema_owner from information_schema.schemata;
schema_name         | schema_owner
--------------------+--------------
raj                | postgres
information_schema | postgres
public             | postgres
pg_catalog         | postgres
pg_toast_temp_1    | postgres
pg_temp_1.         | postgres
pg_toast           | postgres
(7 rows)

postgres=# select nspname as schema_name , pg_get_userbyid(nspowner) as schema_owner from pg_catalog.pg_namespace;
schema_name         | schema_owner
--------------------+--------------
pg_toast            | postgres
pg_temp_1           | postgres
pg_toast_temp_1     | postgres
pg_catalog          | postgres
public              | postgres
information_schema  | postgres
raj                 | postgres
(7 rows)

postgres=# \dn+
List of schemas
Name    | Owner    | Access privileges    | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema| | =UC/postgres |
raj    | postgres |                      |
(2 rows)

Below queries can be used to get schema wise size in postgres db

postgres=# select schemaname,pg_size_pretty(sum(pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::bigint) as schema_size FROM pg_tables group by schemaname;
schemaname          | pg_size_pretty
--------------------+----------------
raj                 | 8192 bytes
public              | 3651 MB
pg_catalog          | 2936 kB
information_schema  | 96 kB
(4 rows)

postgres=# SELECT schemaname,
pg_size_pretty(sum(table_size)::bigint) as schema_size,
(sum(table_size) / pg_database_size(current_database())) * 100 as percentage_of_total_db
FROM (
SELECT pg_catalog.pg_namespace.nspname as schemaname,
pg_relation_size(pg_catalog.pg_class.oid) as table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schemaname
ORDER BY schemaname;

schemaname          | schema_size | percentage_of_total_db
--------------------+-------------+----------------------------
information_schema  | 96 kB       | 0.002561568956316216939600
pg_catalog          | 6120 kB     | 0.16330002096515883000
pg_toast            | 648 kB      | 0.01729059045513446400
public              | 3651 MB     | 99.76265110861169191100
raj                 | 8192 bytes  | 0.000213464079693018078300
(5 rows)

-- Top 10 big tables in postgres

select schemaname as schema_owner,
relname as table_name,
pg_size_pretty(pg_total_relation_size(relid)) as total_size,
pg_size_pretty(pg_relation_size(relid)) as used_size,
pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid))
as free_space
from pg_catalog.pg_statio_user_tables
order by pg_total_relation_size(relid) desc,
pg_relation_size(relid) desc
limit 10;

(or)

SELECT
nspname as schema_name,relname as table_name,pg_size_pretty(pg_relation_size(c.oid)) as "table_size"
from pg_class c left join pg_namespace n on ( n.oid=c.relnamespace)
where nspname not in ('pg_catalog','information_schema')
order by pg_relation_size(c.oid) desc limit 10;

-- Find table sizes and its respective index sizes

SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes limit 10;

--- It wil find the indexes present on a table 'test'

postgres=# select * from pg_indexes where tablename='test';
schemaname  | tablename | indexname | tablespace  | indexdef
------------+-----------+-----------+-------------+----------------------------------------------------------
public.     | test      | tes_idx1  | ts_postgres | CREATE INDEX tes_idx1 ON public.test USING btree (datid)
(1 row)

-- All indexes present in database:

postgres#select * from pg_indexes

-- It will show all index details including size:

postgres=# \di+
List of relations
Schema  | Name     | Type  | Owner    | Table  | Size   | Description
--------+----------+-------+----------+--------+--------+-------------
public  | tes_idx  | index | postgres | test56 | 64 kB |
public  | tes_idx1 | index | postgres | test   | 472 MB |
(2 rows)

-- Find indexes with respective column name for table( here table name is test)

REFERENCE - https://stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql
select
t.relname as table_name,
i.relname as index_name,
array_to_string(array_agg(a.attname), ', ') as column_names
from
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
where
t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = 'r'
and t.relname ='test'
group by
t.relname,
i.relname
order by
t.relname,
i.relname;

postgres=# \det+
List of foreign tables
Schema | Table | Server | FDW options | Description
--------+-------+--------+-------------+-------------
(0 rows)

postgres=# SELECT n.nspname AS "Schema",
c.relname AS "Table",
s.srvname AS "Server",
CASE WHEN ftoptions IS NULL THEN '' ELSE '(' || pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(ftoptions)), ', ') || ')' END AS "FDW options",
d.description AS "Description"
FROM pg_catalog.pg_foreign_table ft
INNER JOIN pg_catalog.pg_class c ON c.oid = ft.ftrelid
INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pg_catalog.pg_foreign_server s ON s.oid = ft.ftserver
LEFT JOIN pg_catalog.pg_description d
ON d.classoid = c.tableoid AND d.objoid = c.oid AND d.objsubid = 0
WHERE pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2;

**************************
List of foreign tables
Schema  | Table | Server | FDW options | Description
--------+-------+--------+-------------+-------------
(0 rows)

postgres=# \d test
Table "public.test"
Column.     | Type    | Collation | Nullable | Default
------------+---------+-----------+----------+---------
sourcefile | text     |           |          |
sourceline | integer  |           |          |
seqno.     | integer  |           |.         |
name.      | text     |           |          |
setting.   | text     |           |          |
applied   | boolean   |           |          |
error     | text.     |           |          |
Indexes:
"test_idx" btree (sourcefile)
"test_idx2" btree (sourceline)

-- Find the table_size ( excluding the index_size)

postgres=# SELECT pg_size_pretty (pg_relation_size('test'));
pg_size_pretty
----------------
30 MB
(1 row)

-- Find the total_index size of the table

postgres=# sELECT pg_size_pretty ( pg_indexes_size('test'));
pg_size_pretty
----------------
26 MB
(1 row)

-- Find particular index size:

postgres=# select pg_size_pretty(pg_total_relation_size('test_idx'));
pg_size_pretty
----------------
19 MB

postgres=# select pg_size_pretty(pg_total_relation_size('test_idx2'));
pg_size_pretty
----------------
6496 kB

Another method:

postgres=# \di+ "test_idx"

List of relations
Schema  | Name     | Type  | Owner.  | Table | Size       | Description
--------+----------+-------+---------+-------+------------+-------------
public  | test_idx | index | dbaprod | test  | 8192 bytes |
(1 row)

Describe the table:

postgres=# \d test
Table "public.test"
Column.      | Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
sourcefile  | text.   | | |    -- >>>> Will get size for this one 
sourceline  | integer  | | |.  -- >>>> Will get size for this one also
seqno.      | integer | |. |
name.        | text  | | |
setting.    | text  | | |
applied     | boolean  | | |
error       | text.  | | |
Indexes:
"test_idx" btree (sourcefile)
"test_idx2" btree (sourceline)
 

-- Find the column size ( for sourcefile and sourceline)

postgres=# select pg_size_pretty(sum(pg_column_size(sourcefile))) as total_size from test;
total_size
------------
12 MB
(1 row)

postgres=# select pg_size_pretty(sum(pg_column_size(sourceline))) as total_size from test;
total_size
------------
1152 kB
(1 row)

-- For getting the physical location of a table:

postgres=# select pg_relation_filepath('test');
pg_relation_filepath
----------------------
base/13635/17395
(1 row)

-- For getting the physical location of an index:

postgres=# select pg_relation_filepath('test_idx');
pg_relation_filepath
----------------------
base/13635/17638
(1 row)

-- Analyze stats for a table testanalyze(schema is public)

dbaclass=# analyze testanalyze;
ANALYZE

-- For analyzing selected columns for emptab table ( schema is dbatest)

dbaclass=# analyze dbatest.emptab (datname,datdba);
ANALYZE

dbaclass=# select relname,reltuples from pg_class where relname in ('testanalyze','emptab');
relname      | reltuples
-------------+-----------
testanalyze  | 4
emptab       | 4
(2 rows)

dbaclass=# select schemaname,relname,analyze_count,last_analyze,last_autoanalyze from pg_stat_user_tables where relname in ('testanalyze','emptab');
schemaname  | relname     | analyze_count | last_analyze                     | last_autoanalyze
------------+-------------+---------------+----------------------------------+------------------
public      | testanalyze | 1             | 2020-07-21 17:00:49.687053+05:30 |
dbatest     | emptab      | 1             | 2020-07-21 17:10:01.111517+05:30 |
(2 rows)

---Analyze command with verbose command

dbaclass=# analyze verbose dbatest.emptab (datname,datdba);

INFO:  analyzing "dbatest.emptab"
INFO:  "emptab": scanned 1 of 1 pages, containing 4 live rows and 0 dead rows; 4 rows in sample, 4 estimated total rows
ANALYZE

---Analyze tables in the current schema that the user has access to.

dbaclass=# analyze ;
ANALYZE

NOTE: ANALYZE requires only a read lock on the target table, so it can run in parallel with other activity on the table.

VACUUM - >  REMOVES DEAD ROWS, AND MARK THEM FOR REUSE, BUT IT DOESN’T RETURN THE SPACE TO ORACLE,. IT DOESN'T NEED EXCLUSIVE LOCK ON THE TABLE.

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

vacuum a table:

dbaclass=# vacuum dbatest.emptab;
VACUUM

both vacuum and analyze:

dbaclass=# vacuum analyze dbatest.emptab;
VACUUM

with verbose:

dbaclass# vacuum verbose analyze dbatest.emptab;

Monitor vacuum process( if vacuum process runs for a long time)

dbaclass#select * from pg_stat_progress_vacuum;

Check vacuum related information for the table

dbaclass=# select schemaname,relname,last_vacuum,vacuum_count from pg_stat_user_tables where relname='emptab';
schemaname  | relname | last_vacuum                      | vacuum_count
------------+---------+----------------------------------+--------------
dbatest     | emptab  | 2020-07-21 18:35:34.801402+05:30 | 2
(1 row)

VACUUM FULL - > JUST LIKE MOVE COMMAND IN ORACLE . IT TAKES MORE TIME, BUT IT RETURNS THE SPACE TO OS BECAUSE OF ITS COMPLEX ALGORITHM. IT also requires additional disk space , which can store the new copy of the table., until the activity is completed. Also it locks the table exclusively, which block all operations on the table .

-- Command to run vacuum full command for table:

dbaclass=# VACUUM FULL dbatest.emptab;
VACUUM

DEMO TO CHECK HOW IT RECLAIMS SPACE:

-- Check existing space and delete some data:
dbaclass=# select pg_size_pretty(pg_relation_size('dbatest.emptab'));
pg_size_pretty
----------------
114 MB
(1 row)

dbaclass=# delete from dbatest.emptab where oid=13634;
DELETE 131072

-- We can observe size is still same:

dbaclass=# select pg_size_pretty(pg_relation_size('dbatest.emptab'));
pg_size_pretty
----------------
114 MB
(1 row)

-- Run vacuum full and observe the space usage:

dbaclass=# VACUUM FULL dbatest.emptab;
VACUUM

dbaclass=# select pg_size_pretty(pg_relation_size('dbatest.emptab'));
pg_size_pretty
----------------
39 MB ---- > from 114MB it came down to 39 MB.
(1 row)

Autovacuum methods automates the executions vacuum,freeze and analyze commands.

-- Find whether autovacuum is enabled or not:

dbaclass=# select name,setting,short_desc,boot_val,pending_restart from pg_settings where name in ('autovacuum','track_counts');
name.         | setting | short_desc                                | boot_val | pending_restart
--------------+---------+-------------------------------------------+----------+-----------------
autovacuum    | on      | Starts the autovacuum subprocess.         | on        | f
track_counts  | on      | Collects statistics on database activity. | on        | f
(2 rows)

-- Find other autovacuum related parameter settings
dbaclass=# select name,setting,short_desc,min_val,max_val,enumvals,boot_val,pending_restart from pg_settings where category like 'Autovacuum';

- Change autovacuum settings:( they need restart)

dbaclass=# alter system set autovacuum_max_workers=10 ;
ALTER SYSTEM

Now restart :

pg_ctl stop
pg_ctl start

REINDEX rebuilds an index using the data stored in the index's table, replacing the old copy of the index. There are several scenarios in which to use REINDEX:

- Rebuild particular index:

postgres=# REINDEX INDEX TEST_IDX2;
REINDEX

-- Rebuild all indexes on a table:

postgres=# REINDEX TABLE TEST;
REINDEX

-- Rebuild all indexes of tables in a schema:
postgres=# reindex schema public;
REINDEX

-- Rebuild all indexes in a database :

postgres=# reindex database dbaclass;
REINDEX

-- Reindex with verbose option:

postgres=# reindex (verbose) table test;
INFO: index "test_idx" was reindexed
DETAIL: CPU: user: 5.44 s, system: 2.72 s, elapsed: 11.96 s
INFO: index "test_idx2" was reindexed
DETAIL: CPU: user: 3.34 s, system: 1.01 s, elapsed: 5.49 s
INFO: index "pg_toast_17395_index" was reindexed
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
REINDEX

Rebuild index without causing lock on the table:( using concurrently option) 

postgres=# REINDEX ( verbose) table concurrently test;
INFO: index "public.test_idx" was reindexed
INFO: index "public.test_idx2" was reindexed
INFO: index "pg_toast.pg_toast_17395_index" was reindexed
INFO: table "public.test" was reindexed
DETAIL: CPU: user: 11.09 s, system: 6.23 s, elapsed: 24.63 s.
REINDEX

dbaclass=# SELECT a.query,p.phase, p.blocks_total,p.blocks_done,p.tuples_total, p.tuples_done FROM pg_stat_progress_create_index p JOIN pg_stat_activity a ON p.pid = a.pid;

-[ RECORD 1 ]+-------------------------------
query        | reindex index test_idx;
phase        | building index: scanning table
blocks_total | 61281
blocks_done  | 15331
tuples_total | 0
tuples_done  | 0

(or)

dbaclass=# select pid,datname,command,phase,tuples_total,tuples_done,partitions_total,partitions_done from pg_stat_progress_create_index;

-[ RECORD 1 ]----+-------------------------------
pid.              | 14944
datname           | postgres
command           | REINDEX
phase             | building index: scanning table
tuples_total      | 0
tuples_done       | 0
partitions_total  | 0
partitions_done   | 0

postgres# select * from pg_stat_progress_vacuum;
-[ RECORD 1 ]------+--------------------
pid                | 12540
datid              | 21192
datname            | b2cnsmst
relid              | 22402
phase              | cleaning up indexes
heap_blks_total.   | 624176
heap_blks_scanned  | 624176
heap_blks_vacuumed | 624176
index_vacuum_count | 0
max_dead_tuples    | 178956970
num_dead_tuples    | 0

--- List users present in postgres:

postgres=# select usename,usesuper,valuntil from pg_user;
usename         | usesuper   | valuntil
---------------+----------+---------------------------
postgres       | t           |
test_dbuser1   | f            | 2020-08-08 00:00:00+05:30

postgres#select usename,usesuper,valuntil from pg_shadow;

usename         | usesuper   | valuntil
---------------+----------+---------------------------
postgres       | t           |
test_dbuser1   | f            | 2020-08-08 00:00:00+05:30

postgres#select usename,usesuper,valuntil from pg_shadow;

postgres=# \du
List of roles
Role name      | Attributes                                                 | Member of
---------------+------------------------------------------------------------+-----------
postgres       | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
test_dbuser1   | Password valid until 2020-08-08 00:00:00+05:30             | {}

NOTE - > \du command output includes both user and roles(custom created roles only).

postgres users are bydefault role, but roles are not bydefault user.

List roles :

postgres=# select rolname,rolcanlogin,rolvaliduntil from pg_roles;
rolname               | rolcanlogin | rolvaliduntil
----------------------+-------------+---------------------------
pg_monitor            | f           |
pg_read_all_settings  | f           |
pg_read_all_stats     | f           |
pg_stat_scan_tables   | f           |
pg_signal_backend     | f           |
postgres              | t           |
test_dbuser1          | f           | 2020-08-08 00:00:00+05:30

rolcanlogin - > If true mean they are role as well as user
                If false mean they are only role( they cannot login)

NOTE - > In postgres users are bydefault role, but roles are not bydefault user. i.e

Bydefault user come with login privilege, where as roles don’t come with login privilege.

CREATE USER:

dbaclass=# create user TEST_DBACLASS with password 'test123';
CREATE ROLE

CREATE USER WITH VALID UNTIL:

dbaclass=# create user TEST_dbuser1 with password 'test123' valid until '2020-08-08';
CREATE ROLE

CREATE USER WITH SUPER USER PRIVILEGE

dbaclass=# create user test_dbuser3 with password 'test123' CREATEDB SUPERUSER;

CREATE ROLE

VIEW USERS:

dbaclass=# select usename,valuntil,usecreatedb from pg_shadow;

dbaclass=# select usename,usesuper,valuntil from pg_user;

dbaclass=# \du+

DROP USER:

drop user DB_user1;

- Create role :

dbaclass=# create role dev_admin;
CREATE ROLE

dbaclass=# create role dev_admin with valid until '10-oct-2020';
CREATE ROLE

-- role with createdb and superuser privilege and login keyword mean it can login to db like a normal user

dbaclass=# create role dev_admin with createdb createrole login ;
CREATE ROLE

DROP ROLE:

dbaclass=# drop role dev_admin;
DROP ROLE

select rolname,rolcanlogin,rolvaliduntil from pg_roles;

-- Rename a user:

postgres=# alter user dbatest rename to dbaprod;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
postgres=# \du
List of roles
Role name  | Attributes                                                 | Member of
-----------+------------------------------------------------------------+-----------
dbaprod   |                                                             | {}
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS  | {}

<< NOTE - AFTER renaming the user, you need to reset the password to same old one.

i.e
-- Change the password a user:

postgres=# alter user dbaprod password 'test';
ALTER ROLE

--- Increase the validity of the user:

postgres=# alter user dbaprod valid until 'Feb 10 2021';
ALTER ROLE

-- providing superuser role will make an user superuser.

postgres=# select usename,usesuper from pg_user where usename='dbatest';
usename  | usesuper
---------+----------
dbatest  | f
(1 row)

postgres=#
postgres=# alter user dbatest with superuser;
ALTER ROLE
postgres=# select usename,usesuper from pg_user where usename='dbatest';
usename  | usesuper
---------+----------
dbatest  | t

-- How to revoke superuser:

postgres=# alter user dbatest with nosuperuser;
ALTER ROLE
postgres=#
postgres=# select usename,usesuper from pg_user where usename='dbatest';
usename  | usesuper
---------+----------
dbatest  | f
(1 row)

--- Lets say you forgot the password of the user and u want to set the same password to that user in same or different db 

1. Set a password for the user dbaprod
postgres=# alter user dbaprod password 'old';
ALTER ROLE
postgres=#

2.Note down the encrypted password
postgres=# SELECT rolname, rolpassword FROM pg_catalog.pg_authid where rolname='dbaprod';
rolname | rolpassword
---------+-------------------------------------
dbaprod | md5bbb103edd695a83d45db75755e459a78 -- > NOTE DOWN THIS ONE
(1 row)

3. Change the password and check the encrypted password

postgres=# alter user dbaprod password 'new';
ALTER ROLE

postgres=# SELECT rolname, rolpassword FROM pg_catalog.pg_authid where rolname='dbaprod';
rolname | rolpassword
---------+-------------------------------------
dbaprod | md5041382740aeba232404af81454f48d7f ( it has been changed)

4.Now update this rolpassword with the value we got at step 2

postgres=# update pg_catalog.pg_authid set rolpassword = 'md5bbb103edd695a83d45db75755e459a78' where rolname='dbaprod';
UPDATE 1

Now try to connect to the database using the first password 'old'

postgres$ PGPASSWORD=old ./psql -d postgres -U dbaprod
Password:
psql (12.3)
Type "help" for help.

Examples on GRANT command

GRANT CONNECT ON DATABASE PRIMDB to DBAUSER1;

GRANT USAGE ON SCHEMA CRM to DBAUSER1;

GRANT INSERT,UPDATE,DELETE ON TABLE CRM.EMPTAB TO DBAUSER1;

GRANT ALL ON TABLE  CRM.EMPTAB TO DBAUSER1;

GRANT CREATE ALL ON DATABASE CRM to DBAUSER2;

GRANT CREATE ON TABLESPACE INV_TS to DBAUSER2;

GRANT ALL ON TABLESPACE INV_TS TO DBAUSER2;

GRANT CREATE ON TABLESPACE INV_TS to DBAUSER2 with grant option:

GRANT EXECUTE ON PROCEDURE PRIM_ID.TEST_PROC;

GRANT EXECUTE ON FUNCTION PRIM_ID.TEST_FUNC;

for more commands: use the help command

#\h GRANT

Examples on REVOKE command

REVOKE CONNECT ON DATABASE PRIMDB FROM DBAUSER1;

REVOKE USAGE ON SCHEMA CRM FROM DBAUSER1;

REVOKE INSERT,UPDATE,DELETE ON TABLE CRM.EMPTAB FROM DBAUSER1;

REVOKE ALL ON TABLE CRM.EMPTAB FROM DBAUSER1;

REVOKE CREATE ALL ON DATABASE CRM FROM DBAUSER2;

REVOKE CREATE ON TABLESPACE INV_TS FROM DBAUSER2;

REVOKE ALL ON TABLESPACE INV_TS FROM DBAUSER2;

REVOKE CREATE ON TABLESPACE INV_TS FROM DBAUSER2 ;

REVOKE EXECUTE ON PROCEDURE PRIM_ID.TEST_PROC FROM DBAUSER2;

REVOKE EXECUTE ON FUNCTION PRIM_ID.TEST_FUNC FROM DBAUSER2;

for more commands: use the help command

#\h REVOKE

--- in edb postgres advanced server we can create user profile
---- similar to that of oracle.

-- Create profile:

# create profile REPORTING_PROFILE limit FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LIFE_TIME 90;

--- Alter profile:

# alter profile REPORTING_PROFILE limit FAILED_LOGIN_ATTEMPTS 1;

-- view profile details:

# select * from dba_profiles;

• A schema is a named collection of tables. A schema can also contain views, indexes, sequences, data types, operators, and functions.

--- Create schema:

postgres=# create schema dba_schema;
CREATE SCHEMA

-- Create schema with authorize particular user:

postgres=# create schema dba_schema authorization raj2;
CREATE SCHEMA

-- Drop schema

postgres=# drop schema dba_schema;
DROP SCHEMA

-- List down schemas present

postgres=# \dn+
List of schemas
Name        | Owner.   | Access privileges    | Description
------------+----------+----------------------+------------------------
dba_schema | raj2      |                      |
public     | postgres  | postgres=UC/postgres+| standard public schema| | =UC/postgres |
raj.       | postgres | |
(3 rows)

-- Find search_path of users in a particular database ( replace your db_name(EDB))

SELECT r.rolname, d.datname, drs.setconfig
FROM pg_db_role_setting drs
LEFT JOIN pg_roles r ON r.oid = drs.setrole
LEFT JOIN pg_database d ON d.oid = drs.setdatabase
WHERE d.datname = 'EDB';

-- Find search_path of users in postgres db cluster( all database)

SELECT r.rolname, d.datname, drs.setconfig
FROM pg_db_role_setting drs
LEFT JOIN pg_roles r ON r.oid = drs.setrole
LEFT JOIN pg_database d ON d.oid = drs.setdatabase;

-- set search_path for a user in particular db:

postgres# alter user prod_user in database "EDB" set search_path="$user", public, prim_db;

-- set search_path for a user in postgres cluster( all dbs)

postgres=# alter user prod_user set search_path="$user", public, prim_db;

VIEW TABLESPACE INFO IN POSTGRES:

postgres=# select * from pg_tablespace;

(OR)

postgres=# \db+

(or)

-- For getting size of specific tablespace:

postgres=# select pg_size_pretty(pg_tablespace_size('ts_dbaclass'));

pg_size_pretty

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

96 bytes

(1 row)

Pre-configured tablespaces:( these are default tablespaces)

Pg_global - > PGDATA/global - > used for cluster wide table and system catalog
Pg_default - > PGDATA/base directory - > it stores databases and relations

CREATE TABLESPACE:
postgres=# create tablespace ts_postgres location '/Library/PostgreSQL/TEST/TS_POSTGRES';
CREATE TABLESPACE

RENAME TABLESPACE:
postgres=# alter tablespace ts_postgres rename to ts_dbaclass;
ALTER TABLESPACE

DROP TABLESPACE:

postgres=# drop tablespace ts_dbaclass;
DROP TABLESPACE

<<Before dropping tablespace make sure it is emptry>>

postgres=# show default_tablespace;
default_tablespace
--------------------

(1 row)
<<<< If output is blank means default is pg_default tablespace>>>>>

--To change the default tablespace at database level:

postgres=# alter system set default_tablespace=ts_postgres;
ALTER SYSTEM

postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

postgres=# show default_tablespace;
default_tablespace
--------------------
ts_postgres
(1 row)

postgres=# SELECT name, setting FROM pg_settings where name='default_tablespace';
name | setting
--------------------+-------------
default_tablespace | ts_postgres
(1 row)

Steps to change default tablespace at session level:

postgres=# set default_tablespace=ts_postgres;
SET

VIEW DEFAULT TEMP TABLESPACE:
dbaclass=# SELECT name, setting FROM pg_settings where name='temp_tablespaces';
name | setting
------------------+---------
temp_tablespaces |
(1 row)

dbaclass=# show temp_tablespaces
dbaclass-# ;
temp_tablespaces
------------------

(1 row)

CHANGE DEFAULT TEMP TABLESPACE

postgres=# alter system set temp_tablespaces=TS_TEMP;
ALTER SYSTEM

postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)

postgres=# show temp_tablespaces;
temp_tablespaces
------------------
ts_temp
(1 row)

postgres=# SELECT name, setting FROM pg_settings where name='temp_tablespaces';
name | setting
------------------+---------
temp_tablespaces | ts_temp
(1 row)

-- export specific column data to text file:

copy EMPLOYEE( EMP_NAME,EMP_ID) to '/tmp/emp.txt';

-- export complete table data to text file:

copy EMPLOYEE to '/tmp/emp.txt';

-- export table data to csv file:

copy EMPLOYEE to '/tmp/emp.csv' with csv headers;

-- export specific query output to csv file:

copy ( select ename,depname from emp where depname='HR') to '/tmp/emp.csv' with csv headers;

-- This provides a summary of contents of client authentication config file pg_hba.conf 

postgres=# select * from pg_hba_file_rules;
line_number | type | database | user_name | address | netmask | auth_method | options | error
-------------+-------+---------------+-----------+---------------+-----------------------------------------+-------------+---------+-------
80 | local | {all} | {all} | | | md5 | |
82 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | ident | |
84 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | ident | |
85 | host | {all} | {all} | 172.21.19.148 | 255.255.255.255 | trust | |
88 | local | {replication} | {all} | | | peer | |
89 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | ident | |
90 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | ident | |
(7 rows)

-- Check auditing setting :

postgres=# show log_statement;
log_statement
---------------
none

-- For logging all ddl activites:

postgres=# alter system set log_statement=ddl;
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t

-- For logging all DDL DML activities:

postgres=# alter system set log_statement=mod;
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t

-- For logging all statement( i.e ddl , dml and even select statements)

postgres=# alter system set log_statement='all';
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t

- Enable audit for connection and disconnection to postgres.

postgres=# select name,setting from pg_settings where name in ('log_disconnections','log_connections');
name                | setting
--------------------+---------
log_connections     | off
log_disconnections  | off

postgres=# alter system set log_disconnections=off;
ALTER SYSTEM

postgres=# alter system set log_connections=on;
ALTER SYSTEM

postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t

Now all log on and log off will logged in the log file.

<<<<<<<<< cd /Library/PostgreSQL/10/data/log/ >>>>>>>
2020-07-06 12:51:39.042 IST [10212] LOG: connection received: host=[local]
2020-07-06 12:51:53.416 IST [10215] LOG: connection received: host=[local]
2020-07-06 12:51:53.420 IST [10215] LOG: connection authorized: user=postgres database=postgres