POSTGRESQL

FOR
ORACLE DBAS
-Ritesh Das
 PostgreSQL for Oracle DBAS
Table of Contents

High Level Compairson Architecture

01 Oracle/Postgresql 02 • Database Cluster & Instance
• Process Comparison
• Functions of Postmaster

-Ritesh Das
• Listener in Postgres?
• Memory Comparison

03 Installation 04 Working with PSQL

• Playing around with psql
• .pgpass
• .psqlrc

Ritesh Das
 Contents Of $PGDATA & Config
05 Instance Management 06 Files

PostgreSQL for Oracle DBAS

• Startup Modes
• Folder Structure of PGDATA
• Shutdown Modes • Control File
• Reload • Configuration Files
• Important Parameters – [Link]
• Context of Parameter to determine Static/Dynamic
Parameters
• Using tools for setting recommended parameters.
• pg_hba.conf

Logical & Physical

-Ritesh Das
07 Structures 08 System Catalog
• Global Objects
• Tablespaces
• System Catalog
• Temporary Tablespace • Corelation of Dictionary Views
• Datafiles • pg_catalog vs information_schema
• TOAST (The Oversized-Attribute Storage • Functions
Technique)

Ritesh Das
09 Concurrency Control 10 VACUUM

PostgreSQL for Oracle DBAS

• VACUUM
• AUTOVACUUM
• Transaction ID Wrap Around
• Autovacuum parameters
• Understanding these parameters

11 Extensions 12 PGADMIN
• Top Extensions
• Installation and Managing Extensions

-Ritesh Das
13 User Management 14 Backup & Recovery
• Understanding LSN, WAL Segments & WAL Logs
• Enable Archive Mode
• Database Crash Recovery
• Timelines (Incarnations)
• Logical Backups
• Physical Backup (pg_basebackup)
• Point in time Recovery

Ritesh Das
15 Patching & Upgrades 16 Performance Tuning

PostgreSQL for Oracle DBAS

• Comparison of Performance Tuning Options
• Understanding Versioning
• Contacting Postgresql Community for help between Oracle & Postgres
• Minor version Upgrade • Different types of Indexes
• Major Version Upgrade using pg_upgrade • Partitions
• Understanding the Explain Plan
• Using Hints
• Using Hints without Changing Code
• Parallelism
• PGBadger
• Generarting Oracle Style AWR reports using

-Ritesh Das
pg_profile

17 High Availability
18 Database Maintenance
1. Comparison of HA options between Oracle &
Postgres • Regular Administration & Maintenance Activities
2. Understanding Streaming Replication • Managing Bloat
3. Replication Slots • Reindex
4. Replication Manager • Debugging
5. PGBouncer
6. PGPool –II
7. Demo
Ritesh Das
 PostgreSQL for Oracle DBAS
01
High Level

-Ritesh Das
Compairson
Oracle/Postgresql

Ritesh Das
High Level Comparison

PostgreSQL for Oracle DBAS

Feature Oracle PostgreSQL
Transaction Log Redo Log Write-Ahead Logging (WAL)
Backup and Recovery Barman (Backup and Recovery Manager),
RMAN (Recovery Manager)
(Physical) pgBackRest, Pgbasebackup (Default)
Backup and Recovery expdp/impdp (Server Side) pg_dump/pg_dumpall
(Logical) exp/imp (Client Side) pg_restore (Client Side)
Table Storage Tablespaces Tablespaces

-Ritesh Das
Large Object Storage SecureFiles TOAST (The Oversized-Attribute Storage Technique)
Automatic Storage ASM (Automatic Storage
No direct equivalent; managed by filesystem or LVM
Management Management)
Range, Interval, Hash, List,
Partitioning Range, Hash, List, Composite
Composite, Reference
Replication Data Guard, GoldenGate Streaming Replication, Logical Replication
Oracle RAC (Real Application
Cluster Management No direct equivalent
Clusters)
Flashback Database, Flashback No direct equivalent, use Point-in-Time Recovery
Flashback Technology
Table, Flashback Query (PITR)Ritesh Das
 Feature Oracle PostgreSQL

PostgreSQL for Oracle DBAS

Procedural Language PL/SQL PL/pgSQL
Data Compression Advanced Compression TOAST, pg_compress
Optimizer Cost-based Optimizer (CBO) Cost-based Optimizer (CBO)
Data Vault, Virtual Private
Row Level Security (RLS), Transparent Data
Security Database (VPD), Transparent Data
Encryption (TDE) - Through pgcrypto extension.
Encryption (TDE)
Monitoring Tools Oracle Enterprise Manager (OEM) pgAdmin, Prometheus, Grafana, Nagios
Job Scheduling Oracle Scheduler pg_cron, native cron jobs
Multi-Version Concurrency

-Ritesh Das
MVCC (Multiversion Multi-Version Concurrency Control (MVCC) with
Control (MVCC) with Undo
Concurrency Control) Transaction ID
Segments
Oracle Connection Manager,
Connection Pooling PgBouncer, Pgpool-II
DRCP
Remote Databases Database Links Foreign Data Wrappers (FDWs)
Not as advanced as Oracle, use of pg_hint_plan,
SQL Profiles, SQL Baselines, SQL
pg_stat_statements, pg_profile (all of these are
Performance Tuning Tuning Advisor, Hints, AWR,
through extensions), Pgbadger (tool to analyze log
ADDM
files)
Auditing Oracle Audit Vault Ritesh Das
PostgreSQL Audit (pgAudit) extension
Terminology

PostgreSQL for Oracle DBAS

Oracle Postgresql

Table/Index Relation
Row Tuple

-Ritesh Das
Column Attribute

Ritesh Das
 PostgreSQL for Oracle DBAS
02
Architecture

-Ritesh Das
1. Database Cluster & Instance
2. Process Comparison
3. Functions of Postmaster
4. Listener in Postgres?
5. Memory Comparison

Ritesh Das
Database Cluster & Instance

PostgreSQL for Oracle DBAS

-Ritesh Das
2

Ritesh Das
Database Cluster & Instance

PostgreSQL for Oracle DBAS

• Cluster: A database cluster is a collection of databases that is stored at a common file system location (the “data area”).

• Instance: A group of backend and auxiliary processes that communicate using a common shared memory area.

• One postmaster process manages the instance; one instance manages exactly one database cluster with all its databases.

-Ritesh Das
• More than one postgres instance can run on a server at one time, so long as they use different data areas and different
communication ports.

Ritesh Das
[postgres@vagrantpgsql ~]$ ps -ef|grep postgres
postgres 657 1 0 Apr30 ? 00:00:00 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/
postgres 688 657 0 Apr30 ? 00:00:00 postgres: logger

PostgreSQL for Oracle DBAS

postgres 692 657 0 Apr30 ? 00:00:00 postgres: checkpointer
postgres 693 657 0 Apr30 ? 00:00:00 postgres: background writer
postgres 923 657 0 Apr30 ? 00:00:00 postgres: walwriter Here we have
postgres 925 657 0 Apr30 ? 00:00:00 postgres: autovacuum launcher 2 instances running
postgres 926 657 0 Apr30 ? 00:00:00 postgres: logical replication launcher
root 2520 2121 0 01:38 pts/1 00:00:00 su - postgres
postgres 2521 2520 0 01:38 pts/1 00:00:00 -bash
postgres 2812 1 0 02:47 ? 00:00:00 /usr/pgsql-15/bin/postgres -D /var/lib/pgsql/15/data2
postgres 2813 2812 0 02:47 ? 00:00:00 postgres: logger
postgres 2814 2812 0 02:47 ? 00:00:00 postgres: checkpointer
postgres 2815 2812 0 02:47 ? 00:00:00 postgres: background writer
postgres 2817 2812 0 02:47 ? 00:00:00 postgres: walwriter
postgres 2818 2812 0 02:47 ? 00:00:00 postgres: autovacuum launcher
postgres 2819 2812 0 02:47 ? 00:00:00 postgres: logical replication launcher
root 2908 2747 0 02:52 pts/2 00:00:00 su - postgres

-Ritesh Das
postgres 2909 2908 0 02:52 pts/2 00:00:00 -bash
postgres 2941 2909 0 02:53 pts/2 00:00:00 ps -ef
postgres 2942 2909 0 02:53 pts/2 00:00:00 grep --color=auto postgres
[postgres@vagrantpgsql ~]$ pstree -p 657
postmaster(657)─┬─postmaster(688)
├─postmaster(692)
├─postmaster(693)
├─postmaster(923)
├─postmaster(925)
└─postmaster(926)
[postgres@vagrantpgsql ~]$ pstree -p 2812
postgres(2812)─┬─postgres(2813)
├─postgres(2814)
├─postgres(2815)

Ritesh Das
├─postgres(2817)
├─postgres(2818)
└─postgres(2819)
 Process Comparison

PostgreSQL for Oracle DBAS

- Processes with same color have a common
functionality
Oracle Postgresql - Processes with a ** and in green in
Postgresql are additional processes not
Checkpoint (CKPT) Checkpointer
present in Oracle.
Log Writer LGWR WAL Writer
Database Writer (DBWR) Background Writer
Archiver (ARCn) Archiver
User Processes Backend Processes

-Ritesh Das
Oracle

Ritesh Das
Functions of Postmaster

PostgreSQL for Oracle DBAS

Functions Description
Client Connection Listens for incoming client connections and spawns new server processes
Manager (e.g., “postgres”) to handle them.

Loads configuration files, initializes shared memory segments, and prepares the server
Server Initialization
for operation.

-Ritesh Das
Process Lifecycle Starts all PostgreSQL server processes, including background processes for database
Manager management and client connections.
Background Process Continuously monitors the health of all background server processes (such as background
Monitoring writer, checkpoint, and autovacuum processes.
Handles server shutdown gracefully, ensuring proper termination of all server processes
Handling Shutdown and data integrity. This might involve flushing buffers, writing commits to disk, and
closing connections.
Ensures data consistency after an instance crash by applying Write-Ahead Log (WAL)
Crash Recovery
records.
Ritesh Das
Background Processes and its functions

PostgreSQL for Oracle DBAS

Functions Description
-Serve client requests.
-Execute queries and transactions.
Backend Processes
-Manage session state.

-Ritesh Das
-- Access data.
Cleans up dead rows and reclaims space. It also Gathers statistics about
Autovacuum Launcher
database objects (tables, indexes)
Background Writer Writes data changes from memory buffers to data files on disk.
Periodically creates a consistent snapshot of the database for faster recovery in
Checkpoint
case of crashes.
WAL Writer Writes changes (Write-Ahead Logs) to the Write-Ahead Log (WAL) files.
Log Collector Writes error log messages to logfiles
Archiver Copies WAL Files to a separate file system for Point in time Recovery.
Ritesh Das
Listener in Postgres?

PostgreSQL for Oracle DBAS

[postgres@vagrantpgsql ~]$ pstree -p 651
postmaster(651)─┬─postmaster(684)
├─postmaster(688)
├─postmaster(689) Background
├─postmaster(938) processes
├─postmaster(939)
└─postmaster(940)

-Ritesh Das
[postgres@vagrantpgsql ~]$ psql
psql (15.6)
Type "help" for help.

postgres=#
[postgres@vagrantpgsql ~]$ pstree -p 651
postmaster(651)─┬─postmaster(684)
├─postmaster(688)
├─postmaster(689) Background
├─postmaster(938) processes
├─postmaster(939)
├─postmaster(940)
└─postmaster(2067)
[postgres@vagrantpgsql ~]$ Ritesh Das
Backend processes
Memory Comparison

PostgreSQL for Oracle DBAS

• Parameter: shared_buffers (~ 15-25% of Server RAM)
Oracle PostgreSQL
• Shared by all the Databases in the Cluster
• Pages are fetched from Disk to Shared Buffers during R/W
• Dirty Buffers written to disk after a CHECKPOINT.
Buffer Cache Shared Buffer

• Parameter: wal_buffers
Redolog Buffer WAL Buffer • Stores transactional changes before they are written to disk,
providing durability and allowing for crash recovery.

-Ritesh Das
sort_area_size work_mem • Parameter: work_mem
• For each backend process for sorting.
maintenance_work_me
m • Postgres writes to disk(temp files) if memory is not sufficient.

temp_buffers Memory used for certain backend database operations such as

VACUUM, Analyze, Reindexing, CREATE INDEX, ALTER TABLE
Java Pool Java Pool ADD FOREIGN KEY.
Large Pool Large Pool
Memory buffers used by each database session for reading data

Ritesh Das
Shared Pool Shared Pool from temporary tables.
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
Installation
03
 PostgreSQL for Oracle DBAS
04 PSQL>
Working with
Postgres=#

-Ritesh Das
PSQL
1. Playing around with psql
2. .pgpass
3. .psqlrc

Ritesh Das
.pgpass

PostgreSQL for Oracle DBAS

$ psql -h [Link] -p 5432 -U postgres -d postgres
Password for user postgres:
psql (15.7)
Type "help" for help.

postgres=>

By configuring password in ~/.pgpass, it doesn’t ask for password:

-Ritesh Das
Format:
[Link]:mydatabase:myuser:mypassword
[Link]:5432:postgres:postgres:pg_password
chmod 0600 ~/.pgpass

$ psql -h [Link] -p 5432 -U postgres -d postgres

psql (15.7)
Type "help" for help.

postgres=>
Ritesh Das
.psqlrc

PostgreSQL for Oracle DBAS

● Allows to customize the psql environment.
● Location: $HOME
● It supports a wide range of configuration options, including setting custom prompts,
defining aliases, adjusting display formats, enabling or disabling features, and more.

-Ritesh Das
Format Result

\set PROMPT1 '%n@%/%R%# ' Changes psql prompt to test1@erpdb=>

\set sel 'SELECT * FROM ' Define shortcuts for frequently used commands or
queries.
\timing Shows the execution time of queries

\set AUTOCOMMIT off Turns off Autocomit

Ritesh Das
 ● Helpful commands in psql
postgres=# help

PostgreSQL for Oracle DBAS

\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# create database erpdb;
CREATE DATABASE

Add a + to any command below to get extended info • By default, it copies the standard system database
Shortcut Description named template1.

-Ritesh Das
\l List databases in the cluster • If you add objects to template1, these objects will
\d list tables, views, and sequences be copied into subsequently created user databases.
\d [name] describe table, view, sequence, or
• There is also a template0 which is exactly same as
index
\db List of tablespaces the initial contents of template1.

\di List indexes • template0 should never be changed

\dt List only tables
\ds List ony sequences
Ritesh Das
 PostgreSQL for Oracle DBAS
05
Instance

-Ritesh Das
Management
1. Startup Modes
2. Shutdown Modes
3. Reload

Ritesh Das
 Startup Modes

PostgreSQL for Oracle DBAS

● Oracle • Postgresql
Startup Modes Startup Modes

-Ritesh Das
No Mount No
Intermediary
modes
Mount

Open Open

Ritesh Das
 Shutdown Modes

PostgreSQL for Oracle DBAS

● Shutdown Modes
Oracle Postgres
Normal (Default) Comparable
Smart (ms) pg_ctl -D $PGDATA stop -ms

Comparable

-Ritesh Das
pg_ctl -D $PGDATA stop -mf
Immediate Fast (Default)

Transactional

Comparable pg_ctl -D $PGDATA stop -mi

Abort Immediate

Ritesh Das
Reload

PostgreSQL for Oracle DBAS

● The reload option sends a SIGHUP signal, causing it to reread its
configuration files ([Link], pg_hba.conf, etc.). This allows changing of
configuration-file options that do not require a complete restart to take
effect.

● pg_ctl -D $PGDATA reload

-Ritesh Das
Or
● SELECT pg_reload_conf();

Ritesh Das
06

PostgreSQL for Oracle DBAS

Contents Of
$PGDATA &
Config Files

-Ritesh Das
1. Folder Structure of PGDATA
2. Control File
3. Configuration Files
4. Important Parameters – [Link]
5. Context of Parameter to determine Static/Dynamic Parameters
6. Using tools for setting recommended parameters.
7. pg_hba.conf

Ritesh Das
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
pg_control Control File

PostgreSQL for Oracle DBAS

● Usually named pg_control, is an 8KB
binary file located in the cluster’s
global directory.
● It stores essential information about
various aspects of the PostgreSQL
server’s internal state.

-Ritesh Das
● It is essential during database
recovery.
● It is a binary file and is present in
$PGDATA/global. To see the
contents, we use a pg_controldata
executable.

Ritesh Das
[Link]

PostgreSQL for Oracle DBAS

● When you use the “ALTER SYSTEM” command to change a parameter in
PostgreSQL, those changes don’t directly go into the main configuration file called
“[Link].” Instead, they are stored in a separate file called
“[Link].” This separation helps manage dynamic changes without
affecting the main configuration.

-Ritesh Das
● When the PostgreSQL server starts up, it first reads the main configuration file
called “[Link]” Parameters from “[Link]” overwrites ones in
[Link]

Ritesh Das
 Understanding Parameter Context

PostgreSQL for Oracle DBAS

Type Context Description Example
eg. shared_buffers.
Static postmaster Parameters that require a server restart to apply Requires a restart to take effect, impacting overall
memory usage and performance.
These settings cannot be changed directly; they
reflect internally determined values. Some of
eg: block_size, lc_collate, lc_ctype,
Static internal them may be adjustable by rebuilding the server
server_encoding.
with different configuration options, or by
changing options supplied to initdb.
eg: autovacuum_work_mem.

-Ritesh Das
Dynamic sighup Parameters that require a reload to apply Changes can be applied with a pg_ctl reload
command.
The backend parameters can be changed/set
eg: post_auth_delay
Dynamic backend while making a new connection to Postgres, and
Changes apply to new sessions.
cannot be changed once the session is started.
log_min_duration_statement.
Dynamic superuser Parameters that can be changed by superusers Changes apply immediately and can be modified
by superusers to aid in performance diagnostics.
search_path
Changes take effect immediately for the current
Dynamic user Parameters that can be changed by any user
Ritesh Das
session and affect how SQL queries resolve
object names.
Altering Parameters at multiple levels

PostgreSQL for Oracle DBAS

● In Postgres we can make parameter changes at multiple levels – Cluster level , DB level , User level , Session

● Cluster level :
ALTER SYSTEM SET max_parallel_workers TO 4;
● Database level :

-Ritesh Das
ALTER DATABASE database_name SET max_parallel_workers TO 4;
● User level :
ALTER ROLE user_name SET max_parallel_workers TO 2;
● Transaction level:
BEGIN;
SET LOCAL max_parallel_workers TO 4;

Ritesh Das
Some important parameters

PostgreSQL for Oracle DBAS

Parameter Description
listen_addresses Specifies the IP addresses the server listens on for incoming connections.
port Specifies the TCP port the server listens on for connections.
max_connections Sets the maximum number of simultaneous connections allowed to the database server.
shared_buffers Determines the amount of memory dedicated to PostgreSQL for caching data in memory.
work_mem Specifies the amount of memory used for each operation performed by a database query.
maintenance_work_mem Sets the maximum amount of memory used for maintenance operations such as vacuuming.

-Ritesh Das
Determines how much information is written to the write-ahead log for crash recovery
wal_level
and replication.
Specifies the maximum number of simultaneous connections from standby servers for
max_wal_senders
replication.
Values: none, ddl, mod (dml), all. Log Statements in Postgresql Logs –Useful for tracing,
log_statement
troubleshooting.
Enables or disables archiving of WAL (Write Ahead Log) files for backup and point-in-time
archive_mode
recovery.
archive_command Specifies the command to use to archive a WAL file when archive_mode is enabled.

Ritesh Das
 PostgreSQL for Oracle DBAS
log_line_prefix

● The log_line_prefix parameter is used to specify the format of log line prefixes in the PostgreSQL server log.
● By Default it is “%m [%p]” (Time Stamp and Process ID)

2024-05-25 10:09:42.486 UTC [24324] LOG: checkpoint starting: time

We can set it to a more useful value

-Ritesh Das
alter system set log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h’;
%t – timestamp, %p – process id, %l – log line number, %d – dbname, %u – username,
%d – application name, %h – hostname/ip

2024-05-25 11:00:13 UTC [26702]: [2-1]

db=postgres,user=postgres,app=psql,client=[local]STATEMENT: alter system set
auto_explain.log_min_duration = 0 ;

Refer to this link for the complete list of options:

[Link]
Ritesh Das
Parameter Recommendation tools

PostgreSQL for Oracle DBAS

The default postgresql parameters are usually not sufficient for most databases.

Tools to tune [Link]

-Ritesh Das
Tool Description
Web-based tool for generating optimized [Link]
pgtune PostgreSQL settings based on hardware and a/
workload.
An open-source web-based tool provided [Link]
pg_configurator by CyberTec for generating optimized [Link]/
PostgreSQL configurations.
[Link] analyses your [Link]
postgresqltuner.
PostgreSQL instance and produces a report, ostgresqltuner
pl
giving hints. It is inspired by [Link]
Ritesh Das
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
pg_hba.conf

PostgreSQL for Oracle DBAS

● Controls client authentication for
PostgreSQL.
● Defines how clients can connect to the
PostgreSQL server, specifying
authentication methods, allowed users,
and source IP addresses.

-Ritesh Das
Complete list of Methods:
TYPE DATABASE USER ADDRESS METHOD
local db1 user1 CIDR trust "trust", "reject", "md5",
"password", "scram-sha-256",
Host db1,db2 etc user1,user2 reject "gss", "sspi", "ident", "peer",
"pam", "ldap", "radius“, "cert".
hostssl @[Link] @[Link] md5
hostnossl all all password Note that "password" sends
passwords in clear text.
hostgssenc scram-sha-256 "md5" or "scram-sha-256" are
hostnogssenc gss Ritesh Das
preferred since they send
encrypted passwords.
 PostgreSQL for Oracle DBAS
Authentication Methods

Authentication
Description
Method
trust Allows any user to connect without authentication. Use with caution; it bypasses security.
reject Rejects all connection attempts, regardless of user or IP address.

-Ritesh Das
md5 Requires a password for authentication. Passwords are hashed using MD5.
password Requires a password for authentication. Encrypted passwords are sent over the network.
scram-sha-256 Securely hashes passwords using the SCRAM-SHA-256 algorithm.
gss Uses GSSAPI (Kerberos) authentication.
sspi Windows-only authentication using SSPI (Security Support Provider Interface).
cert Requires SSL certificates for authentication.
peer Authenticates the system user based on the client’s operating system username.
ldap Uses LDAP (Lightweight Directory Access Protocol) for authentication.

Ritesh Das
Example

PostgreSQL for Oracle DBAS

Session1- [Link]
TYPE DATABASE USER ADDRESS METHOD rep_user@erp
?

-Ritesh Das
local all senior-dba Trust
Session2 -[Link]
Host erp apps [Link]/24 md5
apps@erp

Ritesh Das
Examples

PostgreSQL for Oracle DBAS

DATAB METHO
TYPE ASES USER ADDRESS D Description
Allows host range "[Link]/24" to connect with ony
apps user to erpdb only. The passwords are hashed

-Ritesh Das
host erpdb apps [Link]/24 md5 using MD5.
Allows connection from localhost to all users and all
local all all trust databases without any password.
Allows host range "[Link]/24" to connect with apps,
erpdb, apps, catuser user to databases erpdb and catalog databases,
host catalog catuser [Link]/24 password using Password based authentication.

Ritesh Das
 PostgreSQL for Oracle DBAS
07
Logical & Physical

-Ritesh Das
Structures
1. Global Objects
2. Tablespaces
3. Temporary Tablespace
4. Datafiles
5. TOAST (The Oversized-Attribute Storage Technique)

Ritesh Das
Global Objects

PostgreSQL for Oracle DBAS

Global objects that are common to all databases, namely database roles,
tablespaces, and privilege grants for configuration parameters..

-Ritesh Das
Global Object Description

Roles are cluster-wide entities, meaning they exist and can be used across all
User/Roles
databases within a PostgreSQL cluster.
Storage locations where database objects are stored, providing flexibility in
Tablespaces
managing storage locations.

Ritesh Das
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
High level structure

PostgreSQL for Oracle DBAS

Schema != Users
Cluster

Database Database
1 2

-Ritesh Das
Schema 1 Schema 2 Schema 1 Schema 2

Tables Table Table Table Table Table Table Table

Ritesh Das
Tablespaces

PostgreSQL for Oracle DBAS

● A tablespace is a logical storage unit where you can store database objects such as
tables, indexes, and other data.

● Tablespaces provide flexibility in controlling the disk layout of a PostgreSQL

installation.

-Ritesh Das
● By placing heavily used objects (e.g., indexes) on fast, highly available disks and less
critical data (e.g., archived data) on slower disks, you can optimize performance.

Ritesh Das
Tablespaces

PostgreSQL for Oracle DBAS

CREATE TABLE orders (
order_id serial PRIMARY KEY,
customer_id INT NOT NULL,
pg_default  ${PGDATA}/base order_date DATE NOT NULL,
● Logical storage unit pg_global  ${PGDATA}/global status VARCHAR(20),
) TABLESPACE flash_tbs;

CREATE TABLE archived_orders (

archive_id serial PRIMARY KEY,
order_id INT NOT NULL,
customer_id INT,
order_date DATE,
status VARCHAR(20),

-Ritesh Das
archive_date TIMESTAMP
) TABLESPACE arch_tbs;

CREATE TABLE employees (

employee_id serial PRIMARY KEY
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE,
) TABLESPACE data_tbs;

# Existing table from pg_default to

data_tbs

Ritesh Das
ALTER TABLE employees
SET TABLESPACE data_tbs;
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
Tablespaces
Temporary Tablespace

PostgreSQL for Oracle DBAS

● Used to create temporary files for operations such as sorting, hashing, and temporary tables during query
execution.

● Segregation of temp files from datafiles.

● Parameter: temp_tablespaces
○ Multiple tablespaces can be defined in this parameter.

-Ritesh Das
○ If no temp tablespace is defined then postgres uses default tablespace.

● Usage:
○ During Large Sorts
○ Hash Joins
○ Temporary Tables

Ritesh Das
Co-relation between work_mem and Temporary

PostgreSQL for Oracle DBAS

tablespace
Spills over

-Ritesh Das
work_mem Temporary tablespace

In Memory, Fast
Disk, Slow

Ritesh Das
Datafiles

PostgreSQL for Oracle DBAS

● The datafiles may either be in the pg_tblspc or in base directory depending on the tablespace it is stored in.
● Any relation(table/index) created in postgresql is stored in its own datafiles.
schema_name | table_name | file_path
-------------+------------+-----------------------------------------
public | employee | pg_tblspc/24639/PG_15_202209061/5/24647
public | dept | pg_tblspc/24639/PG_15_202209061/5/24766

● When a table or a index exceeds 1GB, it is divided into gigabyte-sized segments. The first segment's file name is

-Ritesh Das
the same as the filenode; subsequent segments are named filenode.1, filenode.2, etc.
● Actually, 1 GB is just the default segment size. The segment size can be adjusted using the configuration option --
with-segsize when building PostgreSQL.

[postgres@vagrantpgsql 16387]$ pwd

/var/lib/pgsql/15/data/base/16387
[postgres@vagrantpgsql 16387]$ ls -ltr
-rw-------. 1 postgres postgres 1073741824 Feb 28 11:42 16454
-rw-------. 1 postgres postgres 1073741824 Feb 28 11:43 16454.1
-rw-------. 1 postgres postgres 1073741824 Feb 28 11:43 16454.2
-rw-------. 1 postgres postgres 1073741824 Feb 28 11:44 16454.3
Relation > 1GB is split into 1GB segments
-rw-------. 1 postgres postgres 1073741824 Feb 28 11:44 16454.4

Ritesh Das
-rw-------. 1 postgres postgres 1073741824 Feb 28 11:45 16454.5
-rw-------. 1 postgres postgres 601964544 Feb 28 11:45 16454.6
-rw-------. 1 postgres postgres 0 Feb 28 11:30 16457
-rw-------. 1 postgres postgres 8192 Feb 28 11:30 16458
 PostgreSQL for Oracle DBAS
TOAST
TOAST
(The Oversized-
Attribute Storage

-Ritesh Das
Technique)

Ritesh Das
 Block (8kb) Block

PostgreSQL for Oracle DBAS

ROW
ROW
ROW
Compressed
Oversized ROW
ROW Row

Block

-Ritesh Das
Toast Table

Chunk id Chunk Seq Chunk Data

Oversized ROW

Combined size of all columns in the row > 2kb

Ritesh Das
Data Type

PostgreSQL for Oracle DBAS

Datatype Storage Type Description
Int(Integer), Boolean, Smallint Plain No Compression, No Toast
Numeric, Inet Main Compression, No Toast
Bytea, text,json, jsonb Extended Compression, Toast (out of line
storage)
*no data type by default uses external strategy. This is External No compression, Toast Table
used to sometimes improve performance.

-Ritesh Das
alter table tablename alter column columnname set storage external;

Ritesh Das
 PostgreSQL for Oracle DBAS
08
System Catalog

-Ritesh Das
1. System Catalog
2. Corelation of Dictionary Views
3. pg_catalog vs information_schema
4. Functions

Ritesh Das
System Catalog

PostgreSQL for Oracle DBAS

● pg_catalog is a system schema in PostgreSQL that stores metadata about the
database. It can be thought to be similar to Oracle’s data dictionary.
● System Table and Views: It contains system tables and views that hold
information about the database objects like tables, columns, data types, functions,

-Ritesh Das
and indexes.
● It is included in the search path by default, meaning its tables and views can be
accessed without specifying the schema name.
Complete list of catalogs: [Link]

Ritesh Das
Corelation of Dictionary views

PostgreSQL for Oracle DBAS

Oracle v$ View PostgreSQL Equivalent Description
v$database pg_database Information about databases
v$session pg_stat_activity Information about active database sessions
v$process pg_stat_activity Information about server processes
SQL statements statistics (Needs pg_stat_statements
v$sql pg_stat_statements
extension)
v$parameter pg_settings Current database configuration parameters

-Ritesh Das
v$tablespace pg_tablespace Information about tablespaces
pg_tablespace and Information about data files (PostgreSQL uses
v$datafile
pg_class tablespaces and file storage differently)
v$lock pg_locks Information about locks held in the database
v$sysstat pg_stat_database System statistics
Lists redolog/wal files. Pg_wal directory also has
v$logfile pg_ls_waldir()
metadata

Ritesh Das
Corelation of Dictionary Views

PostgreSQL for Oracle DBAS

Oracle Dictionary
PostgreSQL pg_catalog PostgreSQL information_schema
View
dba_tables pg_tables tables
dba_views pg_views views
dba_indexes pg_indexes indexes
dba_tab_columns pg_attribute columns

-Ritesh Das
dba_constraints pg_constraint table_constraints
dba_cons_columns pg_constraint and pg_attribute key_column_usage
dba_users pg_authid users (not directly comparable)
dba_sequences pg_sequences sequences
dba_synonyms Synononyms not supported in PG Synononyms not supported in PG
dba_triggers pg_trigger triggers
dba_objects pg_class tables, views, sequences, etc.
dba_roles pg_roles applicable_roles

Ritesh Das
pg_catalog vs information_schema

PostgreSQL for Oracle DBAS

● Both are schemas serving similar purpose: ie stores database metadata.
● pg_catalog has data which is meant to be queried within Postgresql.
● Information_schema: (As per postgres documentation)
“ The information schema is defined in the SQL standard and can therefore be expected to be
portable and remain stable — unlike the system catalogs, which are specific to PostgreSQL and are
modeled after implementation concerns. The information schema views do not, however, contain

-Ritesh Das
information about PostgreSQL-specific features; to inquire about those you need to query the system
catalogs or other PostgreSQL-specific views.”

In simple terms it means information_schema is used when we want to implement code

which can run across multiple databases(mysql, postgresql etc).

Ritesh Das
Functions

PostgreSQL for Oracle DBAS

● [Link]
Function Name Example Query Sample Output
pg_is_in_recovery returns true if the server is
pg_is_in_recovery SELECT pg_is_in_recovery();
currently in recovery mode.
pg_get_userbyid returns the name of a database
pg_get_userbyid SELECT pg_get_userbyid(10);
user from their OID.

-Ritesh Das
SELECT pg_get_indexdef returns the definition of an
pg_get_indexdef
pg_get_indexdef('my_index'::regclass); index.
SELECT
pg_create_restore_poi pg_create_restore_point creates a named
pg_create_restore_point('my_restore_point
nt restore point.
');
pg_current_wal_lsn returns the current write-
pg_current_wal_lsn SELECT pg_current_wal_lsn(); ahead log (WAL) location as an LSN (Log
Sequence Number).
SELECT pg_backup_start initiates a base backup operation
pg_backup_start
pg_backup_start('my_backup_label'); with the specified label.
pg_switch_wal SELECT pg_switch_wal();
segment. Ritesh Das
pg_switch_wal forces a switch to a new WAL
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
Concurrency
Control
09
Multi-Version Concurrency Control

PostgreSQL for Oracle DBAS

Postgresql
Table

Table

Oracle
Until
update Session1 commit
New row
update
New row v2
Session1

Old row v1

-Ritesh Das
Undo Tablespace

Old row Session2

Session2 MVCC (Multi Version Concurrency Control)

Ritesh Das
MVCC

PostgreSQL for Oracle DBAS

When querying, session will
fetch the row with the highest
version.
EMP Table
User1
Empno Ename Role Salary In the fig below, when User 3 updates the table,
then a new version of the
2132 Mike System Analyst 3000 row is created and the user updates
that row. User 1 and 2 still reads
118 Scott Manager 7500
the earlier version (ie v1, which was the greatest
1158 Adam Jr Trainee 1500 version when the query
session started.

-Ritesh Das
User2 When a new session, ie user 4 queries
EMP Table
the row, then it find the updated data
Empno Ename Role Salary ie (v2)
2132 Mike System Analyst 3000 Row (v1) is now marked as dead tuple,
118 Scott Manager 7500 which will be cleaned up by the vaccum process

User1 1158 v1 Adam Jr Trainee 1500

1158 v2 Adam Trainee 1700

User2
update
User3 Ritesh Das
 PostgreSQL for Oracle DBAS
10
Vacuum

-Ritesh Das
1. VACUUM
2. AUTOVACUUM
3. Transaction ID Wrap Around
4. Autovacuum parameters
5. Understanding these parameters

Ritesh Das
VACUUM

PostgreSQL for Oracle DBAS

● VACUUM reclaims storage occupied by dead tuples (rows).
● It reclaims space and makes it available for re-use

Regular VACUUM:
○ Reclaims space and makes it available for re-use.
○ Does not obtain exclusive lock.
○ Does not return space back to OS.

-Ritesh Das
VACUUM FULL:
○ Rewrites the entire table contents into a new disk file, allowing unused space to be returned to the
OS.
○ It is slower and requires an ACCESS EXCLUSIVE lock on the table.

Syntax:
VACUUM [ (option [, ...]) ] [table_and_columns [, ...]]
Options:
FULL: Selects “full” vacuum (reclaims more space but takes longer).
VERBOSE: Provides detailed output during the process.
ANALYZE: Performs both VACUUM and ANALYZE for each selected table.
Ritesh Das
AUTOVACUUM

PostgreSQL for Oracle DBAS

● Background utility process that automates the vacuum process.
● Performed on tables with large DML operations.
● These checks use the statistics collection facility, (ie. Needs, track_counts = true) (enabled by default)

It consists of two distinct operations:

● autovacuum vacuum
● autovacuum analyze

-Ritesh Das
Tasks of Autovacuum:
○ Clean up Dead Tuples
o Running Analyze to keep table statistics up-to-date.
o Prevents Transaction Wrap Around.
o Updates visibility map

Ritesh Das
Transaction Wrap Around

PostgreSQL for Oracle DBAS

● PostgreSQL uses a 32-bit transaction ID (XID) system.
● Transaction Wrap Around – Once Max XID is reached ie (2^32 -1 ), it then wraps
around to 0.
● Data Corruption.

● Autovacuum

-Ritesh Das
○ It recycles XIDs by marking XIDs as Frozen.
○ It freezes XIDs which are older than the frozen XID threshold.
○ Frozen XIDs are safe to reuse.
○ Oldest XIDs are recycled first.

Ritesh Das
 Autovacuum parameters

PostgreSQL for Oracle DBAS

Default
Parameter Description
Value
autovacuum Enables or disables the autovacuum daemon. on
autovacuum_max_workers Sets the maximum number of autovacuum worker processes. 3
autovacuum_naptime Specifies the time in seconds between autovacuum runs. 1 minute
Specifies the minimum number of updated or deleted tuples needed to trigger a
autovacuum_vacuum_threshold 50
VACUUM operation.

-Ritesh Das
Specifies the minimum number of updated or inserted tuples needed to trigger an
autovacuum_analyze_threshold 50
ANALYZE operation.
Specifies a fraction of the table size to trigger a VACUUM operation based on the
autovacuum_vacuum_scale_factor 0.2
number of dead tuples.
Specifies a fraction of the table size to trigger an ANALYZE operation based on the
autovacuum_analyze_scale_factor 0.1
number of tuples.
Sets the maximum age in transactions before a table row is considered for anti-
autovacuum_freeze_max_age 200 million
wraparound vacuuming.
autovacuum_multixact_freeze_max Sets the maximum age in multixact transactions before a row is considered for anti-
400 million
_age wraparound vacuuming.
Specifies the delay between VACUUM operations to avoid impacting other database 20
autovacuum_vacuum_cost_delay
autovacuum_vacuum_cost_limit
activity.
Specifies the maximum time that VACUUM operations can consume.
Ritesh Das milliseconds
-1 (no limit)
 autovacuum_max_workers

Autovacuum_naptime
(60 seconds)
Cleanup up
the tables

Yes
No?
Go back to sleep
Work to
be done?

autovacuum_vacuum_scale_factor autovacuum_vacuum_threshold
% of rows changed (20%) # of rows changed (50)

What qualifies as
work?

autovacuum_analyze_scale_factor autovacuum_analyze_threshold
% of rows changed (10%) # of rows changed (50)
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
Understanding these parameters

PostgreSQL for Oracle DBAS

● Autovacuum process wakes up (autovacuum_naptime default 60seconds, so wakes up every 60
seconds) and checks whether there is work to do.
● If there is, then autovacuum will spawn (autovacuum_max_workers 3 by default)

So how do we define if there is work to do?

-Ritesh Das
1. autovacuum_vacuum_scale_factor (0.2 default) – if 20% of data is changed in a table. If a table
has just 1 row, updating 1 row would cause 100% change in rows. That is why we have the next
parameter.
2. autovacuum_vacuum_threshold (50 default) – There should be atleast 50 rows effected.

Similar for statistics:

1. autovacuum_analyze_scale_factor (0.1) – if 10% data is changed, then autovacuum analyze is
triggered.
2. autovacuum_analyze_threshold (50) - There should be atleast 50 rows effected
Ritesh Das
 PostgreSQL for Oracle DBAS
● Work Limit: Autovacuum does some amount of work, like cleaning up a certain
number of dead rows. Once it hits the autovacuum_vacuum_cost_limit,
● Pause: it pauses for the duration set by autovacuum_vacuum_cost_delay (in
milliseconds).
● Resume: After the pause, it continues with the next batch of work.

-Ritesh Das
Ritesh Das
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
Extensions
11
Extensions

PostgreSQL for Oracle DBAS

● Extends the core functionality of Postgresql.
● We can add new features, functions, and types without modifying the database's
core codebase.
● These extensions can be sourced from various places, each serving different needs
and use cases:

-Ritesh Das
○ contrib Module: For stable, officially supported extensions included with PostgreSQL.
○ PGXN: For a wide range of easily installable extensions.
○ Git Repositories: For the latest and custom development versions.

Ritesh Das
TOP Extensions for DBAs

PostgreSQL for Oracle DBAS

Extension Name Description Source
pg_stat_statements Tracks execution statistics of all SQL statements executed. contrib package

pg_repack Reorganizes tables and indexes without locks. PGXN, Git ([Link]
Schedules PostgreSQL commands directly from the
pg_cron Git ([Link]
database.
pg_partman Manages time-based and serial-based table partition sets. PGXN, Git ([Link]

-Ritesh Das
pgaudit Provides detailed session and object audit logging. PGXN, Git ([Link]

pg_hint_plan Enables hinting the planner on how to execute queries. PGXN, Git ([Link]

timescaledb Time-series database extension for PostgreSQL. PGXN, Git ([Link]

pgagent Job scheduler for PostgreSQL. PGXN, Git ([Link]

pglogical Logical replication system for PostgreSQL. Git ([Link]

Collects statistics about the execution of SQL queries.

pg_profile (We will see in example how we use this to generate Git ([Link]
AWR style report in Postgres)
Ritesh Das
Installation of Extension

PostgreSQL for Oracle DBAS

1. Install Needed File at OS level.

● Source PGXN
○ pgxn install <extension name> [Ensure pgxn is installed]
● Source Contrib
○ Ensure contrib module is installed at OS level.

-Ritesh Das
○ sudo yum install -y postgresql15-contrib.x86_64
● Source GIT
○ Follow installation instructions on the git page.

2. Make changes in the [Link] and restart the DB

shared_preload_libraries=‘existing extensions',‘new extension’;

Ritesh Das
3. Install extension at DB level

PostgreSQL for Oracle DBAS

CREATE EXTENSION <Extension Name>;
create extension pg_partman;

4. Validate if Extension is installed.

SELECT name, default_version, installed_version, comment FROM
pg_available_extensions;

-Ritesh Das
Or \dx

Ritesh Das
Managing Extensions

PostgreSQL for Oracle DBAS

● Upgrading Extensions

ALTER EXTENSION extension_name UPDATE TO 'new_version’;

E.g. ALTER EXTENSION pg_stat_statements UPDATE TO '1.8’;

● Removing Extensions

-Ritesh Das
DROP EXTENSION IF EXISTS pg_stat_statements;

Ritesh Das
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
PGADMIN
12
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
 PGADMIN DEMO

PostgreSQL for Oracle DBAS

Refer video for this – Completely based on Demo

-Ritesh Das
Ritesh Das
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
Management
User
13
User and Schema

PostgreSQL for Oracle DBAS

● User != Schema (Schema is a separate logical container)
● Users are at cluster level and not tied up to a specific database.
● Schema is also referred as namespace.
● A Schema is local to each database.

-Ritesh Das
Cluster

Users Tablespaces

Databases

Database 1 Database 2

Schema 1 Schema 2 Schema 1 Schema 2

Ritesh Das
Tables Table Table Table Table Table Table Table
Users & Roles

PostgreSQL for Oracle DBAS

● The concept of user and role are different from Oracle.
● A role can act as a user, a group of users.
● User is considered a role with login privilege.

erpdb3=# CREATE USER apps_erp3 WITH PASSWORD 'apps_erp’;

CREATE ROLE

-Ritesh Das
create user user1 password 'user1'; create role user1 login password 'user1';
Database
● For simplicity we will create users with create user command.
● We need to give explicit privleges to every entity to the user.
○ CREATE USER apps_erp WITH PASSWORD 'apps_erp';
○ GRANT ALL ON DATABASE erpdb2 TO apps_erp ;
○ GRANT ALL ON SCHEMA app_schema TO apps_erp; Schema

Tables
Ritesh Das
 PostgreSQL for Oracle DBAS
Privileges to role and role to User

-Ritesh Das
Privileges directly to User

Ritesh Das
 Attributes

PostgreSQL for Oracle DBAS

Roles (which can represent both users and groups) have various attributes that define their capabilities and
permissions within the database.
PostgreSQL Oracle
Description Description
Attribute Equivalent
Grants unrestricted access to all Grants full administrative privileges, allowing the
SUPERUSER SYSDBA
database objects and operations. user to perform any database operation.
CREATE USER test1 Allows the role to create new CREATE ANY Allows the user to create new databases (at a

-Ritesh Das
CREATEDB
databases. DATABASE higher system level).
WITH PASSWORD
Allows the role to create, alter, and CREATE USER, Allows the user to create and manage other
'test1’ SUPERUSER; CREATEROLE
drop other roles. ALTER USER database users.
Allows the role to inherit the privileges Users can be granted multiple roles and inherit
INHERIT Role Inheritance
ALTER user test1 of roles it is a member of. the privileges of those roles.
SUPERUSER; LOGIN
Allows the role to log in to the CREATE
Allows the user to connect to the database.
database. SESSION
Allows the role to initiate streaming
SYSDG (Data Includes privileges for managing and initiating
REPLICATION replication and manage replication
Guard) data replication and standby databases.
slots.

BYPASSRLS
Allows the role to bypass row-level
security policies.
Bypass
VPD/FGAC
Ritesh Das
Allows the user to bypass Virtual Private
Database (VPD) policies or Fine-Grained Access
Control (FGAC).
Pre-Defined Role Allowed Access
Read all data (tables, views, sequences), as if having SELECT rights on those objects, and USAGE rights on all
schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being

PostgreSQL for Oracle DBAS

pg_read_all_data used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to.
Write all data (tables, views, sequences), as if having INSERT, UPDATE, and DELETE rights on those objects, and
USAGE rights on all schemas, even without having it explicitly. This role does not have the role
attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this
pg_write_all_data role is GRANTed to.
pg_read_all_settings Read all configuration variables, even those normally visible only to superusers.
Read all pg_stat_* views and use various statistics related extensions, even those normally visible only to
pg_read_all_stats superusers.
pg_stat_scan_tables Execute monitoring functions that may take ACCESS SHARE locks on tables, potentially for a long time.
Read/execute various monitoring views and functions. This role is a member
pg_monitor of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables.

-Ritesh Das
pg_database_owner None. Membership consists, implicitly, of the current database owner.
pg_signal_backend Signal another backend to cancel a query or terminate its session.
Allow reading files from any location the database can access on the server with COPY and other file-access
pg_read_server_files functions.
Allow writing to files in any location the database can access on the server with COPY and other file-access
pg_write_server_files functions.
pg_execute_server_pro Allow executing programs on the database server as the user the database runs as with COPY and other functions
gram which allow executing a server-side program.
pg_checkpoint Allow executing the CHECKPOINT command.
pg_use_reserved_conn
ections Allow use of connection slots reserved via reserved_connections.
pg_create_subscription Allow users with CREATE permission on the database to issue CREATE SUBSCRIPTION. Ritesh Das
[Link]
 PostgreSQL for Oracle DBAS
ADMIN-ROLE=RW-Role+
Group Manager
All Privileges
Role Inheritance

Inherits Inherits

RW-Role=RO-Role+
Line Manager

-Ritesh Das
Insert, Update

Inherits Inherits

Employee Role RO-Role

Ritesh Das
Privileges for Object Types

PostgreSQL for Oracle DBAS

Object Type Privileges

Table SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, ALL PRIVILEGES

View SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES

-Ritesh Das
Sequence USAGE, SELECT, UPDATE, ALL PRIVILEGES
Schema CREATE, USAGE, ALL PRIVILEGES
Database CREATE, CONNECT, TEMP, ALL PRIVILEGES
Function EXECUTE, ALL PRIVILEGES
Procedure EXECUTE, ALL PRIVILEGES
Type USAGE, ALL PRIVILEGES
Domain USAGE, ALL PRIVILEGES
Ritesh Das
Abbreviations for Privileges

Privilege Abbreviation Applicable Object Types

PostgreSQL for Oracle DBAS

SELECT r (“read”) LARGE OBJECT, SEQUENCE, TABLE (and table-
like objects), table column
INSERT a (“append”) TABLE, table column
UPDATE w (“write”) LARGE OBJECT, SEQUENCE, TABLE, table
column
DELETE d TABLE
TRUNCATE D TABLE
REFERENCES x TABLE, table column
TRIGGER t TABLE

-Ritesh Das
CREATE C DATABASE, SCHEMA, TABLESPACE
CONNECT c DATABASE
TEMPORARY T DATABASE
EXECUTE X FUNCTION, PROCEDURE
USAGE U DOMAIN, FOREIGN DATA
WRAPPER, FOREIGN
SERVER, LANGUAGE, SCHEMA, SEQUENCE, TY
PE
SET s PARAMETER
ALTER SYSTEM A PARAMETER
Ritesh Das
 PostgreSQL for Oracle DBAS
14
Backup &

-Ritesh Das
Recovery
1. Understanding LSN & WAL Segments
2. Enable Archive Mode
3. Database Crash Recovery
4. Timelines (Incarnations)
5. Logical Backups
6. Physical Backup (pg_basebackup)
7. Point in time Recovery
Ritesh Das
LSN, WAL Segments

PostgreSQL for Oracle DBAS

WAL Buffer

Session WAL Writer

LSN
WAL Segment

-Ritesh Das
Ritesh Das
 WAL Segment Name
000000020000000100000075

PostgreSQL for Oracle DBAS

LSN
1/7528DDB0

Log File sequence Number(X) or

the WAL Segment Number 1

LSN Format is X/Y in this case 1/7528DDB0

-Ritesh Das
LSN (1/7528DDB0)
Byte Offset(Y) |
Hexadecimal |-- Log file sequence number: 1
Timeline ID |-- Byte offset within the log file: 7528DDB0 (hex)=1,957,905,584 (decimal)
|
|-- WAL segment size: 16 MB (16,777,216 bytes)
WAL File Name: 000000020000000100000075 |
|-- WAL segment number: 1,957,905,584 / 16,777,216 ≈ 116 (or 0x75)
Log File Sequence
Number (Logical) (Physical)Segment |
|-- WAL file name: 000000020000000100000075

Ritesh Das
● Get current LSN • Get current Wal File Name:

PostgreSQL for Oracle DBAS

SELECT pg_current_wal_lsn(); SELECT pg_walfile_name(pg_current_wal_lsn());
pg_current_wal_lsn pg_walfile_name
-------------------- --------------------------
0/14BB7580 000000010000000000000014
(1 row)

SELECT * FROM pg_ls_waldir();

name | size | modification

-Ritesh Das
--------------------------+----------+------------------------
000000010000000000000014 | 16777216 | 2024-06-21 16:54:52+09
000000010000000000000015 | 16777216 | 2024-06-21 16:48:01+09
000000010000000000000016 | 16777216 | 2024-06-21 16:48:05+09
000000010000000000000017 | 16777216 | 2024-06-21 16:48:10+09
000000010000000000000018 | 16777216 | 2024-06-21 16:48:15+09
000000010000000000000019 | 16777216 | 2024-06-21 16:48:21+09
00000001000000000000001A | 16777216 | 2024-06-21 16:48:29+09
00000001000000000000001B | 16777216 | 2024-06-21 16:48:38+09
00000001000000000000001C | 16777216 | 2024-06-21 16:48:49+09 Ritesh Das
Enabling Archive Mode

PostgreSQL for Oracle DBAS

• archive_mode = on
• archive_command = 'cp %p /arch/%f'
%p (the path to the current WAL file)
%f (the name of the current WAL file).

-Ritesh Das
• wal_level = replica
WAL Level Description
Minimal Not enough Info for PITR, Only sufficient for Crash Recovery.

Replica Writes enough data to support:

• PITR
• Replication
• Read only queries on Standby.
Logical Adds information necessary to support logical decoding
Ritesh Das
DATABASE CRASH RECOVERY

Startup Process

PostgreSQL for Oracle DBAS

Enter Crash Was the DB shutdown cleanly?
Exit Crash Recovery.
CKP Recovery Mode,
Allow incoming
Stop incoming
Connections
Connections NO
Give me the
Control
last checkpoint,
File
Time line and other
metadata
CKP
Timeline

-Ritesh Das
CKP Perform Cleanup -removing
temporary files

Apply WAL
Logs from
Crash CKP Last CKPT
Until Crash Recovery

8pm

Ritesh Das
WAL Logs
Timeline

PostgreSQL for Oracle DBAS

Accidental Table
delete
Backup

10 Timeline 1
2
Accidental Table drop

-Ritesh Das
Timeline 2

$PGDATA/pg_wal/[Link]
Timeline 3
1 0/1000000 no recovery target time specified
2 0/2000000 recovery target time: 2024-06-06 10:00:00
3 0/3000000 recovery target time: 2024-06-06 12:00:00

Ritesh Das
Backup & Recovery

PostgreSQL for Oracle DBAS

Feature Oracle Postgresql
Client Side Logical Backup Exp/imp pg_dump/pg_dumpall
pg_restore
Server Side Logical Backup Expdp/impdp N/A
Physical Backup rman pg_basebackup (native tool)
pgBackRest (install separately)

-Ritesh Das
Barman (install separately)

Comparison of Backup Methods

Incremental Specific DB/ Global Cross Version

Compression Backup Schema/Table Objects Compatibility S3 Support
Barman ✔ ✔ ✗ ✔ ✗ ✔
pgBackRest ✔ ✔ ✗ ✔ ✗ ✔
pg_basebackup ✔ ✗ ✗ ✔ ✗ ✗
pg_dump ✔ ✗ ✔ ✗ ✔ ✗
pg_dumpall ✗ ✗ ✗ ✔ ✔ ✗ Ritesh Das
pg_dump

PostgreSQL for Oracle DBAS

● Logical backup tool for PostgreSQL to backup a single database.
● Backups database in multiple formats.
● Can selectively backup specific tables or schemas.

-Ritesh Das
● Supports parallelism.
● Allows for compression and encryption.

Ritesh Das
pg_restore

PostgreSQL for Oracle DBAS

● Utility to restore a PostgreSQL database from a dump file created by pg_dump.
● Can selectively restore specific tables, schemas, or other database objects.
● Supports parallel restore.
● Can handle compressed and encrypted dumps.

-Ritesh Das
● Supports post-processing options such as enabling/disabling triggers.

Note: Not possible to remap schemas similar to Oracle. Workaround – Backup to SQL
file.

Ritesh Das
pg_dumpall

PostgreSQL for Oracle DBAS

● Utility to dump all PostgreSQL databases on a server into a single script file.
● Dumps entire PostgreSQL cluster, including global objects (roles, tablespaces)
and all databases.
● Outputs ONLY in plain SQL format.

-Ritesh Das
● Only supports plain text format, not suitable for parallel dumps or selective
restores.
● Usually used to take backup of globals, and then backup individual databases using
pg_dump.

Ritesh Das
 Backup Formats

PostgreSQL for Oracle DBAS

Feature/Attribute Plain Text Custom Tar Directory
Command Line Option -Fp -Fc -Ft -Fd
File Extension .sql .dump, .custom .tar Directory
Compression Supported No Yes Yes No
Plain Text Yes No No No

-Ritesh Das
Parallelism Supported No No No Yes
Human-Readable Yes No No No
Restore Tool psql pg_restore pg_restore pg_restore
Supports Selective Restore Yes (Modify the file) Yes No Yes
Need a human-
Small to medium- Most use cases Very large databases
readable backup
sized databases, requiring needing maximum
Best Suited For for quick
readability, and compression and performance and
inspection
simplicity flexibility
/portability Ritesh Das
flexibility
 Physical backup using pg_basebackup

PostgreSQL for Oracle DBAS

● Backs up running DB. Puts DB Puts DB
● Only backups entire cluster. In Backup Mode $PGDATA Out of Backup Mode
● Backup Format:
Plain Dir, Tar

-Ritesh Das
● Includes Wal Log files Plain
● Supports Compression. Directory

● Used for backup and stanbdy DB creation.

● Restoration using OS commands.
● The backup user needs superuser/replication attributes.
● Generates manifest file in backup directory.
● Can take backup from Primary and standby db server too.
Ritesh Das
Taking the Backup

PostgreSQL for Oracle DBAS

● Pre-Req:
○ Enable Archive Mode.
○ wal_level=replica
○ pg_hba.conf –

-Ritesh Das
host replication backup_user <client_ip_address>/32 md54

● Backup Command:
pg_basebackup -h <source_host> -U backup_user -D /path/to/backup -Ft -z -P
-h <source_host> -U backup_user -D /path/to_store_backup_files
-Ft: Specifies the tar format. -z: Compresses the backup. -P: Shows progress information.

Ritesh Das
 Accidental
Backup Table delete

PostgreSQL for Oracle DBAS

PITR 2
PITR
10

1. Shutdown DB
Parameter Description Scenario
Specifies what point to recover to
2. Touch $PGDATA/[Link] recovery_target
(time, transaction, etc.).
Restore to a specific point in time or event.
A name you gave to a specific Recover to a specific named event, like
recovery_target_name
3. Set appropriate recovery parameters point to restore to. "before_upgrade".

-Ritesh Das
The exact date and time to Restore to just before an unintended
recovery_target_time
4. Startup DB restore the database to. delete happened at 10 AM.
The exact transaction ID to Restore to just after transaction ID 12345
recovery_target_xid
restore to. completed.
File Name Purpose The exact log sequence number Restore to a specific point in the WAL
recovery_target_lsn
[Link] Touch to restore to. logs, like a specific operation or change.
$PGDATA/[Link] Whether to include the target Include the exact time or transaction in the
recovery_target_inclusive
Used to trigger recovery event in the recovery. recovery to verify a specific change.
mode Which timeline to use if there are
Recover to a specific timeline after a
recovery_target_timeline multiple due to previous
[Link] Used to trigger standby failover situation.
recoveries.

Ritesh Das
mode Pause recovery when reaching the target
What to do when the target is
recovery_target_action for inspection or automatically promote
reached, like pause or promote.
the standby.
 PostgreSQL for Oracle DBAS
15
Patching &

-Ritesh Das
Upgrades
1. Understanding Versioning
2. Contacting Postgresql Community for help
3. Minor version Upgrade
4. Major Version Upgrade using pg_upgrade

Ritesh Das
Understanding Versioning

PostgreSQL for Oracle DBAS

● Usually, a major version is released every year around the Sep-Oct time frame and
one older version is de-supported.
● Minor versions are released atleast once every quarter (second Thursday of
February, May, August, and November).

-Ritesh Das
● The minor version represents a patch version, which is a bug / security fix release.
● Version 16.1 (16 is the Major release and 1 is the minor release).
● Versions > 10 (Format is 10.23 – 10 major, 23 is minor)
● Versions < 10 (Format 6.4.2 – 6.4 is major, 2 is minor)

Ritesh Das
Contacting Postgresql Community for help

PostgreSQL for Oracle DBAS

● If you’ve encountered a bug in PostgreSQL, you can report it using the bug reporting form available on
the PostgreSQL support page ([Link] Make sure to read over the bug
reporting guidelines before submitting your report.
● If commercial support services are needed, then there are many vendors which provides professional
services to the PostgreSQL community. Refer [Link]

-Ritesh Das
● Some popular vendors are: EDB, Percona, Fujitsu, Cloud vendors such as AWS, Azure, GCP etc.
● Interesting FAQ: [Link]

A bug I'm encountering is fixed in a newer minor release of PostgreSQL, but I don't want to upgrade. Can I get a patch
for just this issue?
No. Nobody will make a custom patch for you so you can (say) extract a fix from 8.4.3 and apply it to 8.4.1 . That's
because there should never be any need to do so. If you really feel you have to do this you will need to extract the
patch from the sources yourself.

Ritesh Das
Patching to Minor Version

PostgreSQL for Oracle DBAS

Backup

Stop

-Ritesh Das
Database

Yum install
new binaries

Start
Database

Ritesh Das
Upgrading to Major Version

PostgreSQL for Oracle DBAS

● Traditional Upgrade involves logical backup/restore.
● Postgres introduced a tool called pg_upgrade, to upgrade without logical
backup & restore.
● Higher version of Postgres often change the layout of the system tables, but the
internal data storage format rarely changes.
● Upgrade Method

-Ritesh Das
○ Link (Uses hard link to reference existing data files ${PGDATA})
○ Copy (Copies data ${PGDATA} from old cluster to new)
● Extensions:
○ If 3rd party extensions exist, validate its compatibility.

Ritesh Das
 Comparison of Methods in pg_upgrade

PostgreSQL for Oracle DBAS

Feature Link Method (--link or -k) Copy Method (--copy)
Data Uses hard links to reference Copies data files from the old
Handling existing data files. cluster to the new cluster.
Faster, as it avoids duplicating Slower, as it involves copying each
Speed
data files. data file.
Requires less additional disk Requires more disk space, as it
Disk Space
space, as it only creates links. duplicates the data.

-Ritesh Das
Old cluster is unusable after
Old Old cluster remains intact and
the upgrade, as files are
Cluster usable after the upgrade.
linked.
Less safe, since issues with the Safer, since the old cluster
Safety new cluster can affect the old remains unchanged and can be
cluster. used as a fallback.
Risk of data corruption if the
Data Lower risk of data corruption, as
upgrade fails, as files are
Integrity files are duplicated.
shared.

Rollback
Rollback is more complex due
to shared files.
Easier rollback to old cluster if
needed.
Ritesh Das
 PostgreSQL for Oracle DBAS
Copy Method
Old PGDATA New PGDATA

/var/lib/psql/13/data /var/lib/psql/15/data

-Ritesh Das
Link Method

Ritesh Das
 PostgreSQL for Oracle DBAS
Copy Method
Old PGDATA New PGDATA

/var/lib/psql/13/data /var/lib/psql/15/data

-Ritesh Das
Link Method

Ritesh Das
Upgrading to Major Version

PostgreSQL for Oracle DBAS

pg_upgrade
Backup

Start
Install New Dababase

-Ritesh Das
Binaries

Post Upgrade
pg_upgrade Steps
check

Delete Old
Binaries
Stop DB

Ritesh Das
 PostgreSQL for Oracle DBAS
16
Performance
Tuning

-Ritesh Das
1. Comparison of Performance Tuning Options between
Oracle & Postgres
2. Different types of Indexes
3. Partitions
4. Understanding the Explain Plan
5. Using Hints
6. Using Hints without Changing Code
7. Parallelism
8. PGBadger
9. Generarting Oracle Style AWR reports using pg_profile
Ritesh Das
Comparison of Performance Tuning Options

PostgreSQL for Oracle DBAS

Feature Oracle Postgresql
SQL Profile Yes Partially through (pg_hint_plan
extension)
SQL Baselines Yes No
SQL Tuning Yes No
Advisor
Hint Yes Yes (pg_hint_plan extension)

-Ritesh Das
AWR Report Yes Yes (PG_Profile Extension, PGBadger
tool)
Explain Plan Yes Yes (Explain, Explain Analyze) (Run time
capture using Autoexplain Extension)
Indexing Yes Yes
Statistics Yes (DBMS_STATS) Analyze command
Parallelism Yes Yes
Partition Range, Interval, Hash, List, Composite, Reference Ritesh Das
Range, Hash, List, Composite
 Types of Indexes

PostgreSQL for Oracle DBAS

Index
Description Use Case Syntax and Commands
Type
CREATE INDEX idx_orders_id ON orders (order_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE
order_id = 12345;
Use for equality and range queries.
This index optimizes search for order_id values.
Default index type.
B-Tree Example: Finding an order by order ID
Balanced tree structure
Index or retrieving orders within a date CREATE INDEX idx_orders_date ON orders (order_date);
for efficient search.

-Ritesh Das
range. EXPLAIN ANALYZE SELECT * FROM orders WHERE
order_date BETWEEN '2023-01-01' AND '2023-01-31';
This index optimizes range queries for order_date.

CREATE INDEX idx_orders_tracking_hash ON orders

USING hash (tracking_number);
Uses a hash table for Use for simple equality comparisons.
EXPLAIN ANALYZE SELECT * FROM orders WHERE
Hash Index EQUALITY (=) Example: Checking if an order with a
tracking_number = '1Z999AA10123456784';
comparisons only specific tracking number exists.
This index speeds up equality search for tracking_number.
Ritesh Das
 PostgreSQL for Oracle DBAS
Index Type Description Use Case Syntax and Commands

CREATE INDEX idx_orders_shipped ON orders (order_id)

WHERE shipped = true;
Index on a subset of rows Use when frequently querying a subset of EXPLAIN ANALYZE SELECT * FROM orders WHERE shipped =
Partial Index that meet a specific rows. Example: Indexing only orders that are true AND order_id = 12345;
condition. marked as shipped.
This index optimizes queries on orders marked as shipped with
order_id.
CREATE INDEX idx_orders_lower_email ON orders
(lower(customer_email));
Use when queries involve expressions.
Expression Index on the result of an EXPLAIN ANALYZE SELECT * FROM orders WHERE

-Ritesh Das
Example: Case-insensitive search by
Index expression or function. lower(customer_email) = 'customer@[Link]’;
lowercased customer email addresses.
This index optimizes searches where customer_email is compared
in lowercase.

BRIN Index Covered in next slide

GiST, SP-GiST, GIN - Out of Scope – These are used for complex data types such as Spatial, Geometric and full text searches. Refer to
Postgres Documentation for more information.

Ritesh Das
 BRIN Index

PostgreSQL for Oracle DBAS

Use cases:
• It is useful for columns with monotonically
increasing values, such as timestamps. Such
as time series data.
• For extremely large tables where B-tree
would be impractical.
• It is suitable for range scans and sequential

-Ritesh Das
access patterns.
• Read heavy workloads

When not to use:

• Small tables
• Non-Sequential Data
• Heavy DML activity

Table Size B-Tree Index Size Brim Index Size

4GB 458 MB Ritesh Das
0.84 MB
 Partition Type Description Example
Divide data based on ranges of values (e.g., date ranges).
Example:

PostgreSQL for Oracle DBAS

Splits data into ranges of
Range CREATE TABLE sales (id serial PRIMARY KEY, sale_date date NOT NULL, amount numeric NOT NULL)
values, typically dates or
Partitioning PARTITION BY RANGE (sale_date);
numbers.
CREATE TABLE sales_2023_01 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
CREATE TABLE sales_2023_02 PARTITION OF sales FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
Distribute data across partitions based on a hash function.
Example:
CREATE TABLE users (id serial PRIMARY KEY, username text NOT NULL, email text NOT NULL) PARTITION
Distributes data across
BY HASH (id);
Hash Partitioning partitions using a hash
CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
function.
CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_p2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_p3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3);
Partition data based on a list of predefined values (e.g., categories).

-Ritesh Das
Example:
CREATE TABLE orders (id serial PRIMARY KEY, order_status text NOT NULL, order_date date NOT NULL)
Divides data based on
List Partitioning PARTITION BY LIST (order_status);
predefined list of values.
CREATE TABLE orders_pending PARTITION OF orders FOR VALUES IN ('pending');
CREATE TABLE orders_completed PARTITION OF orders FOR VALUES IN ('completed');
CREATE TABLE orders_cancelled PARTITION OF orders FOR VALUES IN ('cancelled');
Use a combination of partitioning strategies (e.g., range and list).
Example:
CREATE TABLE metrics (id serial PRIMARY KEY, region text NOT NULL, event_date date NOT NULL, value
Combines two or more
Composite numeric NOT NULL) PARTITION BY RANGE (event_date) PARTITION BY LIST (region);
partitioning methods,
Partitioning CREATE TABLE metrics_2023_east PARTITION OF metrics FOR VALUES FROM ('2023-01-01') TO ('2024-01-
such as range and list.
01') FOR VALUES IN ('east');

Ritesh Das
CREATE TABLE metrics_2023_west PARTITION OF metrics FOR VALUES FROM ('2023-01-01') TO ('2024-01-
01') FOR VALUES IN ('west');
 Explain vs Explain Analyze

PostgreSQL for Oracle DBAS

erpdb=# explain
SELECT abalance FROM pgbench_accounts WHERE aid = 212843;
QUERY PLAN
----------------------------------------------------------------------------------------------
Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.43..8.45 rows=1 width=4)
Index Cond: (aid = 212843)
(2 rows)

-Ritesh Das
erpdb=# explain analyze
erpdb-# SELECT abalance FROM pgbench_accounts WHERE aid = 212843;
QUERY PLAN
-------------------------------------------------------------------------------------------------
---------------------------------------
Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.43..8.45 rows=1 width=4)
(actual time=5.001..5.006 rows=1 loops=1)
Index Cond: (aid = 212843)
Planning Time: 0.063 ms
Execution Time: 5.057 ms
(4 rows)

Ritesh Das
 Estimated Startup Cost Estimated # Rows

PostgreSQL for Oracle DBAS

EXPLAIN Seq Scan on employees (cost=0.00..10.20 rows=100 width=128)
SELECT * FROM employees WHERE department_id = 10; Filter: (department_id = 10)

Estimated Total Cost Estimated Avg Width

of rows

Term Meaning Explanation

Estimated start- Cost to start retrieving For Example when there is no order by clause the startup

-Ritesh Das
up cost rows. cost is lower, however it would be higher incase of order by.
Estimated total Total cost to retrieve all The total cost to retrieve all rows (including the start-up
cost rows, including start-up. cost) is 10.20.
Estimated
Number of rows expected The planner estimates that 100 rows will be retrieved from
number of
to be returned. the employees table where department_id = 10.
rows
Estimated
Average width in bytes of The planner estimates the average width of each row in the
average width
of rows
each returned row. result set to be 128 bytes.
Ritesh Das
 PostgreSQL for Oracle DBAS
-Ritesh Das
2
3
4
21926.33/26355.61 = 83.2%
1
5
5 1690.55/26355.61 = 6.4%
6
2
3
4
4

Ritesh Das
Using Hints

PostgreSQL for Oracle DBAS

● pg_hint_plan makes it possible to tweak execution plans using "hints", which are simple descriptions in the SQL
comment of special form.
● A hint can be specified by prefixing it with the sequence “/*+" and ending it with "*/“
● Example:
/*+ SeqScan(table_name) */ Forces sequential scan on the table.

-Ritesh Das
/*+ IndexScan (table_name index_name) */ Forces Index Scan
● Complete list of hints can be found in:
[Link]

Ritesh Das
 testdb=# EXPLAIN SELECT *
testdb-# FROM pgbench_branches b
testdb-# JOIN pgbench_accounts a ON [Link] = [Link]

PostgreSQL for Oracle DBAS

testdb-# ORDER BY [Link];
Without hint

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.57..66151.81 rows=1000000 width=461)
-> Index Scan using pgbench_accounts_pkey on pgbench_accounts a (cost=0.42..42377.43 rows=1000000 width=97)
-> Memoize (cost=0.15..0.16 rows=1 width=364)
Cache Key: [Link]
Cache Mode: logical
-> Index Scan using pgbench_branches_pkey on pgbench_branches b (cost=0.14..0.15 rows=1 width=364)
Index Cond: (bid = [Link])
(7 rows)

testdb=# /*+
testdb*# HashJoin(a b)

-Ritesh Das
testdb*# SeqScan(a)
testdb*# */
testdb-# EXPLAIN SELECT *
testdb-# FROM pgbench_branches b
testdb-# JOIN pgbench_accounts a ON [Link] = [Link]
With hint

testdb-# ORDER BY [Link];

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Sort (cost=31465.84..31715.84 rows=100000 width=197)
Sort Key: [Link]
-> Hash Join (cost=1.02..4016.02 rows=100000 width=197)
Hash Cond: ([Link] = [Link])
-> Seq Scan on pgbench_accounts a (cost=0.00..2640.00 rows=100000 width=97)
-> Hash (cost=1.01..1.01 rows=1 width=100)

Ritesh Das
-> Seq Scan on pgbench_branches b (cost=0.00..1.01 rows=1 width=100)
(7 rows)
The hints table

PostgreSQL for Oracle DBAS

● When we try to tune application code, where we cannot modify the source code, we can add hints by
placing hints in hint_plan.hints
=# INSERT INTO hint_plan.hints(norm_query_string, application_name, hints)
VALUES (‘NORMALIZED_QUERY', '', 'IndexScan (t1 Indx1)');

=# UPDATE hint_plan.hints
SET hints = ‘SeqScan(t1)'
WHERE id = 1;

-Ritesh Das
UPDATE 1

● Ensure pg_hint_plan.enable_hint and pg_hint_plan.enable_hint_table are ON

● Normalized Query:
○ all carriage returns removed
○ all spaces converted to single spaces
○ all literals and parameters replaced with a “?”
Example:
Regular Query Normalized Query
select * from emp select * from emp where empno=?;
where empno=1234;

Ritesh Das
Parallelism

PostgreSQL for Oracle DBAS

Parameter Description
limits the number of parallel worker processes that can be active at the same time
max_parallel_workers
in the whole database cluster. It must be less or equal to max_worker_processes.

max_parallel_workers_per_gather limits the number of parallel worker processes that can be used for a single query.

-Ritesh Das
min_parallel_table_scan_size Sets the minimum size of a table for a parallel scan to be considered.

min_parallel_index_scan_size Sets the minimum size of an index for a parallel scan to be considered.

Determines whether the leader also executes subplans during parallel query
parallel_leader_participation
execution.
Sets the maximum number of parallel workers that can be used for maintenance
max_parallel_maintenance_workers
operations like VACUUM and CREATE INDEX.
parallel_setup_cost Sets the planner's estimate of the cost of launching parallel workers.
Sets the planner's estimate of the cost of passing a tuple from a parallel worker to
parallel_tuple_cost
another process. Ritesh Das
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
PGBadger
Configuring for PGBadger

PostgreSQL for Oracle DBAS

● Pre-Req: pg_stat_statements extension must be enabled.
● Below parameters can be set depending on what level of information you want to capture (not all mentioned
below are mandatory):

alter system set log_checkpoints = 'on';

alter system set log_connections = 'on';
alter system set log_disconnections = 'on';
alter system set log_lock_waits = 'on';

-Ritesh Das
alter system set log_temp_files = 0;
alter system set log_autovacuum_min_duration = 0;
alter system set log_error_verbosity = 'default';
alter system set log_min_duration_statement = 0;
alter system set auto_explain.log_min_duration = 0 ;
alter system set auto_explain.log_analyze = true ;
alter system set auto_explain.log_verbose = true ;
alter system set auto_explain.log_timing = true ;
alter system set auto_explain.log_nested_statements = true;
alter system set pg_stat_statements.track= 'all’;

We should never set log_min_duration_statement, auto_explain.log_min_duration = 0, this severely

Ritesh Das
degrades performance and captures every statement executed in the database. If this is OLTP, set it to
a value of about 5 seconds(ie 5000ms), ie it capture queries > 5 seconds per execution.
PG_Profile

PostgreSQL for Oracle DBAS

● PGProfile extension is the closest replica of the old Oracle 11g style AWR report.
● It internally uses pg_stat_statements (extension). The stats collected in
pg_stat_statements are cumulative.
● Pg profiler creates multiple snapshots (like AWR snapshots) in a separate database
(or in same database).
● We can collect snapshots of any interval in cron or in pg_cron.

-Ritesh Das
erp pg_profile
Pulls various
performance metrics
profile
via db link

Cluster Ritesh Das

PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
Generating the Report

PostgreSQL for Oracle DBAS

● Installation: [Link]
● Once extension is installed:
● Configure DB for which reports need to be collected.
SELECT create_server('localhost','host=[Link] dbname=erpdb port=5432 user=postgres
password=postgres’);

-Ritesh Das
● Take Snapshots(Either manually through psql, or through cron, pgcron):
select take_sample('localhost');

● Generate AWR/PG_Profile report

psql -U postgres -d pg_profile -tA -c "select get_report('localhost',2,3);" --output
/tmp/awr_report_postgres.html

Ritesh Das
 PostgreSQL for Oracle DBAS
17
High Availability

-Ritesh Das
1. Comparison of HA options between Oracle & Postgres
2. Understanding Streaming Replication
3. Replication Slots
4. Replication Manager
5. PGBouncer
6. PGPool –II
7. Demo

Ritesh Das
Comparison of HA options - Oracle & Postgres

PostgreSQL for Oracle DBAS

Feature Oracle PostgreSQL
Active Active DB Cluster Real Application Cluster None
Physical Replication Dataguard Physical Replication
Logical Replication Golden Gate Logical Replication

-Ritesh Das
Standby Modes:

• WARM Standby Server: Similar to Oracle Standby Server.

• HOT Standby Server(Also known as Read Replica): Similar to Oracle’s Active Data Guard

Physical Replication:

• File based Replication: Similar to Oracle’s Archived Redo Log Shipping

• Streaming Replication: Similar to Oracle’s Real time apply

Ritesh Das
 PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
Understanding Streaming replication
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
Standby
Primary
 Replication Slots

PostgreSQL for Oracle DBAS

wal_keep_segment - Specifies the minimum number of past log file segments kept in the the
pg_wal directory.
wal_keep_size - Specifies the maximum size of WAL files that the system retains for standby
servers.
Introducing – Replication Slots:

-Ritesh Das
• Replications slots ensure that the master
maintains all the required WAL files needed
to recover the standby database.
• Replication slots will store WAL segments
indefinitely.

Ritesh
max_slot_wal_keep_size (to limit size of WAL files retained by replication slots)Das
 Replication

PostgreSQL for Oracle DBAS

Slot

Physical Logical

-Ritesh Das
restart_lsn is crucial
because it indicates the
oldest WAL record needed
by the standby server.

Ritesh Das
 PostgreSQL for Oracle DBAS
Update
Metada in Primary Standby
${PGDATA}/
pg_repslot
Update
Wal record
restart_lsn

Replication
Slot

-Ritesh Das
Ensure all wal segments since
restart_lsn is retained.

$PGDATA/pg_wal

WAL Segments
Ritesh Das
REPMGR

PostgreSQL for Oracle DBAS

● Open source tool to ease management of replication, switchover and failover of postgres
databases.
● Monitors replication clusters and manages failover.
● Provides CLI tools for common replication tasks.
● repmgr daemon (repmgrd)
○ monitors the health and status of the nodes in the cluster.

-Ritesh Das
○ detects node failures and promotes standby to primary node.
● [Link] is its configuration file.
● Key Features:
○ Node Management: Registers/Unregisters nodes in replication cluster. Also shows the
status of the cluster and node membership information.
○ Replication Setup: We can trigger a clone to setup a new standby server from a primary
server.
○ Failover/Switchover – Using CLI to easily execute switchover and manual/automatic
failover.
○ Monitoring using repmgrd.
Ritesh Das
 PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
CONNECTION POOLING
 PostgreSQL for Oracle DBAS
-Ritesh Das
Advantages of using PGBouncer:

1. Connection Pooling
• Reduce Connection Overhead
• Reuse Connections
2. Improved Performance and Scalability
• Efficient Resource Utilization:
• Support for High Concurrency
3. Idle Connection Management
4. Increased Reliability
Ritesh Das
PostgreSQL for Oracle DBAS -Ritesh Das
Ritesh Das
 PostgreSQL for Oracle DBAS
-Ritesh Das
High availability
Load Balancing
Automatic Failover
HA in PGPOOL-II itself Ritesh Das
DEMO

PostgreSQL for Oracle DBAS

Hostname:
trainining_db1

repmgr

-Ritesh Das
Hostname:
pgpool
Hostname:
Ritesh Das
training_db2
 PostgreSQL for Oracle DBAS
18
Database

-Ritesh Das
Maintenance
1. Regular Administration & Maintenance
Activities
2. Managing Bloat
3. Reindex
4. Debugging

Ritesh Das
Regular Administration & Maintenance Activities

PostgreSQL for Oracle DBAS

● Backup and Validation
● Monitoring and Alerting:
○ OS Level
○ DB Level
● Vacuum

-Ritesh Das
● Bloat
● Identifying Unused indexes
● Re-Index
● Stale Statistics

Ritesh Das
Bloat

PostgreSQL for Oracle DBAS

● Detecting Bloat :
○ CREATE EXTENSION pgstattuple;

erpdb=# SELECT * FROM pgstattuple('pgbench_accounts');

-[ RECORD 1 ]------+----------
table_len | 136503296
tuple_count | 1000000

-Ritesh Das
tuple_len | 121000000
tuple_percent | 88.64
dead_tuple_count | 15782
dead_tuple_len | 1909622
dead_tuple_percent | 1.4
free_space | 1786604
free_percent | 1.31

Ritesh Das
Fixing Bloat

PostgreSQL for Oracle DBAS

1. Regular Vacuuming
2. Vacuum Full
3. Using pg_repack
○ Similar to full vacuum – but it does not require an exclusive lock on a table.
○ How does it work?
a. Similar to full vacuum, it re-creates a new table to release space back to OS.
b. It then sets up triggers to replicate new data, and fills it with existing data.

-Ritesh Das
Feature VACUUM FULL pg_repack
Locking Requires exclusive table lock. Non-blocking; allows concurrent access.
Downtime Can cause significant downtime. Minimal downtime; suitable for production.
Binary Built-in PostgreSQL command. Extension (needs to be installed separately).
Use Case Smaller tables or planned maintenance windows. Large tables or production environnents

4. Clustering
Ritesh Das
 CLUSTERING A TABLE

PostgreSQL for Oracle DBAS

Table without Clustering:
Rows are stored in the order they were inserted, which can be
random.
Table with Clustering:
Data is reorganized based on a specified index (e.g., sale_date),
grouping related rows together. Each group of related data is stored
contiguously.

-Ritesh Das
Clustered
Empno Ename Dept Empno Ename Dept
EMP Table is clustered by department
5 Eve HR 4 Carol Sales index.
Non-Clustered

2 Dave Engineering 2 Dave Engineering Related rows are grouped together.

4 Carol Sales 1 Bon Engineering Clustering can help reduce this bloat by
physically reorganizing the table data based
1 Bon Engineering 5 Eve HR on an index, which results in contiguous
3 Alice HR 3 Alice HR Ritesh Das
storage of related rows and removal of
fragmented spaces.
Re-Indexing

PostgreSQL for Oracle DBAS

REINDEX rebuilds an index by replacing the old copy of the index.
● Reasons to Reindex:
○ An index has become corrupted.
○ An index has become “bloated”,
○ If an index build fails with the CONCURRENTLY option, this index is left as “invalid”.
○ After bulk DML
● The space can be reclaimed back to OS by Re-Indexing, (Vacuum full does clean up

-Ritesh Das
of tables + index).
● Like Full Vacuum, Re-Index holds an exclusive lock on the index being rebuild,
preventing writes on the indexed table.
● To be scheduled during off business hours or Use CONCURRENTLY.
● Improves performance of query by optimizing the index.

REINDEX can be done at system (all databases), database, table, index level.
Ritesh Das
Debugging

PostgreSQL for Oracle DBAS

● Increase logging level at appropriate levels to get additional information.
● Examine logfiles.
● After finishing troubleshooting, we disable logging level.
● Which Parameters needs to be changed?

-Ritesh Das
[Link]

Ritesh Das
 Parameter Description Possible Values
debug5, debug4, debug3, debug2, debug1,

PostgreSQL for Oracle DBAS

log_min_messages Sets the message levels that are logged.
info, notice, warning, error, log, fatal, panic
Logs statements that generate an error of debug5, debug4, debug3, debug2, debug1,
log_min_error_statement
the specified severity or higher. info, notice, warning, error, log, fatal, panic
Logs the duration of each completed
-1 (disabled), 0 (log all statements), N (log
log_min_duration_statement statement that takes at least this time in
statements longer than N ms)
milliseconds.
Logs the execution plan for statements which -1 (disabled), 0 (log all statements), N (log
auto_explain.log_min_duration
runs over set time (ms) statements longer than N ms)
log_statement Logs the SQL statements that are issued. none, ddl, mod, all

-Ritesh Das
Logs the duration of each completed SQL
log_duration on, off
statement.
Logs performance statistics of each executed
log_statement_stats on, off
statement.
Controls the amount of detail written in the
log_error_verbosity terse, default, verbose
server log for each message that is logged.
log_lock_waits Logs long lock waits. on, off
Logs the use of temporary files larger than -1 (disabled), 0 (log all temp files), N (log
log_temp_files
the specified size (in kilobytes). temp files larger than N KB)
log_line_prefix Controls the format of log lines. Ritesh Das
Various placeholders for details like
timestamp, user, database, etc.
 PostgreSQL for Oracle DBAS
log_line_prefix
By Default it is “%m [%p]” (Time Stamp and Process ID)

2024-05-25 10:09:42.486 UTC [24324] LOG: checkpoint starting: time

We can set it to a more useful value

alter system set log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h’;

-Ritesh Das
%t – timestamp, %p – process id, %l – log line number, %d – dbname, %u – username,
%d – application name, %h – hostname/ip

2024-05-25 11:00:13 UTC [26702]: [2-1]

db=postgres,user=postgres,app=psql,client=[local]STATEMENT: alter system set
auto_explain.log_min_duration = 0 ;

Refer to this link for the complete list of options:

[Link] Ritesh Das
PostgreSQL for Oracle DBAS -Ritesh Das
Das
RITESH DAS
Ritesh