PostgreSQL Source Code Installation

 Build From Source Code:

PostgreSQL Source Code Installation means compiling and installing PostgreSQL directly from its source files instead of using prebuilt packages.

Prerequisite:

Before we install PostgreSQL software/cluster, we need to ensure that we have all the necessary tools and libraries on our system.

We need install software using root user only 

○ C Compiler: PostgreSQL is written in C, so a C compiler is a must. Popular choices include GCC (GNU Compiler Collection) and Clang.

○ Build Tools: Ensure you have essential build tools like make and autoconf installed. These tools help in compiling the source code efficiently.

○ Libraries: PostgreSQL requires certain libraries like readline and zlib. We need to install these development libraries on our system.

○ readline-devel: Provides the necessary libraries for command-line editing and history.

○ zlib-devel: Required for compression support.

○ gcc: The GNU Compiler Collection needed to compile the PostgreSQL source code.

○ make: A build tool used to compile and install the software.

Step1) Install Required Packages

[root@post101 ~]# yum install readline-devel zlib-devel openssl-devel libxml2-devel libxslt-devel systemd-devel wget tar perl perl-core perl-FindBin -y

OR

[root@post101 ~]# sudo dnf install readline-devel zlib-devel openssl-devel libxml2-devel libxslt-devel systemd-devel wget tar perl perl-core perl-FindBin -y

Step2) Create postgres user and add user to sudoers file

[root@post101 ~]# adduser postgres

[root@post101 ~]# passwd postgres

Retype new password:

passwd: all authentication tokens updated successfully.

[root@post101 ~]# vi /etc/sudoers

## Allow root to run any commands anywhere

root    ALL=(ALL)       ALL

postgres ALL=(ALL)      ALL

Step3) 

Download Source Code From https://www.postgresql.org/download/ 

Download software and copy software using WinSCP

OR

Use below command if we have internet connection 

[root@post101 ~]# 

[root@post101 ~]# wget https://ftp.postgresql.org/pub/source/v17.1/postgresql-17.1.tar.gz

Step4) untar zip file and go to postgresql-17.1

[root@post101 ~]# tar -xvzf postgresql-17.1.tar.gz

[root@post101 ~]# cd postgresql-17.1

[root@post101 postgresql-17.1]# ls -lrt

total 820

drwxrwxr-x. 16 root root   4096 Nov 12  2024 src

-rw-rw-r--.  1 root root    983 Nov 12  2024 README.md

-rw-rw-r--.  1 root root   6484 Nov 12  2024 meson_options.txt

-rw-rw-r--.  1 root root 115220 Nov 12  2024 meson.build

-rw-rw-r--.  1 root root   1822 Nov 12  2024 Makefile

-rw-rw-r--.  1 root root    277 Nov 12  2024 HISTORY

-rw-rw-r--.  1 root root   4176 Nov 12  2024 GNUmakefile.in

drwxrwxr-x.  3 root root     87 Nov 12  2024 doc

-rw-rw-r--.  1 root root   1192 Nov 12  2024 COPYRIGHT

drwxrwxr-x. 59 root root   4096 Nov 12  2024 contrib

-rw-rw-r--.  1 root root  88218 Nov 12  2024 configure.ac

-rwxrwxr-x.  1 root root 578504 Nov 12  2024 configure

drwxrwxr-x.  2 root root   4096 Nov 12  2024 config

-rw-rw-r--.  1 root root    365 Nov 12  2024 aclocal.m4

[root@post101 postgresql-17.1]#

Step5) Create directory for software binaries installation

[root@post101 ~]# mkdir -p /u01/pgsql-17/

[root@post101 ~]# cd /u01/pgsql-17/

[root@post101 pgsql-17]# ls -lrt

total 0

[root@post101 pgsql-17]#

 

Step6) ./configure

Configure the Build

This prepares the build environment.

prefix ==> Installation directory

configure ==> Checks OS libraries & prepares compilation

[root@post101 postgresql-17.1]# ./configure --prefix=/u01/pgsql-17

i got the below errors , so can skip by using below command '

checking for icu-uc icu-i18n... no

configure: error: ICU library not found

If you have ICU already installed, see config.log for details on the

failure.  It is possible the compiler isn't looking in the proper directory.

Use --without-icu to disable ICU support.

[root@post101 postgresql-17.1]# ./configure --prefix=/u01/pgsql-17 --without-icu --without-readline

This time this got succeed without any erros 

config.status: creating src/include/pg_config_ext.h

config.status: creating src/interfaces/ecpg/include/ecpg_config.h

config.status: linking src/backend/port/tas/dummy.s to src/backend/port/tas.s

config.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.c

config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c

config.status: linking src/include/port/linux.h to src/include/pg_config_os.h

config.status: linking src/makefiles/Makefile.linux to src/Makefile.port

[root@post101 postgresql-17.1]#

step7) make (Compile the Source Code)

This step builds PostgreSQL binaries.

[root@post101 postgresql-17.1]# make

Steep8) make install (Install PostgreSQL)

[root@post101 postgresql-17.1]# cd /u01/pgsql-17/

[root@post101 pgsql-17]# ls -lrt

total 16

drwxr-xr-x. 6 root root 4096 Mar 11 20:42 include

drwxr-xr-x. 2 root root 4096 Mar 11 20:43 bin

drwxr-xr-x. 6 root root 4096 Mar 11 20:43 share

drwxr-xr-x. 4 root root 4096 Mar 11 20:43 lib

[root@post101 pgsql-17]#

After installation, PostgreSQL will be located at:

 /u01/pgsql-17/

we can see all executable files on bin location

[root@post101 postgresql-17.1]# cd /u01/pgsql-17/

[root@post101 pgsql-17]# ls -lrt

total 16

drwxr-xr-x. 6 root root 4096 Mar 11 20:42 include

drwxr-xr-x. 2 root root 4096 Mar 11 20:43 bin

drwxr-xr-x. 6 root root 4096 Mar 11 20:43 share

drwxr-xr-x. 4 root root 4096 Mar 11 20:43 lib

[root@post101 pgsql-17]# cd bin/

[root@post101 bin]# ls -lrt

total 15360

-rwxr-xr-x. 1 root root 10154008 Mar 11 20:42 postgres

-rwxr-xr-x. 1 root root  1083408 Mar 11 20:42 ecpg

-rwxr-xr-x. 1 root root   170704 Mar 11 20:42 initdb

-rwxr-xr-x. 1 root root   115224 Mar 11 20:42 pg_amcheck

-rwxr-xr-x. 1 root root    49664 Mar 11 20:42 pg_archivecleanup

-rwxr-xr-x. 1 root root   173376 Mar 11 20:42 pg_basebackup

-rwxr-xr-x. 1 root root   114304 Mar 11 20:42 pg_createsubscriber

-rwxr-xr-x. 1 root root   116408 Mar 11 20:42 pg_receivewal

-rwxr-xr-x. 1 root root   116160 Mar 11 20:42 pg_recvlogical

-rwxr-xr-x. 1 root root    85440 Mar 11 20:42 pg_checksums

-rwxr-xr-x. 1 root root   187312 Mar 11 20:42 pg_combinebackup

-rwxr-xr-x. 1 root root    47808 Mar 11 20:42 pg_config

-rwxr-xr-x. 1 root root    66592 Mar 11 20:42 pg_controldata

-rwxr-xr-x. 1 root root    82176 Mar 11 20:42 pg_ctl

-rwxr-xr-x. 1 root root   440376 Mar 11 20:42 pg_dump

-rwxr-xr-x. 1 root root   220784 Mar 11 20:42 pg_restore

-rwxr-xr-x. 1 root root   136528 Mar 11 20:42 pg_dumpall

-rwxr-xr-x. 1 root root    77088 Mar 11 20:42 pg_resetwal

-rwxr-xr-x. 1 root root   169448 Mar 11 20:42 pg_rewind

-rwxr-xr-x. 1 root root    55160 Mar 11 20:42 pg_test_fsync

-rwxr-xr-x. 1 root root    44120 Mar 11 20:42 pg_test_timing

-rwxr-xr-x. 1 root root   189328 Mar 11 20:42 pg_upgrade

-rwxr-xr-x. 1 root root   139072 Mar 11 20:42 pg_verifybackup

-rwxr-xr-x. 1 root root   123536 Mar 11 20:42 pg_waldump

-rwxr-xr-x. 1 root root    76488 Mar 11 20:42 pg_walsummary

-rwxr-xr-x. 1 root root   211344 Mar 11 20:43 pgbench

-rwxr-xr-x. 1 root root   528168 Mar 11 20:43 psql

-rwxr-xr-x. 1 root root    83648 Mar 11 20:43 createdb

-rwxr-xr-x. 1 root root    79032 Mar 11 20:43 dropdb

-rwxr-xr-x. 1 root root    88576 Mar 11 20:43 createuser

-rwxr-xr-x. 1 root root    78968 Mar 11 20:43 dropuser

-rwxr-xr-x. 1 root root    83888 Mar 11 20:43 clusterdb

-rwxr-xr-x. 1 root root    97744 Mar 11 20:43 vacuumdb

-rwxr-xr-x. 1 root root    93024 Mar 11 20:43 reindexdb

-rwxr-xr-x. 1 root root    78472 Mar 11 20:43 pg_isready

[root@post101 bin]#

Step8) Create Data Directory and grant Permissions to postgres user

[root@post101 bin]# mkdir -p /u01/pgdata

[root@post101 bin]# cd /u01/pgdata

[root@post101 pgdata]# ls -lrt

total 0

[root@post101 pgdata]#

[root@post101 pgdata]# chown -R postgres:postgres /u01/pgdata

Step9) switch to postgres user 

[root@post101 pgdata]# su - postgres

[postgres@post101 ~]$ cd /u01/pg

pgdata/   pgsql-17/

[postgres@post101 ~]$ cd /u01/pgdata/

[postgres@post101 pgdata]$ pwd

/u01/pgdata

[postgres@post101 pgdata]$ ls -lrt

total 0

[postgres@post101 pgdata]$

Step10)  Initialize Database Cluster

[postgres@post101 bin]$ /u01/pgsql-17/bin/initdb -D /u01/pgdata

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /u01/pgdata ... ok

creating subdirectories ... ok

selecting dynamic shared memory implementation ... posix

selecting default "max_connections" ... 100

selecting default "shared_buffers" ... 128MB

selecting default time zone ... Asia/Kolkata

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections

initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /u01/pgsql-17/bin/pg_ctl -D /u01/pgdata -l logfile start

[postgres@post101 bin]$

Step11) Start PostgreSQL cluster 

[postgres@post101 bin]$ /u01/pgsql-17/bin/pg_ctl  -D /u01/pgdata start

waiting for server to start....2026-03-11 20:56:42.024 IST [9732] LOG:  starting PostgreSQL 17.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44.0.3), 64-bit

2026-03-11 20:56:42.035 IST [9732] LOG:  listening on IPv6 address "::1%1", port 5432

2026-03-11 20:56:42.035 IST [9732] LOG:  listening on IPv4 address "127.0.0.1", port 5432

2026-03-11 20:56:42.417 IST [9732] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"

2026-03-11 20:56:42.432 IST [9735] LOG:  database system was shut down at 2026-03-11 20:54:02 IST

2026-03-11 20:56:42.446 IST [9732] LOG:  database system is ready to accept connections

 done

server started

[postgres@post101 bin]$

[postgres@post101 bin]$ ps -ef|grep postgres

root      7357  3458  0 20:51 pts/0    00:00:00 su - postgres

postgres  7358  7357  0 20:51 pts/0    00:00:00 -bash

postgres  9732     1  0 20:56 ?        00:00:00 /u01/pgsql-17/bin/postgres -D /u01/pgdata

postgres  9733  9732  0 20:56 ?        00:00:00 postgres: checkpointer

postgres  9734  9732  0 20:56 ?        00:00:00 postgres: background writer

postgres  9736  9732  0 20:56 ?        00:00:00 postgres: walwriter

postgres  9737  9732  0 20:56 ?        00:00:00 postgres: autovacuum launcher

postgres  9738  9732  0 20:56 ?        00:00:00 postgres: logical replication launcher

postgres 10878  7358 99 20:59 pts/0    00:00:00 ps -ef

postgres 10879  7358  0 20:59 pts/0    00:00:00 grep --color=auto postgres

[postgres@post101 bin]$

Step12) Set Environment Variables

[postgres@post101 ~]$ vi .bash_profile

export PATH=$PATH:/u01/pgsql-17/bin

export PGDATA=/u01/pgdata

[postgres@post101 ~]$ . .bash_profile

[postgres@post101 ~]$ source .bash_profile

[postgres@post101 ~]$ psql

psql (17.1)

Type "help" for help.

postgres=# \l

                                                     List of databases

   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU

Rules |   Access privileges

-----------+----------+----------+-----------------+-------------+-------------+--------+-----

------+-----------------------

 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |

      |

 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |

      | =c/postgres          +

           |          |          |                 |             |             |        |

      | postgres=CTc/postgres

 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |        |

      | =c/postgres          +

           |          |          |                 |             |             |        |

      | postgres=CTc/postgres

(3 rows)

Step13) How to start postgres services auto start whenever server rebooted/shutdown 

Switch to root user

[postgres@post101 ~]$ su -

Password:

Last login: Wed Mar 11 19:59:13 IST 2026 from 2406:7400:9a:e591:fc9d:9082:d6a7:20c1 on pts/0

[root@post101 ~]# 

[root@post101 ~]# cd /etc/rc.d

[root@post101 rc.d]# ls -lrt

total 8

drwxr-xr-x. 2 root root   61 Jun 12  2024 rc6.d

drwxr-xr-x. 2 root root   61 Jun 12  2024 rc5.d

drwxr-xr-x. 2 root root   61 Jun 12  2024 rc4.d

drwxr-xr-x. 2 root root   61 Jun 12  2024 rc3.d

drwxr-xr-x. 2 root root   61 Jun 12  2024 rc2.d

drwxr-xr-x. 2 root root   61 Jun 12  2024 rc1.d

drwxr-xr-x. 2 root root   61 Jun 12  2024 rc0.d

-rw-r--r--. 1 root root  473 Oct 10  2024 rc.local

drwxr-xr-x. 2 root root 4096 Feb  6 17:55 init.d

[root@post101 rc.d]# vi rc.local

[root@post101 rc.d]# cat rc.local

#!/bin/bash

# THIS FILE IS ADDED FOR COMPATIBILITY PURPOSES

#

# It is highly advisable to create own systemd services or udev rules

# to run scripts during boot instead of using this file.

#

# In contrast to previous versions due to parallel execution during boot

# this script will NOT be run after all other services.

#

# Please note that you must run 'chmod +x /etc/rc.d/rc.local' to ensure

# that this script will be executed during boot.

touch /var/lock/subsys/local

su - postgres -c 'pg_ctl -D $PGDATA start'

[root@post101 rc.d]#

[root@post101 rc.d]# chmod +x /etc/rc.d/rc.local

RMAN Backup scripts ( Full, Level 0 and Level 1 Incremental )

Oracle Database, Recovery Manager (RMAN) backups are mainly of two types:

Full Backup and Incremental Backup (Level 0, Level 1).

Level 1 can be Differential or Cumulative.

RMAN Full Backup

A Full Backup copies all blocks of the database datafiles, regardless of whether they changed or not.

Backs up entire database

Includes used + unused blocks

Does not act as a base for incremental backups

Larger backup size

Syntax 

    Backup database; ## FRA Location

    Backup database plus archivelog;

    Backup database format ='/backup/rman/Bak_%d_%T_%U.bkp'

    Backup database format ='/backup/rman/Bak_%d_%T_%U.bkp' plus archivelog format ='/backup/rman/Arch_%d_%T_%U.bkp';

RMAN Incremental Backup

Incremental backups copy only changed blocks since a previous backup.

Two levels of backups

Level 0

Level 1

Level 0 Backup

A Level 0 backup is the base backup for incremental strategy.

Copies all used blocks

Acts as base for Level 1 backups

Similar to full backup but supports incremental chain

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE format ='/backup/rman/Bak_L0_%d_%T_%U.bkp';

Level 0 = foundation backup

Level 1 Backup

Level 1 backs up only blocks changed since the Level 0 backup or previous Level 1 backup.

Two types exist:

Differential

Cumulative

Level 1 Differential Backup

Backs up blocks changed since the last incremental backup (Level 0 or Level 1).

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE format ='/backup/rman/Bak_L0_%d_%T_%U.bkp';

Level 0 (Sunday)

     |---- Level 1 Differential (Mon)

     |---- Level 1 Differential (Tue)

     |---- Level 1 Differential (Wed)

     |---- Level 1 Differential (Thu)

     |---- Level 1 Differential (Fri)

     |---- Level 1 Differential (Sat)

Level 1 Cumulative Backup

Backs up all blocks changed since the last Level 0 backup.

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE  DATABASE format ='/backup/rman/Bak_L0_%d_%T_%U.bkp';

    Level 0 (Sunday)

     |---- Level 1 Cum (Mon -Changes from since Sun)

     |---- Level 1 Cum (Tue -Changes from since Sun)

     |---- Level 1 Cum (Wed -Changes from since Sun)

     |---- Level 1 Cum (Thu -Changes from since Sun)

     |---- Level 1 Cum (Fri -Changes from since Sun)

     |---- Level 1 Cum (Sat -Changes from since Sun)

Backup Archive Log

RMAN> Backup archivelog all;

RMAN> Backup archivelog all format='/backup/rman/Arch_%d_%T_%U.bkp';

Crontab Schedule :

crontab -e

# Sunday Level 0 Backup at 2 AM

0 2 * * 0 /u01/scripts/rman_level0.sh >> /u01/scripts/logs/l0_backup.log 2>&1

# Monday–Saturday Level 1 Cumulative Backup at 2 AM

0 2 * * 1-6 /u01/scripts/rman_level1_cum.sh >> /u01/scripts/logs/l1_cum_backup.log 2>&1

#Sunday – Level 0 Backup Script

#!/bin/bash

export ORACLE_SID=PROD

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'WEEKLY_SUN_L0';

BACKUP ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL c1;

}

EXIT;

EOF

# Weekdays – Level 1 Differential Script

#!/bin/bash

export ORACLE_SID=PROD

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

BACKUP INCREMENTAL LEVEL 1 DATABASE TAG 'DAILY_L1';

BACKUP ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL c1;

}

EXIT;

EOF

Sunday Level 0 RMAN Script

#!/bin/bash

export ORACLE_SID=PRODDB

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

BACKUP INCREMENTAL LEVEL 0 DATABASE

TAG 'WEEKLY_L0'

FORMAT '/backup/rman/L0_%d_%T_%U.bkp';

BACKUP ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL c1;

}

EXIT;

EOF

#Weekdays Level 1 Cumulative Script

# %d = Database, %T = Date Time and %U= 8 number 

#!/bin/bash

export ORACLE_SID=PROD

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

RUN {

ALLOCATE CHANNEL c1 DEVICE TYPE DISK;

BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE

TAG 'DAILY_L1_CUM'

FORMAT '/backup/rman/L1C_%d_%T_%U.bkp';

BACKUP ARCHIVELOG ALL DELETE INPUT;

RELEASE CHANNEL c1;

}

EXIT;

EOF

### Script 

export ORACLE_SID=PROD

export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

rman target / <<EOF

run

{

Allocate channel ch1 device type disk;

allocate channel ch2 device type disk;

crosscheck backup;

delete noprompt expired backup;

crosscheck archivelog all;

delete noprompt archivelog all completed before 'SYSDATE -7'; 

backup as backupset format='/u01/app/data/PROD/Backup_%T_%d_%U'

plus archivelog format ='/u01/app/data/PROD/Archive_%T_%d_%U';

backup current controlfile format='/u01/app/data/PROD/controlfile_%F';

release channel ch1;

release channel ch2;

}

quit;

EOF

RMAN Settings/Configuration Parameters

[oracle@ORA-TEST301 ~]$ rman target /

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name TEST are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

       CONFIGURE RETENTION POLICY TO Redundancy 7; ## Number of backup files

       CONFIGURE RETENTION POLICY TO Recovery window 7 days; ## Number of days backup files

CONFIGURE BACKUP OPTIMIZATION OFF; # default

        CONFIGURE BACKUP OPTIMIZATION ON;

            Read only tablespace and data files not backup.

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

    Defines where backups are stored Disk or Tape/SBT

CONFIGURE CONTROLFILE AUTOBACKUP ON;

    Automatically backs up the control file and SPFILE after backup operations.

    Full backup,Level0 , Level 1. Archive log backup and data file backup

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/2c/data/oracle12c/rman_bkup/SDADS/%F';

    %F 12 character alph numaric 

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

    How many backup streams run simultaneously.

    RMAN uses 4 channels

    Backup runs faster because multiple files are backed up in parallel.

    CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

    Backup Types

        Backup of copy ==> An Image Copy is an exact byte-to-byte copy of a datafile.

        Backup as copy database;

        Backup sets ==> RMAN stores backups in compressed binary files.

        Backup as bakupset database;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/2c/data/oracle12c/rman_bkup/TEST/bkup_%U';

    Backup file naming convention.

    %d database name

    %U unique backup identifier

    %T date

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

    Only for backup sets

    If we keep MAXSETSIZE to 100GB if your database size is 300GB it will take 3 backup files

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

    Backup will be encryption 

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

   Default AES125 Backup algorithm, we need to enable TDE 

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

    Backup size will be reduced 

    Default is Basic but we have Low , Medium and High

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

    How many days RMAN Backup information we have to keep on control file

    List backup;

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

    NONE means anyone can delete Archive log files on OS level

    If we have Standby server, if we keep NONE Primary and DR will go out of sync 

    CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/2c/data/oracle12c/rman_bkup/TEST/snapcf_TEST.f';

    During backup:

            Datafiles are being backed up

            Control file records are constantly updating

            Archivelogs are generated

 

SQL Server Always ON Readbale Secondary Options

 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 validate the Backups in SQL server

 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 Architecture

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

 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;