Tag Archives: PostgreSQL Configuration

PostgreSQL

Uncategorized, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

PostgreSQL DBA Step by Step Learning

#PostgreSQL DBA Topics
1How to Install PostgreSQL ON Linux?
2How to Install PostgreSQL on Linux 7 using source code?
3How to START/STOP PostgreSQL ON Linux?
4How to Create Database in PostgreSQL?
5PostgreSQL User Management
6PostgreSQL pg_hba.conf Guide
7PostgreSQL Change Data Directory
8Understanding WAL Files in PostgreSQL – For Oracle DBAs
9Change PostgreSQL WAL Directory Path (pg_wal)
10Enable Archive Mode in PostgreSQL 17
11How to Disable ARCHIVELOG Mode
12PostgreSQL Tablespace Management
13PostgreSQL pg_dump and pg_restore Guide
14PostgreSQL Backup and Restore Using pg_dumpall and psql
15pg_basebackup – Backup, Restore, and Recovery
16Backup & Restore PostgreSQL DB Cluster to Another Host (No Archive Mode)
17Backup & Restore PostgreSQL DB Cluster on Same Host
18Restore PostgreSQL to New Host using pg_basebackup + WAL Archives
19PostgreSQL PITR – Point in Time Recovery
20Configure Streaming Replication in PostgreSQL
21Manual Failover in PostgreSQL Streaming Replication
22Convert Asynchronous Replication to Synchronous Replication

 

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Backup and Restore on Same Host Using pg_basebackup

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

How to Backup and Restore PostgreSQL DB Cluster on the Same Host Using pg_basebackup

Table of Contents

1. Environment
2. Verify Existing DB Setup
3. Pre-requisites
4. Take Backup
5. Prepare & Restore
     5.1 Stop PostgreSQL and Clean Directories
     5.2 Remove all from Data & WAL directories
     5.3 Restore Data and WAL
         A. Copy Backup files to $PGDATA
         B. Copy WAL files from $PGDATA/pg_wal to /pgWal/pgsql17/wal/
         C. Remove the existing $PGDATA/pg_wal directory
         D. Create a symbolic link pointing $PGDATA/pg_wal to a separate WAL directory
         E. Set Permissions
6. Start PostgreSQL
7. Final Verification

1. Environment

ASPECTEnv
Hostnamelxicbpgdsgv01
IP Address192.168.2.51
OSRHEL 9
DB VersionPostgreSQL v17.6
Archive modearchive_mode=off
pgData/pgData/pgsql17/data
WAL Directory/pgWal/pgsql17/wal
Tablespacepg_default
DatabasesDELL, ORCL

2. Verify Existing DB Setup

[postgres@lxicbpgdsgv01 ~]$ psql
psql (17.6)
Type "help" for help.

postgres=# \l+
                                                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------+---------+------------+--------------------------------------------
 dell      | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 7763 kB | pg_default |
 orcl      | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 7907 kB | pg_default |
 postgres  | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 492 MB  | pg_default | default administrative connection database
 template0 | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           | =c/postgres          +| 7545 kB | pg_default | unmodifiable empty database
           |          |          |                 |             |             |        |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           | =c/postgres          +| 7723 kB | pg_default | default template for new databases
           |          |          |                 |             |             |        |           | postgres=CTc/postgres |         |            |
(5 rows)

postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres |
 pg_global  | postgres |
(2 rows)

postgres=# \c dell
You are now connected to database "dell" as user "postgres".
dell=# select * from test.emp;
 name | designation | project | company
------+-------------+---------+---------
 Sugi | DBA         | Jetstar | iGATE
 Teja | DBA         | RCM     | iGATE
 RAJ  | DBA         | RCM     | iGATE
(3 rows)

dell=#

3. Pre-requisites

  • Verify postgresql.conf
postgres=# SHOW wal_level;
 wal_level
-----------
 replica <----
(1 row)

postgres=#
postgres=# SHOW max_wal_senders;
 max_wal_senders
-----------------
 10
(1 row)

postgres=# SHOW archive_mode;
 archive_mode
--------------
 off
(1 row)

postgres=#
  • Verify pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS             METHOD
# Local connections for replication (for pg_basebackup run locally)
local   replication     all                                 trust

# Remote connections for replication (for pg_basebackup run remotely)
#host    replication     repl_user       192.168.2.52/32     scram-sha-256
  • Verify user permissions:REPLICATION or SUPERUSER required
postgres=# \du
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres=#
  • Verify free space for backup
[postgres@lxicbpgdsgv01 ~]$ du -sh /pgData/pgsql17/data/
524M    /pgData/pgsql17/data/
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ du -sh /pgWal/pgsql17/wal/
801M    /pgWal/pgsql17/wal/
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ df -h /pgBackup/
Filesystem      Size  Used Avail Use% Mounted on
/dev/sdd1       100G  746M  100G   1% /pgBackup <----
[postgres@lxicbpgdsgv01 ~]$ mkdir -p /pgBackup/pgsql17/backup/basebackup_10OCT2025
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ mkdir -p /pgBackup/pgsql17/backup/log/
[postgres@lxicbpgdsgv01 ~]$

4. Take Backup

[postgres@lxicbpgdsgv01 ~]$ ls -ltrh /pgWal/pgsql17/wal | wc -l
53 <---- 
[postgres@lxicbpgdsgv01 ~]$

pg_basebackup does not include all files from the pg_wal directory in the backup. Instead, it selectively includes only the WAL files required to make the base backup consistent at the point in time the backup was taken.

-- If you want to store the base backup and WAL files in separate backup directories. 

nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/basebackup_10OCT2025 --waldir=/pgBackup/pgsql17/backup/wal_backup -Fp -Xs -P -v > /pgBackup/pgsql17/backup/log/basebackup_10OCT2025.log 2>&1 &

The --waldir option in pg_basebackup is supported only when using the plain format (-Fp), not with the tar format (-Ft).

If we want symlinks preserved → use (both -Fp & --waldir use together) -Fp --waldir=/pgWal/pgsql17/wal

-- OR --

The directory mush be empty: /pgBackup/pgsql17/backup/basebackup_10OCT2025

[postgres@lxicbpgdsgv01 ~]$ nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/basebackup_10OCT2025 -Fp -Xs -P -v > /pgBackup/pgsql17/backup/log/basebackup_10OCT2025.log 2>&1 &
[1] 4973
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ cat /pgBackup/pgsql17/backup/log/basebackup_10OCT2025.log
nohup: ignoring input
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/85000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_4975"
279413/536037 kB (52%), 0/1 tablespace (...asebackup_10OCT2025/base/5/16533)
536047/536047 kB (100%), 0/1 tablespace (...ckup_10OCT2025/global/pg_control)
536047/536047 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/85000158
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed  <-----
[postgres@lxicbpgdsgv01 ~]$

5. Prepare & Restore

5.1 Stop PostgreSQL and Clean Directories

[root@lxicbpgdsgv01 ~]# systemctl stop postgresql-17.service
[root@lxicbpgdsgv01 ~]# 
[root@lxicbpgdsgv01 ~]# ps -ef | grep postgres
root        5057    3151  0 18:57 pts/0    00:00:00 grep --color=auto postgres
[root@lxicbpgdsgv01 ~]#

5.2 Remove all from Data & WAL directories

-- Remove all from PGDATA directory

[root@lxicbpgdsgv01 ~]# rm -rf /pgData/pgsql17/data/*
[root@lxicbpgdsgv01 ~]# ls -ltr /pgData/pgsql17/data/
total 0
[root@lxicbpgdsgv01 ~]#

-- Remove all from WAL directory 

[root@lxicbpgdsgv01 ~]# rm -rf /pgWal/pgsql17/wal/*
[root@lxicbpgdsgv01 ~]# ls -ltr /pgWal/pgsql17/wal/
total 0
[root@lxicbpgdsgv01 ~]#

5.3 Restore Data and WAL

A. Copy Backup files to $PGDATA
Since we did not use the -Fp format with the --waldir option, all required WAL files will be included in the backup under the default path: PGDATA/pg_wal.

[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgData/pgsql17/data/
total 0
[postgres@lxicbpgdsgv01 ~]$ cd /pgBackup/pgsql17/backup/basebackup_10OCT2025/
[postgres@lxicbpgdsgv01 basebackup_10OCT2025]$ cp -Rp * /pgData/pgsql17/data/
[postgres@lxicbpgdsgv01 basebackup_10OCT2025]$
[postgres@lxicbpgdsgv01 basebackup_10OCT2025]$ ls -ltr /pgData/pgsql17/data/
total 296
-rw-------. 1 postgres postgres    227 Oct 10 18:41 backup_label
drwx------. 4 postgres postgres     77 Oct 10 18:41 pg_wal
drwx------. 7 postgres postgres     59 Oct 10 18:41 base
drwx------. 4 postgres postgres     68 Oct 10 18:41 pg_logical
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_dynshmem
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_commit_ts
drwx------. 2 postgres postgres    110 Oct 10 18:41 log
-rw-------. 1 postgres postgres   1169 Oct 10 18:41 postgresql.conf.bkp_10sep2025
-rw-------. 1 postgres postgres  30702 Oct 10 18:41 postgresql.conf.bkp
-rw-------. 1 postgres postgres     88 Oct 10 18:41 postgresql.auto.conf
drwx------. 2 postgres postgres     18 Oct 10 18:41 pg_xact
-rw-------. 1 postgres postgres      3 Oct 10 18:41 PG_VERSION
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_twophase
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_tblspc
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_subtrans
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_stat_tmp
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_stat
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_snapshots
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_serial
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_replslot
drwx------. 2 postgres postgres      6 Oct 10 18:41 pg_notify
drwx------. 4 postgres postgres     36 Oct 10 18:41 pg_multixact
-rw-------. 1 postgres postgres   2640 Oct 10 18:41 pg_ident.conf
-rw-------. 1 postgres postgres   5600 Oct 10 18:41 pg_hba.conf
-rw-------. 1 postgres postgres   1171 Oct 10 18:41 postgresql.conf
drwx------. 2 postgres postgres   4096 Oct 10 18:41 global
-rw-------. 1 postgres postgres     30 Oct 10 18:41 current_logfiles
-rw-------. 1 postgres postgres 227736 Oct 10 18:41 backup_manifest
[postgres@lxicbpgdsgv01 basebackup_10OCT2025]$
B. Copy WAL files from $PGDATA/pg_wal to /pgWal/pgsql17/wal/
[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgWal/pgsql17/wal/
total 0
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ cd /pgData/pgsql17/data/pg_wal/
[postgres@lxicbpgdsgv01 pg_wal]$ cp -Rp * /pgWal/pgsql17/wal
[postgres@lxicbpgdsgv01 pg_wal]$
[postgres@lxicbpgdsgv01 pg_wal]$ ls -ltr /pgWal/pgsql17/wal
total 16384
drwx------. 2 postgres postgres        6 Oct 10 18:41 summaries
drwx------. 2 postgres postgres        6 Oct 10 18:41 archive_status
-rw-------. 1 postgres postgres 16777216 Oct 10 18:41 000000010000000000000085
[postgres@lxicbpgdsgv01 pg_wal]$ cd
[postgres@lxicbpgdsgv01 ~]$
C. Remove the existing $PGDATA/pg_wal directory
[postgres@lxicbpgdsgv01 ~]$ rm -rf /pgData/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv01 ~]$
D. Create a symbolic link pointing $PGDATA/pg_wal to a separate WAL directory
[postgres@lxicbpgdsgv01 ~]$ ln -s /pgWal/pgsql17/wal /pgData/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal
lrwxrwxrwx. 1 postgres postgres 18 Oct 10 19:43 /pgData/pgsql17/data/pg_wal -> /pgWal/pgsql17/wal
[postgres@lxicbpgdsgv01 ~]$
E. Set Permissions
[postgres@lxicbpgdsgv01 ~]$ chown -R postgres:postgres /pgData/pgsql17/data
[postgres@lxicbpgdsgv01 ~]$ chmod 700 /pgData/pgsql17/data
[postgres@lxicbpgdsgv01 ~]$ chown -R postgres:postgres /pgWal/pgsql17/wal
[postgres@lxicbpgdsgv01 ~]$ chmod 700 /pgWal/pgsql17/wal

6. Start PostgreSQL

[root@lxicbpgdsgv01 ~]# systemctl start postgresql-17.service
[root@lxicbpgdsgv01 ~]# systemctl status postgresql-17.service
● postgresql-17.service - PostgreSQL 17 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
     Active: active (running) since Fri 2025-10-10 19:45:21 +08; 5s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 5230 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 5235 (postgres)
      Tasks: 7 (limit: 15835)
     Memory: 34.2M
        CPU: 94ms
     CGroup: /system.slice/postgresql-17.service
             ├─5235 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─5236 "postgres: logger "
             ├─5237 "postgres: checkpointer "
             ├─5238 "postgres: background writer "
             ├─5240 "postgres: walwriter "
             ├─5241 "postgres: autovacuum launcher "
             └─5242 "postgres: logical replication launcher "

Oct 10 19:45:21 lxicbpgdsgv01.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 10 19:45:21 lxicbpgdsgv01.rajasekhar.com postgres[5235]: 2025-10-10 19:45:21.177 +08 [5235] LOG:  redirecting log output to logging collector process
Oct 10 19:45:21 lxicbpgdsgv01.rajasekhar.com postgres[5235]: 2025-10-10 19:45:21.177 +08 [5235] HINT:  Future log output will appear in directory "log".
Oct 10 19:45:21 lxicbpgdsgv01.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv01 ~]#

7. Final Verification

[postgres@lxicbpgdsgv01 ~]$ psql
psql (17.6)
Type "help" for help.

postgres=# \l+
                                                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------+---------+------------+--------------------------------------------
 dell      | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 7609 kB | pg_default |
 orcl      | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 7753 kB | pg_default |
 postgres  | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           |                       | 492 MB  | pg_default | default administrative connection database
 template0 | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           | =c/postgres          +| 7545 kB | pg_default | unmodifiable empty database
           |          |          |                 |             |             |        |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | en_SG.UTF-8 | en_SG.UTF-8 |        |           | =c/postgres          +| 7569 kB | pg_default | default template for new databases
           |          |          |                 |             |             |        |           | postgres=CTc/postgres |         |            |
(5 rows)

postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres |
 pg_global  | postgres |
(2 rows)

postgres=# \c dell
You are now connected to database "dell" as user "postgres".
dell=# select * from test.emp;
 name | designation | project | company
------+-------------+---------+---------
 Sugi | DBA         | Jetstar | iGATE
 Teja | DBA         | RCM     | iGATE
 RAJ  | DBA         | RCM     | iGATE
(3 rows)

dell=#

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Disable ARCHIVELOG Mode

PostgreSQL, , , , , , , , , , , , , ,

How to Disable ARCHIVELOG Mode

Table of Contents

1. Verify Existing Archive Mode
2. Edit the archive settings
3. Restart PostgreSQL
4. Verify Current Mode
5. Verify WAL Archiving Behavior

1. Verify Existing Archive Mode

postgres=# SHOW archive_mode;
 archive_mode
--------------
 on  <------ 
(1 row)

postgres=#

postgres=# SHOW archive_command;
        archive_command
-------------------------------
 cp %p /pgArch/pgsql17/arch/%f  <----- 
(1 row)

postgres=#

2. Edit the archive settings


[postgres@lxicbpgdsgv01 ~]$ cp /pgData/pgsql17/data/postgresql.conf /pgData/pgsql17/data/postgresql.conf.bkp_10sep2025
[postgres@lxicbpgdsgv01 ~]$ vi /pgData/pgsql17/data/postgresql.conf

#archive_mode = on
#archive_command = 'cp %p /pgArch/pgsql17/arch/%f'

3. Restart PostgreSQL

[root@lxicbpgdsgv01 ~]# systemctl stop postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# systemctl start  postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# systemctl status postgresql-17.service
● postgresql-17.service - PostgreSQL 17 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
     Active: active (running) since Thu 2025-10-09 16:34:01 +08; 3s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 3492 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 3497 (postgres)
      Tasks: 7 (limit: 15835)
     Memory: 17.6M
        CPU: 92ms
     CGroup: /system.slice/postgresql-17.service
             ├─3497 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─3498 "postgres: logger "
             ├─3499 "postgres: checkpointer "
             ├─3500 "postgres: background writer "
             ├─3502 "postgres: walwriter "
             ├─3503 "postgres: autovacuum launcher "
             └─3504 "postgres: logical replication launcher "

Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com postgres[3497]: 2025-10-09 16:34:01.929 +08 [3497] LOG:  redirecting log output to logging collector process
Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com postgres[3497]: 2025-10-09 16:34:01.929 +08 [3497] HINT:  Future log output will appear in directory "log".
Oct 09 16:34:01 lxicbpgdsgv01.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv01 ~]#

4. Verify Current Mode

[postgres@lxicbpgdsgv01 ~]$ psql
psql (17.6)
Type "help" for help.

postgres=# SHOW archive_mode;
 archive_mode
--------------
 off  <------ it's disabled
(1 row)

postgres=# SHOW archive_command;
 archive_command
-----------------
 (disabled) <-------
(1 row)

postgres=#

5. Verify WAL Archiving Behavior


postgres=# CHECKPOINT;
CHECKPOINT
postgres=#
postgres=# CHECKPOINT;
CHECKPOINT
postgres=# CHECKPOINT;
CHECKPOINT
postgres=#
postgres=# exit
postgres=# SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/44000000
(1 row)

postgres=# SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/44000000
(1 row)

postgres=# SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/44000000
(1 row)

postgres=#
[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgArch/pgsql17/arch/
total 0  <---- Archivelogs not generating
[postgres@lxicbpgdsgv01 ~]$

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Change PostgreSQL WAL Directory Path (pg_wal)

PostgreSQL, , , , , , , , , , , , , , , ,

Table of Contents

0. Aim
1. Verify Existing pg_wal Directory
2. Create the New Directory
3. Stop PostgreSQL Service
4. Copy WAL Files to New Location
5. Backup Old WAL Directory
6. Create Symlink
7. Fix Permissions
8. Start PostgreSQL Service
9. Verify WAL Functionality
10. Remove Old WAL Directory (Optional)

0. Aim

To change the PostgreSQL 17 WAL File directory from its default location to new mount point

From : /var/lib/pgsql/17/data

TO : /pgData/pgsql17/data

1. Verify Existing pg_wal directory


[postgres@lxicbpgdsgv01 ~]$ psql
psql (17.6)
Type "help" for help.

postgres=# SELECT current_setting('data_directory') || '/pg_wal' AS wal_directory;
        wal_directory
-----------------------------
 /pgData/pgsql17/data/pg_wal  <----- 
(1 row)

postgres=# exit
[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgData/pgsql17/data/pg_wal
total 48M
drwx------. 2 postgres postgres   6 Sep 30 21:50 summaries
-rw-------. 1 postgres postgres 16M Oct  8 04:24 000000010000000000000008
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000007
drwx------. 2 postgres postgres  43 Oct  8 16:08 archive_status
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ du -sh /pgData/pgsql17/data/pg_wal
48M     /pgData/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv01 ~]$

2. Create the New Directory on a new disk

[root@lxicbpgdsgv01 ~]# mkdir -p /pgData/pgsql17/data
[root@lxicbpgdsgv01 ~]# chown postgres:postgres /pgData/pgsql17/data
[root@lxicbpgdsgv01 ~]# chmod 700 /pgData/pgsql17/data
[root@lxicbpgdsgv01 ~]#

3. Stop PostgreSQL Service

[root@lxicbpgdsgv01 ~]# systemctl stop postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# ps -ef | grep postgres
root        6887    6721  0 16:08 pts/0    00:00:00 grep --color=auto postgres
[root@lxicbpgdsgv01 ~]#

4. Copy Existing WAL Files to New Location

[postgres@lxicbpgdsgv01 ~]$ nohup rsync -avh --progress /pgData/pgsql17/data/pg_wal/ /pgWal/pgsql17/wal/ > rsync_pgwal.log 2>&1 &
[1] 6943
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$
[1]+  Done                    nohup rsync -avh --progress /pgData/pgsql17/data/pg_wal/ /pgWal/pgsql17/wal/ > rsync_pgwal.log 2>&1
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ cat rsync_pgwal.log
nohup: ignoring input
sending incremental file list
./
000000010000000000000006
         16.78M 100%   81.89MB/s    0:00:00 (xfr#1, to-chk=5/7)
000000010000000000000007
         16.78M 100%   43.84MB/s    0:00:00 (xfr#2, to-chk=4/7)
000000010000000000000008
         16.78M 100%   31.07MB/s    0:00:00 (xfr#3, to-chk=3/7)
archive_status/
archive_status/000000010000000000000006.done
              0 100%    0.00kB/s    0:00:00 (xfr#4, to-chk=0/7)
summaries/

sent 50.34M bytes  received 107 bytes  33.56M bytes/sec
total size is 50.33M  speedup is 1.00
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgWal/pgsql17/wal/
total 48M
drwx------. 2 postgres postgres   6 Sep 30 21:50 summaries
-rw-------. 1 postgres postgres 16M Oct  8 04:24 000000010000000000000008
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000007
drwx------. 2 postgres postgres  43 Oct  8 16:08 archive_status
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ du -sh /pgWal/pgsql17/wal/
48M     /pgWal/pgsql17/wal/
[postgres@lxicbpgdsgv01 ~]$

5. Move old directory as backup

[postgres@lxicbpgdsgv01 ~]$ mv /pgData/pgsql17/data/pg_wal /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal
ls: cannot access '/pgData/pgsql17/data/pg_wal': No such file or directory
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal.bak
drwx------. 4 postgres postgres 141 Oct  8 16:08 /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$

6. Create symlink

[postgres@lxicbpgdsgv01 ~]$ ln -s /pgWal/pgsql17/wal /pgData/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgData/pgsql17/data/pg_wal
lrwxrwxrwx. 1 postgres postgres 18 Oct  8 16:16 /pgData/pgsql17/data/pg_wal -> /pgWal/pgsql17/wal
[postgres@lxicbpgdsgv01 ~]$

7. Fix permissions (if required)

[postgres@lxicbpgdsgv01 ~]$ chown -R postgres:postgres /pgWal/pgsql17/wal

8. Start PostgreSQL Service

[root@lxicbpgdsgv01 ~]# systemctl start postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# systemctl status postgresql-17.service
● postgresql-17.service - PostgreSQL 17 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
     Active: active (running) since Wed 2025-10-08 16:20:46 +08; 7s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 7079 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 7084 (postgres)
      Tasks: 8 (limit: 15835)
     Memory: 18.1M
        CPU: 94ms
     CGroup: /system.slice/postgresql-17.service
             ├─7084 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─7086 "postgres: logger "
             ├─7087 "postgres: checkpointer "
             ├─7088 "postgres: background writer "
             ├─7090 "postgres: walwriter "
             ├─7091 "postgres: autovacuum launcher "
             ├─7092 "postgres: archiver "
             └─7093 "postgres: logical replication launcher "

Oct 08 16:20:46 lxicbpgdsgv01.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 08 16:20:46 lxicbpgdsgv01.rajasekhar.com postgres[7084]: 2025-10-08 16:20:46.162 +08 [7084] LOG:  redirecting log output to logging collector process
Oct 08 16:20:46 lxicbpgdsgv01.rajasekhar.com postgres[7084]: 2025-10-08 16:20:46.162 +08 [7084] HINT:  Future log output will appear in directory "log".
Oct 08 16:20:46 lxicbpgdsgv01.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv01 ~]#

9. Verify

-- Load WAL File generation (Testing)

postgres=# -- Create test table
DROP TABLE IF EXISTS wal_test;
CREATE TABLE wal_test (
    id serial PRIMARY KEY,
    data text
);

-- Generate WAL traffic
DO $$
DECLARE
    i integer;
BEGIN
    FOR i IN 1..50 LOOP
        -- INSERT: 10,000 rows
        INSERT INTO wal_test (data)
        SELECT repeat('wal_test_data_', 50)
        FROM generate_series(1, 10000);

        -- UPDATE: 5,000 rows using CTE with LIMIT
        WITH to_update AS (
            SELECT id FROM wal_test WHERE id % 2 = 0 LIMIT 5000
        )
        UPDATE wal_test
        SET data = data || '_updated'
        WHERE id IN (SELECT id FROM to_update);

        -- DELETE: 5,000 rows using CTE with LIMIT
        WITH to_delete AS (
            SELECT id FROM wal_test WHERE id % 3 = 0 LIMIT 5000
        )
        DELETE FROM wal_test
        WHERE id IN (SELECT id FROM to_delete);

        -- Commit to flush WAL
        COMMIT;

        -- Optional pause to slow down the loop
        PERFORM pg_sleep(0.5);
    END LOOP;
END$$;
DROP TABLE
CREATE TABLE
DO
postgres=# exit
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ ls -lrth  /pgWal/pgsql17/wal
total 752M
drwx------. 2 postgres postgres    6 Sep 30 21:50 summaries
-rw-------. 1 postgres postgres  16M Oct  8 16:34 000000010000000000000009
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000A
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000B
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000C
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000D
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000E
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000F
-rw-------. 1 postgres postgres  16M Oct  8 16:34 000000010000000000000010
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000011
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000012
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000013
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000014
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000015
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000016
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000017
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000018
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000019
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001A
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001B
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001C
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001D
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001E
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001F
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000020
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000021
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000022
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000023
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000024
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000025
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000026
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000027
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000028
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000029
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002A
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002B
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002C
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002D
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002E
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002F
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000030
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000031
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000032
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000033
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000034
-rw-------. 1 postgres postgres  16M Oct  8 16:36 000000010000000000000035
drwx------. 2 postgres postgres 4.0K Oct  8 16:36 archive_status
-rw-------. 1 postgres postgres  16M Oct  8 16:36 xlogtemp.7289
[postgres@lxicbpgdsgv01 ~]$

10. Remove Old WAL Directory (Optional, later)

[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal.bak
drwx------. 4 postgres postgres 141 Oct  8 16:08 /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$ rm -rf /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

WAL Files

PostgreSQL, , , , , , , , , , , , ,

Understanding WAL Files in PostgreSQL – For Oracle DBAs

What is WAL?

WAL (Write-Ahead Logging) ensures data durability and crash recovery. Every change is first written to WAL before being flushed to data files. 

1. WAL Location: WAL files are stored in $PGDATA/pg_wal/.
2. Fixed Size: Each WAL segment is pre-allocated with a fixed size (typically 16MB), set at cluster initialization (initdb).
3. Writing & Switching: WAL segments are written sequentially. PostgreSQL switches to the next segment when the current one is full or a manual switch is triggered (e.g., via pg_switch_wal()).
4. WAL and Archive File Size: When archived, the exact same 16MB WAL segment file is copied as raw binary to the archive destination. Therefore, archive files always match the WAL segment size.
5. Archiving: If archive_mode = on, PostgreSQL uses the archive_command to copy completed WAL segments to the archive destination (e.g., /pgArch/...). Only full segments are archived.
6. Recycling: If archiving is disabled, PostgreSQL reuses old WAL segments once they’re no longer needed for recovery or replication.

WAL Files = Redo Log Files
WAL Archives = Archive Log Files

PostgreSQL vs Oracle Comparison

AspectOraclePostgreSQL
Redo Log FilesCalled Redo Log FilesCalled WAL Files
Archive Log FilesCalled Archive Log FilesCalled WAL Archives
Redo Log File Size~200MB (default)16MB (default and fixed)
Redo Log Files CountMinimum 2 or more redo log groups
(Best practice: 3)		No fixed number
Controlled by: min_wal_size / wal_segment_size
Example: 80MB / 16MB = 5 WAL files
Archive Log File Size~200MB (same as redo log file size)16MB (same as WAL file size)
Log File StorageRedo log files stored in redo log groupsWAL files stored in pg_wal directory
Archiving MechanismArchive logs created if archiving is enabledArchived via archive_command if archive_mode = on
Overwrite BehaviorRedo logs are overwritten when full (if archive not enabled)WAL segments are recycled/overwritten when full (if archive_mode is off)
Force Switch BehaviorALTER SYSTEM SWITCH LOGFILE;SELECT pg_switch_wal();

PostgreSQL WAL Configuration Parameters

ParameterMeaningDefault
wal_segment_sizeSize of each WAL file16MB
min_wal_sizeMinimum total WAL size retained80MB (5 WAL files)
Common in production: 8GB
max_wal_sizeMax WAL before triggering a checkpoint1GB
Common in production: 16GB

Check Configuration

[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW wal_segment_size;"
wal_segment_size
------------------
16MB  <----- Each redo log file size
(1 row)

[postgres@lxicbpgdsgv01 ~]$


[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW min_wal_size;"
 min_wal_size
--------------
 80MB <---- 80MB/16MB = 5 <--- Total 5 redo log files with each 16MB
(1 row)

[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW max_wal_size;"
 max_wal_size
--------------
 1GB  <---- 1024MB
(1 row)

[postgres@lxicbpgdsgv01 ~]$

View WAL files (Redo log files)

postgres=# SELECT current_setting('data_directory') || '/pg_wal' AS wal_directory;
        wal_directory
-----------------------------
 /pgData/pgsql17/data/pg_wal  <---- Redo log file directory
(1 row)

postgres=# 
[postgres@lxicbpgdsgv01 ~]$ ls -lh /pgData/pgsql17/data/pg_wal | grep -E '^-.* [0-9]+ .* [0-9A-F]{24}$'
-rw-------. 1 postgres postgres 16M Oct 8 01:33 000000010000000000000005
-rw-------. 1 postgres postgres 16M Oct 8 01:28 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct 8 01:28 000000010000000000000007
[postgres@lxicbpgdsgv01 ~]$

min_wal_size = 80MB means PostgreSQL will try to keep at least 5 WAL segment files on disk (since 80MB ÷ 16MB = 5 files).

However, in our case, only 3 WAL files are currently present. This is likely because the system is under low load in the testing environment, so fewer WAL files are needed at the moment.

---- Lets Generate Load

postgres=# -- Create test table
DROP TABLE IF EXISTS wal_test;
CREATE TABLE wal_test (
    id serial PRIMARY KEY,
    data text
);

-- Generate WAL traffic
DO $$
DECLARE
    i integer;
BEGIN
    FOR i IN 1..50 LOOP
        -- INSERT: 10,000 rows
        INSERT INTO wal_test (data)
        SELECT repeat('wal_test_data_', 50)
        FROM generate_series(1, 10000);

        -- UPDATE: 5,000 rows using CTE with LIMIT
        WITH to_update AS (
            SELECT id FROM wal_test WHERE id % 2 = 0 LIMIT 5000
        )
        UPDATE wal_test
        SET data = data || '_updated'
        WHERE id IN (SELECT id FROM to_update);

        -- DELETE: 5,000 rows using CTE with LIMIT
        WITH to_delete AS (
            SELECT id FROM wal_test WHERE id % 3 = 0 LIMIT 5000
        )
        DELETE FROM wal_test
        WHERE id IN (SELECT id FROM to_delete);

        -- Commit to flush WAL
        COMMIT;

        -- Optional pause to slow down the loop
        PERFORM pg_sleep(0.5);
    END LOOP;
END$$;
DROP TABLE
CREATE TABLE
DO
postgres=# exit
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ 
ls -lrth /pgData/pgsql17/data/pg_wal
total 752M
drwx------. 2 postgres postgres    6 Sep 30 21:50 summaries
-rw-------. 1 postgres postgres  16M Oct  8 16:34 000000010000000000000009
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000A
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000B
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000C
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000D
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000E
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000F
-rw-------. 1 postgres postgres  16M Oct  8 16:34 000000010000000000000010
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000011
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000012
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000013
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000014
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000015
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000016
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000017
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000018
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000019
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001A
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001B
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001C
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001D
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001E
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001F
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000020
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000021
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000022
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000023
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000024
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000025
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000026
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000027
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000028
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000029
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002A
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002B
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002C
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002D
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002E
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002F
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000030
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000031
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000032
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000033
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000034
-rw-------. 1 postgres postgres  16M Oct  8 16:36 000000010000000000000035
drwx------. 2 postgres postgres 4.0K Oct  8 16:36 archive_status
-rw-------. 1 postgres postgres  16M Oct  8 16:36 xlogtemp.7289
[postgres@lxicbpgdsgv01 ~]$ 

-- Now We can see Many WAL Files (Redo log files) generated, count increased from 3 WAL files to 50+ WAL files

View Archived WAL Files (Archive log files)

postgres=# SHOW archive_command;
        archive_command
-------------------------------
 cp %p /pgArch/pgsql17/arch/%f
(1 row)

postgres=#

[postgres@lxicbpgdsgv01 ~]$ 
[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgArch/pgsql17/arch/
total 976M
-rw-------. 1 postgres postgres 16M Oct  7 23:50 000000010000000000000001
-rw-------. 1 postgres postgres 16M Oct  7 23:51 000000010000000000000002
-rw-------. 1 postgres postgres 16M Oct  8 01:28 000000010000000000000003
-rw-------. 1 postgres postgres 16M Oct  8 01:28 000000010000000000000004
-rw-------. 1 postgres postgres 16M Oct  8 04:24 000000010000000000000005
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct  8 16:24 000000010000000000000007
-rw-------. 1 postgres postgres 16M Oct  8 16:25 000000010000000000000008
-rw-------. 1 postgres postgres 16M Oct  8 16:34 000000010000000000000009
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000A
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000B
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000C
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000D
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000E
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000F
-rw-------. 1 postgres postgres 16M Oct  8 16:34 000000010000000000000010
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000011
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000012
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000013
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000014
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000015
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000016
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000017
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000018
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000019
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001A
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001B
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001C
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001D
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001E
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001F
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000020
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000021
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000022
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000023
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000024
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000025
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000026
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000027
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000028
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000029
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002A
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002B
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002C
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002D
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002E
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002F
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000030
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000031
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000032
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000033
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000034
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000035
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000036
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000037
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000038
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000039
-rw-------. 1 postgres postgres 16M Oct  8 16:36 00000001000000000000003A
-rw-------. 1 postgres postgres 16M Oct  8 16:36 00000001000000000000003B
-rw-------. 1 postgres postgres 16M Oct  8 16:36 00000001000000000000003C
-rw-------. 1 postgres postgres 16M Oct  8 16:36 00000001000000000000003D
[postgres@lxicbpgdsgv01 ~]$

-- WAL and Archive File Size: When archived, the exact same 16MB WAL segment file is copied as raw binary to the archive destination. Therefore, archive files always match the WAL segment size (redo log size).

Best Practices

  • Use a dedicated mount point for pg_wal (WAL directory) – eg. /pgWal
  • Set min_wal_size to higher value in production (e.g. 8GB)
  • Keep WAL archive files on a separate mount point for better I/O and space isolation – eg. /pgArch
  • Monitor WAL archive directory regularly to avoid disk full errors

Note:

  • WAL file size is fixed at cluster init (default: 16MB). You cannot change it for an existing cluster.
  • PostgreSQL WAL Size Be Can Increased only at NEW cluster creation time via initdb (64MB)
    /usr/pgsql-17/bin/initdb –wal-segsize=64 -D /pgData/pgsql17/data

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Enable Archive Mode (WAL Archiving)

PostgreSQL, , , , , , , , , , , , , , ,

Enable Archive Mode in PostgreSQL 17

Table of Contents

0. Importance of Archive Log Files
1. Verify Existing Archive Mode
2. Ensure Archive Directory Exists
3. Edit postgresql.conf
4. Restart PostgreSQL
5. Verify the Configuration
6. Test Archiving Works

0. Importance of Archive Log Files

Archive log files, also known as WAL archives (Write-Ahead Log archives) , play a critical role in PostgreSQL for data protection, recovery, and replication.

A. Point-in-Time Recovery (PITR): Restore the database to a specific point in time, useful for accidental data changes or loss.
B. Continuous Backup: Works with base backups to enable robust disaster recovery.
C. Streaming Replication Support: Helps standby servers catch up if they fall behind.
D. Disaster Recovery: Enables full recovery after hardware or data corruption.
E. Data Audit & Analysis: Allows decoding WAL logs for auditing and compliance.

🛑 Important Note:

If WAL archiving is disabled and a backup is taken, you can only restore to the exact backup time, not to any point after.

1. Verify Existing Archive Mode

[postgres@lxicbpgdsgv01 ~]$ psql
psql (17.6)
Type "help" for help.

postgres=# SHOW archive_mode;
 archive_mode 
--------------
 off <--- it's off
(1 row)

postgres=# SHOW archive_command;
 archive_command 
-----------------
 (disabled) <----
(1 row)
[root@lxicbpgdsgv01 ~]# systemctl status postgresql-17
● postgresql-17.service - PostgreSQL 17 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
     Active: active (running) since Tue 2025-10-07 18:33:05 +08; 2min 26s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 5911 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 5916 (postgres)
      Tasks: 7 (limit: 15700)
     Memory: 21.3M
        CPU: 177ms
     CGroup: /system.slice/postgresql-17.service
             ├─5916 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─5917 "postgres: logger "
             ├─5918 "postgres: checkpointer "
             ├─5919 "postgres: background writer "
             ├─5921 "postgres: walwriter "
             ├─5922 "postgres: autovacuum launcher "
             └─5923 "postgres: logical replication launcher "

Oct 07 18:33:05 lxicbpgdsgv01.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 07 18:33:05 lxicbpgdsgv01.rajasekhar.com postgres[5916]: 2025-10-07 18:33:05.392 +08 [5916] LOG:  redirecting log output to logging collector process
Oct 07 18:33:05 lxicbpgdsgv01.rajasekhar.com postgres[5916]: 2025-10-07 18:33:05.392 +08 [5916] HINT:  Future log output will appear in directory "log".
Oct 07 18:33:05 lxicbpgdsgv01.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv01 ~]#

Currently, there is no active archive process running in the background.

2. Ensure Archive Directory Exists

[root@lxicbpgdsgv01 ~]# mkdir -p /pgArch/pgsql17/arch/
[root@lxicbpgdsgv01 ~]# chown postgres:postgres /pgArch/pgsql17/arch/
[root@lxicbpgdsgv01 ~]# chmod 700 /pgArch/pgsql17/arch/

3. Edit postgresql.conf

[postgres@lxicbpgdsgv01 ~]$ vi /pgData/pgsql17/data/postgresql.conf

Update or add the following lines:

archive_mode = on
archive_command = 'cp %p /pgArch/pgsql17/arch/%f'

Explanation:

  • %p = Full path of WAL file
  • %f = WAL file name
  • cp %p /pgArch/pgsql17/arch/%f = Command to copy the WAL file to archive directory

4. Restart PostgreSQL

[root@lxicbpgdsgv01 ~]# systemctl status postgresql-17
● postgresql-17.service - PostgreSQL 17 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
     Active: active (running) since Tue 2025-10-07 23:48:29 +08; 18s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 7868 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 7873 (postgres)
      Tasks: 8 (limit: 15700)
     Memory: 19.0M
        CPU: 96ms
     CGroup: /system.slice/postgresql-17.service
             ├─7873 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─7874 "postgres: logger "
             ├─7875 "postgres: checkpointer "
             ├─7876 "postgres: background writer "
             ├─7878 "postgres: walwriter "
             ├─7879 "postgres: autovacuum launcher "
             ├─7880 "postgres: archiver "  <------
             └─7881 "postgres: logical replication launcher "

Oct 07 23:48:29 lxicbpgdsgv01.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 07 23:48:29 lxicbpgdsgv01.rajasekhar.com postgres[7873]: 2025-10-07 23:48:29.503 +08 [7873] LOG:  redirecting log output to logging collector process
Oct 07 23:48:29 lxicbpgdsgv01.rajasekhar.com postgres[7873]: 2025-10-07 23:48:29.503 +08 [7873] HINT:  Future log output will appear in directory "log".
Oct 07 23:48:29 lxicbpgdsgv01.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv01 ~]#

Make sure the archiver process is running.

5. Verify the Configuration

[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW archive_mode;"
 archive_mode 
--------------
 on <-----------
(1 row)

[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW archive_command;"
        archive_command         
-------------------------------
 cp %p /pgArch/pgsql17/arch/%f  <-----------------
(1 row)

6. Test Archiving Works

[postgres@lxicbpgdsgv01 ~]$ psql -c "SELECT pg_switch_wal();"

Check archive folder for new WAL files:

[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgArch/pgsql17/arch/
total 16M
-rw-------. 1 postgres postgres 16M Oct  7 23:50 000000010000000000000001

[postgres@lxicbpgdsgv01 ~]$ psql -c "SELECT pg_switch_wal();"
[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgArch/pgsql17/arch/
total 32M
-rw-------. 1 postgres postgres 16M Oct  7 23:50 000000010000000000000001
-rw-------. 1 postgres postgres 16M Oct  7 23:51 000000010000000000000002

 Archiving is working successfully.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Change Data Directory

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , ,

PostgreSQL 17 – Change Data Directory

0. Overview
1. Stop PostgreSQL Service
2. Create the New Directory
3. Copy Existing Data to New Location
4. Move existing data directory
5. Update PostgreSQL Configuration to Use New Data Directory
6. Reload systemd and start PostgreSQL
7. Edit postgres .bash_profile
8. Verify New Data Directory is in Use
9. Remove Old Data Directory (Optional – A week after)

0. Overview

To change the PostgreSQL 17 data directory from its default location

From: /var/lib/pgsql/17/data To: /pgData/pgsql17/data

 

1. Stop PostgreSQL Service

[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW data_directory;"
     data_directory
------------------------
 /var/lib/pgsql/17/data <---- existing data directory
(1 row)

[postgres@lxicbpgdsgv01 ~]$

[root@lxicbpgdsgv01 ~]# systemctl stop postgresql-17
[root@lxicbpgdsgv01 ~]# 
[root@lxicbpgdsgv01 ~]# ps -ef | grep postgres
root        5759    4079  0 18:09 pts/1    00:00:00 grep --color=auto postgres
[root@lxicbpgdsgv01 ~]#

2. Create the New Directory

[root@lxicbpgdsgv01 ~]# mkdir -p /pgData/pgsql17/data
[root@lxicbpgdsgv01 ~]# chown postgres:postgres /pgData/pgsql17/data
[root@lxicbpgdsgv01 ~]# chmod 700 /pgData/pgsql17/data
[root@lxicbpgdsgv01 ~]#

3. Copy Existing Data to New Location

[postgres@lxicbpgdsgv01 ~]$ nohup rsync -av --progress /var/lib/pgsql/17/data/ /pgData/pgsql17/data/ > rsync_pgdata.log 2>&1 &
[1] 5863
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ tail -f rsync_pgdata.log
pg_wal/000000010000000000000002
     16,777,216 100%   17.68MB/s    0:00:00 (xfr#1621, to-chk=3/1652)
pg_wal/archive_status/
pg_wal/summaries/
pg_xact/
pg_xact/0000
          8,192 100%    8.84kB/s    0:00:00 (xfr#1622, to-chk=0/1652)

sent 73,827,441 bytes  received 31,005 bytes  49,238,964.00 bytes/sec
total size is 73,717,583  speedup is 1.00
^C[1]+  Done                    nohup rsync -av --progress /var/lib/pgsql/17/data/ /pgData/pgsql17/data/ > rsync_pgdata.log 2>&1

[postgres@lxicbpgdsgv01 ~]$

4. Move existing data directory

[postgres@lxicbpgdsgv01 ~]$ mv /var/lib/pgsql/17/data /var/lib/pgsql/17/data_bkp_10072025
[postgres@lxicbpgdsgv01 ~]$

5. Update PostgreSQL Configuration to Use New Data Directory

[postgres@lxicbpgdsgv01 ~]$ cat /usr/lib/systemd/system/postgresql-17.service | grep -i "Environment=PGDATA"
Environment=PGDATA=/var/lib/pgsql/17/data/  <-----
[postgres@lxicbpgdsgv01 ~]$

[root@lxicbpgdsgv01 ~]# ls -ltr /usr/lib/systemd/system/postgresql-17.service
-rw-r--r--. 1 root root 1788 Aug 13 17:39 /usr/lib/systemd/system/postgresql-17.service
[root@lxicbpgdsgv01 ~]#

[root@lxicbpgdsgv01 ~]# sed -i 's|^Environment=PGDATA=.*|Environment=PGDATA=/pgData/pgsql17/data/|' /usr/lib/systemd/system/postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# cat /usr/lib/systemd/system/postgresql-17.service | grep -i "Environment=PGDATA"
Environment=PGDATA=/pgData/pgsql17/data/ <------
[root@lxicbpgdsgv01 ~]#

6. Reload systemd and start PostgreSQL

[root@lxicbpgdsgv01 ~]# systemctl daemon-reload
[root@lxicbpgdsgv01 ~]#

[root@lxicbpgdsgv01 ~]# systemctl start postgresql-17
[root@lxicbpgdsgv01 ~]#

[root@lxicbpgdsgv01 ~]# systemctl cat postgresql-17 | grep -i Environment=PGDATA
Environment=PGDATA=/pgData/pgsql17/data/ <-----
[root@lxicbpgdsgv01 ~]#

[root@lxicbpgdsgv01 ~]# systemctl status postgresql-17
● postgresql-17.service - PostgreSQL 17 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
     Active: active (running) since Tue 2025-10-07 18:33:05 +08; 2min 26s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 5911 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 5916 (postgres)
      Tasks: 7 (limit: 15700)
     Memory: 21.3M
        CPU: 177ms
     CGroup: /system.slice/postgresql-17.service
             ├─5916 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─5917 "postgres: logger "
             ├─5918 "postgres: checkpointer "
             ├─5919 "postgres: background writer "
             ├─5921 "postgres: walwriter "
             ├─5922 "postgres: autovacuum launcher "
             └─5923 "postgres: logical replication launcher "

Oct 07 18:33:05 lxicbpgdsgv01.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 07 18:33:05 lxicbpgdsgv01.rajasekhar.com postgres[5916]: 2025-10-07 18:33:05.392 +08 [5916] LOG:  redirecting log output to logging collector process
Oct 07 18:33:05 lxicbpgdsgv01.rajasekhar.com postgres[5916]: 2025-10-07 18:33:05.392 +08 [5916] HINT:  Future log output will appear in directory "log".
Oct 07 18:33:05 lxicbpgdsgv01.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.

7. Edit postgres .bash_profile

[postgres@lxicbpgdsgv01 ~]$ cat .bash_profile | grep -i PGDATA=
PGDATA=/var/lib/pgsql/17/data
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ sed -i 's|^PGDATA=.*|PGDATA=/pgData/pgsql17/data|' ~/.bash_profile
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ cat .bash_profile | grep -i PGDATA=
PGDATA=/pgData/pgsql17/data <-----
[postgres@lxicbpgdsgv01 ~]$ . .bash_profile <-- reload the profile
[postgres@lxicbpgdsgv01 ~]$

8. Verify New Data Directory is in Use

[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW data_directory;"
    data_directory
----------------------
 /pgData/pgsql17/data  <---- new data directory
(1 row)

[postgres@lxicbpgdsgv01 ~]$

9. Remove Old Data Directory (Optional – A week after)

[postgres@lxicbpgdsgv01 ~]$ ls -ld /var/lib/pgsql/17/data_bkp_10072025
drwx------. 20 postgres postgres 4096 Oct  7 18:20 /var/lib/pgsql/17/data_bkp_10072025
[postgres@lxicbpgdsgv01 ~]$ rm -rf /var/lib/pgsql/17/data_bkp_10072025
[postgres@lxicbpgdsgv01 ~]$

 

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

PostgreSQL pg_hba.conf

PostgreSQL, , , , , , , , , ,

PostgreSQL pg_hba.conf Guide

Table of Contents

1. What is pg_hba.conf?
2. Authentication Methods in pg_hba.conf
3. Create Database and User
4. Contents of pg_hba.conf
5. Play with peer
6. Play with trust
7. Play with reject
8. Restrict by User
9. Restrict by Network
10. Verify logs


1. What is pg_hba.conf?

HBA = Host-Based Authentication.
Location: usually inside PostgreSQL data directory (/var/lib/pgsql/<version>/data/pg_hba.conf or /pgData/pgsql15/data/pg_hba.conf depending on your setup).



Format:

# TYPE  DATABASE  USER  ADDRESS        METHOD
TYPE: local, host, hostssl, hostnossl
DATABASE: which DBs (e.g. all, postgres, mydb)
USER: which roles (e.g. all, myuser)
ADDRESS: client IP/CIDR (127.0.0.1/32, 192.168.2.0/24)
METHOD: authentication method (trust, md5, scram-sha-256, peer, reject, cert)

OPTIONS	: optional settings (e.g., clientcert=1)


2. Authentication Methods in pg_hba.conf

trust:
--- > No password needed. Anyone who can connect to the server is trusted.
--- > Use: testing only.
--- > Not secure in production.

Example:

host    all    all    127.0.0.1/32    trust

md5
--- > Password required, stored as MD5 hash.
--- > Legacy support. Weaker security, avoid if possible.

Example:

host    all    all    127.0.0.1/32    md5

scram-sha-256
--- > Password required, stored as salted SCRAM-SHA-256 hash.
--- > Stronger than MD5.
--- > Recommended method for production.

Example:

host    all    all    127.0.0.1/32    scram-sha-256


peer
--- > Works for local connections only.
--- > The OS user name must match the PostgreSQL role.
--- > Good for local scripts/services under same user.

Example:

local   all    all                     peer


reject
--- > Explicitly denies access.
----> Useful for blocking unwanted connections (like a firewall rule).

Example:

host    all    all    0.0.0.0/0       reject


cert
---> Requires SSL/TLS client certificate.
---> PostgreSQL role must match certificate username.
---> Very secure for enterprise / production with SSL.

Example:
hostssl all   all    192.168.1.0/24   cert clientcert=1


3. Create Database and User

postgres=# CREATE USER teja WITH PASSWORD 'teja123';
CREATE ROLE
postgres=# CREATE DATABASE orcl OWNER teja;
CREATE DATABASE
postgres=#


4. Contents of pg_hba.conf

[postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf
# TYPE  DATABASE         USER            ADDRESS                METHOD

# Allow local peer access for postgres
local   all             postgres                                peer

# Allow local password access for all
local   all             all                                     scram-sha-256

[postgres@pg17 ~]$


5. Play with peer

Works for local connections only. The OS user name must match the PostgreSQL role.

[postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf
# TYPE  DATABASE         USER            ADDRESS                METHOD

# Allow local peer access for postgres

local   all             postgres                                peer


# Allow local password access for all
local   all             all                                     scram-sha-256

[postgres@pg17 ~]$

[postgres@pg17 ~]$ psql -U teja -d orcl
 Password for user teja: <---- it's asking password 

psql (15.13)
Type "help" for help.

orcl=> exit
[postgres@pg17 ~]$

 Password Not asking for user postgres 
[postgres@pg17 ~]$ psql -U postgres -d orcl
psql (15.13)
Type "help" for help.

orcl=#


6. Play with trust

No password needed. Anyone who can connect to the server is trusted.
Use: testing only. Not secure in production.

Change pg_hba.conf:

[postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf
# TYPE  DATABASE         USER            ADDRESS                METHOD

# Allow local peer access for postgres
local   all             postgres                                peer

# Allow local password access for all

local   all             all                                     trust

[postgres@pg17 ~]$

[postgres@pg17 ~]$ /usr/pgsql-15/bin/pg_ctl reload -D /pgData/pgsql15/data/
server signaled
[postgres@pg17 ~]$ psql -U teja -d orcl  <--- It won’t ask for a password. 
psql (15.13)
Type "help" for help.

orcl=>


7. Play with reject

[postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf
# TYPE  DATABASE         USER            ADDRESS                METHOD

# Allow local peer access for postgres
local   all             postgres                                peer

# Allow local password access for all

local   all             all                                     reject

[postgres@pg17 ~]$

[postgres@pg17 ~]$ /usr/pgsql-15/bin/pg_ctl reload -D /pgData/pgsql15/data/
server signaled
[postgres@pg17 ~]$ psql -U teja -d orcl

psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  pg_hba.conf rejects connection for host "[local]", user "teja", database "orcl", no encryption

[postgres@pg17 ~]$


8. Restrict by User

Allow only user teja, block everyone else:

[postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf
# TYPE  DATABASE         USER            ADDRESS                METHOD

# Allow local peer access for postgres
local   all             postgres                                peer

# Allow local password access for all
local   all             all                                     trust

# Allow IPv4 localhost

host    orcl            teja             192.168.2.31/32         scram-sha-256
host    orcl            all              192.168.2.31/32         reject


[postgres@pg17 ~]$

[postgres@pg17 ~]$ /usr/pgsql-15/bin/pg_ctl reload -D /pgData/pgsql15/data/
server signaled
[postgres@pg17 ~]$ psql -U teja -d orcl -h 192.168.2.31
Password for user teja:
psql (15.13)
Type "help" for help.

orcl=> \conninfo
You are connected to database "orcl" as user "teja" on host "192.168.2.31" at port "5432".
orcl=>
orcl=> exit
[postgres@pg17 ~]$


[postgres@pg17 ~]$ psql -U postgres -d orcl -h 192.168.2.31

psql: error: connection to server at "192.168.2.31", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "192.168.2.31", user "postgres", database "orcl", no encryption

[postgres@pg17 ~]$


Notice: Only user Teja able to connect where as user postgres not able to connect


9. Restrict by Network

We want to block connections from IP 192.168.2.0/24 

[postgres@pg17 ~]$ cat /pgData/pgsql15/data/pg_hba.conf
# TYPE  DATABASE         USER            ADDRESS                METHOD

# Allow local peer access for postgres
local   all             postgres                                peer

# Allow local password access for all
local   all             all                                     trust

# Allow IPv4 localhost

host    all             all             192.168.2.0/24          reject

[postgres@pg17 ~]$

[postgres@pg17 ~]$ /usr/pgsql-15/bin/pg_ctl reload -D /pgData/pgsql15/data/
server signaled
[postgres@pg17 ~]$ 
[postgres@pg17 ~]$ psql -U teja -d orcl -h 192.168.2.31

psql: error: connection to server at "192.168.2.31", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "192.168.2.31", user "teja", database "orcl", no encryption

[postgres@pg17 ~]$


10. Verify logs

postgres=# SELECT * FROM pg_hba_file_rules;
 line_number | type  | database | user_name  |   address    |     netmask     |  auth_method  | options | error
-------------+-------+----------+------------+--------------+-----------------+---------------+---------+-------
           4 | local | {all}    | {postgres} |              |                 | peer          |         |
           7 | local | {all}    | {all}      |              |                 | trust         |         |
          11 | host  | {orcl}   | {teja}     | 192.168.2.31 | 255.255.255.255 | scram-sha-256 |         |
          12 | host  | {orcl}   | {all}      | 192.168.2.31 | 255.255.255.255 | reject        |         |
(4 rows)

postgres=#

[postgres@pg17 ~]$ tail -f /pgData/pgsql15/data/log/postgresql-Tue.log
2025-09-16 04:08:37.244 EDT [8294] LOG:  database system was shut down at 2025-09-16 04:08:32 EDT
2025-09-16 04:08:37.251 EDT [8289] LOG:  database system is ready to accept connections
2025-09-16 04:11:27.459 EDT [8289] LOG:  received SIGHUP, reloading configuration files
2025-09-16 04:11:31.842 EDT [8401] FATAL:  pg_hba.conf rejects connection for host "[local]", user "teja", database "orcl", no encryption
2025-09-16 04:13:37.568 EDT [8292] LOG:  checkpoint starting: time
2025-09-16 04:13:37.573 EDT [8292] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.002 s, total=0.006 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB
2025-09-16 04:16:15.863 EDT [8289] LOG:  received SIGHUP, reloading configuration files
2025-09-16 04:16:52.318 EDT [8474] FATAL:  pg_hba.conf rejects connection for host "192.168.2.31", user "teja", database "orcl", no encryption
2025-09-16 04:27:15.932 EDT [8289] LOG:  received SIGHUP, reloading configuration files
2025-09-16 04:27:50.593 EDT [8613] FATAL:  pg_hba.conf rejects connection for host "192.168.2.31", user "postgres", database "orcl", no encryption

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

PSQL

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

PostgreSQL DBA Step by Step Learning

#PostgreSQL DBA Topics
1How to Install PostgreSQL ON Linux?
2How to Install PostgreSQL on Linux 7 using source code?
3How to START/STOP PostgreSQL ON Linux?
4How to Create Database in PostgreSQL?
5PostgreSQL User Management
6PostgreSQL pg_hba.conf Guide
7PostgreSQL Change Data Directory
8Understanding WAL Files in PostgreSQL – For Oracle DBAs
9Change PostgreSQL WAL Directory Path (pg_wal)
10Enable Archive Mode in PostgreSQL 17
11How to Disable ARCHIVELOG Mode
12PostgreSQL Tablespace Management
13PostgreSQL pg_dump and pg_restore Guide
14PostgreSQL Backup and Restore Using pg_dumpall and psql
15pg_basebackup – Backup, Restore, and Recovery
16Backup & Restore PostgreSQL DB Cluster to Another Host (No Archive Mode)
17Backup & Restore PostgreSQL DB Cluster on Same Host
18Restore PostgreSQL to New Host using pg_basebackup + WAL Archives
19PostgreSQL PITR – Point in Time Recovery
20Configure Streaming Replication in PostgreSQL
21Manual Failover in PostgreSQL Streaming Replication
22Convert Asynchronous Replication to Synchronous Replication

 

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/