Tag Archives: database administration

PostgreSQL

PostgreSQL DBA Step by Step Learning

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

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

Change Data Directory

PostgreSQL.bash_profile, Change Data Directory, chmod, chown, daemon-reload, database administration, How to change PGDATA, Linux PostgreSQL, mkdir, mv, pgdata, PostgreSQL 17, PostgreSQL Configuration, PostgreSQL Directory Migration, PostgreSQL rsync Copy, PostgreSQL Service Configuration, PostgreSQL Setup, PostgreSQL Tuning, postgresql-17.service, psql, Relocate PostgreSQL Data, rsync, sed, systemctl, Systemd PostgreSQL

PostgreSQL 17 – Change Data Directory

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

0. Overview

To change the PostgreSQL 17 data directory from its default location

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

1. Stop PostgreSQL Service

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

[postgres@lxicbpgdsgv01 ~]$

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

2. Create the New Directory

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

3. Copy Existing Data to New Location

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

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

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

[postgres@lxicbpgdsgv01 ~]$

4. Move existing data directory

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

5. Update PostgreSQL Configuration to Use New Data Directory

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

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

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

6. Reload systemd and start PostgreSQL

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

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

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

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

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

7. Edit postgres .bash_profile

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

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

8. Verify New Data Directory is in Use

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

[postgres@lxicbpgdsgv01 ~]$

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

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

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

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

PostgreSQL Tablespace

Uncategorizeddatabase administration, Database Management, Database Storage, Manage Tablespaces, pg_tablespace, PostgreSQL, PostgreSQL DBA, PostgreSQL File System, PostgreSQL Performance, PostgreSQL Storage, PostgreSQL Tablespace, PostgreSQL Tips, PostgreSQL Tuning, Tablespace Management, Tablespace Usage

PostgreSQL Tablespace Management

Table of Contents

___________________________________________________________________________________________________

0. Introduction
1. How big can a PostgreSQL tablespace grow?
2. Create a Tablespace
3. Create a Database with default tablespace
4. Create Tables on Data tablspace
5. Crate Index on Index tablespaces
6. Create a Dedicated Tablespace for Temp
7. Assign Temp Tablespace to a Database
8. Check Tablespace OIDs
9. Move Table Between Tablespaces
10. Verify Database Directory (Filesystem)
11. Rename tablespace
12. Drop Tablespace
13. Change ownership for Tablespace
14. Verify Symbolic Links
15. How to find tablespace size
___________________________________________________________________________________________________

0. Introduction

What is a Tablespace?

--> Tablespace is a logical structure in which objects are stored.

--> A tablespace is simply a physical storage location on disk where PostgreSQL stores database objects (tables, indexes, etc.). 

--> By default database objects are stored in the current default tablespace of the database.

List tablespaces:

\db+ or select * from pg_tablespace;

Types of Tablespaces: Default Tablespaces and Non-Default Tablespaces
I. Default Tablespaces

These come built-in with PostgreSQL:

pg_global – for shared system data
pg_default – for regular user data (tables, indexes, etc.)

Feature	`pg_default`	`pg_global`
Purpose	Stores regular user data	Stores global system data
Used by default?	Yes, for tables/indexes	No, only used internally
Can store user tables?	✅ Yes	❌ No
Location	`$PGDATA/base/`	`$PGDATA/global/`
Droppable?	❌ No	❌ No

II. Non-Default Tablespaces :

--> These are created by users to store data in custom locations.
--> Useful for managing storage better (e.g., putting large tables on faster disks).

1. How big can a PostgreSQL tablespace grow?

There is no fixed maximum size for a tablespace in PostgreSQL itself.
The limit depends on your operating system and filesystem where the tablespace directory is located.

For example:

On modern filesystems like ext4 or XFS, a single file can be several terabytes (TB) or even petabytes (PB).
PostgreSQL breaks large tables into multiple files (each typically up to 1 GB) internally, so extremely large tablespaces are supported.
Your disk/storage capacity is the main practical limit.

2. Create a Tablespace

[root@pg17 ~]# mkdir -p /pgData/pgsql15/edbua_tbs01
[root@pg17 ~]# mkdir -p /pgData/pgsql15/edbua_tbs02
[root@pg17 ~]# mkdir -p /pgData/pgsql15/edbua_ixtbs01
[root@pg17 ~]# mkdir -p /pgData/pgsql15/edbua_temptbs01
[root@pg17 ~]# chown postgres:postgres /pgData/pgsql15/edbua*
[root@pg17 ~]# chmod 700 /pgData/pgsql15/edbua*
[root@pg17 ~]#

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

postgres=# CREATE TABLESPACE edbua_tbs01 LOCATION '/pgData/pgsql15/edbua_tbs01';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE edbua_tbs02 LOCATION '/pgData/pgsql15/edbua_tbs02';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE edbua_ixtbs01 LOCATION '/pgData/pgsql15/edbua_ixtbs01';
CREATE TABLESPACE
postgres=#

3. Create a Database with default tablespace

postgres=# CREATE DATABASE edbua TABLESPACE edbua_tbs01;
CREATE DATABASE
postgres=# 
postgres=# SELECT oid, datname, dattablespace, datistemplate, datdba FROM pg_database WHERE datname = 'edbua';
  oid  | datname | dattablespace | datistemplate | datdba
-------+---------+---------------+---------------+--------
 92666 | edbua   |         92662 | f             |     10
(1 row)

postgres=#

4. Create Tables on Data tablspace

postgres=# \c edbua
You are now connected to database "edbua" as user "postgres".
edbua=# CREATE TABLE emp01 (id int, name text) TABLESPACE edbua_tbs01;
CREATE TABLE
edbua=# CREATE TABLE emp02 (id int, name text) TABLESPACE edbua_tbs01;
CREATE TABLE
edbua=# CREATE TABLE emp03 (id int, name text) TABLESPACE edbua_tbs01;
CREATE TABLE
edbua=#

5. Create Index on Index tablespaces

edbua=# CREATE INDEX emp01_ix ON emp01(name) TABLESPACE edbua_ixtbs01;
CREATE INDEX
edbua=# CREATE INDEX emp02_ix ON emp02(name) TABLESPACE edbua_ixtbs01;
CREATE INDEX
edbua=# CREATE INDEX emp03_ix ON emp03(name) TABLESPACE edbua_ixtbs01;
CREATE INDEX
edbua=# SELECT c.oid, c.relname, c.relkind, t.spcname AS tablespace, c.relfilenode
edbua-# FROM pg_class c
edbua-# LEFT JOIN pg_tablespace t ON c.reltablespace = t.oid
edbua-# WHERE c.relname IN ('emp01','emp02','emp03','emp01_ix','emp02_ix','emp03_ix');
  oid  | relname  | relkind |  tablespace   | relfilenode
-------+----------+---------+---------------+-------------
 92685 | emp01    | r       |               |       92685
 92690 | emp02    | r       |               |       92690
 92695 | emp03    | r       |               |       92695
 92700 | emp01_ix | i       | edbua_ixtbs01 |       92700
 92701 | emp02_ix | i       | edbua_ixtbs01 |       92701
 92702 | emp03_ix | i       | edbua_ixtbs01 |       92702
(6 rows)

edbua=#

If a table or index is created in the database’s default tablespace, PostgreSQL does not explicitly record the tablespace in pg_class.reltablespace.

But when it’s created in a non-default tablespace, PostgreSQL records that tablespace OID in pg_class.reltablespace.

If you want to always see the tablespace name (even when it's the default)

edbua=# SELECT
edbua-#     c.oid,
edbua-#     c.relname,
edbua-#     c.relkind,
edbua-#     COALESCE(t1.spcname, t2.spcname) AS tablespace,
edbua-#     c.relfilenode
edbua-# FROM pg_class c
edbua-# LEFT JOIN pg_tablespace t1 ON c.reltablespace = t1.oid
edbua-# LEFT JOIN pg_database d ON d.datname = current_database()
edbua-# LEFT JOIN pg_tablespace t2 ON d.dattablespace = t2.oid
edbua-# WHERE c.relname IN ('emp01','emp02','emp03','emp01_ix','emp02_ix','emp03_ix');
  oid  | relname  | relkind |  tablespace   | relfilenode
-------+----------+---------+---------------+-------------
 92685 | emp01    | r       | edbua_tbs01   |       92685
 92690 | emp02    | r       | edbua_tbs01   |       92690
 92695 | emp03    | r       | edbua_tbs01   |       92695
 92700 | emp01_ix | i       | edbua_ixtbs01 |       92700
 92701 | emp02_ix | i       | edbua_ixtbs01 |       92701
 92702 | emp03_ix | i       | edbua_ixtbs01 |       92702
(6 rows)

edbua=#

pg_tblspc/<tbs_oid>/PG_15_<catalog_version>/<db_oid>/<relfilenode>

edbua=# SELECT relname, pg_relation_filepath(oid) AS filepath
edbua-# FROM pg_class
edbua-# WHERE relname IN ('emp01','emp02','emp03','emp01_ix','emp02_ix','emp03_ix');
 relname  |                  filepath
----------+---------------------------------------------
 emp01    | pg_tblspc/92662/PG_15_202209061/92666/92685
 emp02    | pg_tblspc/92662/PG_15_202209061/92666/92690
 emp03    | pg_tblspc/92662/PG_15_202209061/92666/92695
 emp01_ix | pg_tblspc/92664/PG_15_202209061/92666/92700
 emp02_ix | pg_tblspc/92664/PG_15_202209061/92666/92701
 emp03_ix | pg_tblspc/92664/PG_15_202209061/92666/92702
(6 rows)

edbua=#

6. Create a Dedicated Tablespace for Temp

postgres=# CREATE TABLESPACE edbua_temptbs01 LOCATION '/pgData/pgsql15/edbua_temptbs01';
CREATE TABLESPACE
postgres=#

7. Assign Temp Tablespace to a Database

postgres=# ALTER DATABASE edbua SET temp_tablespaces = edbua_temptbs01;
ALTER DATABASE
postgres=#

postgres=# \c edbua
You are now connected to database "edbua" as user "postgres".
edbua=# 

edbua=# CREATE TEMP TABLE emp_tmp (id int, name text);
CREATE TABLE
edbua=#
edbua=# SELECT c.oid, c.relname, t.spcname, pg_relation_filepath(c.oid)
edbua-# FROM pg_class c
edbua-# LEFT JOIN pg_tablespace t ON c.reltablespace = t.oid
edbua-# WHERE c.relname = 'emp_tmp';
  oid  | relname |     spcname     |              pg_relation_filepath
-------+---------+-----------------+------------------------------------------------
 92705 | emp_tmp | edbua_temptbs01 | pg_tblspc/92665/PG_15_202209061/92666/t4_92705
(1 row)

edbua=#

pg_tblspc/<tbs_oid>/PG_15_<catalog_version>/<db_oid>/<relfilenode>

8. Check Tablespace OIDs

edbua=# SELECT oid, spcname AS tablespace_name, pg_tablespace_location(oid) AS location FROM pg_tablespace ORDER BY oid;
  oid  | tablespace_name |            location
-------+-----------------+---------------------------------
  1663 | pg_default      |
  1664 | pg_global       |
 24581 | dell_ts_data    | /u01/data/dell_ts_data
 24582 | dell_ts_index   | /u01/data/dell_ts_index
 24594 | dell_tbs        | /pgData/pgsql15/data
 24597 | trd_tb01        | /pgData/pgsql15/data/trd_tb01
 24598 | trd_ix01        | /pgData/pgsql15/data/trd_ix01
 32787 | geb_tb          | /pgData/pgsql15/pgtb/geb_tb
 32788 | geb_ix          | /pgData/pgsql15/pgix/geb_ix
 92662 | edbua_tbs01     | /pgData/pgsql15/edbua_tbs01
 92663 | edbua_tbs02     | /pgData/pgsql15/edbua_tbs02
 92664 | edbua_ixtbs01   | /pgData/pgsql15/edbua_ixtbs01
 92665 | edbua_temptbs01 | /pgData/pgsql15/edbua_temptbs01
(13 rows)

edbua=#

9. Move Table Between Tablespaces

edbua=# ALTER TABLE emp01 SET TABLESPACE edbua_tbs02;
ALTER TABLE
edbua=# ALTER TABLE emp02 SET TABLESPACE edbua_tbs02;
ALTER TABLE
edbua=# ALTER TABLE emp03 SET TABLESPACE edbua_tbs02;
ALTER TABLE
edbua=#

pg_tblspc/<tbs_oid>/PG_15_<catalog_version>/<db_oid>/<relfilenode>

edbua=# select pg_relation_filepath('emp01');
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/92663/PG_15_202209061/92666/92710
(1 row)

edbua=# 

edbua=# SELECT relname, pg_relation_filepath(oid) AS filepath
FROM pg_class
WHERE relname IN ('emp01','emp02','emp03','emp01_ix','emp02_ix','emp03_ix');
 relname  |                  filepath
----------+---------------------------------------------
 emp01    | pg_tblspc/92663/PG_15_202209061/92666/92710
 emp02    | pg_tblspc/92663/PG_15_202209061/92666/92713
 emp03    | pg_tblspc/92663/PG_15_202209061/92666/92716
 emp01_ix | pg_tblspc/92664/PG_15_202209061/92666/92700
 emp02_ix | pg_tblspc/92664/PG_15_202209061/92666/92701
 emp03_ix | pg_tblspc/92664/PG_15_202209061/92666/92702
(6 rows)

edbua=#

Important: Indexes do not move automatically when you move a table.
They remain in their original tablespace (edbua_ixtbs01 in this case).
If you want indexes in edbua_tbs02, you must explicitly run:

edbua=# ALTER INDEX emp01_ix SET TABLESPACE edbua_tbs02;
ALTER INDEX
edbua=# ALTER INDEX emp02_ix SET TABLESPACE edbua_tbs02;
ALTER INDEX
edbua=# ALTER INDEX emp03_ix SET TABLESPACE edbua_tbs02;
ALTER INDEX
edbua=#
edbua=# SELECT c.relname, t.spcname AS tablespace, pg_relation_filepath(c.oid)
edbua-# FROM pg_class c
edbua-# LEFT JOIN pg_tablespace t ON c.reltablespace = t.oid
edbua-# WHERE c.relname IN ('emp01','emp02','emp03','emp01_ix','emp02_ix','emp03_ix');
 relname  | tablespace  |            pg_relation_filepath
----------+-------------+---------------------------------------------
 emp01_ix | edbua_tbs02 | pg_tblspc/92663/PG_15_202209061/92666/92719
 emp02_ix | edbua_tbs02 | pg_tblspc/92663/PG_15_202209061/92666/92720
 emp03_ix | edbua_tbs02 | pg_tblspc/92663/PG_15_202209061/92666/92721
 emp01    | edbua_tbs02 | pg_tblspc/92663/PG_15_202209061/92666/92710
 emp02    | edbua_tbs02 | pg_tblspc/92663/PG_15_202209061/92666/92713
 emp03    | edbua_tbs02 | pg_tblspc/92663/PG_15_202209061/92666/92716
(6 rows)

edbua=#

edbua=# SELECT
edbua-#     c.relname,
edbua-#     COALESCE(t1.spcname, t2.spcname) AS tablespace,
edbua-#     pg_catalog.pg_tablespace_location(COALESCE(c.reltablespace, d.dattablespace)) AS tablespace_path,
edbua-#     current_setting('data_directory') AS data_directory,
edbua-#     CASE
edbua-#         WHEN c.reltablespace = 0 THEN
edbua-#             current_setting('data_directory') || '/base/' || d.oid || '/' || c.relfilenode
edbua-#         ELSE
edbua-#             pg_catalog.pg_tablespace_location(c.reltablespace) || '/PG_' ||
edbua-#             split_part(split_part(version(), ' ', 2), '.', 1) || '_' ||
edbua-#             '202209061/' || d.oid || '/' || c.relfilenode
edbua-#     END AS full_path_guess
edbua-# FROM pg_class c
edbua-# LEFT JOIN pg_tablespace t1 ON c.reltablespace = t1.oid
edbua-# LEFT JOIN pg_database d ON d.datname = current_database()
edbua-# LEFT JOIN pg_tablespace t2 ON d.dattablespace = t2.oid
edbua-# WHERE c.relname IN ('emp01','emp02','emp03','emp01_ix','emp02_ix','emp03_ix');
 relname  | tablespace  |       tablespace_path       |    data_directory    |                     full_path_guess
----------+-------------+-----------------------------+----------------------+---------------------------------------------------------
 emp01_ix | edbua_tbs02 | /pgData/pgsql15/edbua_tbs02 | /pgData/pgsql15/data | /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92719
 emp02_ix | edbua_tbs02 | /pgData/pgsql15/edbua_tbs02 | /pgData/pgsql15/data | /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92720
 emp03_ix | edbua_tbs02 | /pgData/pgsql15/edbua_tbs02 | /pgData/pgsql15/data | /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92721
 emp01    | edbua_tbs02 | /pgData/pgsql15/edbua_tbs02 | /pgData/pgsql15/data | /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92710
 emp02    | edbua_tbs02 | /pgData/pgsql15/edbua_tbs02 | /pgData/pgsql15/data | /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92713
 emp03    | edbua_tbs02 | /pgData/pgsql15/edbua_tbs02 | /pgData/pgsql15/data | /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92716
(6 rows)

edbua=#

10. Verify Database Directory (Filesystem)

edbua=# SELECT oid, datname FROM pg_database WHERE datname = current_database();
  oid  | datname
-------+---------
 92666 | edbua
(1 row)

edbua=#

[root@pg17 ~]# ls -ltr /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/*
-rw-------. 1 postgres postgres    0 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92710
-rw-------. 1 postgres postgres    0 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92711
-rw-------. 1 postgres postgres 8192 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92712
-rw-------. 1 postgres postgres    0 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92713
-rw-------. 1 postgres postgres    0 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92714
-rw-------. 1 postgres postgres 8192 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92715
-rw-------. 1 postgres postgres    0 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92716
-rw-------. 1 postgres postgres    0 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92717
-rw-------. 1 postgres postgres 8192 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92718
-rw-------. 1 postgres postgres 8192 Sep 23 00:52 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92719
-rw-------. 1 postgres postgres 8192 Sep 23 00:52 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92720
-rw-------. 1 postgres postgres 8192 Sep 23 00:52 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92721
[root@pg17 ~]#


Issue: Why are there 12 files when I have only 6 objects?

created only: 3 tables: emp01, emp02, emp03 & 3 indexes: emp01_ix, emp02_ix, emp03_ix

But in the tablespace directory, we're seeing 12 files: 3 tables + 3 indexes + 3 TOAST tables + 3 TOAST indexes

edbua=# SELECT
edbua-#     c.relname,
edbua-#     c.relfilenode,
edbua-#     c.relkind,
edbua-#     n.nspname AS schema,
edbua-#     t.spcname AS tablespace
edbua-# FROM
edbua-#     pg_class c
edbua-# JOIN
edbua-#     pg_namespace n ON n.oid = c.relnamespace
edbua-# LEFT JOIN
edbua-#     pg_tablespace t ON c.reltablespace = t.oid
edbua-# WHERE
edbua-#     c.reltablespace = (SELECT oid FROM pg_tablespace WHERE spcname = 'edbua_tbs02')
edbua-# ORDER BY
edbua-#     c.relfilenode;
       relname        | relfilenode | relkind |  schema  | tablespace
----------------------+-------------+---------+----------+-------------
 emp01                |       92710 | r       | public   | edbua_tbs02
 pg_toast_92685       |       92711 | t       | pg_toast | edbua_tbs02
 pg_toast_92685_index |       92712 | i       | pg_toast | edbua_tbs02
 emp02                |       92713 | r       | public   | edbua_tbs02
 pg_toast_92690       |       92714 | t       | pg_toast | edbua_tbs02
 pg_toast_92690_index |       92715 | i       | pg_toast | edbua_tbs02
 emp03                |       92716 | r       | public   | edbua_tbs02
 pg_toast_92695       |       92717 | t       | pg_toast | edbua_tbs02
 pg_toast_92695_index |       92718 | i       | pg_toast | edbua_tbs02
 emp01_ix             |       92719 | i       | public   | edbua_tbs02
 emp02_ix             |       92720 | i       | public   | edbua_tbs02
 emp03_ix             |       92721 | i       | public   | edbua_tbs02
(12 rows)

edbua=#

11. Rename tablespace

edbua=# ALTER TABLESPACE edbua_tbs02 RENAME TO edbua_tbs03;
ALTER TABLESPACE
edbua=#

edbua=# \db+ edbua*
                                                List of tablespaces
      Name       |  Owner   |            Location             | Access privileges | Options |  Size   | Description
-----------------+----------+---------------------------------+-------------------+---------+---------+-------------
 edbua_ixtbs01   | postgres | /pgData/pgsql15/edbua_ixtbs01   |                   |         | 6 bytes |
 edbua_tbs01     | postgres | /pgData/pgsql15/edbua_tbs01     |                   |         | 7789 kB |
 edbua_tbs03     | postgres | /pgData/pgsql15/edbua_tbs02     |                   |         | 48 kB   |
 edbua_temptbs01 | postgres | /pgData/pgsql15/edbua_temptbs01 |                   |         | 6 bytes |
(4 rows)

edbua=#

12. Drop Tablespace

edbua=# drop tablespace edbua_tbs03;
ERROR:  tablespace "edbua_tbs03" is not empty
edbua=#

edbua=# SELECT
edbua-#   CASE
edbua-#     WHEN c.relkind = 'r' THEN
edbua-#       format('ALTER TABLE %I.%I SET TABLESPACE edbua_tbs01;', n.nspname, c.relname)
edbua-#     WHEN c.relkind = 'i' THEN
edbua-#       format('ALTER INDEX %I.%I SET TABLESPACE edbua_tbs01;', n.nspname, c.relname)
edbua-#     WHEN c.relkind = 'S' THEN
edbua-#       format('ALTER SEQUENCE %I.%I SET TABLESPACE edbua_tbs01;', n.nspname, c.relname)
edbua-#   END AS alter_statement
edbua-# FROM pg_class c
edbua-# JOIN pg_namespace n ON n.oid = c.relnamespace
edbua-# WHERE c.reltablespace = (SELECT oid FROM pg_tablespace WHERE spcname = 'edbua_tbs03')
edbua-#   AND c.relkind IN ('r', 'i', 'S')
edbua-#   AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
edbua-# ORDER BY n.nspname, c.relname;
                     alter_statement
---------------------------------------------------------
 ALTER TABLE public.emp01 SET TABLESPACE edbua_tbs01;
 ALTER INDEX public.emp01_ix SET TABLESPACE edbua_tbs01;
 ALTER TABLE public.emp02 SET TABLESPACE edbua_tbs01;
 ALTER INDEX public.emp02_ix SET TABLESPACE edbua_tbs01;
 ALTER TABLE public.emp03 SET TABLESPACE edbua_tbs01;
 ALTER INDEX public.emp03_ix SET TABLESPACE edbua_tbs01;
(6 rows)

edbua=#

edbua=# ALTER TABLE public.emp01 SET TABLESPACE edbua_tbs01;
 ALTER INDEX public.emp02_ix SET TABLESPACE edbua_tbs01;
 ALTER TABLE public.emp03 SET TABLESPACE edbua_tbs01;
 ALTER INDEX public.emp03_ix SET TABLESPACE edbua_tbs01;ALTER TABLE
edbua=#  ALTER INDEX public.emp01_ix SET TABLESPACE edbua_tbs01;
ALTER INDEX
edbua=#  ALTER TABLE public.emp02 SET TABLESPACE edbua_tbs01;
ALTER TABLE
edbua=#  ALTER INDEX public.emp02_ix SET TABLESPACE edbua_tbs01;
ALTER INDEX
edbua=#  ALTER TABLE public.emp03 SET TABLESPACE edbua_tbs01;
ALTER TABLE
edbua=#  ALTER INDEX public.emp03_ix SET TABLESPACE edbua_tbs01;
ALTER INDEX
edbua=#

edbua=# drop tablespace edbua_tbs03;
DROP TABLESPACE
edbua=# \db+ edbua*
                                                List of tablespaces
      Name       |  Owner   |            Location             | Access privileges | Options |  Size   | Description
-----------------+----------+---------------------------------+-------------------+---------+---------+-------------
 edbua_ixtbs01   | postgres | /pgData/pgsql15/edbua_ixtbs01   |                   |         | 6 bytes |
 edbua_tbs01     | postgres | /pgData/pgsql15/edbua_tbs01     |                   |         | 7837 kB |
 edbua_temptbs01 | postgres | /pgData/pgsql15/edbua_temptbs01 |                   |         | 6 bytes |
(3 rows)

edbua=#

13. Change ownership for Tablespace

edbua=# ALTER TABLESPACE edbua_tbs01 OWNER TO teja;
ALTER TABLESPACE
edbua=# \db+ edbua*
                                                List of tablespaces
      Name       |  Owner   |            Location             | Access privileges | Options |  Size   | Description
-----------------+----------+---------------------------------+-------------------+---------+---------+-------------
 edbua_ixtbs01   | postgres | /pgData/pgsql15/edbua_ixtbs01   |                   |         | 6 bytes |
 edbua_tbs01     | teja     | /pgData/pgsql15/edbua_tbs01     |                   |         | 7837 kB |
 edbua_temptbs01 | postgres | /pgData/pgsql15/edbua_temptbs01 |                   |         | 6 bytes |
(3 rows)

edbua=# ALTER TABLESPACE edbua_tbs01 OWNER TO postgres;
ALTER TABLESPACE
edbua=# \db+ edbua*
                                                List of tablespaces
      Name       |  Owner   |            Location             | Access privileges | Options |  Size   | Description
-----------------+----------+---------------------------------+-------------------+---------+---------+-------------
 edbua_ixtbs01   | postgres | /pgData/pgsql15/edbua_ixtbs01   |                   |         | 6 bytes |
 edbua_tbs01     | postgres | /pgData/pgsql15/edbua_tbs01     |                   |         | 7837 kB |
 edbua_temptbs01 | postgres | /pgData/pgsql15/edbua_temptbs01 |                   |         | 6 bytes |
(3 rows)

edbua=#

14. Verify Symbolic Links

edbua=# show data_directory;
    data_directory
----------------------
 /pgData/pgsql15/data  <----
(1 row)

edbua=#

[root@pg17 pg_tblspc]# ls -ltr /pgData/pgsql15/data/pg_tblspc/*
lrwxrwxrwx. 1 postgres postgres 22 Aug 18 06:36 /pgData/pgsql15/data/pg_tblspc/24581 -> /u01/data/dell_ts_data
lrwxrwxrwx. 1 postgres postgres 23 Aug 18 06:36 /pgData/pgsql15/data/pg_tblspc/24582 -> /u01/data/dell_ts_index
lrwxrwxrwx. 1 postgres postgres 20 Sep  3 09:52 /pgData/pgsql15/data/pg_tblspc/24594 -> /pgData/pgsql15/data
lrwxrwxrwx. 1 postgres postgres 29 Sep  3 11:55 /pgData/pgsql15/data/pg_tblspc/24597 -> /pgData/pgsql15/data/trd_tb01
lrwxrwxrwx. 1 postgres postgres 29 Sep  3 11:55 /pgData/pgsql15/data/pg_tblspc/24598 -> /pgData/pgsql15/data/trd_ix01
lrwxrwxrwx. 1 postgres postgres 27 Sep  4 05:59 /pgData/pgsql15/data/pg_tblspc/32787 -> /pgData/pgsql15/pgtb/geb_tb
lrwxrwxrwx. 1 postgres postgres 27 Sep  4 06:03 /pgData/pgsql15/data/pg_tblspc/32788 -> /pgData/pgsql15/pgix/geb_ix
lrwxrwxrwx. 1 postgres postgres 27 Sep 20 07:20 /pgData/pgsql15/data/pg_tblspc/92662 -> /pgData/pgsql15/edbua_tbs01
lrwxrwxrwx. 1 postgres postgres 29 Sep 20 07:21 /pgData/pgsql15/data/pg_tblspc/92664 -> /pgData/pgsql15/edbua_ixtbs01
lrwxrwxrwx. 1 postgres postgres 31 Sep 20 07:21 /pgData/pgsql15/data/pg_tblspc/92665 -> /pgData/pgsql15/edbua_temptbs01
[root@pg17 pg_tblspc]#

15. How to find tablespace size

edbua=# SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) AS size FROM pg_tablespace;
     spcname     |  size
-----------------+---------
 pg_default      | 1660 MB
 pg_global       | 571 kB
 dell_ts_data    | 7761 kB
 dell_ts_index   | 16 kB
 dell_tbs        | 0 bytes
 trd_tb01        | 1615 MB
 trd_ix01        | 2648 kB
 geb_tb          | 13 GB
 geb_ix          | 8950 MB
 edbua_ixtbs01   | 6 bytes
 edbua_temptbs01 | 6 bytes
 edbua_tbs01     | 7837 kB
(12 rows)

edbua=#

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

PostgreSQL pg_hba.conf

PostgreSQLdatabase administration, Host-Based Authentication, pg_hba, pg_hba.conf, PostgreSQL, PostgreSQL 15, PostgreSQL Authentication, PostgreSQL Configuration, PostgreSQL Roles and Users, PostgreSQL security, PostgreSQL Tutorial

PostgreSQL pg_hba.conf Guide

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

1. What is pg_hba.conf?

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



Format:

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

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

2. Authentication Methods in pg_hba.conf

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

Example:

host    all    all    127.0.0.1/32    trust

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

Example:

host    all    all    127.0.0.1/32    md5

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

Example:

host    all    all    127.0.0.1/32    scram-sha-256


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

Example:

local   all    all                     peer


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

Example:

host    all    all    0.0.0.0/0       reject


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

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

3. Create Database and User

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

4. Contents of pg_hba.conf

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

# Allow local peer access for postgres
local   all             postgres                                peer

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

[postgres@pg17 ~]$

5. Play with peer

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

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

# Allow local peer access for postgres

local   all             postgres                                peer


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

[postgres@pg17 ~]$

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

psql (15.13)
Type "help" for help.

orcl=> exit
[postgres@pg17 ~]$

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

orcl=#

6. Play with trust

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

Change pg_hba.conf:

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

# Allow local peer access for postgres
local   all             postgres                                peer

# Allow local password access for all

local   all             all                                     trust

[postgres@pg17 ~]$

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

orcl=>

7. Play with reject

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

# Allow local peer access for postgres
local   all             postgres                                peer

# Allow local password access for all

local   all             all                                     reject

[postgres@pg17 ~]$

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

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

[postgres@pg17 ~]$

8. Restrict by User

Allow only user teja, block everyone else:

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

# Allow local peer access for postgres
local   all             postgres                                peer

# Allow local password access for all
local   all             all                                     trust

# Allow IPv4 localhost

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


[postgres@pg17 ~]$

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

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


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

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

[postgres@pg17 ~]$


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

9. Restrict by Network

We want to block connections from IP 192.168.2.0/24 

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

# Allow local peer access for postgres
local   all             postgres                                peer

# Allow local password access for all
local   all             all                                     trust

# Allow IPv4 localhost

host    all             all             192.168.2.0/24          reject

[postgres@pg17 ~]$

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

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

[postgres@pg17 ~]$

10. Verify logs

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

postgres=#

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

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

YouTube

YouTubebright dba, database administration, oracle dba videos, Rajasekhar Amudala

1. How to Install Oracle 19c on Linux 7

2. How to create Oracle Database using DBCA?

3. How to Drop Database Manually?

4. How to start database?

5. How to Shutdown Database?

6. How to connect as SYSDBA?

7. How to connect to database remotely in SQL Developer?

8.Install Oracle 19c database software in silent mode

9. Create database in silent mode.

PSQL

PostgreSQLadvanced PostgreSQL, asynchronous replication PostgreSQL, autovacuum tuning, change PGDATA, compile PostgreSQL, create database PostgreSQL, database administration, database performance tuning, DBA Training, install PostgreSQL Linux, Linux PostgreSQL DBA, logical replication PostgreSQL, manual failover PostgreSQL, migrate Oracle to PostgreSQL, Open Source Database, Oracle DBA to PostgreSQL, Patroni PostgreSQL, pg_basebackup, pg_dump, pg_dumpall, pg_hba.conf, pg_restore, pg_stat_activity, pg_wal, point in time recovery, Postgres admin guide, Postgres DBA course, Postgres PITR, Postgres query optimization, Postgres real time scenarios, PostgreSQL, PostgreSQL 17, PostgreSQL access control, PostgreSQL administration, PostgreSQL architecture, PostgreSQL archive mode, PostgreSQL Authentication, PostgreSQL automatic failover, PostgreSQL AWS, PostgreSQL Azure, PostgreSQL Backup, PostgreSQL backup and recovery, PostgreSQL CentOS, PostgreSQL cloud, PostgreSQL Cluster Backup, PostgreSQL clustering, PostgreSQL Configuration, PostgreSQL data directory, PostgreSQL database creation, PostgreSQL DBA, PostgreSQL disaster recovery, PostgreSQL failover, PostgreSQL for beginners, PostgreSQL GCP, PostgreSQL HA, PostgreSQL high availability, PostgreSQL Indexing, PostgreSQL installation, PostgreSQL internals, PostgreSQL Linux 7, PostgreSQL logical backup, postgresql migration, PostgreSQL Monitoring, PostgreSQL on Linux, PostgreSQL Performance, PostgreSQL performance tuning, PostgreSQL physical backup, PostgreSQL PITR, PostgreSQL recovery, PostgreSQL replication, PostgreSQL Restore, PostgreSQL RHEL, PostgreSQL roles, PostgreSQL Roles and Privileges, PostgreSQL security, PostgreSQL service management, PostgreSQL source installation, PostgreSQL SSL setup, PostgreSQL step by step, PostgreSQL storage management, PostgreSQL Tablespace, PostgreSQL training, PostgreSQL Troubleshooting, PostgreSQL Tutorial, PostgreSQL user management, PostgreSQL users, PostgreSQL WAL, PostgreSQL WAL directory, REPMGR, SQL Tuning, Start PostgreSQL, Stop PostgreSQL, streaming replication, streaming replication PostgreSQL, synchronous replication PostgreSQL, vacuum analyze PostgreSQL, WAL archiving, WAL archiving PostgreSQL, write-ahead logging

PostgreSQL DBA Step by Step Learning

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

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

Bright DBA

ORACLE DBA – Tips and Techniques

Tag Archives: database administration

PostgreSQL

PostgreSQL DBA Step by Step Learning

Change Data Directory

PostgreSQL 17 – Change Data Directory

0. Overview

1. Stop PostgreSQL Service

2. Create the New Directory

3. Copy Existing Data to New Location

4. Move existing data directory

5. Update PostgreSQL Configuration to Use New Data Directory

6. Reload systemd and start PostgreSQL

7. Edit postgres .bash_profile

8. Verify New Data Directory is in Use

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

PostgreSQL Tablespace

PostgreSQL Tablespace Management

PostgreSQL pg_hba.conf

PostgreSQL pg_hba.conf Guide

Table of Contents

YouTube

PSQL

PostgreSQL DBA Step by Step Learning