Database and Middleware Knowledge Sharing: June 2023

****DATABASE MANAGEMENT SCRIPTS ****

Create a database in Postgres:

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

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

Switch to another database:

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

Drop database in 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 "SAMPLE";

-- Drop database using dropdb os utility

postgres$ pwd
/Library/PostgreSQL/10/bin

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

Find the database details in postgres

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

Find postgres db sizes

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;

(or)

How to find timezone info

Commands to find timezone information

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

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

sample=# 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)

How to find postgres version

Below commands can be used to find postgres version:

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

sample=# 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

How to enable archiving(wal) in postgres

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

How to rotate server log in postgres

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

Find query execution time using pg_stat_statement

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

sample=# create extension pg_stat_statements;
CREATE EXTENSION

How to find data directory location

HOW TO FIND DATA DIRECTORY LOCATION

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

sample=# show data_directory;

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

sample=# 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

sample=# 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 current sessions in postgres

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='sample';

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

(1 row)

Kill a session in postgres

-- First find the pid of the session:

sample#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..>>

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

Cancel a session in postgres

-- First find the pid of the session:

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

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

Kill all session of a user in postgres

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

-- List all the session of that user.
sample#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.

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

Find locks present in 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;

Find blocking sessions in postgres

-- QUERY TO FIND BLOCKING SESSION DETAILS

sample#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 conf files

Find location of postgres related conf files

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)

Find current data/time on postgres db

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

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

Find startup time and uptime postgres

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

Find archiver process status

Find postgres configuration values

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

Find the last pg config reload time

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

How to do wal switch manually

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

Monitor archiving progress

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/modify connection limit of database

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

Find wal file details and its size

-- List down all the wal files present in pg_wal

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

Find temp file usage of databases

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)

*****OBJECT MANAGEMENT SCRIPTS****

Create/drop table in postgres

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

Create/drop index commands in postgres

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

Find list of schemas in postgres

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

postgres=# \dn+

list of objects presents in a schema

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

Find schema wise size in postgres db

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;

Find top 10 big tables in postgres

-- 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 tables and its index sizes

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

List down index details in postgres

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

-- All indexes present in database:

postgres#select * from pg_indexes

-- It will show all index details including size:

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

Find the size of a column

Describe the table:

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

Find respective physical file of a table/index

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

Find list of views present

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

Manage sequences in postgres

-- Find the sequence details:

select * from pg_sequences;

(or)

\ds+

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

Create Partial index in postgres

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

Find foreign key details in postgres

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

Find specific table/index size

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

Find list of partitioned table details

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

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

POSTGRES MAINTENANCE SCRIPTS

Update statistics of a table using analyze

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

sample=# analyze testanalyze;
ANALYZE

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

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

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

---Analyze command with verbose command

sample=# 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.

sample=# 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.

Reorg a table using VACUUM command

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:

sample=# vacuum dbatest.emptab;
VACUUM

both vacuum and analyze:

sample=# vacuum analyze dbatest.emptab;
VACUUM

with verbose:

sample# vacuum verbose analyze dbatest.emptab;

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

sample#select * from pg_stat_progress_vacuum;

Check vacuum related information for the table

Reorg a table using VACUUM FULL command

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:

sample=# VACUUM FULL dbatest.emptab;
VACUUM

DEMO TO CHECK HOW IT RECLAIMS SPACE:

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

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

-- We can observe size is still same:

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

-- Run vacuum full and observe the space usage:

sample=# VACUUM FULL dbatest.emptab;
VACUUM

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

Manage autovacuum process in postgres

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

-- Find whether autovacuum is enabled or not:

-- Find other autovacuum related parameter settings
sample=# 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)

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

Now restart :

pg_ctl stop
pg_ctl start

Rebuild indexes using REINDEX

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

Monitor index creation or rebuild

sample=# 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;

(or)

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

Monitor vacuum operation

find and change statistics level of a column

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

Find vaccum settings of tables

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

Modify autovacuum setting of table/index

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

Find last vaccum/analyze details of a table

-- Here the table_name is test

Find how much bloating a table has

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

****USER MANAGEMENT SCRIPTS****

List users present in postgres

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

List Tablespace present in postgres and the usage:

SELECT sp.spcname AS tablespace_name,

pg_size_pretty(pg_tablespace_size(sp.oid)) AS tablespace_size,

pg_size_pretty(pg_tablespace_size(sp.oid) - pg_total_relation_size(c.oid)) AS space_available

FROM pg_tablespace sp

LEFT JOIN pg_class c ON sp.spcname = pg_tablespace_location(c.reltablespace);

List Tablespace and relevant database present in postgres :

SELECT d.datname AS database_name,

t.spcname AS tablespace_name

FROM pg_database d

JOIN pg_tablespace t ON d.dattablespace = t.oid;

List Tablespace and relevant database with path present in postgres :

SELECT d.datname AS database_name,

t.spcname AS tablespace_name,

pg_tablespace_location(t.oid) AS tablespace_path

FROM pg_database d

JOIN pg_tablespace t ON d.dattablespace = t.oid;

List roles present in postgres

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/drop user in postgres

CREATE USER:

sample=# create user TEST_SAMPLE with password 'test123';
CREATE ROLE

CREATE USER WITH VALID UNTIL:

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

CREATE USER WITH SUPER USER PRIVILEGE

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

CREATE ROLE

VIEW USERS:

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

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

sample=# \du+

DROP USER:

drop user DB_user1;

Create/drop role in postgres

- Create role :

sample=# create role dev_admin;
CREATE ROLE

sample=# 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

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

DROP ROLE:

sample=# drop role dev_admin;
DROP ROLE

select rolname,rolcanlogin,rolvaliduntil from pg_roles;

Alter an user in postgres

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

Convert a user to superuser

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

Set password to original one without knowing

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

GRANT privilege commands

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

REVOKE privilege commands

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

Create user profile - EDB Postgres

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

Create/Drop schema in postgres

• 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

Find search_path setting of users

-- 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 of a user in postgres

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

Find privileges granted to a user postgres

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

Find the roles granted to user/role

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;

Find how much bloating a table has

-- Create the pgstattuple extension:

postgres=# create extension pgstattuple;
CREATE EXTENSION

-- bloating percentage of the table "test":

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

****TABLESPACE MANAGEMENT SCRIPTS****

View tablespace info in postgres

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_sample'));

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

(OR)

=# SELECT oid,datname FROM pg_database;

create/drop/rename tablespace in postgres

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_sample;
ALTER TABLESPACE

DROP TABLESPACE:

postgres=# drop tablespace ts_sample;
DROP TABLESPACE

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

find/change default tablespace

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

find/change default temp tablespace

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

sample=# show temp_tablespaces
sample-# ;
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)

How to change tablespace owner

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

Move database to new tablespace in postgres

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

****BACKUP AND RECOVERY SCRIPT****

export table data to file using COPY

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

***************************EOD***************************

Database and Middleware Knowledge Sharing

Thursday, June 1, 2023

SQL queries and scripts for PostgreSQL database administration

POSTGRES MAINTENANCE SCRIPTS

In Oracle How to shrink Securefile LOB tables

Report Abuse

Thursday, June 1, 2023

SQL queries and scripts for PostgreSQL database administration

***POSTGRES MAINTENANCE SCRIPTS***

In Oracle How to shrink Securefile LOB tables

POSTGRES MAINTENANCE SCRIPTS