Tag Archives: PostgreSQL administration

Are Uncommitted Transactions Also Written to WAL?

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

Are Uncommitted Transactions Also Written to WAL?

Table of Contents

1. The Big Question

When you run BEGIN; INSERT INTO ... ; but have not yet committed, is that data already written to the WAL (Write-Ahead Log)?

Short answer: YES. Every data modification is written to WAL the moment the statement executes, regardless of whether the transaction is later committed or rolled back. The WAL records the action, not the outcome. The commit or rollback is recorded as a separate WAL record afterward.

This post proves it with a hands-on demo — we will insert data, leave it uncommitted, and physically inspect the WAL file on disk to find our data sitting there.

2. What is WAL and Why Does It Exist?

The Write-Ahead Log (WAL) is PostgreSQL’s crash-recovery mechanism. The core rule is simple:

  • Before any change is written to the actual data files (heap), the change is first appended to the WAL.
  • If the server crashes, PostgreSQL replays the WAL on startup to restore the database to a consistent state.
  • Committed transactions are replayed. Rolled-back transactions are undone during recovery using the ABORT records in the WAL.

This means the WAL must record everything — including uncommitted work — so that recovery knows what to undo if the server crashes mid-transaction.

3. Setup — Create Demo Table

postgres=# DROP TABLE IF EXISTS wal_demo;
DROP TABLE
postgres=#

postgres=# CREATE TABLE wal_demo (
    id      SERIAL PRIMARY KEY,
    fname   TEXT
);
CREATE TABLE
postgres=#

4. Check synchronous_commit Setting

This setting controls when WAL is flushed to disk. The default on means every COMMIT waits for the WAL to be physically written to disk.

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

postgres=#

Important: Even with synchronous_commit = on, individual INSERT statements within a transaction write to the WAL buffer immediately. The COMMIT is what triggers the flush-to-disk guarantee. The data still hits the WAL file on disk very quickly either way.

5. Record WAL Position Before Any Transaction

We capture the current WAL position (LSN = Log Sequence Number) so we can compare it after our insert.

postgres=# SELECT
    pg_current_wal_lsn() AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn()) AS wal_file;
 current_lsn |         wal_file
-------------+--------------------------
 5/5F02E8C0  | 00000001000000050000005F
(1 row)

postgres=#

Our starting point is LSN 5/5F02E8C0 in WAL file 00000001000000050000005F

6. BEGIN + INSERT (Uncommitted) — Check WAL Position

Now we start a transaction and insert a row, but we do NOT commit.

postgres=# BEGIN;
BEGIN
postgres=*#

postgres=*# SELECT txid_current();
 txid_current
--------------
         2384 <-----------
(1 row)

postgres=*#

postgres=*# INSERT INTO wal_demo (fname) VALUES ('SUGIVARDHAN SUNDARRAJAN - UNCOMMITTED COPY');
INSERT 0 1
postgres=*#

-- The row is visible inside this transaction
postgres=*# SELECT * FROM wal_demo;
 id |                   fname
----+--------------------------------------------
  1 | SUGIVARDHAN SUNDARRAJAN - UNCOMMITTED COPY
(1 row)

postgres=*#

Now check the WAL position without committing:

postgres=*# SELECT
    pg_current_wal_lsn() AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn()) AS wal_file;
 current_lsn |         wal_file
-------------+--------------------------
 5/5F02EBE0  | 00000001000000050000005F
(1 row)

postgres=*#


-- Also check the flush position
postgres=*# SELECT pg_current_wal_flush_lsn();
 pg_current_wal_flush_lsn
--------------------------
 5/5F02EBE0
(1 row)

postgres=*#

The WAL LSN moved! It went from 5/5F02E8C0 to 5/5F02EBE0. The INSERT was written to WAL even though we have not committed. The flush LSN matches, meaning the data is already on disk.

7. Grep the WAL File — Is the Uncommitted Data There?

Let’s physically inspect the WAL file on disk using the strings command.

[postgres@pgdb01 pg_wal]$ strings $PGDATA/pg_wal/00000001000000050000005F | grep -i "SUGIVARDHAN"
WSUGIVARDHAN SUNDARRAJAN - UNCOMMITTED COPY
[postgres@pgdb01 pg_wal]$ 

[postgres@pgdb01 ~]$ xxd $PGDATA/pg_wal/00000001000000050000005F | grep -i -B2 -A2 "sugiv"
0002e9e0: 800a 0000 4d23 4834 0060 3500 7f06 0000  ....M#H4.`5.....
0002e9f0: 0500 0000 ca01 0100 0000 0000 ff03 0200  ................
0002ea00: 0208 1800 0100 0000 5753 5547 4956 4152  ........WSUGIVAR
0002ea10: 4448 414e 2053 554e 4441 5252 414a 414e  DHAN SUNDARRAJAN
0002ea20: 202d 2055 4e43 4f4d 4d49 5454 4544 2043   - UNCOMMITTED C
[postgres@pgdb01 ~]$

There it is! The uncommitted row’s data is physically present in the WAL file on disk. Anyone with read access to the WAL directory can see it. This is why WAL files should be protected with proper file permissions and encryption.

8. ROLLBACK — Table is Empty, But WAL Remembers

postgres=*# ROLLBACK;
ROLLBACK
postgres=#


-- The table is empty — the row is gone from the user's perspective
postgres=# SELECT * FROM wal_demo;
 id | fname
----+-------
(0 rows)

postgres=#
postgres=# SELECT pg_current_wal_flush_lsn();
 pg_current_wal_flush_lsn
--------------------------
 5/5F02EE18
(1 row)

postgres=#

Our ending point is LSN 5/5F02EE18 in WAL file 00000001000000050000005F

But is the data still in the WAL file?

[postgres@pgdb01 ~]$ strings $PGDATA/pg_wal/00000001000000050000005F | grep -i "SUGIVARDHAN"
WSUGIVARDHAN SUNDARRAJAN - UNCOMMITTED COPY
[postgres@pgdb01 ~]$

[postgres@pgdb01 ~]$ xxd $PGDATA/pg_wal/00000001000000050000005F | grep -i -B2 -A2 "sugiv"
0002e9e0: 800a 0000 4d23 4834 0060 3500 7f06 0000  ....M#H4.`5.....
0002e9f0: 0500 0000 ca01 0100 0000 0000 ff03 0200  ................
0002ea00: 0208 1800 0100 0000 5753 5547 4956 4152  ........WSUGIVAR
0002ea10: 4448 414e 2053 554e 4441 5252 414a 414e  DHAN SUNDARRAJAN
0002ea20: 202d 2055 4e43 4f4d 4d49 5454 4544 2043   - UNCOMMITTED C
[postgres@pgdb01 ~]$

The data is STILL in the WAL file even after ROLLBACK. The ROLLBACK did not erase the WAL record. Instead, it wrote a new ABORT record to the WAL. During crash recovery, PostgreSQL replays the INSERT and then sees the ABORT, so it undoes the change. The WAL is an append-only log — records are never deleted or modified in place.

9. Inspect WAL with pg_waldump — The Full Story

pg_waldump decodes binary WAL files into human-readable records. Let’s inspect what PostgreSQL wrote for our uncommitted transaction.

[postgres@pgdb01 ~]$ pg_waldump $PGDATA/pg_wal/00000001000000050000005F \
    --start 5/5F02E8C0 --end 5/5F02EE18 \
    | grep -i "Insert\|Commit\|Abort"

rmgr: Heap        len (rec/tot):    102/   102, tx:       2384, lsn: 5/5F02E9D0, prev 5/5F02E998, desc: INSERT+INIT off: 1, flags: 0x00, blkref #0: rel 1663/5/65994 blk 0
rmgr: Btree       len (rec/tot):     64/    64, tx:       2384, lsn: 5/5F02EA98, prev 5/5F02EA38, desc: INSERT_LEAF off: 1, blkref #0: rel 1663/5/66000 blk 1
rmgr: Transaction len (rec/tot):     34/    34, tx:       2384, lsn: 5/5F02EBE0, prev 5/5F02EBA8, desc: ABORT 2026-09-25 19:23:15.559348 +08

[postgres@pgdb01 ~]$

Let’s break down every record in order:

#Record TypeTX IDWhat It Means
1Heap INSERT+INIT2384The actual row data was written to table block 0. The INIT flag indicates that this insert initialized a brand-new page.
2Btree INSERT_LEAF2384The primary key index (id SERIAL PRIMARY KEY) was updated with a new leaf entry in index block 1 under the same transaction ID.
3ABORT2384The ROLLBACK command logged an ABORT record along with the exact timestamp. This tells crash recovery to ignore/undo records #1 and #2.

Key Takeaway: Both the heap insertion and the primary key index update were written to the WAL before the transaction ended. When we executed ROLLBACK, PostgreSQL appended an ABORT record. The WAL is an append-only log: rolled-back records are never wiped or overwritten.

Why is this necessary? If the database crashes mid-transaction, PostgreSQL recovery reads the WAL sequentially. It must replay the uncommitted changes and then read the ABORT record to cleanly roll them back. Without logging uncommitted work, crash recovery would have no way to restore consistency.

10. Now COMMIT — Compare the WAL Records

Let’s do the same experiment, but this time we COMMIT.

postgres=# SELECT
    pg_current_wal_lsn() AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn()) AS wal_file;
 current_lsn |         wal_file
-------------+--------------------------
 5/5F02EE18  | 00000001000000050000005F
(1 row)

postgres=#

postgres=# BEGIN;
BEGIN
postgres=*#

postgres=*# INSERT INTO wal_demo (fname) VALUES ('RAJASEKHAR AMUDALA - COMMITTED COPY');
INSERT 0 1
postgres=*#

postgres=*# COMMIT;
COMMIT
postgres=#


-- Record the new WAL position
postgres=# SELECT
    pg_current_wal_lsn() AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn()) AS wal_file;
 current_lsn |         wal_file
-------------+--------------------------
 5/5F02F080  | 00000001000000050000005F
(1 row)

postgres=#


-- Verify the row is in the table
postgres=# SELECT * FROM wal_demo;
 id |                fname
----+-------------------------------------
  2 | RAJASEKHAR AMUDALA - COMMITTED COPY
(1 row)

postgres=#


-- Check the transaction visibility metadata
postgres=# SELECT
    fname,
    xmin                                      AS inserting_xid,
    xmin::text::bigint < txid_current()       AS is_in_past
FROM wal_demo;
                fname                | inserting_xid | is_in_past
-------------------------------------+---------------+------------
 RAJASEKHAR AMUDALA - COMMITTED COPY |          2385 | t
(1 row)

postgres=#

Grep the WAL file for the committed data:

[postgres@pgdb01 ~]$ strings $PGDATA/pg_wal/00000001000000050000005F | grep -i "RAJASEKHAR"
IRAJASEKHAR AMUDALA - COMMITTED COPYP
[postgres@pgdb01 ~]$

Inspect with pg_waldump:

[postgres@pgdb01 ~]$ pg_waldump $PGDATA/pg_wal/00000001000000050000005F \
    --start 5/5F02EE18 --end 5/5F02F080 \
    | grep -i "Insert\|Commit\|Abort"

rmgr: Heap        len (rec/tot):     54/   222, tx:       2385, lsn: 5/5F02EE80, prev 5/5F02EE18, desc: INSERT off: 2, flags: 0x00, blkref #0: rel 1663/5/65994 blk 0 FPW
rmgr: Btree       len (rec/tot):     53/   133, tx:       2385, lsn: 5/5F02EF60, prev 5/5F02EE80, desc: INSERT_LEAF off: 2, blkref #0: rel 1663/5/66000 blk 1 FPW
rmgr: Transaction len (rec/tot):     34/    34, tx:       2385, lsn: 5/5F02F020, prev 5/5F02EFE8, desc: COMMIT 2026-09-25 19:50:32.487755 +08

[postgres@pgdb01 ~]$

Detailed breakdown of the committed records:

#Record TypeTX IDDetails & Explanation
1Heap INSERT (FPW)2385The tuple was inserted at offset 2 of table block 0. Because a checkpoint finished right before this statement, the first touch to this page triggered a Full Page Write (FPW), storing the entire backup image into WAL (total length: 222 bytes).
2Btree INSERT_LEAF (FPW)2385The index entry for the primary key was written to index block 1. Just like the heap page, modifying this index block also produced an FPW.
3COMMIT2385The transaction committed successfully. Instead of ABORT, WAL logged a COMMIT record containing the exact timestamp (2026-09-25 19:50:32.487755 +08).

The Proof:

  • Uncommitted / Aborted: Heap INSERT → Btree INSERT_LEAF → ABORT
  • Committed: Heap INSERT → Btree INSERT_LEAF → COMMIT

The heap modification and index updates were written to the WAL file in both scenarios before the transaction ended. The only structural difference is the final record: ABORT vs COMMIT.

11. Side-by-Side Comparison

AspectUncommitted (ROLLBACK)Committed (COMMIT)
INSERT written to WAL?✅ Yes — immediately✅ Yes — immediately
Data visible in WAL file?✅ Yes — strings finds it✅ Yes — strings finds it
WAL LSN advances?✅ Yes — before commit/rollback✅ Yes — before commit
Transaction end recordABORTCOMMIT
Row visible in table?❌ No — rolled back✅ Yes — committed
Crash recovery behaviorReplay INSERT, then undo via ABORTReplay INSERT, keep via COMMIT
WAL record structureINSERT → ABORTINSERT → COMMIT

Key takeaway: The WAL does not wait for COMMIT to record your data. It writes every modification as it happens. The only difference between a committed and uncommitted transaction in the WAL is the final record: COMMIT vs ABORT. This is by design — if the server crashes mid-transaction, PostgreSQL needs the WAL to know what to undo.

Security implication: Sensitive data (passwords, credit card numbers, PII) written in a transaction that is later rolled back is still present in the WAL files on disk. WAL files should be protected with strict file permissions (0700).

12. Quick Summary Cheatsheet

ConceptDetail
Are uncommitted writes in WAL?Yes. Every INSERT/UPDATE/DELETE is written to WAL immediately, before commit.
Why?Crash recovery needs to know what to undo. Without the INSERT record, the ABORT record is meaningless.
What differs between COMMIT and ROLLBACK?Only the final WAL record: COMMIT vs ABORT. The data records are identical.
Is the WAL append-only?Yes. Records are never modified or deleted in place. Old WAL files are eventually recycled or archived.
Check current WAL positionSELECT pg_current_wal_lsn();
Check WAL flush positionSELECT pg_current_wal_flush_lsn();
Find WAL file name from LSNSELECT pg_walfile_name(pg_current_wal_lsn());
Inspect WAL contentspg_waldump $PGDATA/pg_wal/<file>
Search WAL for datastrings $PGDATA/pg_wal/<file> | grep "text"
Security concernRolled-back sensitive data persists in WAL. Protect WAL files and consider 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: linkedin.com/in/rajasekhar-amudala

PostgreSQL Configuration File

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

PostgreSQL Configuration File (postgresql.conf & auto.conf)

Table of Contents

1. What is postgresql.conf?

postgresql.conf is the main configuration file for PostgreSQL. It controls nearly every aspect of the server’s behavior — memory, connections, WAL, logging, query planner, autovacuum, and more.

But there is actually a second file most DBAs overlook:

  • postgresql.conf — the main file you edit by hand.
  • postgresql.auto.conf — created and managed by ALTER SYSTEM. It is read after postgresql.conf, so its values always win.

This post walks through both files, shows the difference between static (restart-required) and dynamic (reload-only) parameters, demonstrates the four levels of parameter scope, and shows what happens when things go wrong.

2. Locate the Configuration Files

The location varies by OS and installation method. Ask PostgreSQL directly:

postgres=# SHOW data_directory;
    data_directory
----------------------
 /pgData/pgsql17/data
(1 row)

postgres=# SHOW config_file;
             config_file
--------------------------------------
 /pgData/pgsql17/data/postgresql.conf
(1 row)

postgres=# SHOW hba_file;
             hba_file
----------------------------------
 /pgData/pgsql17/data/pg_hba.conf
(1 row)

postgres=# SHOW ident_file;
             ident_file
------------------------------------
 /pgData/pgsql17/data/pg_ident.conf
(1 row)

postgres=#

Key fact: postgresql.auto.conf always lives in the same directory as postgresql.conf (normally $PGDATA). It is created and updated only by ALTER SYSTEM.

3. Understand pg_settings — The Columns That Matter

The pg_settings view is your single source of truth for every configuration parameter. Here are the columns you need to know:

postgres=# SELECT
    name,
    setting,
    unit,
    context,
    source,
    sourcefile,
    sourceline,
    pending_restart
FROM pg_settings
WHERE name = 'max_connections';
      name       | setting | unit |  context   |       source       |              sourcefile              | sourceline | pending_restart
-----------------+---------+------+------------+--------------------+--------------------------------------+------------+-----------------
 max_connections | 100     |      | postmaster | configuration file | /pgData/pgsql17/data/postgresql.conf |          1 | f   <--------------
(1 row)

postgres=#

Column breakdown:

  • context — WHO/WHEN can change it. This is what defines static vs dynamic.
  • source — Where the current active value came from (default, configuration file, override, session, etc.).
  • sourcefile — The actual file that set it (.conf or .auto.conf).
  • sourceline — Line number within that file.
  • pending_restart — true = value was changed but needs a RESTART to apply.

4. Static vs Dynamic Parameters

Every parameter has a context that determines how and when it can be changed.

STATIC — context = postmaster (restart required)

postgres=# SELECT name, setting, context
FROM pg_settings
WHERE context = 'postmaster'
ORDER BY name
LIMIT 20;
                name                 |               setting                |  context
-------------------------------------+--------------------------------------+------------
 archive_mode                        | on                                   | postmaster
 autovacuum_freeze_max_age           | 200000000                            | postmaster
 autovacuum_max_workers              | 3                                    | postmaster
 autovacuum_multixact_freeze_max_age | 400000000                            | postmaster
 bonjour                             | off                                  | postmaster
 bonjour_name                        |                                      | postmaster
 cluster_name                        |                                      | postmaster
 commit_timestamp_buffers            | 32                                   | postmaster
 config_file                         | /pgData/pgsql17/data/postgresql.conf | postmaster
 data_directory                      | /pgData/pgsql17/data                 | postmaster
 data_sync_retry                     | off                                  | postmaster
 debug_io_direct                     |                                      | postmaster
 dynamic_shared_memory_type          | posix                                | postmaster
 event_source                        | PostgreSQL                           | postmaster
 external_pid_file                   |                                      | postmaster
 hba_file                            | /pgData/pgsql17/data/pg_hba.conf     | postmaster
 hot_standby                         | on                                   | postmaster
 huge_pages                          | try                                  | postmaster
 huge_page_size                      | 0                                    | postmaster
 ident_file                          | /pgData/pgsql17/data/pg_ident.conf   | postmaster
(20 rows)

postgres=#

DYNAMIC — everything else (no restart needed)

postgres=# SELECT name, setting, context
FROM pg_settings
WHERE context IN ('sighup','backend','superuser','user')
ORDER BY context, name
LIMIT 20;
                 name                  |            setting            | context
---------------------------------------+-------------------------------+---------
 ignore_system_indexes                 | off                           | backend
 post_auth_delay                       | 0                             | backend
 allow_alter_system                    | on                            | sighup
 archive_cleanup_command               |                               | sighup
 archive_command                       | cp %p /pgArch/pgsql17/arch/%f | sighup
 archive_library                       |                               | sighup
 archive_timeout                       | 0                             | sighup
 authentication_timeout                | 60                            | sighup
 autovacuum                            | on                            | sighup
 autovacuum_analyze_scale_factor       | 0.1                           | sighup
 autovacuum_analyze_threshold          | 50                            | sighup
 autovacuum_naptime                    | 60                            | sighup
 autovacuum_vacuum_cost_delay          | 2                             | sighup
 autovacuum_vacuum_cost_limit          | -1                            | sighup
 autovacuum_vacuum_insert_scale_factor | 0.2                           | sighup
 autovacuum_vacuum_insert_threshold    | 1000                          | sighup
 autovacuum_vacuum_scale_factor        | 0.2                           | sighup
 autovacuum_vacuum_threshold           | 50                            | sighup
 autovacuum_work_mem                   | -1                            | sighup
 bgwriter_delay                        | 200                           | sighup
(20 rows)

postgres=#

Count parameters per context

postgres=# SELECT context, count(*) AS num_params
FROM pg_settings
GROUP BY context
ORDER BY num_params DESC;
      context      | num_params
-------------------+------------
 user              |        145
 sighup            |         98
 postmaster        |         65
 superuser         |         46
 internal          |         19
 superuser-backend |          4
 backend           |          2
(7 rows)

postgres=#

Context cheat sheet:

  • postmaster → RESTART required. Cannot change on the fly.
  • sighup → RELOAD is enough (pg_reload_conf() or SIGHUP).
  • backend → Reload + only takes effect for new connections.
  • superuser / user → Can be changed live with SET, per session.

5. pg_file_settings — What’s Actually Written in the Files

pg_file_settings shows the raw contents of the conf files, independent of whether the value is currently applied.

postgres=# SELECT sourcefile, sourceline, name, setting, applied, error
FROM pg_file_settings
ORDER BY sourcefile, sourceline
LIMIT 15;
              sourcefile              | sourceline |            name            |      setting      | applied | error
--------------------------------------+------------+----------------------------+-------------------+---------+-------
 /pgData/pgsql17/data/postgresql.conf |          1 | max_connections            | 100               | t       |
 /pgData/pgsql17/data/postgresql.conf |          2 | shared_buffers             | 128MB             | t       |
 /pgData/pgsql17/data/postgresql.conf |          3 | dynamic_shared_memory_type | posix             | t       |
 /pgData/pgsql17/data/postgresql.conf |          4 | max_wal_size               | 1GB               | t       |
 /pgData/pgsql17/data/postgresql.conf |          5 | min_wal_size               | 80MB              | t       |
 /pgData/pgsql17/data/postgresql.conf |          6 | log_destination            | stderr            | t       |
 /pgData/pgsql17/data/postgresql.conf |          7 | logging_collector          | on                | t       |
 /pgData/pgsql17/data/postgresql.conf |          8 | log_directory              | log               | t       |
 /pgData/pgsql17/data/postgresql.conf |          9 | log_filename               | postgresql-%a.log | t       |
 /pgData/pgsql17/data/postgresql.conf |         10 | log_rotation_age           | 1d                | t       |
 /pgData/pgsql17/data/postgresql.conf |         11 | log_rotation_size          | 0                 | t       |
 /pgData/pgsql17/data/postgresql.conf |         12 | log_truncate_on_rotation   | on                | t       |
 /pgData/pgsql17/data/postgresql.conf |         13 | log_line_prefix            | %m [%p]           | t       |
 /pgData/pgsql17/data/postgresql.conf |         14 | log_timezone               | Asia/Singapore    | t       |
 /pgData/pgsql17/data/postgresql.conf |         15 | datestyle                  | iso, dmy          | t       |
(15 rows)

postgres=#

Key columns:

  • applied = false — the setting was overridden by a later file. For example, postgresql.auto.conf is read after postgresql.conf, so it wins.
  • error — non-null if that specific line failed to parse or apply.

6. Dynamic Parameter Change — Reload Only, No Restart

log_min_duration_statement has context = 'sighup', so a reload is enough.

-- Change the value using ALTER SYSTEM
postgres=# ALTER SYSTEM SET log_min_duration_statement = 500;
ALTER SYSTEM
postgres=#

-- Apply with reload (no restart needed)
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#

-- Confirm it applied immediately
postgres=# SELECT name, setting, context, source, sourcefile, pending_restart
FROM pg_settings
WHERE name = 'log_min_duration_statement';
            name            | setting |  context  |       source       |                sourcefile                 | pending_restart
----------------------------+---------+-----------+--------------------+-------------------------------------------+-----------------
 log_min_duration_statement | 500     | superuser | configuration file | /pgData/pgsql17/data/postgresql.auto.conf | f
(1 row)

postgres=#
postgres=# SHOW log_min_duration_statement;
 log_min_duration_statement
----------------------------
 500ms   <-------------------
(1 row)

postgres=#

Change is LIVE right now. setting = 500, source = 'override', sourcefile = postgresql.auto.conf, pending_restart = false. No restart was needed.

7. Static Parameter Change — Reload is NOT Enough

max_connections has context = 'postmaster'. A reload picks up the file change, but the running server cannot apply it without a restart.

-- Change the value
postgres=# ALTER SYSTEM SET max_connections = 250;
ALTER SYSTEM
postgres=#

-- Reload
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#

-- Check the setting — the OLD value is still active!
postgres=# SELECT name, setting, context, source, sourcefile, pending_restart
FROM pg_settings
WHERE name = 'max_connections';
      name       | setting |  context   |       source       |              sourcefile              | pending_restart
-----------------+---------+------------+--------------------+--------------------------------------+-----------------
 max_connections | 100     | postmaster | configuration file | /pgData/pgsql17/data/postgresql.conf | t   <----------------
(1 row)

postgres=#

[postgres@pgdb01 ~]$ pg_ctl restart -D $PGDATA
waiting for server to shut down.... done
server stopped
waiting for server to start....2026-09-25 13:43:50.273 +08 [3390] LOG:  redirecting log output to logging collector process
2026-09-25 13:43:50.273 +08 [3390] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb01 ~]$

postgres=# SELECT name, setting, context, source, sourcefile, pending_restart
FROM pg_settings
WHERE name = 'max_connections';
      name       | setting |  context   |       source       |                sourcefile                 | pending_restart
-----------------+---------+------------+--------------------+-------------------------------------------+-----------------
 max_connections | 250     | postmaster | configuration file | /pgData/pgsql17/data/postgresql.auto.conf | f  <-------------
(1 row)

postgres=#

pending_restart = true. The file has the new value (250), but the running server still shows 100. A postmaster-context parameter requires a full restart to take effect. pg_reload_conf() alone is not enough.

8. How postgresql.auto.conf Overrides postgresql.conf

Every ALTER SYSTEM SET writes to postgresql.auto.conf, not to postgresql.conf. Since postgresql.auto.conf is parsed last, its values always win.

postgres=# SELECT name, setting, sourcefile, sourceline
FROM pg_settings
WHERE name IN ('log_min_duration_statement','max_connections')
ORDER BY name;
            name            | setting |                sourcefile                 | sourceline
----------------------------+---------+-------------------------------------------+------------
 log_min_duration_statement | 500     | /pgData/pgsql17/data/postgresql.auto.conf |          3
 max_connections            | 250     | /pgData/pgsql17/data/postgresql.auto.conf |          4
(2 rows)

postgres=#

Verify from the OS shell:

-- postgresql.conf
[postgres@pgdb01 ~]$ cat /pgData/pgsql17/data/postgresql.conf|grep max_connections
max_connections = 100                   # (change requires restart)
[postgres@pgdb01 ~]$

-- postgresql.auto.conf
[postgres@pgdb01 ~]$ cat /pgData/pgsql17/data/postgresql.auto.conf|grep max_connections
max_connections = '250'
[postgres@pgdb01 ~]$

Both files mention max_connections with different values. The auto.conf value (250) is what PostgreSQL uses. This can cause confusion if you edit postgresql.conf and forget that auto.conf is overriding it.

9. Reset a Parameter Back to postgresql.conf’s Value

ALTER SYSTEM RESET removes the line from postgresql.auto.conf entirely, so PostgreSQL falls back to postgresql.conf (or the built-in default).

postgres=# SELECT name, setting, source, sourcefile
FROM pg_settings
WHERE name = 'log_min_duration_statement';
            name            | setting |       source       |                sourcefile
----------------------------+---------+--------------------+-------------------------------------------
 log_min_duration_statement | 500     | configuration file | /pgData/pgsql17/data/postgresql.auto.conf
(1 row)

postgres=#

postgres=# ALTER SYSTEM RESET log_min_duration_statement;
ALTER SYSTEM
postgres=#
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#
postgres=# SELECT name, setting, source, sourcefile
FROM pg_settings
WHERE name = 'log_min_duration_statement';
            name            | setting | source  | sourcefile
----------------------------+---------+---------+------------
 log_min_duration_statement | -1      | default |
(1 row)

postgres=#

[postgres@pgdb01 ~]$ cat /pgData/pgsql17/data/postgresql.auto.conf|grep log_min_duration_statement
[postgres@pgdb01 ~]$

Back to postgresql.conf. The auto.conf override is gone. source now reads configuration file and sourcefile points back to postgresql.conf.

10. Set a WRONG Value and See the Error

Scenario A: ALTER SYSTEM catches it immediately

postgres=# ALTER SYSTEM SET shared_buffers = 'not_a_number';
ERROR:  invalid value for parameter "shared_buffers": "not_a_number"
postgres=#

ALTER SYSTEM validates most values up front. The bad value is rejected immediately — nothing is written to auto.conf, nothing to reload.

Scenario B: Hand-edit the file (the real-world “fat-finger” scenario)

# Someone accidentally edits postgresql.conf directly:

[postgres@pgdb01 ~]$ cp /pgData/pgsql17/data/postgresql.conf /pgData/pgsql17/data/postgresql.conf.bkp
[postgres@pgdb01 ~]$


[postgres@pgdb01 ~]$ echo "work_mem = 'banana'" >> /pgData/pgsql17/data/postgresql.conf
[postgres@pgdb01 ~]$


# Then reloads:
[postgres@pgdb01 ~]$ psql -c "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

[postgres@pgdb01 ~]$

Check the PostgreSQL log:

[postgres@pgdb01 ~]$ tail -5 /pgData/pgsql17/data/log/postgresql-Fri.log
2026-09-25 13:53:14.494 +08 [3428] ERROR:  invalid value for parameter "shared_buffers": "not_a_number"
2026-09-25 13:53:14.494 +08 [3428] STATEMENT:  ALTER SYSTEM SET shared_buffers = 'not_a_number';
2026-09-25 13:56:38.363 +08 [3390] LOG:  received SIGHUP, reloading configuration files
2026-09-25 13:56:38.364 +08 [3390] LOG:  invalid value for parameter "work_mem": "banana"
2026-09-25 13:56:38.365 +08 [3390] LOG:  configuration file "/pgData/pgsql17/data/postgresql.conf" contains errors; unaffected changes were applied
[postgres@pgdb01 ~]$

# Manually edit and update right Value
[postgres@pgdb01 ~]$ cat /pgData/pgsql17/data/postgresql.conf | grep work_mem
work_mem = '4MB'
[postgres@pgdb01 ~]$

[postgres@pgdb01 ~]$ psql -c "SELECT pg_reload_conf();"
 pg_reload_conf
----------------
 t
(1 row)

[postgres@pgdb01 ~]$
[postgres@pgdb01 ~]$ tail -5 /pgData/pgsql17/data/log/postgresql-Fri.log
2026-09-25 13:53:14.494 +08 [3428] STATEMENT:  ALTER SYSTEM SET shared_buffers = 'not_a_number';
2026-09-25 13:56:38.363 +08 [3390] LOG:  received SIGHUP, reloading configuration files
2026-09-25 13:56:38.364 +08 [3390] LOG:  invalid value for parameter "work_mem": "banana"
2026-09-25 13:56:38.365 +08 [3390] LOG:  configuration file "/pgData/pgsql17/data/postgresql.conf" contains errors; unaffected changes were applied

-- New output

2026-09-25 14:03:56.537 +08 [3390] LOG:  received SIGHUP, reloading configuration files
[postgres@pgdb01 ~]$

For a sighup-context bad value: PostgreSQL logs the error but keeps the previous good value running. The server does not crash. Fix the typo and reload again.

For a postmaster-context bad value: If you restart with a malformed listen_addresses or shared_buffers, the server will fail to start entirely. The log will show a fatal parse error.

11. Find Every Parameter Pending a Restart

This is the single most useful query for a DBA before scheduling a restart window. It tells you exactly which staged changes are waiting.

postgres=# SELECT
    name,
    setting AS current_running_value,
    context,
    sourcefile
FROM pg_settings
WHERE pending_restart = true
ORDER BY name;
 name | current_running_value | context | sourcefile
------+-----------------------+---------+------------
(0 rows)

postgres=# ALTER SYSTEM SET max_connections=300;  <---- static parameter
ALTER SYSTEM
postgres=# 

postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#

postgres=# SELECT
    name,
    setting AS current_running_value,
    context,
    sourcefile
FROM pg_settings
WHERE pending_restart = true
ORDER BY name;
      name       | current_running_value |  context   |                sourcefile
-----------------+-----------------------+------------+-------------------------------------------
 max_connections | 250                   | postmaster | /pgData/pgsql17/data/postgresql.auto.conf
(1 row)

postgres=#

postgres=# SELECT count(*) AS still_pending
FROM pg_settings
WHERE pending_restart = true;
 still_pending
---------------
             1  <----- means something require to restart 
(1 row)

postgres=#
postgres=# SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name = 'max_connections';
      name       | setting |  context   | pending_restart
-----------------+---------+------------+-----------------
 max_connections | 250     | postmaster | t  <----------------
(1 row)

postgres=#

DBA tip: Run this query before every planned restart. If it returns zero rows, there is nothing staged and the restart won’t change any configuration. This prevents unnecessary downtime.

12. Restart and Confirm pending_restart Clears

# Restart the server (OS shell)
[postgres@pgdb01 ~]$ pg_ctl restart -D $PGDATA
waiting for server to shut down.... done
server stopped
waiting for server to start....2026-09-25 14:16:29.475 +08 [3600] LOG:  redirecting log output to logging collector process
2026-09-25 14:16:29.475 +08 [3600] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb01 ~]$

After the restart, reconnect and verify:

postgres=# SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name = 'max_connections';

      name       | setting |  context   | pending_restart
-----------------+---------+------------+-----------------
 max_connections | 300     | postmaster | f   <-------------
(1 row)

postgres=#

-- Confirm nothing else is pending
postgres=# SELECT count(*) AS still_pending
FROM pg_settings
WHERE pending_restart = true;

 still_pending
---------------
             0  <--------- means nothing pending for restart
(1 row)

postgres=#

postgres=# SELECT
    name,
    setting AS current_running_value,
    context,
    sourcefile
FROM pg_settings
WHERE pending_restart = true
ORDER BY name;
 name | current_running_value | context | sourcefile
------+-----------------------+---------+------------
(0 rows)

postgres=#

max_connections is now 300 and pending_restart = false. The restart applied the staged change.

13. Parameter Scope — Cluster / Database / User / Session

PostgreSQL lets you set most dynamic parameters at four different scopes, each narrower than the last. The narrowest scope always wins.

Precedence (narrowest wins):

SESSION  >  ROLE+DATABASE  >  ROLE  >  DATABASE  >  CLUSTER  >  built-in default

13a. CLUSTER Level (ALTER SYSTEM)

Affects the whole server — every database, every user, every session.

postgres=# SHOW work_mem;
 work_mem
----------
 4MB <------
(1 row)

postgres=# ALTER SYSTEM SET work_mem = '8MB';
ALTER SYSTEM
postgres=#
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#
postgres=# SHOW work_mem;
 work_mem
----------
 8MB   <----------------------
(1 row)

postgres=#

13b. DATABASE Level (overrides cluster for one database)

Only sessions connecting to this specific database get this value.

postgres=# ALTER DATABASE postgres SET work_mem = '16MB';
ALTER DATABASE
postgres=#

-- Reconnect to "postgres" to see it take effect
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/run/postgresql" at port "5432".
postgres=#

postgres=#
postgres=# SHOW work_mem;
 work_mem
----------
 16MB  <-------------
(1 row)

postgres=# SELECT d.datname, r.rolname, s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_database d ON d.oid = s.setdatabase
LEFT JOIN pg_roles r ON r.oid = s.setrole
ORDER BY d.datname NULLS LAST, r.rolname NULLS LAST;
 datname  | rolname |    setconfig
----------+---------+-----------------
 postgres |         | {work_mem=16MB}
(1 row)

postgres=# \c raj
You are now connected to database "raj" as user "postgres".
raj=#
raj=# SHOW work_mem;
 work_mem
----------
 8MB  <---------------
(1 row)

raj=#

13c. USER / ROLE Level (overrides cluster and database for one role)

postgres=# ALTER ROLE postgres SET work_mem = '32MB';
ALTER ROLE
postgres=#

-- Combine ROLE + DATABASE for maximum specificity
postgres=# ALTER ROLE postgres IN DATABASE postgres SET work_mem = '64MB';
ALTER ROLE
postgres=#

-- Check the catalog
postgres=# SELECT d.datname, r.rolname, s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_database d ON d.oid = s.setdatabase
LEFT JOIN pg_roles r ON r.oid = s.setrole
ORDER BY d.datname NULLS LAST, r.rolname NULLS LAST;
 datname  | rolname  |    setconfig
----------+----------+-----------------
 postgres | postgres | {work_mem=64MB}    <--------- ROLE(USER)+DB level
 postgres |          | {work_mem=16MB}    <--------- Database level (overrides cluster for one database)
          | postgres | {work_mem=32MB}    <--------- ROLE(USER LEVEL)  overrides cluster and database for one role
(3 rows)

postgres=#

postgres=# \c raj
You are now connected to database "raj" as user "postgres".
raj=#
raj=# SHOW work_mem;
 work_mem
----------
 8MB  <----- CLUSTER LEVEL WE SET
(1 row)

raj=#

The postgres role connecting to the postgres database gets 64MB (the combined ROLE+DATABASE row wins over the role-only row, which wins over the database-only row, which wins over the cluster-wide 8MB).

PostgreSQL checks limits in this order (from most specific to least specific):

  1. ROLE + DATABASE together = 64MB 
  2. ROLE only = 32MB
  3. DATABASE only = 16MB
  4. Whole cluster = 8MB 

13d. SESSION Level (overrides everything, but only for this connection)

[postgres@lxceftsgvdb01 ~]$ psql -d postgres -U postgres
postgres=# SHOW work_mem;
 work_mem
----------
 64MB  <-----------------
(1 row)

postgres=#


-- SET lasts for this session only
postgres=# SET work_mem = '128MB';
SET
postgres=#

postgres=# SHOW work_mem;
 work_mem
----------
 128MB <----------------
(1 row)

postgres=#

-- SET LOCAL is even narrower — lasts only for the current transaction
postgres=# BEGIN;
BEGIN
postgres=*# SET LOCAL work_mem = '256MB';
SET
postgres=*# SHOW work_mem;
 work_mem
----------
 256MB   <---------------- Changed from 128MB to 256MB
(1 row)

postgres=*# COMMIT;
COMMIT
postgres=#
postgres=# SHOW work_mem;
 work_mem
----------
 128MB  <--------------- Came back to session setting 128MB
(1 row)

postgres=#

After COMMIT: work_mem reverted to 128MB (the session-level SET), not 256MB. SET LOCAL only lives inside the transaction. Disconnecting entirely drops the session-level SET and falls back to the role/database/cluster chain.

13e. See the Full Precedence Chain at a Glance

postgres=# SELECT name, setting AS effective_value, source, sourcefile
FROM pg_settings
WHERE name = 'work_mem';
   name   | effective_value | source  | sourcefile
----------+-----------------+---------+------------
 work_mem | 131072          | session |
(1 row)

postgres=#

The source column tells you which level actually won for the current session:

  • session → a SET / SET LOCAL you ran yourself
  • database → ALTER DATABASE ... SET
  • user → ALTER ROLE ... SET
  • override → ALTER SYSTEM (cluster level, via postgresql.auto.conf)
  • configuration file → plain postgresql.conf
  • default → built-in default, nothing overrode it

13f. Classify Every Parameter by Settable Scope

postgres=# SELECT name,
       context,
       CASE context
         WHEN 'internal'           THEN 'READ-ONLY (compile-time)'
         WHEN 'postmaster'         THEN 'CLUSTER only (restart required)'
         WHEN 'sighup'             THEN 'CLUSTER only (reload required)'
         WHEN 'backend'            THEN 'CLUSTER/DATABASE/ROLE (connect time only)'
         WHEN 'superuser-backend'  THEN 'CLUSTER/DATABASE/ROLE (connect time, superuser)'
         WHEN 'superuser'          THEN 'CLUSTER/DATABASE/ROLE/SESSION/TXN (superuser)'
         WHEN 'user'               THEN 'CLUSTER/DATABASE/ROLE/SESSION/TXN (any user)'
       END AS settable_scope
FROM pg_settings
ORDER BY context, name
LIMIT 15;
         name          | context  |              settable_scope
-----------------------+----------+-------------------------------------------
 ignore_system_indexes | backend  | CLUSTER/DATABASE/ROLE (connect time only)
 post_auth_delay       | backend  | CLUSTER/DATABASE/ROLE (connect time only)
 block_size            | internal | READ-ONLY (compile-time)
 data_checksums        | internal | READ-ONLY (compile-time)
 data_directory_mode   | internal | READ-ONLY (compile-time)
 debug_assertions      | internal | READ-ONLY (compile-time)
 huge_pages_status     | internal | READ-ONLY (compile-time)
 in_hot_standby        | internal | READ-ONLY (compile-time)
 integer_datetimes     | internal | READ-ONLY (compile-time)
 max_function_args     | internal | READ-ONLY (compile-time)
 max_identifier_length | internal | READ-ONLY (compile-time)
 max_index_keys        | internal | READ-ONLY (compile-time)
 segment_size          | internal | READ-ONLY (compile-time)
 server_encoding       | internal | READ-ONLY (compile-time)
 server_version        | internal | READ-ONLY (compile-time)
(15 rows)

postgres=#

Quick counts by scope:

-- Fully flexible (all 5 levels)
postgres=# SELECT count(*) AS fully_flexible
FROM pg_settings WHERE context IN ('user','superuser');
 fully_flexible
----------------
            191
(1 row)

postgres=#


-- Cluster-only (conf file or ALTER SYSTEM, never per-session)
postgres=# SELECT count(*) AS cluster_only
FROM pg_settings WHERE context IN ('postmaster','sighup');
 cluster_only
--------------
          163
(1 row)

postgres=#


-- Connect-time only (database/role, but not SET mid-session)
postgres=# SELECT count(*) AS connect_time_only
FROM pg_settings WHERE context IN ('backend','superuser-backend');
 connect_time_only
-------------------
                 6
(1 row)

postgres=#

14. Quick Cheatsheet

TaskCommand
Find config fileSHOW config_file;
View a settingSHOW work_mem; or SELECT * FROM pg_settings WHERE name = 'work_mem';
See non-default settingsSELECT * FROM pg_settings WHERE source != 'default';
See raw file contentsSELECT * FROM pg_file_settings;
Static vs DynamicSELECT name, context FROM pg_settings WHERE context = 'postmaster';
Change at cluster levelALTER SYSTEM SET work_mem = '64MB';
Change at database levelALTER DATABASE mydb SET work_mem = '64MB';
Change at role levelALTER ROLE myuser SET work_mem = '64MB';
Change at session levelSET work_mem = '128MB';
Change for one transactionSET LOCAL work_mem = '256MB';
Remove auto.conf overrideALTER SYSTEM RESET work_mem;
Apply without restartSELECT pg_reload_conf();
Find pending restartsSELECT * FROM pg_settings WHERE pending_restart = true;
Apply with restartpg_ctl restart -D $PGDATA
Check for config errorsSELECT * FROM pg_file_settings WHERE error IS NOT NULL;

15. Sample postgresql.conf

-- Server Config
# =============================================================================
# RAM   : 128 GB & CPU(s): 8  & OS: Redhat Linux 8
# =============================================================================

-- Sample parameters

# =============================================================================
# CONNECTION SETTINGS
# =============================================================================
listen_addresses = '*'                          
port = 6543                                     
max_connections = 1500
unix_socket_permissions = 0700

# =============================================================================
# SSL/TLS SECURITY SETTINGS
# =============================================================================
ssl = on
ssl_ca_file = '/pgData/pgsql17/certs/root.crt'
ssl_cert_file = '/pgData/pgsql17/certs/server.crt'
ssl_key_file = '/pgData/pgsql17/certs/server.key'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'       
ssl_prefer_server_ciphers = on
ssl_min_protocol_version = 'TLSv1.2'

# =============================================================================
# MEMORY SETTINGS
# =============================================================================
shared_buffers = 32GB                           # min 128kB
work_mem = 2796kB                               # min 64kB
maintenance_work_mem = 2GB                      # min 1MB
dynamic_shared_memory_type = posix              
effective_cache_size = 96GB
temp_tablespaces = 'pg_tstmp'

# =============================================================================
# PARALLEL EXECUTION & WORKER PROCESSES
# =============================================================================
effective_io_concurrency = 200                  # 1-1000; 0 disables prefetching
max_worker_processes = 8                        # (change requires restart)
max_parallel_workers_per_gather = 4             # taken from max_parallel_workers
max_parallel_maintenance_workers = 4            # taken from max_parallel_workers
max_parallel_workers = 8                        # maximum number of max_worker_processes that

# =============================================================================
# QUERY PLANNING & OPTIMIZATION
# =============================================================================
random_page_cost = 1.1                          # same scale as above
default_statistics_target = 1500                # range 1-10000

# =============================================================================
# WRITE-AHEAD LOGGING (WAL) SETTINGS
# =============================================================================
wal_level = replica                             
wal_buffers = 16MB                              # min 32kB, -1 sets based on shared_buffers
wal_log_hints = on                              
full_page_writes = on                           
max_wal_senders = 10                            # max number of walsender processes
wal_keep_size = 128000                          # in megabytes; 0 disables
hot_standby = on                                # 'off' disallows queries during recovery

# =============================================================================
# CHECKPOINT & WAL SIZE SETTINGS
# =============================================================================
checkpoint_completion_target = 0.9              
max_wal_size = 4GB
min_wal_size = 1GB

# =============================================================================
# ARCHIVING 
# =============================================================================
archive_mode = on                               
archive_command = 'rsync -a %p /pgArch/pgsql17/arch/%f'

#archive_command = '/pgBackup/pgsql17/backup/arch_copy.sh %p %f && pgbackup'
#archive_cleanup_command = 'pg_archivecleanup /pgArch/pgsql17/arch/ %r'

# =============================================================================
# LOGGING SETTINGS
# =============================================================================
logging_collector = on                          
log_destination = 'stderr'
log_directory = '/pgLog/pgsql17/logs'
log_filename = 'postgres-%Y-%m-%d_%HMS.log'
log_file_mode = 0600
log_connections = on
log_disconnections = on
log_error_verbosity = verbose
log_hostname = on
log_line_prefix = '%m [%p] user=%u,database=%d,app_name=%a client_host=%h '
log_lock_waits = on
log_statement = 'ddl'
log_min_duration_statement = 2000   # log statements taking longer than 2000ms
log_timezone = 'Asia/Singapore'

# Optional
#log_truncate_on_rotation = on       # truncate rather than append on rotation
#log_rotation_age = 1d               # Automatic rotation by time
#log_rotation_size = 0               # Automatic rotation by size (0=disabled)
#log_duration = off

# ========================================================================
# AUTOVACUUM SETTINGS
# ========================================================================
autovacuum_naptime = 10min
autovacuum_vacuum_threshold = 10000
autovacuum_analyze_threshold = 10000
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 600

# ========================================================================
# QUERY TRACKING SETTINGS
# ========================================================================
track_activities = on
track_counts = on
track_io_timing = off
track_activity_query_size = 10240

# ========================================================================
# QUERY PLANNING SETTINGS
# ========================================================================
seq_page_cost = 1.0                 # measured on an arbitrary scale
random_page_cost = 2.0              # same scale as above
cpu_tuple_cost = 0.03               # same scale as above
from_collapse_limit = 20
join_collapse_limit = 20            # 1 disables collapsing of explicit JOIN clauses
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: linkedin.com/in/rajasekhar-amudala

MVCC in PostgreSQL

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

MVCC in PostgreSQL

Table of Contents

1. The Core Concept (Why MVCC?)

MVCC stands for Multi-Version Concurrency Control.

Its primary goal is simple: Readers should never block Writers, and Writers should never block Readers.

Instead of locking a row when someone is updating it (which makes everyone else wait), PostgreSQL keeps multiple versions of the same row active at the same time. Different transactions see different versions depending on when they started.

2. Hidden Columns (xmin & xmax)

Every table you create in PostgreSQL has hidden system columns. The two most important ones for MVCC are:

  • xmin: The Transaction ID that created/inserted this row version.
  • xmax: The Transaction ID that deleted or updated this row version. (If it is 0, the row is still actively alive).
-- To view these columns by explicitly querying them:
SELECT xmin, xmax, * FROM table_name;

3. Create a Test Table

-- Create a simple products table and  insert our first record and see how Postgres assigns the transaction ID.

[postgres@pgdb01 ~]$ psql -c "CREATE TABLE products (id SERIAL PRIMARY KEY,name TEXT,price INT);"
CREATE TABLE
[postgres@pgdb01 ~]$

-- Insert a row
[postgres@pgdb01 ~]$ psql -c "INSERT INTO products (name, price) VALUES ('Laptop', 1000);"
INSERT 0 1
[postgres@pgdb01 ~]$ 

-- Query the row with xmin and xmax
[postgres@pgdb01 ~]$ psql -c "SELECT xmin, xmax, id, name, price FROM products;"
 xmin | xmax | id |  name  | price
------+------+----+--------+-------
 2156 |    0 |  1 | Laptop |  1000
(1 row)

[postgres@pgdb01 ~]$
  • xmin = 2156: xmin represents the transaction ID that created the row version. xmin = 2156
  • xmax = 0: xmax represents the transaction ID that deleted or updated. xmax = 0 means No one has deleted/updated it yet . It is actively visible to everyone.

4. UPDATE Creates a New Version

Now, let’s see MVCC in action. Open two separate database sessions/terminal windows.

Terminal 1 (The Writer): Start a transaction and update the price, but do not commit yet.

postgres=# BEGIN;
BEGIN
postgres=*# SELECT txid_current();
 txid_current
--------------
         2157
(1 row)

postgres=*# UPDATE products SET price = 1200 WHERE id = 1;
UPDATE 1
postgres=*#

Terminal 2 (The Reader): Check what Terminal 2 sees:

[postgres@pgdb01 ~]$ psql -c "SELECT xmin, xmax, id, name, price FROM products;"
 xmin | xmax | id |  name  | price
------+------+----+--------+-------
 2156 | 2157 |  1 | Laptop |  1000
(1 row)

[postgres@pgdb01 ~]$
Internally, PostgreSQL creates a new row version.

Before UPDATE

Row Version A
+----------------+
| id = 1         |
| price = 1000   |
+----------------+


After UPDATE

Old Version                New Version
+----------------+          +----------------+
| id = 1         |          | id = 1         |
| price = 1000   |          | price = 1200   |
| xmax = 2157    |          | xmin = 2157    |
+----------------+          +----------------+
        |                           |
        | old version               | new version
        v                           v
      xmax                         xmin

xmax = old version (xmax refers Dead rows)
xmin = new version
The old row version is not immediately physically removed.
  • The reader still sees the original price of 1000. They are not blocked!
  • The row now has an xmax of 2157. This tells Postgres: “Transaction 2157 is currently changing or deleting this row. If you aren’t Transaction 2157, keep looking at the old data.”
  • Under the hood, Postgres has already written a new row version with a price of 1200 and xmin = 2157, but it is completely invisible to Terminal 2 because transaction 2157 hasn’t committed yet.

5. After COMMIT

Let’s finalize the transaction and see the transition.

Terminal 1 (The Writer): Commit the change.

postgres=# BEGIN;
BEGIN
postgres=*# SELECT txid_current();
 txid_current
--------------
         2157
(1 row)

postgres=*# UPDATE products SET price = 1200 WHERE id = 1;
UPDATE 1
postgres=*# COMMIT;   <------------------ (only run COMMIT)
COMMIT
postgres=#

Terminal 2 (The Reader): Query the table again. What Terminal 2 sees now

[postgres@pgdb01 ~]$ psql -c "SELECT xmin, xmax, id, name, price FROM products;"
 xmin | xmax | id |  name  | price
------+------+----+--------+-------
 2157 |    0 |  1 | Laptop |  1200
(1 row)

[postgres@pgdb01 ~]$
  • The old row version (price 1000) is now marked as dead(invisible).
  • The new row version (price 1200) is now alive(current row) with xmin = 2157 and xmax = 0.

6. DELETE Doesn’t Actually Delete

In Postgres, a DELETE statement does not physically erase data from the hard drive immediately. It just changes a flag.

postgres=# BEGIN;
BEGIN
postgres=*# DELETE FROM products WHERE id = 1;
DELETE 1
postgres=*# SELECT txid_current();
 txid_current
--------------
         2158
(1 row)

postgres=*# COMMIT;
COMMIT
postgres=#


A DELETE Internally, simply marks the existing row as dead.

Before DELETE

+----------------+
| id=1           |
| pirce = 1200   |
+----------------+

After DELETE

+----------------+
| id=1           |
| price = 1200   |
| xmax = 2158    |
+----------------+

Later, VACUUM can remove dead tuples when they are no longer needed by active transactions.

Under the hood, Postgres simply updated the existing row’s xmax value to 2158. Because transaction 2158 is committed, Postgres now knows that this row is dead and shouldn’t be shown to any new queries. The actual space on the disk won’t be freed until we vacuum.

7. Dead tuples and VACUUM

Because UPDATE creates new rows and DELETE just marks them as dead, database will accumulate “dead tuples” (bloat) over time.
In simple way, Since UPDATE and DELETE don’t remove old rows, dead rows pile up. This is called bloat.

Postgres uses a process called VACUUM to find these dead rows, clear them out, and mark that space as reusable for future inserts.

-- 1. Check how many dead rows are lurking a table

[postgres@pgdb01 ~]$ psql -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'products';"
 relname  | n_live_tup | n_dead_tup
----------+------------+------------
 products |          0 |          2   <--------------------
(1 row)

[postgres@pgdb01 ~]$


-- 2. Run a manual vacuum to clean up

[postgres@pgdb01 ~]$ psql -c "VACUUM VERBOSE products;"
INFO:  vacuuming "postgres.public.products"
INFO:  table "products": truncated 1 to 0 pages
INFO:  finished vacuuming "postgres.public.products": index scans: 1
pages: 1 removed, 0 remain, 1 scanned (100.00% of total)
tuples: 2 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 2159, which was 1 XIDs old when operation ended
new relfrozenxid: 2159, which is 4 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 1 pages from table (100.00% of total) had 1 dead item identifiers removed
index "products_pkey": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 0.000 MB/s, avg write rate: 11.905 MB/s
buffer usage: 61 hits, 0 misses, 8 dirtied
WAL usage: 11 records, 8 full page images, 47529 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  vacuuming "postgres.pg_toast.pg_toast_65663"
INFO:  finished vacuuming "postgres.pg_toast.pg_toast_65663": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 2160, which was 0 XIDs old when operation ended
new relfrozenxid: 2160, which is 5 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 22.978 MB/s, avg write rate: 22.978 MB/s
buffer usage: 27 hits, 1 misses, 1 dirtied
WAL usage: 1 records, 1 full page images, 8097 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
[postgres@pgdb01 ~]$


-- 3. Check again; dead rows should now be 0

[postgres@pgdb01 ~]$ psql -c "SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'products';"
 relname  | n_live_tup | n_dead_tup
----------+------------+------------
 products |          0 |          0  
(1 row)

[postgres@pgdb01 ~]$

Note: PostgreSQL runs an Autovacuum background process automatically, so you rarely need to run manual vacuums in production.

8. Quick Summary Cheatsheet

To keep MVCC simple, remember these four rules:

OperationWhat Postgres Actually Does
INSERTCreates a row with xmin = Current TXID.
DELETEMark row as dead, does not physically erase it. Sets xmax = Current TXID
UPDATE INSERT new version + DELETE (mark old version as dead).
VACUUMSweeps up the dead rows to reclaim storage space.
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: linkedin.com/in/rajasekhar-amudala

WAL Commit Process

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

PostgreSQL WAL Commit Process

1. The Modification (In-Memory)

When a user executes a data-modifying query (like INSERT, UPDATE, or DELETE):

  • The change is made to the table or index data inside the Shared Buffers (RAM). The page in memory is now marked as “dirty.”

  • Simultaneously, a record of this exact change is constructed and written sequentially into the WAL Buffers (also in RAM).

2. The COMMIT Command Issued

When the client sends the COMMIT command, PostgreSQL must guarantee that this change will survive a sudden power outage or crash before it can tell the user “Success.”

3. Flushing to Disk (XLogFlush)

To ensure durability without the massive overhead of writing entire data pages to disk immediately, Postgres uses the Write-Ahead Logging protocol:

  • The internal function XLogFlush() is called.

  • It identifies the exact position (Log Sequence Number, or LSN) of the commit record in the WAL Buffer.

  • It issues a synchronous write to flush all WAL buffers up to that LSN out of RAM and into the current 16MB WAL segment file on permanent storage.

  • An fsync() system call is issued to ensure the OS cache actually commits the data to physical disk platters or flash memory.

4. Acknowledgment to the Client

Once the operating system confirms that the WAL record is safely written to the physical storage, the transaction status is updated to “committed” in the commit log (CLOG), and PostgreSQL sends a success acknowledgment back to the client application.

Crucial Architectural Concepts

  • Write-Ahead Rule: The core rule of WAL is that changes to data pages must not be written to permanent database files on disk until the log records describing those changes have been flushed to stable storage. If the server crashes, Postgres reads the WAL from the last checkpoint forward and reapplies the changes (“redoes” them).

  • Asynchronous Commit Alternative: If you set the configuration parameter synchronous_commit = off, Postgres will acknowledge the client’s COMMIT before the WAL buffer is flushed to disk (relying on the WAL Writer background process to flush it within roughly 3 times wal_writer_delay). This massively increases write throughput but introduces a risk of losing up to a split-second of recent transactions if the server suddenly loses power.

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: linkedin.com/in/rajasekhar-amudala

Database Directory Layout

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

PostgreSQL Physical Directory Layout

Installation Directory Layout

[postgres@pgdb02 ~]$ ls -ltr /usr/pgsql-17/
total 16
drwxr-xr-x. 3 postgres postgres 23 Jun 4 20:38 doc
drwxr-xr-x. 8 postgres postgres 4096 Jun 22 21:08 share
drwxr-xr-x. 2 postgres postgres 4096 Jun 22 21:08 bin
drwxr-xr-x. 3 postgres postgres 4096 Jun 22 21:08 lib
[postgres@pgdb02 ~]$

[postgres@pgdb02 ~]$ ls -lt /usr/pgsql-17/bin
total 14492
-rwxr-xr-x. 1 postgres postgres 71072 May 21 13:49 clusterdb
-rwxr-xr-x. 1 postgres postgres 75240 May 21 13:49 createdb
-rwxr-xr-x. 1 postgres postgres 75624 May 21 13:49 createuser
-rwxr-xr-x. 1 postgres postgres 66632 May 21 13:49 dropdb
-rwxr-xr-x. 1 postgres postgres 66576 May 21 13:49 dropuser
-rwxr-xr-x. 1 postgres postgres 113288 May 21 13:49 initdb
-rwxr-xr-x. 1 postgres postgres 41400 May 21 13:49 oid2name
-rwxr-xr-x. 1 postgres postgres 96824 May 21 13:49 pg_amcheck
-rwxr-xr-x. 1 postgres postgres 41056 May 21 13:49 pg_archivecleanup
-rwxr-xr-x. 1 postgres postgres 142928 May 21 13:49 pg_basebackup
-rwxr-xr-x. 1 postgres postgres 162984 May 21 13:49 pgbench
-rwxr-xr-x. 1 postgres postgres 49632 May 21 13:49 pg_checksums
-rwxr-xr-x. 1 postgres postgres 116872 May 21 13:49 pg_combinebackup
-rwxr-xr-x. 1 postgres postgres 36656 May 21 13:49 pg_config
-rwxr-xr-x. 1 postgres postgres 44984 May 21 13:49 pg_controldata
-rwxr-xr-x. 1 postgres postgres 70768 May 21 13:49 pg_createsubscriber
-rwxr-xr-x. 1 postgres postgres 62296 May 21 13:49 pg_ctl
-rwxr-xr-x. 1 postgres postgres 447368 May 21 13:49 pg_dump
-rwxr-xr-x. 1 postgres postgres 117680 May 21 13:49 pg_dumpall
-rwxr-xr-x. 1 postgres postgres 36952 May 21 13:49 pg_isready
-rwxr-xr-x. 1 postgres postgres 92000 May 21 13:49 pg_receivewal
-rwxr-xr-x. 1 postgres postgres 54544 May 21 13:49 pg_recvlogical
-rwxr-xr-x. 1 postgres postgres 53808 May 21 13:49 pg_resetwal
-rwxr-xr-x. 1 postgres postgres 194496 May 21 13:49 pg_restore
-rwxr-xr-x. 1 postgres postgres 108584 May 21 13:49 pg_rewind
-rwxr-xr-x. 1 postgres postgres 41120 May 21 13:49 pg_test_fsync
-rwxr-xr-x. 1 postgres postgres 32632 May 21 13:49 pg_test_timing
-rwxr-xr-x. 1 postgres postgres 171856 May 21 13:49 pg_upgrade
-rwxr-xr-x. 1 postgres postgres 95928 May 21 13:49 pg_verifybackup
-rwxr-xr-x. 1 postgres postgres 95968 May 21 13:49 pg_waldump
-rwxr-xr-x. 1 postgres postgres 41184 May 21 13:49 pg_walsummary
-rwxr-xr-x. 1 postgres postgres 10875088 May 21 13:49 postgres
-rwxr-xr-x. 1 postgres postgres 729400 May 21 13:49 psql
-rwxr-xr-x. 1 postgres postgres 83816 May 21 13:49 reindexdb
-rwxr-xr-x. 1 postgres postgres 88344 May 21 13:49 vacuumdb
-rwxr-xr-x. 1 postgres postgres 41200 May 21 13:49 vacuumlo
-rwxr-xr-x. 1 postgres postgres 2167 May 21 13:49 postgresql-17-check-db-dir
-rwxr-xr-x. 1 postgres postgres 9617 May 21 13:49 postgresql-17-setup
[postgres@pgdb02 ~]$

Database (Data) Directory Layout

[postgres@pgdb02 ~]$ ls -l /pgData/pgsql17/data
total 68
drwx------. 9 postgres postgres    89 Jun 30 19:40 base
-rw-------. 1 postgres postgres    30 Jul  3 14:30 current_logfiles
drwx------. 2 postgres postgres  4096 Jul  3 14:58 global
drwx------. 2 postgres postgres   162 Jul  3 00:00 log
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_commit_ts
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_dynshmem
-rw-------. 1 postgres postgres  5798 Jun 29 23:45 pg_hba.conf
-rw-------. 1 postgres postgres  2640 Jun 22 21:11 pg_ident.conf
drwx------. 4 postgres postgres    68 Jul  3 14:30 pg_logical
drwx------. 4 postgres postgres    36 Jun 22 21:11 pg_multixact
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_notify
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_replslot
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_serial
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_snapshots
drwx------. 2 postgres postgres     6 Jul  3 14:29 pg_stat
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_stat_tmp
drwx------. 2 postgres postgres    18 Jun 22 21:11 pg_subtrans
drwx------. 2 postgres postgres    19 Jul  2 18:32 pg_tblspc
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_twophase
-rw-------. 1 postgres postgres     3 Jun 22 21:11 PG_VERSION
lrwxrwxrwx. 1 postgres postgres    18 Jun 22 21:11 pg_wal -> /pgWal/pgsql17/wal
drwx------. 2 postgres postgres    18 Jun 22 21:11 pg_xact
-rw-------. 1 postgres postgres    88 Jun 22 21:11 postgresql.auto.conf
-rw-------. 1 postgres postgres 30881 Jun 29 23:37 postgresql.conf
-rw-------. 1 postgres postgres    55 Jul  3 14:30 postmaster.opts
-rw-------. 1 postgres postgres    89 Jul  3 14:30 postmaster.pid
[postgres@pgdb02 ~]$
NameDirectory or FileDescription
baseDirectoryContains subdirectories for each database. Each database is stored using its own database OID.
current_logfilesFileStores information about the log file currently being written by the logging collector process.
globalDirectoryContains cluster-wide shared system catalog tables such as pg_database, pg_authid, pg_tablespace, etc.
logDirectoryStores PostgreSQL server logs and error log files.
pg_commit_tsDirectoryStores transaction commit timestamp data when track_commit_timestamp is enabled.
pg_dynshmemDirectoryStores files used by PostgreSQL dynamic shared memory subsystem.
pg_hba.confFileClient authentication configuration file. Controls which users and hosts can connect.
pg_ident.confFileStores user name mapping rules between operating system users and database users.
pg_logicalDirectoryStores metadata and status information used for logical replication and logical decoding.
pg_multixactDirectoryStores multitransaction status information used for shared row-level locking.
pg_notifyDirectoryStores LISTEN and NOTIFY asynchronous notification status data.
pg_replslotDirectoryStores physical and logical replication slot information.
pg_serialDirectoryStores metadata related to committed serializable transactions.
pg_snapshotsDirectoryStores exported transaction snapshot files.
pg_statDirectoryStores permanent statistics data used by PostgreSQL statistics subsystem.
pg_stat_tmpDirectoryStores temporary statistics files generated by statistics collector.
pg_subtransDirectoryStores subtransaction status information for nested transactions.
pg_tblspcDirectoryContains symbolic links pointing to user-created tablespaces.
pg_twophaseDirectoryStores prepared transaction state files used for two-phase commit.
PG_VERSIONFileStores the PostgreSQL major version number of the database cluster.
pg_walDirectory (Symlink)Stores WAL (Write Ahead Log) files for crash recovery and replication. Example: /pgWal/pgsql17/wal
pg_xactDirectoryStores transaction commit status information and transaction metadata logs.
postgresql.auto.confFileStores configuration parameters automatically updated by ALTER SYSTEM.
postgresql.confFileMain PostgreSQL configuration file containing server parameter settings.
postmaster.optsFileStores command-line options used when PostgreSQL server was last started.
postmaster.pidFileStores Postmaster PID, startup time, port number, socket path, listen address, and shared memory details.

Difference between Base Directory and Global Directory

In PostgreSQL, both the Base Directory and the Global Directory reside within the PGDATA (the data cluster) folder. The primary difference lies in the scope and type of data they store.

postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres | <----- pg_default physical location: Located at $PGDATA/base/
 pg_global  | postgres | <----- pg_global physical location: Located at $PGDATA/global/
(2 rows)

postgres=#

-- List of Files and Directoires from Global Directory

[postgres@lxceftsgvdb01 ~]$ ls -la /pgData/pgsql17/data/global
total 580
drwx------.  2 postgres postgres  4096 Jul  3 16:03 .
drwx------. 19 postgres postgres  4096 Jul  3 16:01 ..
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1213
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 1213_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1213_vm
-rw-------.  1 postgres postgres     0 Jul  3 16:01 1214
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1232
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1233
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1260
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 1260_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1260_vm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1261
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 1261_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1261_vm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1262
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 1262_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1262_vm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2396
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 2396_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2396_vm
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2397
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2671
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2672
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2676
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2677
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2694
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2695
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2697
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2698
-rw-------.  1 postgres postgres     0 Jul  3 16:01 2846
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2847
-rw-------.  1 postgres postgres     0 Jul  3 16:01 2964
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2965
-rw-------.  1 postgres postgres     0 Jul  3 16:01 2966
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2967
-rw-------.  1 postgres postgres     0 Jul  3 16:01 3592
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 3593
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4060
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4061
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4175
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4176
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4177
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4178
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4181
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4182
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4183
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4184
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4185
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4186
-rw-------.  1 postgres postgres     0 Jul  3 16:01 6000
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6001
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6002
-rw-------.  1 postgres postgres     0 Jul  3 16:01 6100
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6114
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6115
-rw-------.  1 postgres postgres     0 Jul  3 16:01 6243
-rw-------.  1 postgres postgres     0 Jul  3 16:01 6244
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6245
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6246
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6247 
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 6302
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 6303
-rw-------.  1 postgres postgres  8192 Jul  3 16:06 pg_control
-rw-------.  1 postgres postgres   524 Jul  3 16:01 pg_filenode.map
-rw-------.  1 postgres postgres 28724 Jul  3 16:03 pg_internal.init
[postgres@lxceftsgvdb01 ~]$

-- List of Files and Directories from Base Directory 

[postgres@lxceftsgvdb01 ~]$ ls -la /pgData/pgsql17/data/base
total 40
drwx------.  5 postgres postgres   33 Jul  3 16:01 .
drwx------. 19 postgres postgres 4096 Jul  3 16:01 ..
drwx------.  2 postgres postgres 8192 Jul  3 16:03 1
drwx------.  2 postgres postgres 8192 Jul  3 16:01 4
drwx------.  2 postgres postgres 8192 Jul  3 16:02 5
[postgres@lxceftsgvdb01 ~]$

1. The Global Directory (pg_global)

The global directory contains data that is shared across the entire database cluster.

  • Path: $PGDATA/global/
  • Scope: Cluster-wide.
  • Contents: It stores “Shared System Catalogs.” These are tables that contain information applicable to every database in the instance.
  • Key Files:
    • pg_database: Information about all databases in the cluster.
    • pg_authid: User roles and passwords (since a user can log into any database in the cluster).
    • pg_tablespace: Information about defined storage locations.
  • Identification: Objects in this directory always have an OID (Object Identifier), but they do not belong to a specific database ID.

Lists only actual shared/global catalog tables (no indexes, no TOAST tables) from the global directory

-- Let's look at inside postgres global directory (only actual tables, excluding indexes and TOAST)

[postgres@lxceftsgvdb01 ~]$ cat list_global_tables.sh
#!/bin/bash
cd $PGDATA
# Header
printf "%-8s | %-40s | %-10s | %-15s | %-10s\n" \
"OID" "OBJECT_NAME" "TABLEOID" "SCHEMA" "TABLESPACE"

printf -- "-----------------------------------------------------------------------------------------------\n"

# Loop through files in global directory
for i in $(find global -maxdepth 1 -type f | awk -F/ '{print $2}' | egrep '^[0-9]' | egrep -v 'fsm|vm')
do
    # Get object details from oid2name
    result=$(oid2name -x -S -q -o $i)

    # Parse fields
    oid=$(echo $result | awk '{print $1}')
    name=$(echo $result | awk '{print $2}')
    tableoid=$(echo $result | awk '{print $3}')
    schema=$(echo $result | awk '{print $4}')
    db=$(echo $result | awk '{print $5}')

    # Check relation type from pg_class
    relkind=$(psql -At -c "SELECT relkind FROM pg_class WHERE oid=$oid;" 2>/dev/null)

    # Show only actual tables (relkind = r)
    if [ "$relkind" = "r" ]; then
        printf "%-8s | %-40s | %-10s | %-15s | %-10s\n" \
        "$oid" "$name" "$tableoid" "$schema" "$db"
    fi
done
[postgres@lxceftsgvdb01 ~]$

[postgres@lxceftsgvdb01 ~]$ ./list_global_tables.sh
OID      | OBJECT_NAME                              | TABLEOID   | SCHEMA          | TABLESPACE
-----------------------------------------------------------------------------------------------
1262     | pg_database                              | 1262       | pg_catalog      | pg_global
2964     | pg_db_role_setting                       | 2964       | pg_catalog      | pg_global
1213     | pg_tablespace                            | 1213       | pg_catalog      | pg_global
1260     | pg_authid                                | 1260       | pg_catalog      | pg_global
1261     | pg_auth_members                          | 1261       | pg_catalog      | pg_global
1214     | pg_shdepend                              | 1214       | pg_catalog      | pg_global
2396     | pg_shdescription                         | 2396       | pg_catalog      | pg_global
6000     | pg_replication_origin                    | 6000       | pg_catalog      | pg_global
3592     | pg_shseclabel                            | 3592       | pg_catalog      | pg_global
6243     | pg_parameter_acl                         | 6243       | pg_catalog      | pg_global
6100     | pg_subscription                          | 6100       | pg_catalog      | pg_global
[postgres@pgdb02 ~]$

[postgres@lxceftsgvdb01 ~]$ tree /pgData/pgsql17/data/global
/pgData/pgsql17/data/global
├── 1213
├── 1213_fsm
├── 1213_vm
├── 1214
├── 1232
├── 1233
├── 1260
├── 1260_fsm
├── 1260_vm
├── 1261
├── 1261_fsm
├── 1261_vm
├── 1262
├── 1262_fsm
├── 1262_vm
├── 2396
├── 2396_fsm
├── 2396_vm
├── 2397
├── 2671
├── 2672
├── 2676
├── 2677
├── 2694
├── 2695
├── 2697
├── 2698
├── 2846
├── 2847
├── 2964
├── 2965
├── 2966
├── 2967
├── 3592
├── 3593
├── 4060
├── 4061
├── 4175
├── 4176
├── 4177
├── 4178
├── 4181
├── 4182
├── 4183
├── 4184
├── 4185
├── 4186
├── 6000
├── 6001
├── 6002
├── 6100
├── 6114
├── 6115
├── 6243
├── 6244
├── 6245
├── 6246
├── 6247
├── 6302
├── 6303
├── pg_control
├── pg_filenode.map
└── pg_internal.init

0 directories, 63 files
[postgres@lxceftsgvdb01 ~]$

2. The Base Directory (pg_base)

The base directory contains the actual user data and system catalogs that are private to each specific database.

  • Path: $PGDATA/base/
  • Scope: Database-specific.
  • Structure: Inside base, there is a subdirectory for every database in the cluster, named after the database’s OID (e.g., $PGDATA/base/16384/).
  • Contents:
    • User Tables and Indexes: The actual data you insert into your tables.
    • Local System Catalogs: Tables like pg_class, pg_attribute, and pg_type which describe the structure of that specific database.
  • Identification: If you want to find where a specific table is stored, you look in base/[Database OID]/[Table Filenode]
[postgres@pgdb02 ~]$ psql -c "SELECT oid, datname FROM pg_database order by oid;"
 oid |  datname
-----+-----------
   1 | template1
   4 | template0
   5 | postgres
(3 rows)

[postgres@pgdb02 ~]$
[postgres@lxceftsgvdb01 ~]$ ls /pgData/pgsql17/data/base | sort -n
1  <---- template1
4  <---- template0
5  <---- postgres
[postgres@pgdb02 ~]$

-- Let's look at inside postgres Base directory (only actual tables, excluding indexes and TOAST)

[postgres@lxceftsgvdb01 ~]$ cat list_base_tables.sh
#!/bin/bash

export PGHOME=/usr/pgsql-17
export PGDATA=/pgData/pgsql17/data
export PATH=$PGHOME/bin:$PATH

DBNAME="postgres"

# Get database OID
DBOID=$(psql -At -d $DBNAME -c "SELECT oid FROM pg_database WHERE datname='$DBNAME'")

# Header
printf "%-8s | %-40s | %-10s | %-15s | %-10s\n" \
"OID" "OBJECT_NAME" "TABLEOID" "SCHEMA" "Tablespace"

printf -- "-----------------------------------------------------------------------------------------------\n"

# Use absolute path ($PGDATA/base/)
for i in $(find $PGDATA/base/$DBOID -maxdepth 1 -type f | awk -F/ '{print $NF}' | egrep '^[0-9]' | egrep -v 'fsm|vm')
do
    # Get object details
    result=$(oid2name -d $DBNAME -x -q -o $i)

    # Parse fields
    oid=$(echo "$result" | awk '{print $1}')
    name=$(echo "$result" | awk '{print $2}')
    tableoid=$(echo "$result" | awk '{print $3}')
    schema=$(echo "$result" | awk '{print $4}')
    db=$(echo "$result" | awk '{print $5}')

    # Check relation type
    relkind=$(psql -At -d $DBNAME -c "SELECT relkind FROM pg_class WHERE oid=$oid;" 2>/dev/null)

    # Show only actual tables
    if [ "$relkind" = "r" ]; then
        printf "%-8s | %-40s | %-10s | %-15s | %-10s\n" \
        "$oid" "$name" "$tableoid" "$schema" "$db"
    fi
done
[postgres@lxceftsgvdb01 ~]$

[postgres@lxceftsgvdb01 ~]$ ./list_base_tables.sh

OID      | OBJECT_NAME                              | TABLEOID   | SCHEMA          | TABLESPACE
-----------------------------------------------------------------------------------------------
1255     | pg_proc                                  | 1255       | pg_catalog      | pg_default
1247     | pg_type                                  | 1247       | pg_catalog      | pg_default
1249     | pg_attribute                             | 1249       | pg_catalog      | pg_default
1259     | pg_class                                 | 1259       | pg_catalog      | pg_default
2604     | pg_attrdef                               | 2604       | pg_catalog      | pg_default
2606     | pg_constraint                            | 2606       | pg_catalog      | pg_default
2611     | pg_inherits                              | 2611       | pg_catalog      | pg_default
2610     | pg_index                                 | 2610       | pg_catalog      | pg_default
2617     | pg_operator                              | 2617       | pg_catalog      | pg_default
2753     | pg_opfamily                              | 2753       | pg_catalog      | pg_default
2616     | pg_opclass                               | 2616       | pg_catalog      | pg_default
2601     | pg_am                                    | 2601       | pg_catalog      | pg_default
2602     | pg_amop                                  | 2602       | pg_catalog      | pg_default
2603     | pg_amproc                                | 2603       | pg_catalog      | pg_default
2612     | pg_language                              | 2612       | pg_catalog      | pg_default
2995     | pg_largeobject_metadata                  | 2995       | pg_catalog      | pg_default
2613     | pg_largeobject                           | 2613       | pg_catalog      | pg_default
2600     | pg_aggregate                             | 2600       | pg_catalog      | pg_default
2619     | pg_statistic                             | 2619       | pg_catalog      | pg_default
3381     | pg_statistic_ext                         | 3381       | pg_catalog      | pg_default
3429     | pg_statistic_ext_data                    | 3429       | pg_catalog      | pg_default
2618     | pg_rewrite                               | 2618       | pg_catalog      | pg_default
2620     | pg_trigger                               | 2620       | pg_catalog      | pg_default
3466     | pg_event_trigger                         | 3466       | pg_catalog      | pg_default
2609     | pg_description                           | 2609       | pg_catalog      | pg_default
2605     | pg_cast                                  | 2605       | pg_catalog      | pg_default
3501     | pg_enum                                  | 3501       | pg_catalog      | pg_default
2615     | pg_namespace                             | 2615       | pg_catalog      | pg_default
2607     | pg_conversion                            | 2607       | pg_catalog      | pg_default
2608     | pg_depend                                | 2608       | pg_catalog      | pg_default
3602     | pg_ts_config                             | 3602       | pg_catalog      | pg_default
3603     | pg_ts_config_map                         | 3603       | pg_catalog      | pg_default
3600     | pg_ts_dict                               | 3600       | pg_catalog      | pg_default
3601     | pg_ts_parser                             | 3601       | pg_catalog      | pg_default
3764     | pg_ts_template                           | 3764       | pg_catalog      | pg_default
3079     | pg_extension                             | 3079       | pg_catalog      | pg_default
2328     | pg_foreign_data_wrapper                  | 2328       | pg_catalog      | pg_default
1417     | pg_foreign_server                        | 1417       | pg_catalog      | pg_default
1418     | pg_user_mapping                          | 1418       | pg_catalog      | pg_default
3118     | pg_foreign_table                         | 3118       | pg_catalog      | pg_default
3256     | pg_policy                                | 3256       | pg_catalog      | pg_default
826      | pg_default_acl                           | 826        | pg_catalog      | pg_default
3394     | pg_init_privs                            | 3394       | pg_catalog      | pg_default
3596     | pg_seclabel                              | 3596       | pg_catalog      | pg_default
3456     | pg_collation                             | 3456       | pg_catalog      | pg_default
3350     | pg_partitioned_table                     | 3350       | pg_catalog      | pg_default
3541     | pg_range                                 | 3541       | pg_catalog      | pg_default
3576     | pg_transform                             | 3576       | pg_catalog      | pg_default
2224     | pg_sequence                              | 2224       | pg_catalog      | pg_default
6104     | pg_publication                           | 6104       | pg_catalog      | pg_default
6237     | pg_publication_namespace                 | 6237       | pg_catalog      | pg_default
6106     | pg_publication_rel                       | 6106       | pg_catalog      | pg_default
6102     | pg_subscription_rel                      | 6102       | pg_catalog      | pg_default
14382    | sql_features                             | 14382      | information_schema | pg_default
14387    | sql_implementation_info                  | 14387      | information_schema | pg_default
14392    | sql_parts                                | 14392      | information_schema | pg_default
14397    | sql_sizing                               | 14397      | information_schema | pg_default
[postgres@lxceftsgvdb01 ~]$

-- Let's look at inside postgres Base directory (Complete List)

[postgres@lxceftsgvdb01 ~]$ tree /pgData/pgsql17/data/base/5
/pgData/pgsql17/data/base/5
├── 112
├── 113
├── 1247
├── 1247_fsm
├── 1247_vm
├── 1249
├── 1249_fsm
├── 1249_vm
├── 1255
├── 1255_fsm
├── 1255_vm
├── 1259
├── 1259_fsm
├── 1259_vm
├── 1417
├── 1418
├── 14382
├── 14382_fsm
├── 14382_vm
├── 14385
├── 14386
├── 14387
├── 14387_fsm
├── 14387_vm
├── 14390
├── 14391
├── 14392
├── 14392_fsm
├── 14392_vm
├── 14395
├── 14396
├── 14397
├── 14397_fsm
├── 14397_vm
├── 14400
├── 14401
├── 174
├── 175
├── 2187
├── 2224
├── 2228
├── 2328
├── 2336
├── 2337
├── 2579
├── 2600
├── 2600_fsm
├── 2600_vm
├── 2601
├── 2601_fsm
├── 2601_vm
├── 2602
├── 2602_fsm
├── 2602_vm
├── 2603
├── 2603_fsm
├── 2603_vm
├── 2604
├── 2605
├── 2605_fsm
├── 2605_vm
├── 2606
├── 2606_fsm
├── 2606_vm
├── 2607
├── 2607_fsm
├── 2607_vm
├── 2608
├── 2608_fsm
├── 2608_vm
├── 2609
├── 2609_fsm
├── 2609_vm
├── 2610
├── 2610_fsm
├── 2610_vm
├── 2611
├── 2612
├── 2612_fsm
├── 2612_vm
├── 2613
├── 2615
├── 2615_fsm
├── 2615_vm
├── 2616
├── 2616_fsm
├── 2616_vm
├── 2617
├── 2617_fsm
├── 2617_vm
├── 2618
├── 2618_fsm
├── 2618_vm
├── 2619
├── 2619_fsm
├── 2619_vm
├── 2620
├── 2650
├── 2651
├── 2652
├── 2653
├── 2654
├── 2655
├── 2656
├── 2657
├── 2658
├── 2659
├── 2660
├── 2661
├── 2662
├── 2663
├── 2664
├── 2665
├── 2666
├── 2667
├── 2668
├── 2669
├── 2670
├── 2673
├── 2674
├── 2675
├── 2678
├── 2679
├── 2680
├── 2681
├── 2682
├── 2683
├── 2684
├── 2685
├── 2686
├── 2687
├── 2688
├── 2689
├── 2690
├── 2691
├── 2692
├── 2693
├── 2696
├── 2699
├── 2701
├── 2702
├── 2703
├── 2704
├── 2753
├── 2753_fsm
├── 2753_vm
├── 2754
├── 2755
├── 2756
├── 2757
├── 2830
├── 2831
├── 2832
├── 2833
├── 2834
├── 2835
├── 2836
├── 2836_fsm
├── 2836_vm
├── 2837
├── 2838
├── 2838_fsm
├── 2838_vm
├── 2839
├── 2840
├── 2840_fsm
├── 2840_vm
├── 2841
├── 2995
├── 2996
├── 3079
├── 3079_fsm
├── 3079_vm
├── 3080
├── 3081
├── 3085
├── 3118
├── 3119
├── 3164
├── 3256
├── 3257
├── 3258
├── 3350
├── 3351
├── 3379
├── 3380
├── 3381
├── 3394
├── 3394_fsm
├── 3394_vm
├── 3395
├── 3429
├── 3430
├── 3431
├── 3433
├── 3439
├── 3440
├── 3455
├── 3456
├── 3456_fsm
├── 3456_vm
├── 3466
├── 3467
├── 3468
├── 3501
├── 3502
├── 3503
├── 3534
├── 3541
├── 3541_fsm
├── 3541_vm
├── 3542
├── 3574
├── 3575
├── 3576
├── 3596
├── 3597
├── 3598
├── 3599
├── 3600
├── 3600_fsm
├── 3600_vm
├── 3601
├── 3601_fsm
├── 3601_vm
├── 3602
├── 3602_fsm
├── 3602_vm
├── 3603
├── 3603_fsm
├── 3603_vm
├── 3604
├── 3605
├── 3606
├── 3607
├── 3608
├── 3609
├── 3712
├── 3764
├── 3764_fsm
├── 3764_vm
├── 3766
├── 3767
├── 3997
├── 4143
├── 4144
├── 4145
├── 4146
├── 4147
├── 4148
├── 4149
├── 4150
├── 4151
├── 4152
├── 4153
├── 4154
├── 4155
├── 4156
├── 4157
├── 4158
├── 4159
├── 4160
├── 4163
├── 4164
├── 4165
├── 4166
├── 4167
├── 4168
├── 4169
├── 4170
├── 4171
├── 4172
├── 4173
├── 4174
├── 5002
├── 548
├── 549
├── 6102
├── 6104
├── 6106
├── 6110
├── 6111
├── 6112
├── 6113
├── 6116
├── 6117
├── 6175
├── 6176
├── 6228
├── 6229
├── 6237
├── 6238
├── 6239
├── 826
├── 827
├── 828
├── pg_filenode.map
├── pg_internal.init
└── PG_VERSION

0 directories, 299 files
[postgres@lxceftsgvdb01 ~]$
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/

Types of Shutdown in PostgreSQL

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

Types of Shutdown in PostgreSQL

PostgreSQL Shutdown TypeOracleBehavior
Smart (SIGTERM)SHUTDOWN NORMALThe server stops accepting new connections but allows existing sessions to continue running normally. PostgreSQL shuts down only after all active sessions disconnect and their transactions complete successfully.
Fast (SIGINT – Default)SHUTDOWN IMMEDIATEThe server stops accepting new connections and immediately terminates all active sessions. Any uncommitted transactions are rolled back, and PostgreSQL performs a clean shutdown gracefully.
Immediate (SIGQUIT)SHUTDOWN ABORTThe server terminates all processes instantly without performing a proper shutdown. No cleanup or checkpoint occurs, so PostgreSQL performs crash recovery by replaying WAL files during the next startup.
Shutdown TypeSignalCommand ExampleBehavior
SmartSIGTERMpg_ctl stop -D /pgData -m smartStops new connections, waits for existing sessions to finish normally, then shuts down.
Fast (Default)SIGINTpg_ctl stop -D /pgdata -m fastStops new connections, terminates active sessions, rolls back uncommitted transactions, and shuts down cleanly.
ImmediateSIGQUITpg_ctl stop -D /pgdata -m immediateStops immediately without proper shutdown. Recovery from WAL files happens during next startup.

Difference Between Reload and Restart

  • When we change server configuration parameters, PostgreSQL needs to read those changes before they become active.
  • Reload applies the new configuration without stopping or restarting the database service. Existing connections and database activity continue normally.
  • Some configuration parameter changes cannot be applied through reload and will only take effect after a restart.
  • Restart stops the PostgreSQL server gracefully, ends all running activities, releases resources, closes open files, and then starts the server again with the new configuration.

 

Smart Shutdown (SIGTERM):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m smart
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:26:59.741 +08 [3476] LOG:  received smart shutdown request
2026-07-03 14:26:59.752 +08 [3476] LOG:  background worker "logical replication launcher" (PID 3483) exited with exit code 1
2026-07-03 14:26:59.753 +08 [3478] LOG:  shutting down
2026-07-03 14:26:59.755 +08 [3478] LOG:  checkpoint starting: shutdown immediate
2026-07-03 14:26:59.765 +08 [3478] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.003 s, total=0.012 s; sync files=2, longest=0.003 s, average=0.002 s; distance=0 kB, estimate=0 kB; lsn=1/105465F0, redo lsn=1/105465F0
2026-07-03 14:26:59.771 +08 [3476] LOG:  database system is shut down


[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:27:18.394 +08 [3487] LOG:  redirecting log output to logging collector process
2026-07-03 14:27:18.394 +08 [3487] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:27:18.394 +08 [3487] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:27:18.396 +08 [3487] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:27:18.396 +08 [3487] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:27:18.399 +08 [3487] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:27:18.404 +08 [3487] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:27:18.413 +08 [3491] LOG:  database system was shut down at 2026-07-03 14:26:59 +08
2026-07-03 14:27:18.425 +08 [3487] LOG:  database system is ready to accept connections

Fast Shutdown (SIGINT – Default):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m fast
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:28:26.839 +08 [3487] LOG:  received fast shutdown request
2026-07-03 14:28:26.844 +08 [3487] LOG:  aborting any active transactions
2026-07-03 14:28:26.854 +08 [3487] LOG:  background worker "logical replication launcher" (PID 3494) exited with exit code 1
2026-07-03 14:28:26.860 +08 [3489] LOG:  shutting down
2026-07-03 14:28:26.862 +08 [3489] LOG:  checkpoint starting: shutdown immediate
2026-07-03 14:28:26.874 +08 [3489] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.005 s, total=0.015 s; sync files=2, longest=0.004 s, average=0.003 s; distance=0 kB, estimate=0 kB; lsn=1/105466A0, redo lsn=1/105466A0
2026-07-03 14:28:26.882 +08 [3487] LOG:  database system is shut down

[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:29:00.574 +08 [3505] LOG:  redirecting log output to logging collector process
2026-07-03 14:29:00.574 +08 [3505] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:29:00.575 +08 [3505] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:29:00.579 +08 [3505] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:29:00.580 +08 [3505] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:29:00.582 +08 [3505] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:29:00.587 +08 [3505] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:29:00.595 +08 [3509] LOG:  database system was shut down at 2026-07-03 14:28:26 +08
2026-07-03 14:29:00.607 +08 [3505] LOG:  database system is ready to accept connections

Immediate Shutdown (SIGQUIT):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m immediate
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:29:47.471 +08 [3505] LOG:  received immediate shutdown request
2026-07-03 14:29:47.485 +08 [3505] LOG:  database system is shut down


[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:30:17.965 +08 [3520] LOG:  redirecting log output to logging collector process
2026-07-03 14:30:17.965 +08 [3520] HINT:  Future log output will appear in directory "log".
. done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:30:17.966 +08 [3520] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:30:17.966 +08 [3520] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:30:17.966 +08 [3520] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:30:17.970 +08 [3520] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:30:17.974 +08 [3520] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:30:17.983 +08 [3524] LOG:  database system was interrupted; last known up at 2026-07-03 14:29:00 +08
2026-07-03 14:30:19.211 +08 [3524] LOG:  database system was not properly shut down; automatic recovery in progress
2026-07-03 14:30:19.216 +08 [3524] LOG:  redo starts at 1/10546718
2026-07-03 14:30:19.216 +08 [3524] LOG:  invalid record length at 1/10546750: expected at least 24, got 0
2026-07-03 14:30:19.217 +08 [3524] LOG:  redo done at 1/10546718 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2026-07-03 14:30:19.221 +08 [3522] LOG:  checkpoint starting: end-of-recovery immediate wait
2026-07-03 14:30:19.231 +08 [3522] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.003 s, total=0.012 s; sync files=2, longest=0.002 s, average=0.002 s; distance=0 kB, estimate=0 kB; lsn=1/10546750, redo lsn=1/10546750
2026-07-03 14:30:19.239 +08 [3520] LOG:  database system is ready to accept connections

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/

Create a Second PostgreSQL Instance on the Same Linux Server

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

Create a Second PostgreSQL Instance on the Same Linux Server

Table of Contents

0. Verify existing setup

[postgres@pgdb02 ~]$ ps -ef | grep postgres
root        3641    3561  0 16:57 pts/1    00:00:00 su - postgres
postgres    3642    3641  0 16:57 pts/1    00:00:00 -bash
postgres    3711       1  0 17:03 ?        00:00:00 /pgBin/pgsql/17.4/bin/postgres -D /pgData/pgsql/17.4
postgres    3712    3711  0 17:03 ?        00:00:00 postgres: PGDB_SIT: checkpointer
postgres    3713    3711  0 17:03 ?        00:00:00 postgres: PGDB_SIT: background writer
postgres    3715    3711  0 17:03 ?        00:00:00 postgres: PGDB_SIT: walwriter
postgres    3716    3711  0 17:03 ?        00:00:00 postgres: PGDB_SIT: autovacuum launcher
postgres    3717    3711  0 17:03 ?        00:00:00 postgres: PGDB_SIT: logical replication launcher
postgres    3731    3642  0 17:03 pts/1    00:00:00 psql
postgres    3732    3711  0 17:03 ?        00:00:00 postgres: PGDB_SIT: postgres postgres [local] idle
root        3748    3164  0 17:09 pts/0    00:00:00 su - postgres
postgres    3749    3748  0 17:09 pts/0    00:00:00 -bash
postgres    3911    3749  0 17:59 pts/0    00:00:00 ps -ef
postgres    3912    3749  0 17:59 pts/0    00:00:00 grep --color=auto postgres
[postgres@pgdb02 ~]$

1. Create new directories

[root@pgdb02 ~]# mkdir -p /pgData/pgsql/17.4/dev
[root@pgdb02 ~]# mkdir -p /pgWal/pgsql/17.4/dev
[root@pgdb02 ~]# chown -R postgres:postgres /pgData /pgWal
[root@pgdb02 ~]#

2. Initialize second cluster

# As postgres user 

[postgres@pgdb02 ~]$ /pgBin/pgsql/17.4/bin/initdb -D /pgData/pgsql/17.4/dev --waldir=/pgWal/pgsql/17.4/dev --wal-segsize=128
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_SG.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 /pgData/pgsql/17.4/dev ... ok
fixing permissions on existing directory /pgWal/pgsql/17.4/dev ... 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/Singapore
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:

    /pgBin/pgsql/17.4/bin/pg_ctl -D /pgData/pgsql/17.4/dev -l logfile start

[postgres@pgdb02 ~]$

[postgres@pgdb02 ~]$ ls -ld /pgData/pgsql/17.4/dev
drwx------. 18 postgres postgres 4096 Jun 20 18:03 /pgData/pgsql/17.4/dev
[postgres@pgdb02 ~]$
[postgres@pgdb02 ~]$ ls -ltr /pgData/pgsql/17.4/dev
total 56
lrwxrwxrwx. 1 postgres postgres    21 Jun 20 18:03 pg_wal -> /pgWal/pgsql/17.4/dev
drwx------. 2 postgres postgres     6 Jun 20 18:03 pg_commit_ts
drwx------. 2 postgres postgres     6 Jun 20 18:03 pg_dynshmem
drwx------. 2 postgres postgres     6 Jun 20 18:03 pg_twophase
drwx------. 2 postgres postgres     6 Jun 20 18:03 pg_tblspc
drwx------. 2 postgres postgres     6 Jun 20 18:03 pg_stat_tmp
drwx------. 2 postgres postgres     6 Jun 20 18:03 pg_snapshots
drwx------. 2 postgres postgres     6 Jun 20 18:03 pg_serial
drwx------. 2 postgres postgres     6 Jun 20 18:03 pg_replslot
drwx------. 2 postgres postgres     6 Jun 20 18:03 pg_notify
drwx------. 4 postgres postgres    36 Jun 20 18:03 pg_multixact
-rw-------. 1 postgres postgres     3 Jun 20 18:03 PG_VERSION
-rw-------. 1 postgres postgres 30718 Jun 20 18:03 postgresql.conf
-rw-------. 1 postgres postgres    88 Jun 20 18:03 postgresql.auto.conf
-rw-------. 1 postgres postgres  5711 Jun 20 18:03 pg_hba.conf
-rw-------. 1 postgres postgres  2640 Jun 20 18:03 pg_ident.conf
drwx------. 2 postgres postgres    18 Jun 20 18:03 pg_xact
drwx------. 2 postgres postgres    18 Jun 20 18:03 pg_subtrans
drwx------. 2 postgres postgres  4096 Jun 20 18:03 global
drwx------. 5 postgres postgres    33 Jun 20 18:03 base
drwx------. 4 postgres postgres    68 Jun 20 18:03 pg_logical
drwx------. 2 postgres postgres    25 Jun 20 18:03 pg_stat
[postgres@pgdb02 ~]$

**** we can see here 700 permission set by initdb command.

3. Edit second instance config

[postgres@pgdb02 ~]$ egrep "^(#)?(port|cluster_name)" /pgData/pgsql/17.4/dev/postgresql.conf
#port = 5432                            # (change requires restart)
#cluster_name = ''                      # added to process titles if nonempty
[postgres@pgdb02 ~]$


[postgres@pgdb02 ~]$ cp /pgData/pgsql/17.4/dev/postgresql.conf /pgData/pgsql/17.4/dev/postgresql.conf.bkp
[postgres@pgdb02 ~]$

[postgres@pgdb02 ~]$ sed -i "s/^#port = 5432.*/port = 5433/" /pgData/pgsql/17.4/dev/postgresql.conf
[postgres@pgdb02 ~]$ sed -i "s/^#cluster_name = ''.*/cluster_name = 'PGDB_UAT'/" /pgData/pgsql/17.4/dev/postgresql.conf
[postgres@pgdb02 ~]$
[postgres@pgdb02 ~]$ egrep "^(#)?(port|cluster_name)" /pgData/pgsql/17.4/dev/postgresql.conf
port = 5433
cluster_name = 'PGDB_UAT'
[postgres@pgdb02 ~]$

4. Start PostgreSQL Service

[postgres@pgdb02 ~]$ /pgBin/pgsql/17.4/bin/pg_ctl -D /pgData/pgsql/17.4/dev -l logfile start
waiting for server to start.... done
server started
[postgres@pgdb02 ~]$

5. Connect to second instance

[postgres@pgdb02 ~]$ psql -p 5433
psql (17.4)
Type "help" for help.

postgres=# SHOW port;
 port
------
 5433  <-----------
(1 row)

postgres=# SHOW cluster_name;
 cluster_name
--------------
 PGDB_UAT  <---------------
(1 row)

postgres=#\q

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql/17.4/dev
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

6. Create second systemd service

[root@pgdb02 ~]# cp /etc/systemd/system/postgresql-17.service /etc/systemd/system/postgresql-17-dev.service
[root@pgdb02 ~]#
[root@pgdb02 ~]# grep -i Environment=PGDATA /etc/systemd/system/postgresql-17-dev.service
Environment=PGDATA=/pgData/pgsql/17.4
[root@pgdb02 ~]#

[root@pgdb02 ~]# sed -i 's|Environment=PGDATA=/pgData/pgsql/17.4|Environment=PGDATA=/pgData/pgsql/17.4/dev|' /etc/systemd/system/postgresql-17-dev.service
[root@pgdb02 ~]# grep -i Environment=PGDATA /etc/systemd/system/postgresql-17-dev.service
Environment=PGDATA=/pgData/pgsql/17.4/dev
[root@pgdb02 ~]#

[root@pgdb02 ~]# systemctl daemon-reload
[root@pgdb02 ~]# systemctl start postgresql-17-dev
[root@pgdb02 ~]# systemctl enable postgresql-17-dev
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-17-dev.service → /etc/systemd/system/postgresql-17-dev.service.
[root@pgdb02 ~]#
[root@pgdb02 ~]# systemctl status postgresql-17-dev
● postgresql-17-dev.service - PostgreSQL 17 Database Server
     Loaded: loaded (/etc/systemd/system/postgresql-17-dev.service; enabled; preset: disabled)
     Active: active (running) since Sat 2026-06-20 18:25:39 +08; 12s ago
       Docs: https://www.postgresql.org/docs/17/
   Main PID: 4106 (postgres)
      Tasks: 6 (limit: 15700)
     Memory: 15.2M
        CPU: 62ms
     CGroup: /system.slice/postgresql-17-dev.service
             ├─4106 /pgBin/pgsql/17.4/bin/postgres -D /pgData/pgsql/17.4/dev
             ├─4107 "postgres: PGDB_UAT: checkpointer "
             ├─4108 "postgres: PGDB_UAT: background writer "
             ├─4110 "postgres: PGDB_UAT: walwriter "
             ├─4111 "postgres: PGDB_UAT: autovacuum launcher "
             └─4112 "postgres: PGDB_UAT: logical replication launcher "

Jun 20 18:25:39 pgdb02 systemd[1]: Starting PostgreSQL 17 Database Server...
Jun 20 18:25:39 pgdb02 pg_ctl[4103]: waiting for server to start.... done
Jun 20 18:25:39 pgdb02 pg_ctl[4103]: server started
Jun 20 18:25:39 pgdb02 systemd[1]: Started PostgreSQL 17 Database Server.
[root@pgdb02 ~]#

7. Verify instances

[postgres@pgdb02 ~]$ ps -ef | grep postgres
postgres    3711       1  0 17:03 ?        00:00:00 /pgBin/pgsql/17.4/bin/postgres -D /pgData/pgsql/17.4
postgres    3712    3711  0 17:03 ?        00:00:00 postgres: PGDB_SIT: checkpointer
postgres    3713    3711  0 17:03 ?        00:00:00 postgres: PGDB_SIT: background writer
postgres    3715    3711  0 17:03 ?        00:00:00 postgres: PGDB_SIT: walwriter
postgres    3716    3711  0 17:03 ?        00:00:00 postgres: PGDB_SIT: autovacuum launcher
postgres    3717    3711  0 17:03 ?        00:00:00 postgres: PGDB_SIT: logical replication launcher
root        3748    3164  0 17:09 pts/0    00:00:00 su - postgres
postgres    3749    3748  0 17:09 pts/0    00:00:00 -bash
postgres    4106       1  0 18:25 ?        00:00:00 /pgBin/pgsql/17.4/bin/postgres -D /pgData/pgsql/17.4/dev
postgres    4107    4106  0 18:25 ?        00:00:00 postgres: PGDB_UAT: checkpointer
postgres    4108    4106  0 18:25 ?        00:00:00 postgres: PGDB_UAT: background writer
postgres    4110    4106  0 18:25 ?        00:00:00 postgres: PGDB_UAT: walwriter
postgres    4111    4106  0 18:25 ?        00:00:00 postgres: PGDB_UAT: autovacuum launcher
postgres    4112    4106  0 18:25 ?        00:00:00 postgres: PGDB_UAT: logical replication launcher
postgres    4157    3749 99 18:26 pts/0    00:00:00 ps -ef
postgres    4158    3749  0 18:26 pts/0    00:00:00 grep --color=auto postgres
[postgres@pgdb02 ~]$

[postgres@pgdb02 ~]$ ss -tulpn | grep 543
tcp   LISTEN 0      200        127.0.0.1:5433       0.0.0.0:*    users:(("postgres",pid=4106,fd=7))
tcp   LISTEN 0      200        127.0.0.1:5432       0.0.0.0:*    users:(("postgres",pid=3711,fd=7))
tcp   LISTEN 0      200            [::1]:5432          [::]:*    users:(("postgres",pid=3711,fd=6))
tcp   LISTEN 0      200            [::1]:5433          [::]:*    users:(("postgres",pid=4106,fd=6))
[postgres@pgdb02 ~]$

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/

Uninstall PostgreSQL 17 on Linux (RPM Method)

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

Uninstall PostgreSQL 17 on Linux (Installed Using RPM Method)

Table of Contents

0. Important Caution Before Proceeding

** Before uninstalling PostgreSQL PostgreSQL 17, first take a logical or physical backup. **

If PostgreSQL was installed using RPM packages, removing the package does not automatically remove:

Database files
WAL files
Backup directories
Custom configuration files

1. Check Installed PostgreSQL RPM Packages

[root@pgdb02 ~]# rpm -qa | grep postgres
postgresql17-libs-17.6-1PGDG.rhel9.x86_64
postgresql17-17.6-1PGDG.rhel9.x86_64
postgresql17-server-17.6-1PGDG.rhel9.x86_64
postgresql17-contrib-17.6-1PGDG.rhel9.x86_64
[root@pgdb02 ~]#

2. Stop PostgreSQL Service

[root@pgdb02 ~]# systemctl stop postgresql-17
[root@pgdb02 ~]# systemctl status postgresql-17
○ postgresql-17.service - PostgreSQL 17 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
Active: inactive (dead)
Docs: https://www.postgresql.org/docs/17/static/
[root@pgdb02 ~]#

3. Disable PostgreSQL Service

[root@pgdb02 ~]# systemctl disable postgresql-17
Removed "/etc/systemd/system/multi-user.target.wants/postgresql-17.service".
[root@pgdb02 ~]#

4. Remove and Verify PostgreSQL RPM Packages

WARNING: This permanently deletes all binaries, libraries, and data. Ensure your data is backed up before this step.

[root@pgdb02 ~]# rpm -qa | grep postgres
postgresql17-libs-17.6-1PGDG.rhel9.x86_64
postgresql17-17.6-1PGDG.rhel9.x86_64
postgresql17-server-17.6-1PGDG.rhel9.x86_64
postgresql17-contrib-17.6-1PGDG.rhel9.x86_64
[root@pgdb02 ~]#
[root@pgdb02 ~]# rpm -e postgresql17-contrib-17.6-1PGDG.rhel9.x86_64
[root@pgdb02 ~]# rpm -e postgresql17-server-17.6-1PGDG.rhel9.x86_64
[root@pgdb02 ~]# rpm -e postgresql17-17.6-1PGDG.rhel9.x86_64
[root@pgdb02 ~]# rpm -e postgresql17-libs-17.6-1PGDG.rhel9.x86_64
[root@pgdb02 ~]#

5. Verify Packages Are Removed

[root@pgdb02 ~]# rpm -qa | grep postgres
[root@pgdb02 ~]#

6. Remove PostgreSQL System User (Optional)

[root@pgdb02 ~]# id postgres
uid=26(postgres) gid=26(postgres) groups=26(postgres)
[root@pgdb02 ~]# userdel postgres
[root@pgdb02 ~]#
[root@pgdb02 ~]# groupdel postgres
groupdel: group 'postgres' does not exist
[root@pgdb02 ~]#
[root@pgdb02 ~]# id postgres
id: ‘postgres’: no such user
[root@pgdb02 ~]#

7. Remove PostgreSQL Configuration Files

# It's already removed in step no. 3

[root@pgdb02 ~]# ls -l /usr/lib/systemd/system/postgresql-17.service
ls: cannot access '/usr/lib/systemd/system/postgresql-17.service': No such file or directory
[root@pgdb02 ~]# 
[root@pgdb02 ~]# ls -l /etc/systemd/system/postgresql-17.service
-rw-r--r--. 1 root root 588 Jun 19 21:20 /etc/systemd/system/postgresql-17.service
[root@pgdb02 ~]#
[root@pgdb02 ~]# rm -f /etc/systemd/system/postgresql-17.service
[root@pgdb02 ~]#

8. Verify Complete Removal

[root@pgdb02 ~]# id postgres
id: ‘postgres’: no such user
[root@pgdb02 ~]#
[root@pgdb02 ~]# rpm -qa | grep postgres
[root@pgdb02 ~]#
[root@pgdb02 ~]# systemctl status postgresql-17
Unit postgresql-17.service could not be found.
[root@pgdb02 ~]#

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

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

Change WAL Directory Path (pg_wal) in 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 : /pgData/pgsql17/data/pg_wal

TO : /pgWal/pgsql17/wal

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 /pgWal/pgsql17/wal
[root@lxicbpgdsgv01 ~]# chown postgres:postgres /pgWal/pgsql17/wal
[root@lxicbpgdsgv01 ~]# chmod 700 /pgWal/pgsql17/wal
[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: linkedin.com/in/rajasekhar-amudala

PostgreSQL Backup and Restore Using pg_dumpall

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

PostgreSQL Backup and Restore Using pg_dumpall and psql

Table of Contents

0. Introduction

Backup:

1. Backup All Databases
2. Backup Users/Roles Definition
3. Backup Tablespaces Definition
4. Backup Schema Only (No Data)
5. Backup Data Only (No Schema)
6. Backup Data as INSERT Commands
7. Backup Global Objects Only (No Databases)

Restore:

8. Restore on Same Host

9. Restore to Another Host (Different Tablespace Paths)

Click to expand pg_dumpall –help
[postgres@lxtrdpgdsgv01 ~]$ pg_dumpall --help
pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.

Usage:
  pg_dumpall [OPTION]...

General options:
  -f, --file=FILENAME          output file name
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -c, --clean                  clean (drop) databases before recreating
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -g, --globals-only           dump only global objects, no databases
  -O, --no-owner               skip restoration of object ownership
  -r, --roles-only             dump only roles, no databases or tablespaces
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in the dump
  -t, --tablespaces-only       dump only tablespaces, no databases or roles
  -x, --no-privileges          do not dump privileges (grant/revoke)
  --binary-upgrade             for use by upgrade utilities only
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --exclude-database=PATTERN   exclude databases whose name matches PATTERN
  --extra-float-digits=NUM     override default setting for extra_float_digits
  --if-exists                  use IF EXISTS when dropping objects
  --inserts                    dump data as INSERT commands, rather than COPY
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  --no-role-passwords          do not dump passwords for roles
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --no-sync                    do not wait for changes to be written safely to disk
  --no-table-access-method     do not dump table access methods
  --no-tablespaces             do not dump tablespace assignments
  --no-toast-compression       do not dump TOAST compression methods
  --no-unlogged-table-data     do not dump unlogged table data
  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands
  --quote-all-identifiers      quote all identifiers, even if not key words
  --restrict-key=RESTRICT_KEY  use provided string as psql \restrict key
  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=CONNSTR     connect using connection string
  -h, --host=HOSTNAME      database server host or socket directory
  -l, --database=DBNAME    alternative default database
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If -f/--file is not used, then the SQL script will be written to the standard
output.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
[postgres@lxtrdpgdsgv01 ~]$

 

0. Introduction

Note: pg_dumpall does not support custom format backups.

The pg_dumpall utility is used to back up an entire PostgreSQL environment, including:

* Roles and users
* Tablespaces
* All databases (schemas and data)

It is especially useful for:

* Full cluster migrations
* Disaster recovery
* Environment replication across dev, QA, and prod

When restoring to a different host, you'll need to:

* Update tablespace paths (using tools like sed)
* Pre-create required tablespace directories

This ensures compatibility and successful restoration across different environments.

Backup


1. Backup ALL databases

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.log 2>&1 &
[1] 5610
[postgres@lxtrdpgdsgv01 ~]$ 
[postgres@lxtrdpgdsgv01 ~]$ cat /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.log | grep -i /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql  -Fa 'user=postgres dbname=template1'"
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql --create -Fa 'user=postgres dbname=edpua'"
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql --create -Fa 'user=postgres dbname=gebua'"
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql --create -Fa 'user=postgres dbname=orcl'"
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql  -Fa 'user=postgres dbname=postgres'"
[postgres@lxtrdpgdsgv01 ~]$

[postgres@lxtrdpgdsgv01 ~]$ psql
psql (15.14)
Type "help" for help.

postgres=# \c orcl
You are now connected to database "orcl" as user "postgres".
orcl=# SELECT
orcl-#     schemaname || '.' || relname AS table_name,
orcl-#     pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
orcl-#     pg_size_pretty(pg_relation_size(relid)) AS table_size,
orcl-#     pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
orcl-# FROM
orcl-#     pg_catalog.pg_statio_user_tables
orcl-# ORDER BY
orcl-#     pg_total_relation_size(relid) DESC;
    table_name    | total_size | table_size | index_size
------------------+------------+------------+------------
 trd.metrics_high | 799 MB     | 498 MB     | 301 MB
 trd.metrics_mid  | 638 MB     | 398 MB     | 240 MB
 trd.employees    | 493 MB     | 322 MB     | 172 MB
 trd.sales_2023   | 399 MB     | 249 MB     | 150 MB
 trd.sales_2024   | 399 MB     | 249 MB     | 150 MB
 trd.sales_2022   | 399 MB     | 249 MB     | 150 MB
 trd.sales_2021   | 398 MB     | 248 MB     | 150 MB
 trd.metrics_low  | 159 MB     | 99 MB      | 60 MB
 trd.test_data    | 71 MB      | 50 MB      | 21 MB
 trd.emp_summary  | 24 kB      | 8192 bytes | 16 kB
 trd.metrics_rest | 8192 bytes | 0 bytes    | 8192 bytes
(11 rows)

orcl=#


2. Backup users/roles definition

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --roles-only -v -f /pgBackup/pgsql15/backup/roles.sql > /pgBackup/pgsql15/backup/roles.log 2>&1 &
[1] 5205
[postgres@lxtrdpgdsgv01 ~]$


3. Backup tablespaces definition

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --tablespaces-only -v -f /pgBackup/pgsql15/backup/tablespaces.sql > /pgBackup/pgsql15/backup/tablespaces.log 2>&1 &
[1] 5183
[postgres@lxtrdpgdsgv01 ~]$


4. Backup dump only the schema, no data

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --schema-only -v -f /pgBackup/pgsql15/backup/schemas.sql > /pgBackup/pgsql15/backup/schemas.log 2>&1 &
[1] 4890
[postgres@lxtrdpgdsgv01 ~]$


5. Backup dump only the data, not the schema

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --data-only -v -f /pgBackup/pgsql15/backup/dataonly.sql > /pgBackup/pgsql15/backup/dataonly.log 2>&1 &
[1] 5233
[postgres@lxtrdpgdsgv01 ~]$


6. Backup dump data as INSERT commands, rather than COPY

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --inserts -v -f /pgBackup/pgsql15/backup/inserts.sql > /pgBackup/pgsql15/backup/inserts.log 2>&1 &
[1] 5274
[postgres@lxtrdpgdsgv01 ~]$


7. Backup dump only global objects, no databases

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --globals-only -v -f /pgBackup/pgsql15/backup/globals.sql > /pgBackup/pgsql15/backup/globals.log 2>&1 &
[1] 5331
[postgres@lxtrdpgdsgv01 ~]$

Restore


8. Restore on same host


A. Drop Existing Databases (Optional)

 

[postgres@lxtrdpgdsgv01 ~]$ psql
psql (15.14)
Type "help" for help.

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 edpua     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 gebua     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 orcl      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | postgres=CTc/postgres+
           |          |          |             |             |            |                 | =c/postgres
(6 rows)

postgres=# drop database edpua;
DROP DATABASE
postgres=# drop database gebua;
DROP DATABASE
postgres=# drop database orcl;
DROP DATABASE
postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | postgres=CTc/postgres+
           |          |          |             |             |            |                 | =c/postgres
(3 rows)

postgres=#

postgres=# \db
             List of tablespaces
    Name    |  Owner   |      Location
------------+----------+---------------------
 edpua_ix   | postgres | /pgIx/pgsql15/edpua
 edpua_tb   | postgres | /pgTb/pgsql15/edpua
 gebua_ix   | postgres | /pgIx/pgsql15/gebua
 gebua_tb   | postgres | /pgTb/pgsql15/gebua
 orcl_ix    | postgres | /pgIx/pgsql15/orcl
 orcl_tb    | postgres | /pgTb/pgsql15/orcl
 pg_default | postgres |
 pg_global  | postgres |
(8 rows)

postgres=#


B. Restore Full Backup

[postgres@lxtrdpgdsgv01 ~]$ nohup psql -U postgres -X -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql -d postgres > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_restore.log
[1] 4132
[postgres@lxtrdpgdsgv01 ~]$ 

-- OR --

nohup psql -U postgres -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_restore.log 2>&1 &

-- Log: backup_all_databases_10OCT2025_restore.log


C. Verification

[postgres@lxtrdpgdsgv01 ~]$ psql
psql (15.14)
Type "help" for help.

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 edpua     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 gebua     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 orcl      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | postgres=CTc/postgres+
           |          |          |             |             |            |                 | =c/postgres
(6 rows)

postgres=#


9. Restore to Another Host (Different Tablespace Paths)

A. Copy Backup File

[postgres@lxtrdpgdsgv01 backup]$ scp backup_all_databases_10OCT2025.sql 192.168.2.31:/pgBackup/pgsql15/backup/
postgres@192.168.2.31's password:
backup_all_databases_10OCT2025.sql                 100% 3556MB  51.5MB/s   01:09
[postgres@lxtrdpgdsgv01 backup]$ 

[postgres@pg17 backup]$ ls -ltr backup_all_databases_10OCT2025.sql
-rw-r--r--. 1 postgres postgres 3729147646 Oct  6 06:17 backup_all_databases_10OCT2025.sql
[postgres@pg17 backup]$


B. Extract Tablespace/Database Info

[postgres@lxtrdpgdsgv01 backup]$ grep -i "CREATE TABLESPACE" /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql
CREATE TABLESPACE orcl_tb OWNER postgres LOCATION '/pgTb/pgsql15/orcl';
CREATE TABLESPACE orcl_ix OWNER postgres LOCATION '/pgIx/pgsql15/orcl';
CREATE TABLESPACE gebua_tb OWNER postgres LOCATION '/pgTb/pgsql15/gebua';
CREATE TABLESPACE gebua_ix OWNER postgres LOCATION '/pgIx/pgsql15/gebua';
CREATE TABLESPACE edpua_tb OWNER postgres LOCATION '/pgTb/pgsql15/edpua';
CREATE TABLESPACE edpua_ix OWNER postgres LOCATION '/pgIx/pgsql15/edpua';
[postgres@lxtrdpgdsgv01 backup]$
[postgres@lxtrdpgdsgv01 backup]$ grep -i "CREATE DATABASE" /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql
CREATE DATABASE edpua WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8' TABLESPACE = edpua_tb;
CREATE DATABASE gebua WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8' TABLESPACE = gebua_tb;
CREATE DATABASE orcl WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8' TABLESPACE = orcl_tb;
[postgres@lxtrdpgdsgv01 backup]$


C. Create Tablespace Directories on Target

[postgres@pg17 ~]$ psql
psql (15.13)
Type "help" for help.

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | postgres=CTc/postgres+
           |          |          |             |             |            |                 | =c/postgres
(3 rows)

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

postgres=#


# Create Directory 

[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/orcl_tb
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/orcl_ix
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/gebua_tb
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/gebua_ix
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/edpua_tb
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/edpua_ix


D. Create Tablespaces in PostgreSQL

postgres=# CREATE TABLESPACE orcl_tb OWNER postgres LOCATION  '/pgData/pgsql15/orcl_tb';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE orcl_ix OWNER postgres LOCATION  '/pgData/pgsql15/orcl_ix';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE gebua_tb OWNER postgres LOCATION '/pgData/pgsql15/gebua_tb';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE gebua_ix OWNER postgres LOCATION '/pgData/pgsql15/gebua_ix';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE edpua_tb OWNER postgres LOCATION '/pgData/pgsql15/edpua_tb';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE edpua_ix OWNER postgres LOCATION '/pgData/pgsql15/edpua_ix';
CREATE TABLESPACE
postgres=#


-- OR --- 
 You can modify the tablespace paths directly in the backup file; however, this approach is not recommended for large backup files due to  reliability concerns.

cd /pgBackup/pgsql15/backup/
# Replace ORCL paths
sed -i 's|/pgTb/pgsql15/orcl|/pgData/pgsql15/orcl_tb|g' backup_all_databases_10OCT2025.sql
sed -i 's|/pgIx/pgsql15/orcl|/pgData/pgsql15/orcl_ix|g' backup_all_databases_10OCT2025.sql

# Replace GEBUA paths
sed -i 's|/pgTb/pgsql15/gebua|/pgData/pgsql15/gebua_tb|g' backup_all_databases_10OCT2025.sql
sed -i 's|/pgIx/pgsql15/gebua|/pgData/pgsql15/gebua_ix|g' backup_all_databases_10OCT2025.sql

# Replace EDPUA paths
sed -i 's|/pgTb/pgsql15/edpua|/pgData/pgsql15/edpua_tb|g' backup_all_databases_10OCT2025.sql
sed -i 's|/pgIx/pgsql15/edpua|/pgData/pgsql15/edpua_ix|g' backup_all_databases_10OCT2025.sql


[postgres@pg17 ~]$ psql
psql (15.13)
Type "help" for help.

postgres=# \db
               List of tablespaces
    Name    |  Owner   |         Location
------------+----------+--------------------------
 edpua_ix   | postgres | /pgData/pgsql15/edpua_ix
 edpua_tb   | postgres | /pgData/pgsql15/edpua_tb
 gebua_ix   | postgres | /pgData/pgsql15/gebua_ix
 gebua_tb   | postgres | /pgData/pgsql15/gebua_tb
 orcl_ix    | postgres | /pgData/pgsql15/orcl_ix
 orcl_tb    | postgres | /pgData/pgsql15/orcl_tb
 pg_default | postgres |
 pg_global  | postgres |
(8 rows)

postgres=#


E. Optional: Backup on Target Host

[postgres@pg17 ~]$ nohup pg_dumpall -U postgres -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_pg17.sql > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_pg17.log 2>&1 &
[1] 4246
[postgres@pg17 ~]$


F. Restore on Target Host

[postgres@pg17 ~]$ nohup psql -U postgres -X -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql -d postgres > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_restore_pg17.log 2>&1 &
[1] 5213
[postgres@pg17 ~]$ 

-- OR --

nohup psql -U postgres -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_restore_pg17.log 2>&1 &

-- Log: backup_all_databases_10OCT2025_restore_pg17.log


G. Final Verification

[postgres@pg17 ~]$ psql
psql (15.13)
Type "help" for help.

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 edpua | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
gebua | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
orcl | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | postgres=CTc/postgres+
| | | | | | | =c/postgres
(6 rows)

postgres=#

[postgres@pg17 ~]$ psql
psql (15.13)
Type "help" for help.

postgres=# \c orcl
You are now connected to database "orcl" as user "postgres".
orcl=# SELECT
orcl-# schemaname || '.' || relname AS table_name,
orcl-# pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
orcl-# pg_size_pretty(pg_relation_size(relid)) AS table_size,
orcl-# pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
orcl-# FROM
orcl-# pg_catalog.pg_statio_user_tables
orcl-# ORDER BY
orcl-# pg_total_relation_size(relid) DESC;
 table_name | total_size | table_size | index_size
------------------+------------+------------+------------
trd.metrics_high | 799 MB | 498 MB | 301 MB
trd.metrics_mid | 638 MB | 398 MB | 240 MB
trd.employees | 493 MB | 322 MB | 172 MB
trd.sales_2023 | 399 MB | 249 MB | 150 MB
trd.sales_2024 | 399 MB | 249 MB | 150 MB
trd.sales_2022 | 399 MB | 249 MB | 150 MB
trd.sales_2021 | 398 MB | 248 MB | 150 MB
trd.metrics_low | 159 MB | 99 MB | 60 MB
trd.test_data | 71 MB | 50 MB | 21 MB
trd.emp_summary | 24 kB | 8192 bytes | 16 kB
trd.metrics_rest | 8192 bytes | 0 bytes | 8192 bytes
(11 rows)

orcl=#

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

Uncategorized, , , , , , , , ,

PostgreSQL User Management

Create Users, Groups and Schema Paths for PostgreSQL

Table of Contents

Step 1: Create a User
Step 2: Change User Password
Step 3: Grant Database Access
Step 4: Expire Password
Step 5: Set Password Never Expire
Step 6: Lock Account
Step 7: Unlock Account
Step 8: Create Schema
Step 9: Create Roles & Users
Step 10: Assign Ownership
Step 11: Grant Schema Privileges
Step 12: Grant RW Privileges
Step 13: Grant RO Privileges
Step 14: Assign Roles to Users
Step 15: Testing
Step 16: Set Schema Search Path
Step 17: Groups
Step 18: PostgreSQL — Table-View Privileges

The primary difference between CREATE ROLE and CREATE USER is that CREATE USER automatically includes the LOGIN privilege, allowing the role to connect to the database, whereas CREATE ROLE does not grant the LOGIN privilege unless it is explicitly specified.

PostgreSQL internally treats users and groups as roles. Therefore, using CREATE ROLE is often recommended. However, CREATE USER and CREATE GROUP are still supported and commonly used by DBAs. You can use whichever command you find easier to understand and manage.


Step 1: Create a User

-- Option 1: Using CREATE USER
CREATE USER john WITH PASSWORD 'mypassword';

-- Option 2: Using CREATE ROLE with LOGIN
CREATE ROLE john LOGIN PASSWORD 'mypassword';

postgres=# CREATE USER john WITH PASSWORD 'mypassword';
CREATE ROLE
postgres=# CREATE ROLE john LOGIN PASSWORD 'mypassword';
ERROR:  role "john" already exists



Step 2: Change User Password

Passwords can be changed by either an admin or the user.

By Admin:

postgres=# ALTER USER john WITH PASSWORD 'newpassword';
ALTER ROLE
postgres=#

-- OR --

postgres=# \password john
Enter new password for user "john":
Enter it again:
postgres=#


By User (self-service): From the psql prompt:

[postgres@pg17 ~]$ psql -h 192.168.2.31 -U john -d mydb -W
Password:

mydb=> \conninfo
You are connected to database "mydb" as user "john" on host "192.168.2.31" at port "5432".

mydb=> \password
Enter new password for user "john":
Enter it again:
mydb=>



Step 3: Grant Database Access

To allow a user to connect to a database:

postgres=# GRANT CONNECT ON DATABASE mydb TO john;
GRANT
postgres=#


Verify user login:

-- Connect as user
[postgres@pg17 ~]$ psql -h 192.168.2.31 -U john -d mydb -W
Password:

-- Check current user
mydb=> SELECT CURRENT_USER;
 current_user
--------------
 john
(1 row)

mydb=>
mydb=> select session_user;
 session_user
--------------
 john
(1 row)

-- Connection info
mydb=> \conninfo
You are connected to database "mydb" as user "john" on host "192.168.2.31" at port "5432".
mydb=>



Step 4: Expire User Password

postgres=# ALTER USER john VALID UNTIL '2025-09-11';
ALTER ROLE
postgres=#
postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 john      | Password valid until 2025-09-11 00:00:00-04                | {}        |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |

postgres=#



Step 5: Set Password to Never Expire

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 john      | Password valid until 2025-09-11 00:00:00-04                | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}


-- Without changing existing password

postgres=# ALTER USER john VALID UNTIL 'infinity';
ALTER ROLE
postgres=#

-- With changing existing password

postgres=# ALTER USER john WITH PASSWORD 'newpassword' VALID UNTIL 'infinity';
ALTER ROLE
postgres=#
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 john      | Password valid until infinity                              | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}


postgres=#

-- Set to future date

postgres=# ALTER USER john VALID UNTIL '2025-12-31';
ALTER ROLE
postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 john      | Password valid until 2025-12-31 00:00:00-05                | {}        |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |

postgres=#



Step 6: Lock User Account

postgres=# ALTER USER john NOLOGIN;
ALTER ROLE
postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 john      | Cannot login                                              +| {}        |
           | Password valid until infinity                              |           |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |

postgres=#



Step 7: Unlock User Account

postgres=# ALTER USER john LOGIN;
ALTER ROLE
postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 john      | Password valid until infinity                              | {}        |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |

postgres=#



Step 8: Create Schema

[postgres@pg17 ~]$ psql
psql (15.13)
Type "help" for help.

postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=#
mydb=# CREATE SCHEMA BLP;
CREATE SCHEMA
mydb=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 blp    | postgres
 public | pg_database_owner
(2 rows)

mydb=#



Step 9: Create Roles & Users

postgres=# CREATE USER "BLP" WITH PASSWORD 'blp';
CREATE ROLE
postgres=# 
postgres=# CREATE ROLE blp_rw NOLOGIN;
CREATE ROLE
postgres=# CREATE ROLE blp_ro NOLOGIN;
CREATE ROLE
postgres=#

postgres=# CREATE USER alice WITH PASSWORD 'alice123';
CREATE ROLE
postgres=# CREATE USER bob WITH PASSWORD 'bob123';
CREATE ROLE
postgres=# CREATE USER charlie WITH PASSWORD 'charlie123';
CREATE ROLE
postgres=#

postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 BLP       |                                                            | {}        |
 alice     |                                                            | {}        |
 blp_ro    | Cannot login                                               | {}        |
 blp_rw    | Cannot login                                               | {}        |
 bob       |                                                            | {}        |
 charlie   |                                                            | {}        |
 john      | Password valid until 2025-12-31 00:00:00-05                | {}        |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
 trduser   |                                                            | {}        |

postgres=#



Step 10: Assign Ownership on Schema

-- Please do NOT grant this privillege, Owner can drop the schema, change privileges, and has full control over all objects inside.

postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=#
mydb=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 blp    | postgres
 public | pg_database_owner
(2 rows)

mydb=# ALTER SCHEMA BLP OWNER TO "BLP";
ALTER SCHEMA
mydb=#

mydb=# \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 blp    | BLP               |                                        |
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(2 rows)

mydb=#



Step 11: Grant Schema Privileges to Owner

-- Full control on schema: usage + create
GRANT USAGE, CREATE ON SCHEMA blp TO "BLP";

mydb=# \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 blp    | BLP               |                                        |
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(2 rows)

mydb=# GRANT USAGE, CREATE ON SCHEMA blp TO "BLP";
GRANT
mydb=# \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 blp    | BLP               | BLP=UC/BLP                             |
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(2 rows)

mydb=#



Step 12: Grant RW Privileges

USAGE → allows the role to see the schema and its objects.

-- Grant schema access without CREATE
GRANT USAGE ON SCHEMA BLP TO blp_rw;

-- Grant DML on all existing tables
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA BLP TO blp_rw;

-- Future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA BLP GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO blp_rw;

mydb=# GRANT USAGE ON SCHEMA BLP TO blp_rw;
GRANT
mydb=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA BLP TO blp_rw;
GRANT
mydb=# ALTER DEFAULT PRIVILEGES IN SCHEMA BLP GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO blp_rw;
ALTER DEFAULT PRIVILEGES
mydb=#
mydb=# \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 blp    | BLP               | BLP=UC/BLP                            +|
        |                   | blp_rw=U/BLP                           |
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(2 rows)

mydb=#



Step 13: Grant RO Privileges

-- Grant schema access without CREATE
GRANT USAGE ON SCHEMA BLP TO BLP_RO;

-- Grant SELECT on all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA BLP TO BLP_RO;

-- Future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA BLP GRANT SELECT ON TABLES TO BLP_RO;

mydb=# GRANT USAGE ON SCHEMA BLP TO BLP_RO;
GRANT
mydb=# GRANT SELECT ON ALL TABLES IN SCHEMA BLP TO BLP_RO;
GRANT
mydb=# ALTER DEFAULT PRIVILEGES IN SCHEMA BLP GRANT SELECT ON TABLES TO BLP_RO;
ALTER DEFAULT PRIVILEGES
mydb=#
mydb=# \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 blp    | BLP               | BLP=UC/BLP                            +|
        |                   | blp_rw=U/BLP                          +|
        |                   | blp_ro=U/BLP                           |
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(2 rows)

mydb=#



Step 14: Assign Roles to Users

mydb=# GRANT BLP_RW TO ALICE;
GRANT ROLE
mydb=# 
mydb=# GRANT BLP_RO TO BOB,CHARLIE;
GRANT ROLE
mydb=#

mydb=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 BLP       |                                                            | {}        |
 alice     |                                                            | {blp_rw}  |
 blp_ro    | Cannot login                                               | {}        |
 blp_rw    | Cannot login                                               | {}        |
 bob       |                                                            | {blp_ro}  |
 charlie   |                                                            | {blp_ro}  |
 john      | Password valid until 2025-12-31 00:00:00-05                | {}        |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
 trduser   |                                                            | {}        |

mydb=#



Step 15: Testing

-- Login to BLP user on mydb database and create table on blp schema. 

[postgres@pg17 ~]$ psql -h 192.168.2.31 -U BLP -d mydb -W
Password:
psql (15.13)
Type "help" for help.

mydb=> \conninfo
You are connected to database "mydb" as user "BLP" on host "192.168.2.31" at port "5432".
mydb=> CREATE TABLE blp.employees (
mydb(>     emp_id SERIAL PRIMARY KEY,
mydb(>     first_name VARCHAR(50),
mydb(>     last_name VARCHAR(50),
mydb(>     hire_date DATE,
mydb(>     salary NUMERIC(10,2)
mydb(> );
CREATE TABLE
mydb=>

mydb=> INSERT INTO blp.employees (first_name, last_name, hire_date, salary) VALUES
mydb-> ('John', 'Doe', '2023-01-15', 5000.00),
mydb-> ('Jane', 'Smith', '2022-11-20', 6000.00),
mydb-> ('Alice', 'Johnson', '2024-03-01', 5500.00);
INSERT 0 3
mydb=>
mydb=> select * from blp.employees;
 emp_id | first_name | last_name | hire_date  | salary
--------+------------+-----------+------------+---------
      1 | John       | Doe       | 2023-01-15 | 5000.00
      2 | Jane       | Smith     | 2022-11-20 | 6000.00
      3 | Alice      | Johnson   | 2024-03-01 | 5500.00
(3 rows)

mydb=>

mydb=> drop table blp.employees;
DROP TABLE
mydb=>

-- Login to alice user on mydb database and update table on blp schema. 



[root@pg17 ~]# psql -h 192.168.2.31 -U alice -d mydb -W
Password:
psql (15.13)
Type "help" for help.

mydb=> \conninfo
You are connected to database "mydb" as user "alice" on host "192.168.2.31" at port "5432".
mydb=>

mydb=> \du+ alice
                  List of roles
 Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
 alice     |            | {blp_rw}  |

mydb=>


mydb=> \dt+ blp.*
                                      List of relations
 Schema |   Name    | Type  | Owner | Persistence | Access method |    Size    | Description
--------+-----------+-------+-------+-------------+---------------+------------+-------------
 blp    | employees | table | BLP   | permanent   | heap          | 8192 bytes |
(1 row)

mydb=> select * from blp.employees;
 emp_id | first_name | last_name | hire_date  | salary
--------+------------+-----------+------------+---------
      1 | John       | Doe       | 2023-01-15 | 5000.00
      2 | Jane       | Smith     | 2022-11-20 | 6000.00
      3 | Alice      | Johnson   | 2024-03-01 | 5500.00
(3 rows)

mydb=> UPDATE blp.employees
SET salary = CASE
                WHEN first_name = 'John' THEN 7000.00
                WHEN first_name = 'Alice' THEN 6500.00
             END
WHERE first_name IN ('John', 'Alice');
UPDATE 2
mydb=> select * from blp.employees;
 emp_id | first_name | last_name | hire_date  | salary
--------+------------+-----------+------------+---------
      2 | Jane       | Smith     | 2022-11-20 | 6000.00
      1 | John       | Doe       | 2023-01-15 | 7000.00
      3 | Alice      | Johnson   | 2024-03-01 | 6500.00
(3 rows)

mydb=>

-- Note, we have granted only DML privilleges, hence create and alter table command failing

mydb=> CREATE TABLE blp.departments (
mydb(>     dept_id SERIAL PRIMARY KEY,
mydb(>     dept_name VARCHAR(100) NOT NULL,
mydb(>     location VARCHAR(100)
mydb(> );
ERROR:  permission denied for schema blp
LINE 1: CREATE TABLE blp.departments (
                     ^
mydb=>

mydb=> ALTER TABLE blp.employees
mydb-> ADD COLUMN department VARCHAR(50);
ERROR:  must be owner of table employees
mydb=>

-- Login to bob user on mydb database and select table on blp schema. 

[postgres@pg17 ~]$ psql -h 192.168.2.31 -U bob -d mydb -W
Password:
psql (15.13)
Type "help" for help.

mydb=> \conninfo
You are connected to database "mydb" as user "bob" on host "192.168.2.31" at port "5432".
mydb=>
mydb=> \du+ bob
                  List of roles
 Role name | Attributes | Member of | Description
-----------+------------+-----------+-------------
 bob       |            | {blp_ro}  |

mydb-> \dt+ blp.*
                                      List of relations
 Schema |   Name    | Type  | Owner | Persistence | Access method |    Size    | Description
--------+-----------+-------+-------+-------------+---------------+------------+-------------
 blp    | employees | table | BLP   | permanent   | heap          | 8192 bytes |
(1 row)

mydb=> select * from blp.employees;
 emp_id | first_name | last_name | hire_date  | salary
--------+------------+-----------+------------+---------
      2 | Jane       | Smith     | 2022-11-20 | 6000.00
      1 | John       | Doe       | 2023-01-15 | 7000.00
      3 | Alice      | Johnson   | 2024-03-01 | 6500.00
(3 rows)

mydb=>

mydb=> INSERT INTO blp.employees (first_name, last_name, hire_date, salary) VALUES
mydb-> ('Bob', 'Williams', '2024-04-01', 5800.00),
mydb-> ('Clara', 'Brown', '2024-05-10', 6200.00),
mydb-> ('David', 'Lee', '2024-06-15', 5300.00);
ERROR:  permission denied for table employees
mydb=>



Step 16: Set Schema Search Path

[postgres@pg17 ~]$ psql -h 192.168.2.31 -U BLP -d mydb -W
Password:
psql (15.13)
Type "help" for help.

mydb=> 
mydb=> \conninfo
You are connected to database "mydb" as user "BLP" on host "192.168.2.31" at port "5432".
mydb=>
mydb=> \dt+ blp.*
                                      List of relations
 Schema |   Name    | Type  | Owner | Persistence | Access method |    Size    | Description
--------+-----------+-------+-------+-------------+---------------+------------+-------------
 blp    | employees | table | BLP   | permanent   | heap          | 8192 bytes |
(1 row)

mydb=> 

mydb=> select * from employees;
ERROR:  relation "employees" does not exist
LINE 1: select * from employees;
mydb=>

mydb=> SET search_path to BLP; -- Temporarily for this session
SET
mydb=> select * from employees;
 emp_id | first_name | last_name | hire_date  | salary
--------+------------+-----------+------------+---------
      2 | Jane       | Smith     | 2022-11-20 | 6000.00
      1 | John       | Doe       | 2023-01-15 | 7000.00
      3 | Alice      | Johnson   | 2024-03-01 | 6500.00
(3 rows)

mydb=>

-- Make the Schema Default for the user BLP -- Permenant
ALTER ROLE blp_owner SET search_path = BLP;


Step 17: Groups

postgres=# CREATE GROUP app_users;
postgres=# ALTER GROUP app_users ADD USER alice;
postgres=# ALTER GROUP app_users ADD USER bob;
postgres=# ALTER GROUP app_users ADD USER charlie;
postgres=# ALTER GROUP app_users DROP USER charlie;
postgres=# ALTER GROUP app_users RENAME TO appusers;
postgres=# DROP GROUP appusers;

mydb=> select * from pg_group;
          groname          | grosysid |    grolist
---------------------------+----------+---------------
 pg_database_owner         |     6171 | {}
 pg_read_all_data          |     6181 | {}
 pg_write_all_data         |     6182 | {}
 pg_monitor                |     3373 | {}
 pg_read_all_settings      |     3374 | {3373}
 pg_read_all_stats         |     3375 | {3373}
 pg_stat_scan_tables       |     3377 | {3373}
 pg_read_server_files      |     4569 | {}
 pg_write_server_files     |     4570 | {}
 pg_execute_server_program |     4571 | {}
 pg_signal_backend         |     4200 | {}
 pg_checkpoint             |     4544 | {}
 blp_rw                    |    84444 | {84447}
 blp_ro                    |    84445 | {84448,84449}
 app_users                 |    84468 | {}
(15 rows)

mydb=>



Step 18: PostgreSQL Object-Level Privilege Summary

mydb=> \z blp.employees
                               Access privileges
 Schema |   Name    | Type  | Access privileges | Column privileges | Policies
--------+-----------+-------+-------------------+-------------------+----------
 blp    | employees | table | BLP=arwdDxt/BLP  +|                   |
        |           |       | blp_ro=r/BLP     +|                   |
        |           |       | blp_rw=arwd/BLP   |                   |
(1 row)

Example:

 

ShortFull PrivilegeExample GRANT
rSELECTGRANT SELECT ON employees TO hr_user;
aINSERTGRANT INSERT ON employees TO hr_user;
wUPDATEGRANT UPDATE ON employees TO hr_user;
dDELETEGRANT DELETE ON employees TO hr_user;
DTRUNCATEGRANT TRUNCATE ON employees TO hr_user;
xREFERENCESGRANT REFERENCES ON employees TO hr_user;
tTRIGGERGRANT TRIGGER ON employees TO hr_user;
RRULEGRANT RULE ON employees TO hr_user; (rarely used)

 

Tip: the shorthand string you see in \z (for example arwdDxt) can be expanded by mapping each letter to the rows above, then converting them into one or more GRANT statements.

 

 

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