Always ON Readbale Secondary Options
| NO |
| Secondary replica cannot accept any read connections. Only used for failover. Yes Secondary replica accepts all read connections (even if the connection string does not specify read-intent). READ_INTENT_ONLY Secondary replica accepts connections only if the client specifies ApplicationIntent=ReadOnly in the connection string.Server=AOAGListener; Database=DB; ApplicationIntent=ReadOnly; |
How to checks whether the SQL backup file is structurally valid and readable.
1) RESTORE VERIFYONLY
2) RESTORE HEADERONLY
3) RESTORE FILELISTONLY
RESTORE VERIFYONLY
FROM DISK = 'D:\SQLData\Backup\DBA_FULL.bak';
RESTORE HEADERONLY
FROM DISK = 'D:\SQLData\Backup\DBA_FULL.bak';
RESTORE FILElistonly
FROM DISK = 'D:\SQLData\Backup\DBA_FULL.bak';
PostgreSQL Cluster / Instance is combination of Memory and Background process, cluster is nothing but collection / Group of databases managed by single instance of the PostgreSQL cluster
Memory Components
Shared Memory
Shared Buffer ==> This is the primary cache of PostgreSQL, where data blocks writes/reads from the disk.
WAL Buffer ==> Buffer for Write ahead logging (WAL) data before it is written to disk.
CLOG Buffer ==> Store commit log information to manage transaction states
Temp Buffers ==> Used for storing temporary tables. Default 8MB
Work Memory ==> Used for sorting operations , Bitmap operation, hash join and merge join operations. Default 4MB
Maintenance Work Memory ==> Used for vacuum and index operations. Default 64MB
Catalog Cache and Execution Memory ==> Memory used for caching system catalogs and executing SQL queries
Backend Process
Postmaster Process
When we start PostgreSQL cluster ,the postmaster process is the first to initialize
Responsibilities of Postmaster
Accepting incoming connection
Forks new backend process
Starts background process
Handles server restart and crash recovery
BG Writer ==> Periodically Writes Dirty buffers/pages from shared buffer to the disk
WAL Writer ==> Writes contents of the WAL buffers to WAL files
Checkpoint ==> Writes dirty buffers to disk when a checkpoint occurs , this reduces the recovery time after a crash.
Stats Collector ==> Collect DBMS usage statistics information such as session activity information(pg_stat_activity) and table usage statistics information (pg_stat_all_tables)
Gathers and maintains statistical data about the database, which the query planner uses to optimize query execution plans
Archiver ==> When in archive log mode , copies the WAL files to the archive log location
Sys Logger ==> Logs error messages and a variety of information to log files
Auto vacuum Launcher ==> Fork the auto vacuum worker when vacuum is required(Automatically reclaims storage occupied by dead tuples( Rows that have updated or deleted), to prevent table bloat and maintains query performance.
Logical Replication Launcher ==> Manages the logical replication workers that handle replication of changes to other databases.
WAL Sender ==> Sending WAL records to standby servers, runs on the primary server
WAL Receiver ==> Receive WAL records and apply on standby server , runs on the standby server
RMAN Expired and Obsolete Backups
Expired Backups:
Backup set or backup pieces are deleted on OS Level, Database control file have backup details on disk but OS level files are not available.
Action:
RMAN> crosscheck backup;
RMAN> delete expired backup;
Obsolete Backups:
The general meaning of OBSOLETE is no longer used or required for recovery.
RMAN considers backups as OBSOLETE when they are no longer required for database recovery.This is done by one of the RMAN CONFIGURATION parameters.
Action:
RMAN> report obsolete;
RMAN> delete obsolete;
RMAN provides multiple backup types, but the most commonly used in production environments are Full backups and Incremental backups. Understanding their differences helps DBAs design efficient backup strategies that optimize storage, speed, and recovery time.
1. Full Backup
A Full Backup (or Level 0 backup) is a complete backup of the entire database.
Key Characteristics
Backs up all data blocks, regardless of whether they changed.
Forms the baseline for incremental backup strategies.
Larger in size than incremental backups.
Takes more time and requires more storage.
Recovery is straightforward—restore full backup + apply archived logs.
2. Incremental Backups
Incremental backups only capture blocks that have changed since a previous L0 backup.
RMAN supports two types:
Differential Incremental Backup (Level 1 Differential)
Cumulative Incremental Backup (Level 1 Cumulative)
Both rely on a Level L0 full backup as a baseline.
2.1. Differential Incremental Backup
A Differential Level 1 backup captures all blocks changed since the last backup (Level 0 or Level 1).
Key Characteristics
Smaller and faster than full backups.
Captures daily changes.
During recovery, RMAN may use multiple Level 1 backups.
When it comes to recovery we need L0 Backup and all L1 backups including Archive logs need to be apply.
2.2. Cumulative Incremental Backup
A Cumulative Level 1 backup captures all blocks changed since the last Level 0.
Key Characteristics
Slightly larger than differential backups.
Faster recovery (fewer incremental files to apply).
Preferred for large, mission-critical systems.
When it comes to recovery we need L0 and recent L1 Cumulative backup including Archive logs need to be apply.
