-- 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://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';

-- 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 cancel that particular session query>>

dbaclass#select pg_cancel_backend(pid) from pg_stat_activity where pid='1124';

-- 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)

-- 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

1 . Get Config values from psql prompt.

postgres=# select * from pg_settings;

\x
postgres=# select * from pg_settings where name='port';

2. Alternatively you can check postgresql.conf file

postgres=# show config_file;
config_file
---------------------------------
/pgdata/data/postgresql.conf
(1 row)

cat /pgdata/data/postgresql.conf

-- 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)

postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/1D392648
(1 row)

postgres#select pg_walfile_name(pg_current_wal_lsn()),last_archived_wal,last_failed_wal,
('x'||substring(pg_walfile_name(pg_current_wal_lsn()),9,8))::bit(32)::int*256 +
('x'||substring(pg_walfile_name(pg_current_wal_lsn()),17))::bit(32)::int -
('x'||substring(last_archived_wal,9,8))::bit(32)::int*256 -
('x'||substring(last_archived_wal,17))::bit(32)::int
as diff from pg_stat_archiver;

---View existing connection limit setting:( datconnlimit )

postgres=# select datname,datallowconn,datconnlimit from pg_database where datname='test_dev';
-[ RECORD 1 ]--+------------
datname        | test_dev
datallowconn  | t.
datconnlimit  | -1.       -- >Means unlimited connections allowed

-- To set a specific limit for connection

test_dev=# alter database test_dev connection limit 100;
ALTER DATABASE

-- To restrict all the connections to db

test_dev=# alter database test_dev connection limit 0;
ALTER DATABASE

NOTE - > Even if connection limit is set to 0 , the superuser will be able to connect to database.

-- List down all the wal files present in pg_wal

postgres=# select * from pg_ls_waldir();
name                     | size     | modification
------------------------------------------+----------+---------------------------
0000000100000079000000D5 | 16777216 | 22-APR-22 20:51:26 +03:00
0000000100000079000000D8 | 16777216 | 22-APR-22 20:39:33 +03:00
0000000100000079000000D6 | 16777216 | 22-APR-22 20:07:40 +03:00
0000000100000079000000D9 | 16777216 | 22-APR-22 20:47:21 +03:00
0000000100000079000000D7 | 16777216 | 22-APR-22 20:21:45 +03:00
00000001000000790000005C.00005BC8.backup | 323 | 21-APR-22 10:14:40 +03:00
(6 rows)

-- Find total size of wal:

postgres=# select sum(size) from pg_ls_waldir();
sum
----------
83886403
(1 row)

-- Find current wal file lsn:

postgres=# select pg_current_wal_insert_lsn(),pg_current_wal_lsn();
pg_current_wal_insert_lsn  | pg_current_wal_lsn
---------------------------+--------------------
79/D5980480                | 79/D5980480
(1 row)

postgres=# SELECT datname, temp_files, temp_bytes, stats_reset FROM pg_stat_database;
datname    | temp_files | temp_bytes | stats_reset
-----------+------------+------------+----------------------------------
| 0        | 0          | 18-APR-22.18:23:33.09366 +03:00
postgres   | 2          | 28000000   | 18-APR-22 18:23:33.093639 +03:00
edb        | 0          | 0          | 18-APR-22 18:23:37.095023 +03:00
template1  | 0          | 0          |
template0  | 0          | 0          |
b2cplmdev  | 0          | 0          | 18-APR-22 18:23:35.093019 +03:00
test_dev   | 0          | 0          | 19-APR-22 10:17:28.826261 +03:00
(7 rows)  

-- 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;

-- Simple create index:
postgres=# create index tab_idx2 on scott.customer(emp_name);
CREATE INDEX

-- Create index with tablespace:

postgres#CREATE INDEX tab_idx2 on scott.customer(emp_name) TABLESPACE IND_TS;
CREATE INDEX

-- Create index without causing blocking:

postgres=# create index concurrently tab_idx2 on scott.customer(emp_name) TABLESPACE IND_TS;
CREATE INDEX

-- Create unique index:

postgres=# create unique index tab_idx2 on scott.customer(emp_name)
CREATE INDEX

-- Create functional index:

postgres=# create index fun_idx on scott.customer(lower(emp_name));
CREATE INDEX

-- Create multi column index:

postgres=# create index multi_idx on scott.customer(emp_name,emp_id);
CREATE INDEX

-- drop an index:

postgres=# drop index fun_idx;
DROP INDEX

-- 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 is for finding objects under schema scott: Replace your schema_name with scott

SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
where n.nspname ='scott'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

NOTE: Make sure that, the schema_name for which you are looking for objects, is present in the search_path of that user. Otherwise it wont return any rows

postgres=# show search_path;
search_path
-----------------------
"$user", public, scott
(1 row)

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;

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)

postgres=# select * from pg_views where schemaname not in ('pg_catalog','information_schema','sys');
count
-------

postgres#\dv
List of relations
Schema | Name | Type | Owner
--------+--------------------+------+--------------
public | pg_stat_statements | view | enterprisedb
(1 row)

-- Find the sequence details:

select * from pg_sequences;

(or)

\ds+

List of relations
Schema  | Name      | Type     | Owner        | Size | Description
--------+-----------+----------+--------------+------------+-------------
public  | class_seq | sequence | enterprisedb | 8192 bytes |
(1 row)

-- Create sequences:

postgres# CREATE SEQUENCE class_seq INCREMENT 1 MINVALUE 1 MAXVALUE 1000 START 1;
CREATE SEQUENCE

-- Create sequence in descending:

postgres# CREATE SEQUENCE class_seq INCREMENT -1 MINVALUE 1 MAXVALUE 1000 START 1000;
CREATE SEQUENCE

-- Alter sequence to change maxvalue:

postgres=# alter sequence class_seq maxvalue 500;
ALTER SEQUENCE

-- Reset a sequence using alter command:

postgres=# alter sequence class_seq restart with 1;
ALTER SEQUENCE

-- Find next_val and currval of a sequence:

postgres=# select nextval('class_seq');
nextval
---------
1
(1 row)

postgres=# select currval('class_seq');
currval
---------
1
(1 row)

Partial index, means index will be created on a specific subset of data of a table.

edbstore=> create index part_emp_idx on orders(tax) where tax > 400;
CREATE INDEX

edbstore=> \d part_emp_idx
Index "edbuser.part_emp_idx"
Column  | Type          | Key? | Definition
--------+---------------+------+------------
tax     | numeric(12,2) | yes  | tax
btree, for table "edbuser.orders", predicate (tax > 400::numeric)

SELECT
o.conname AS constraint_name,
(SELECT nspname FROM pg_namespace WHERE oid=m.relnamespace) AS source_schema,
m.relname AS source_table,
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = m.oid AND a.attnum = o.conkey[1] AND a.attisdropped = false) AS source_column,
(SELECT nspname FROM pg_namespace WHERE oid=f.relnamespace) AS target_schema,
f.relname AS target_table,
(SELECT a.attname FROM pg_attribute a WHERE a.attrelid = f.oid AND a.attnum = o.confkey[1] AND a.attisdropped = false) AS target_column
FROM
pg_constraint o LEFT JOIN pg_class f ON f.oid = o.confrelid LEFT JOIN pg_class m ON m.oid = o.conrelid
WHERE
o.contype = 'f' AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = 'r');

REFERENCE - https://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys

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)

-- List down all partitioned tables present in db

SELECT
nmsp_parent.nspname AS parent_schema,
parent.relname AS parent,
nmsp_child.nspname AS child_schema,
child.relname AS child
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace

-- List down all partitions of a single table:

SELECT
nmsp_parent.nspname AS parent_schema,
parent.relname AS parent,
nmsp_child.nspname AS child_schema,
child.relname AS child
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace
WHERE parent.relname='parent_table_name';

Ref link - > https://dba.stackexchange.com/questions/40441/get-all-partition-names-for-a-table

-- 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

-- Finding statistics level of a column ( orders.orderdate)
-- statistics level range is 1-10000 ( where 100 means 1 percent,10000 means 100 percent)

edbstore=> SELECT attname as column_name , attstattarget as stats_level FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'orders') and attname='orderdate';

column_name  | stats_level
-------------+-------------
orderdate    | 1000
(1 row)

-- To change statistics level of a column:

edbstore=> alter table orders alter column orderdate set statistics 1000;
ALTER TABLE

postgres=# SELECT n.nspname, c.relname,
pg_catalog.array_to_string(c.reloptions || array(
select 'toast.' ||
x from pg_catalog.unnest(tc.reloptions) x),', ')
as relopts
FROM pg_catalog.pg_class c
LEFT JOIN
pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
JOIN
pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
AND nspname NOT IN ('pg_catalog', 'information_schema');

nspname  | relname                             | relopts
---------+-------------------------------------+------------------------
public   | test                                |
public   | city_id2                            |
public   | test2                               | autovacuum_enabled=off

-- Disable autovacuum for a table:

postgres=# alter table test2 set( autovacuum_enabled = off);

-- Enable autovacuum for a table

postgres=# alter table test2 set( autovacuum_enabled = on);

-- Here the table_name is test

postgres=# select * from pg_stat_user_tables where relname='test';
-[ RECORD 1 ]-------+---------------------------------
relid               | 914713
schemaname          | public
relname             | test
seq_scan            | 40
seq_tup_read        | 12778861
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 4774377
n_tup_upd           | 0
n_tup_del           | 4774377
n_tup_hot_upd       | 0
n_live_tup          | 0
n_dead_tup          | 0
n_mod_since_analyze | 0
last_vacuum         |
last_autovacuum     | 22-APR-22 21:27:10.863536 +03:00
last_analyze        | 22-APR-22 21:05:05.874929 +03:00
last_autoanalyze.   | 22-APR-22 21:27:10.865308 +03:00
vacuum_count        | 0
autovacuum_count    | 6
analyze_count       | 2
autoanalyze_count   | 11

-- Create the pgstattuple extension:

postgres=# create extension pgstattuple;
CREATE EXTENSION

-- bloating percentage of the table "test":

postgres=# SELECT pg_size_pretty(pg_relation_size('test')) as table_size,(pgstattuple('test')).dead_tuple_percent;
table_size. | dead_tuple_percent
------------+--------------------
1408 kB     | 0
(1 row)

-- bloating percentage of index "test_x_idx":

select pg_relation_size('test_x_idx') as index_size, 100-(pgstatindex('test_x_idx')).avg_leaf_density as bloat_ratio;

index_size. | bloat_ratio
------------+--------------------
1008 kB     | 0
(1 row)

--- 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;

-- List down table level privileges of user
SELECT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'USER_NAME';

-- List down usage privileges of a user:

select * from usage_privileges where grantee='USER_NAME';

SELECT
r.rolname,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
ORDER BY 1;

-- Create the pgstattuple extension:

postgres=# create extension pgstattuple;
CREATE EXTENSION

-- bloating percentage of the table "test":

postgres=# SELECT pg_size_pretty(pg_relation_size('test')) as table_size,(pgstattuple('test')).dead_tuple_percent;
table_size. | dead_tuple_percent
------------+--------------------
1408 kB     | 0
(1 row)

-- bloating percentage of index "test_x_idx":

select pg_relation_size('test_x_idx') as index_size, 100-(pgstatindex('test_x_idx')).avg_leaf_density as bloat_ratio;

index_size. | bloat_ratio
------------+--------------------
1008 kB     | 0
(1 row)

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)

-- Change ownership of tablespace ts_postgres to user dev_admin

postgres# alter tablespace ts_postgres owner to dev_admin;

postgres# \db+

Move table/index to different tablespace

-- move table to different tablespace
prod_crm=# alter table TEST8 set tablespace pg_crm;
ALTER TABLE

-- Move index to different tablespace

prod_crm=# alter index TEST_ind set tablespace pg_crm;
ALTER TABLE

postgres=#  alter database prod_crm set tablespace crm_tblspc;

Before running this. make sure there are no active connections in the database.
You can kill the existing session using below query.

postgres# select pg_terminate_backend(pid) from pg_stat_activity where datname='DB_NAME';

-- 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

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)

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=# \dew
List of foreign-data wrappers
Name.         | Owner        | Handler            | Validator
--------------+--------------+--------------------+--------------------------
dblink_fdw    | enterprisedb | -                  | dblink_fdw_validator
libpq_dblink  | enterprisedb | libpq_fdw_handler  | edb_dblink_fdw_validator
oci_dblink    | enterprisedb | oci_fdw_handler.   | edb_dblink_fdw_validator
oracle_fdw.   | enterprisedb | oracle_fdw_handler | oracle_fdw_validator
(4 rows)

postgres=# \deu+

List of user mappings
Server.    | User name    | FDW options
-----------+--------------+----------------------------------------
pg_remote  | enterprisedb | ("user" 'dba_raj', password 'dba_raj')
(1 row)

1. Create extension:

postgres# create extension dblink;

2. Create foreign server:( use the target postgres details.

postgres=# CREATE SERVER oracle_dblink FOREIGN DATA WRAPPER dblink_fdw OPTIONS ( host '10.21.120.131' ,dbname 'postgres' , port '5444');

3. Create user mapping details.

postgres=# CREATE USER MAPPING FOR enterprisedb SERVER oracle_dblink OPTIONS ( user 'dba_raj' ,password 'dba_raj');

4. Test the database link:

postgres=# SELECT dblink_connect('my_new_conn', 'oracle_dblink');

dblink_connect
----------------
OK
(1 row)

5. Fetch data using db_link:

postgres=# select * from dblink('oracle_dblink','select object_name from test') as test_object(object_name varchar );
object_name
---------------------------------------------------
PG_AGGREGATE_FNOID_INDEX
PG_AM_NAME_INDEX
PG_AM_OID_INDEX
PG_AMOP_FAM_STRAT_INDEX

1. Create foreign server:( use the target postgres details.

postgres=# CREATE SERVER oracle_dblink FOREIGN DATA WRAPPER dblink_fdw OPTIONS ( host '10.21.120.131' ,dbname 'postgres' , port '5444');

2. Add new parameter to existing foreign server:

postgres=# ALTER SERVER oracle_dblink options ( ADD port '5444');

3. Modify parameters in foreign server:

postgres=# ALTER SERVER oracle_dblink options ( SET port '5432');

4. Drop foreign server:

postgres=# DROP SERVER oracle_dblink CASCADE;

-- Run this on hot standby server 

postgres=# select pg_is_wal_replay_paused();

pg_is_wal_replay_paused
-------------------------
f

-- If the output is f then, streaming recovery is running, if t means not running.

-- Run on this primary server for outgoing replication details

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+---------------------------------
pid              | 18556
usesysid.        | 10
usename          | enterprisedb
application_name | walreceiver
client_addr      | 10.20.76.12
client_hostname  |
client_port      | 44244
backend_start    | 27-MAY-21 13:56:30.131681 +03:00
backend_xmin     |
state            | streaming
sent_lsn.        | 0/401F658
write_lsn        | 0/401F658
flush_lsn        | 0/401F658
replay_lsn.      | 0/401F658
write_lag        |
flush_lag        |
replay_lag.      |
sync_priority.   | 0
sync_state       | async

-- Run on standby database

postgres=# select pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();
-[ RECORD 1 ]-----------------+---------------------------------
pg_last_wal_receive_lsn       | 0/401F658
pg_last_wal_replay_lsn        | 0/401F658
pg_last_xact_replay_timestamp | 27-MAY-21 16:26:18.704299 +03:00

postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+------------------------------------------------------------------------
pid                   | 7933
status                | streaming
receive_start_lsn     | 0/4000000
receive_start_tli     | 1
received_lsn          | 0/401F658
received_tli          | 1
last_msg_send_time.   | 27-MAY-21 20:29:39.599389 +03:00
last_msg_receipt_time | 27-MAY-21 20:29:39.599599 +03:00
latest_end_lsn.       | 0/401F658
latest_end_time       | 27-MAY-21 16:31:20.815183 +03:00
slot_name             |
sender_host           | 10.20.30.40
sender_port           | 5444
conninfo | user=enterprisedb passfile=/bgidata/enterprisedb/.pgpass dbname=replication host=10.20.30.40 port=5444 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any

-- To stop/pause recovery on replication server(standby)

postgres=# select pg_wal_replay_pause();
pg_wal_replay_pause
---------------------

(1 row)

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

-- To Resume recovery on replication server(standby)

postgres=# select pg_wal_replay_resume();
pg_wal_replay_resume
----------------------

(1 row)

postgres=# select pg_is_wal_replay_paused();
pg_is_wal_replay_paused
-------------------------
f
(1 row)

-- Find lag in bytes( run on standby)

postgres# SELECT pg_wal_lsn_diff(sent_lsn, replay_lsn) from pg_stat_replication;

--- Find lag in seconds( run on standby)

postgres# SELECT CASE WHEN pg_last_wal_receive_lsn() =
pg_last_wal_replay_lsn()
THEN 0 ELSE
EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS lag_seconds;

-- Check existing replication slot details

postgres# SELECT redo_lsn, slot_name,restart_lsn, active,
round((redo_lsn-restart_lsn) / 1024 / 1024 / 1024, 2) AS GB_lag
FROM pg_control_checkpoint(), pg_replication_slots;

-- Create replication slots

postgres#SELECT pg_create_physical_replication_slot('slot_one');

-- Drop unused replication slots

postgres=# SELECT pg_drop_replication_slot('slot_one');

select * from pg_stat_subscription;

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)

Bydefault autocommit is set to on in postgres. You can check the setting .

postgres# \echo :AUTOCOMMIT;
ON;

At session level you can change the autocommit setting :

postgres# \set AUTOCOMMIT OFF

-- You can use watch command to run a particular query repeatedly until you cancel it.
-- watch 3 , means for every 3 seconds, the previous query will be executed 

postgres=# select count(*) from test;
count
-------
4226
(1 row)

postgres=# \watch 3
Tue 19 Apr 2022 08:18:17 PM +03 (every 3s)

count
-------
4226
(1 row)

Tue 19 Apr 2022 08:18:20 PM +03 (every 3s)

count
-------
4226
(1 row)