Tag Archives: Open Source Database

Install PostgreSQL 17.4 from Source code

PostgreSQL 17.4 Installation from Source Code (Step-by-Step Guide for Linux)

Author: Rajasekhar Amudala PostgreSQL DBA Guide Source Installation Oracle Linux 9

Install PostgreSQL 17.4
Using Source Code

pgdb01 · 192.168.2.71 Oracle Linux 9 PostgreSQL 17.4 Author: Rajasekhar Amudala

STEP 01

Environment

Parameter	Value
Host Name	pgdb01
IP Address	192.168.2.71
OS	Linux 9 (Oracle Linux)
PostgreSQL Version	17.4
Data Directory	/pgData/pgsql/17.4
WAL Directory	/pgWal/pgsql/17.4
BIN Directory	/pgbin/pgsql/17.4/bin
Software Staging	/pg_Backup/stage

STEP 02

Install Dependencies

root@pgdb01 ~ #
[root@pgdb01 ~]# dnf install -y readline-devel zlib-devel gcc make \
libicu-devel openssl-devel bison flex wget tar

# Packages installed / upgraded:
Installed:  bison flex gcc libicu-devel make openssl-devel
readline-devel zlib-devel glibc-devel glibc-headers
kernel-headers ncurses-devel ...Complete!
[root@pgdb01 ~]#

STEP 03

Create postgres User

root@pgdb01 ~ #
[root@pgdb01 ~]# useradd postgres
[root@pgdb01 ~]# passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
[root@pgdb01 ~]#

STEP 04

Download PostgreSQL Source

root@pgdb01 ~ #
[root@pgdb01 ~]# mkdir -p /pg_Backup/stage
[root@pgdb01 ~]# cd /pg_Backup/stage

[root@pgdb01 stage]# wget https://ftp.postgresql.org/pub/source/v17.4/postgresql-17.4.tar.gz

-- 2026-04-21 20:22:48 --
Resolving ftp.postgresql.org... 151.101.211.52
Length: 28056622 (27M)

postgresql-17.4.tar.gz     100% [========================>]  26.76M  10.2MB/s  in 2.6s

2026-04-21 20:22:51 (10.2 MB/s) - 'postgresql-17.4.tar.gz' saved [28056622/28056622]

[root@pgdb01 stage]#

STEP 05

Extract Source

root@pgdb01 — /pg_Backup/stage
[root@pgdb01 stage]# tar -xvf postgresql-17.4.tar.gz
postgresql-17.4/
postgresql-17.4/.cirrus.star
... (many files) ...
postgresql-17.4/src/tutorial/syscat.source

[root@pgdb01 stage]# ls -ltr
total 27404
drwxrwxr-x. 6 root root     4096 Feb 18  2025 postgresql-17.4
-rw-r--r--. 1 root root 28056622 Feb 18  2025 postgresql-17.4.tar.gz
[root@pgdb01 stage]# cd postgresql-17.4
[root@pgdb01 postgresql-17.4]# pwd
/pg_Backup/stage/postgresql-17.4
[root@pgdb01 postgresql-17.4]#

STEP 06

Configure Build

ℹ

Note configure only checks system dependencies and prepares Makefiles. It records your install path (/pgbin/pgsql/17.4) internally but does not create the bin directory or install anything yet.

root@pgdb01 — /pg_Backup/stage/postgresql-17.4
[root@pgdb01 postgresql-17.4]# ./configure --prefix=/pgbin/pgsql/17.4 --with-pgport=5432
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking which template to use... linux
...
config.status: creating GNUmakefile
config.status: creating src/Makefile.global
config.status: creating src/include/pg_config.h
configure complete.
[root@pgdb01 postgresql-17.4]#

STEP 07

Compile Source

⚠

Error Encountered First make run failed: missing Perl module FindBin.pm. Fixed by installing perl perl-core perl-ExtUtils-MakeMaker, then running make clean before retrying.

Step 1 — Fix missing Perl module
# First make attempt — error:
[root@pgdb01 postgresql-17.4]# make
..
..
Can't locate FindBin.pm in @INC ...
make[2]: *** [Makefile:141: bki-stamp] Error 2
make: *** [src/Makefile.global:384: submake-generated-headers] Error 2

# Fix: install Perl modules
[root@pgdb01 postgresql-17.4]# dnf install -y perl perl-core perl-ExtUtils-MakeMaker
Complete!

# Clean up partial build
[root@pgdb01 postgresql-17.4]# make clean
rm -rf tmp_install/ portlock/
[root@pgdb01 postgresql-17.4]#

Step 2 — Successful make
[root@pgdb01 postgresql-17.4]# make

make -C ./src/backend generated-headers
... (compiling all source files) ...
make[2]: Nothing to be done for 'all'.
make[2]: Leaving directory '.../src/test/perl'
make[1]: Leaving directory '.../src'
make -C config all
make[1]: Nothing to be done for 'all'.
make[1]: Leaving directory '.../config'
[root@pgdb01 postgresql-17.4]#

STEP 08

Install PostgreSQL

ℹ

Note The /pgbin/pgsql/17.4/ bin directory is created automatically by make install.

root@pgdb01 — /pg_Backup/stage/postgresql-17.4
[root@pgdb01 postgresql-17.4]# make install

... (installing binaries, libraries, headers) ...
/usr/bin/install -c -m 755 ./install-sh '/pgbin/pgsql/17.4/lib/pgxs/config/install-sh'
/usr/bin/install -c -m 755 ./missing   '/pgbin/pgsql/17.4/lib/pgxs/config/missing'
[root@pgdb01 postgresql-17.4]#

# Verify installation layout
[root@pgdb01 17.4]# ll /pgbin/pgsql/17.4/
total 16
drwxr-xr-x. 2 root root 4096 Apr 21 21:06 bin
drwxr-xr-x. 6 root root 4096 Apr 21 21:06 include
drwxr-xr-x. 4 root root 4096 Apr 21 21:06 lib
drwxr-xr-x. 6 root root 4096 Apr 21 21:06 share
[root@pgdb01 17.4]#

STEP 09

Install PostgreSQL Contrib (Extensions)

◆

Option A Run from the source root directory

Option A
[root@pgdb01 postgresql-17.4]# make -C contrib install

◆

Option B Run from the contrib subdirectory (shown below)

Option B — root@pgdb01
[root@pgdb01 ~]# cd /pg_Backup/stage/postgresql-17.4/contrib
[root@pgdb01 contrib]# make
... compiling vacuumlo, pg_trgm, hstore, pg_stat_statements, etc. ...
make[1]: Leaving directory '.../contrib/vacuumlo'

[root@pgdb01 contrib]# make install
... installing .so files and extension SQL scripts ...
/usr/bin/install -c -m 644 ./unaccent.rules '/pgbin/pgsql/17.4/share/tsearch_data/'
make[1]: Leaving directory '.../contrib/unaccent'
[root@pgdb01 contrib]#

# Fix ownership and permissions
[root@pgdb01 ~]# chown -R postgres:postgres /pgbin /pgData /pgWal /pgArch /pg_Backup
[root@pgdb01 ~]# chmod 700 /pgbin /pgData /pgWal /pgArch /pg_Backup
[root@pgdb01 ~]#

STEP 10

Set Environment Variables

ℹ

As postgres User Switch to the postgres OS user and add the following exports to ~/.bash_profile.

postgres@pgdb01 — ~/.bash_profile
[root@pgdb01 17.4]# su - postgres
[postgres@pgdb01 ~]$

# Add to ~/.bash_profile:
export PGHOME=/pgbin/pgsql/17.4
export PATH=$PGHOME/bin:$PATH
export PGDATA=/pgData/pgsql/17.4

# Reload profile

[postgres@pgdb01 ~]$ . .bash_profile[postgres@pgdb01 ~]$ which psql
/pgbin/pgsql/17.4/bin/psql
[postgres@pgdb01 ~]$

STEP 11

Validation 1 — pg_config

postgres@pgdb01
[postgres@pgdb01 ~]$ /pgbin/pgsql/17.4/bin/pg_config

BINDIR          = /pgbin/pgsql/17.4/bin
DOCDIR          = /pgbin/pgsql/17.4/share/doc
INCLUDEDIR      = /pgbin/pgsql/17.4/include
LIBDIR          = /pgbin/pgsql/17.4/lib
SHAREDIR        = /pgbin/pgsql/17.4/share
PGXS            = /pgbin/pgsql/17.4/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE       =  '--prefix=/pgbin/pgsql/17.4' '--with-pgport=5432'
CC              = gcc
VERSION         = PostgreSQL 17.4
[postgres@pgdb01 ~]$

STEP 12

Initialize Database Cluster

postgres@pgdb01 — initdb
[postgres@pgdb01 ~]$ mkdir -p /pgData/pgsql/17.4 /pgWal/pgsql/17.4

[postgres@pgdb01 ~]$ /pgbin/pgsql/17.4/bin/initdb \
--pgdata=/pgData/pgsql/17.4 \
--waldir=/pgWal/pgsql/17.4 \
--wal-segsize=128

The files belonging to this database system will be owned by user "postgres".
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".fixing permissions on existing directory /pgData/pgsql/17.4 ... ok
fixing permissions on existing directory /pgWal/pgsql/17.4  ... 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 -A/--auth-local.

Success. You can now start the database server using:
/pgbin/pgsql/17.4/bin/pg_ctl -D /pgData/pgsql/17.4 -l logfile start
[postgres@pgdb01 ~]$

STEP 13

Start PostgreSQL Instance

postgres@pgdb01
[postgres@pgdb01 ~]$ /pgbin/pgsql/17.4/bin/pg_ctl -D /pgData/pgsql/17.4 -l logfile start

waiting for server to start.... done
server started
[postgres@pgdb01 ~]$

STEP 14

Validation

postgres@pgdb01 — Process Check
[postgres@pgdb01 ~]$ ps -ef | grep postgres

postgres 62707     1  0 21:50 ? /pgbin/pgsql/17.4/bin/postgres -D /pgData/pgsql/17.4
postgres 62708 62707  0 21:50 ?   postgres: checkpointer
postgres 62709 62707  0 21:50 ?   postgres: background writer
postgres 62711 62707  0 21:50 ?   postgres: walwriter
postgres 62712 62707  0 21:50 ?   postgres: autovacuum launcher
postgres 62713 62707  0 21:50 ?   postgres: logical replication launcher

postgres@pgdb01 — psql Checks
[postgres@pgdb01 ~]$ psql -c "SELECT VERSION();"version
-----------------------------------------------------------------------
PostgreSQL 17.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0
(1 row)

[postgres@pgdb01 ~]$ psql
psql (17.4)
Type "help" for help.postgres=# \l
List of databases
Name    |  Owner   | Encoding | Collate
-----------+----------+----------+-------------
postgres  | postgres | UTF8     | en_SG.UTF-8
template0 | postgres | UTF8     | en_SG.UTF-8
template1 | postgres | UTF8     | en_SG.UTF-8
(3 rows)postgres=# \du
List of roles
Role name |                   Attributes
-----------+----------------------------------------------
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLSpostgres=# \q
[postgres@pgdb01 ~]$

✔

Installation Complete PostgreSQL 17.4 is compiled from source, running, and validated on pgdb01.

STEP 15

Next Steps

Next 01

Configure Memory & Connections

postgresql.conf — shared_buffers, max_connections, work_mem

Next 02

Configure Client Access

pg_hba.conf — host, method, CIDR rules

Next 03

Enable WAL Archiving

archive_mode = on & archive_command in postgresql.conf

Next 04

Take a Base Backup

pg_basebackup -D /pg_Backup/base ...

⚠ Disclaimer Your use of any information or materials in this guide is entirely at your own risk. It is provided for educational purposes only. It has been tested internally; however, we do not guarantee it will work for you. Always validate in a test environment before applying to production.

Rajasekhar Amudala

PostgreSQL DBA · br8dba@gmail.com
linkedin.com/in/rajasekhar-amudala ↗

PostgreSQL

Uncategorizedadvanced PostgreSQL, asynchronous replication PostgreSQL, autovacuum tuning, backup all databases postgresql linux, build postgresql from source, change PGDATA, change pgdata postgresql linux, change postgresql wal directory, change wal directory postgresql rhel, compile PostgreSQL, configure streaming replication postgresql, convert async to sync replication postgresql, copy postgresql database to new host, create database PostgreSQL, create tablespace postgresql, create user postgresql, database administration, database performance tuning, DBA Training, disable archive mode postgresql, disable wal archiving postgresql, enable archivelog postgresql, how to create database in postgresql linux, how to install postgresql on rhel, how to use pg_basebackup, how to use pg_dump, install postgresql from source code linux, install PostgreSQL Linux, install postgresql linux rpm, Linux PostgreSQL DBA, logical replication PostgreSQL, manage users postgresql linux, manual failover PostgreSQL, migrate Oracle to PostgreSQL, move pg_wal to different disk, move postgresql data directory, move table to tablespace postgresql, Open Source Database, Oracle DBA to PostgreSQL, oracle rman pitr equivalent postgresql, oracle vs postgresql wal, Patroni PostgreSQL, pg_basebackup, pg_basebackup another server, pg_basebackup postgresql, pg_basebackup recovery.conf postgresql, pg_basebackup restore, pg_basebackup same server, pg_basebackup step by step, pg_basebackup wal archive restore, pg_ctl promote postgresql, pg_ctl start stop postgresql, pg_dump, pg_dump command examples linux, pg_dump pg_restore postgresql, pg_dumpall, pg_dumpall postgresql backup, pg_dumpall psql restore, pg_dumpall restore, pg_hba conf examples, pg_hba.conf, pg_hba.conf configuration guide, pg_restore, pg_restore postgresql example, 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 17 archive mode, PostgreSQL access control, PostgreSQL administration, postgresql allow remote connection pg_hba, PostgreSQL architecture, PostgreSQL archive mode, postgresql archive_mode off, postgresql archive_mode on, PostgreSQL Authentication, postgresql authentication methods, PostgreSQL automatic failover, PostgreSQL AWS, PostgreSQL Azure, PostgreSQL Backup, postgresql backup all databases, PostgreSQL backup and recovery, postgresql backup restore different host, postgresql backup restore pg_dump, postgresql backup restore same host, postgresql base backup linux, PostgreSQL CentOS, postgresql change data directory, postgresql client authentication, postgresql clone database linux, PostgreSQL cloud, PostgreSQL Cluster Backup, PostgreSQL clustering, postgresql compile from source rhel7, PostgreSQL Configuration, postgresql continuous archiving, postgresql create database command, PostgreSQL data directory, postgresql data directory location linux, postgresql database copy same host linux, PostgreSQL database creation, postgresql datadir change rhel, PostgreSQL DBA, PostgreSQL DBA Scripts, PostgreSQL DBA training, postgresql default tablespace, postgresql disable archivelog linux, PostgreSQL disaster recovery, postgresql enable archive mode, PostgreSQL failover, postgresql failover streaming replication, PostgreSQL for beginners, postgresql full backup pg_dumpall, PostgreSQL GCP, postgresql grant privileges, PostgreSQL HA, PostgreSQL high availability, PostgreSQL Indexing, PostgreSQL installation, postgresql installation linux step by step, PostgreSQL internals, PostgreSQL Linux 7, postgresql linux tutorial, PostgreSQL logical backup, postgresql logical backup restore, postgresql manual failover, postgresql manual installation linux, postgresql master slave replication linux, postgresql migration, PostgreSQL Monitoring, postgresql new database tutorial, PostgreSQL on Linux, PostgreSQL Performance, PostgreSQL performance tuning, postgresql pg_hba.conf, postgresql pg_wal path change, PostgreSQL physical backup, postgresql physical backup pg_basebackup, PostgreSQL PITR, postgresql pitr example linux, postgresql pitr step by step, postgresql point in time recovery, postgresql point in time recovery new server, postgresql promote replica to primary, PostgreSQL recovery, PostgreSQL replication, postgresql replication step by step, postgresql replication tutorial rhel, postgresql restart linux command, PostgreSQL Restore, postgresql restore new host wal archives, postgresql restore on same machine, postgresql restore to specific time, postgresql restore with wal archives, PostgreSQL RHEL, PostgreSQL roles, PostgreSQL Roles and Privileges, PostgreSQL security, postgresql service commands linux, PostgreSQL service management, postgresql setup linux, PostgreSQL source installation, postgresql source installation linux, PostgreSQL SSL setup, postgresql standby server setup, PostgreSQL step by step, PostgreSQL storage management, postgresql streaming replication setup, postgresql switchover manual, postgresql sync replication configuration, postgresql synchronous commit, postgresql synchronous replication, postgresql synchronous_standby_names, PostgreSQL Tablespace, postgresql tablespace linux, postgresql tablespace management, PostgreSQL training, postgresql transaction logs explained, PostgreSQL Troubleshooting, postgresql turn off archiving, PostgreSQL Tutorial, PostgreSQL user management, postgresql user permissions, PostgreSQL users, PostgreSQL WAL, postgresql wal archiving setup, postgresql wal based recovery, PostgreSQL WAL directory, postgresql wal files explained, postgresql wal location linux, postgresql write ahead logging, promote standby postgresql, psql create database, REPMGR, restore postgresql to another server no archive, SQL Tuning, Start PostgreSQL, start stop postgresql linux, Stop PostgreSQL, streaming replication, streaming replication PostgreSQL, synchronous replication PostgreSQL, synchronous replication postgresql setup, systemctl postgresql start stop, vacuum analyze PostgreSQL, WAL archiving, WAL archiving PostgreSQL, wal files oracle dba postgresql, what is wal in 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/

PostgreSQL Versions & Features

Uncategorizeddata management, database features, database management system, database performance, DBMS concepts, Open Source Database, PostgreSQL, PostgreSQL 10, PostgreSQL 11, PostgreSQL 12, PostgreSQL 13, PostgreSQL 14, PostgreSQL 15, PostgreSQL 16, PostgreSQL 17, PostgreSQL 18, PostgreSQL 9.6, PostgreSQL comparison, PostgreSQL features, PostgreSQL release history, PostgreSQL table, PostgreSQL Tutorial, PostgreSQL Upgrade, PostgreSQL versions, relational database, SQL database, SQL features

PostgreSQL Versions & Key Features (as of April 2026)

Version	Initial Release	Latest Minor	End of Support	Key New Features
18	Sep 25, 2025	18.3 (Feb 2026)	Nov 2030	Asynchronous I/O (AIO) subsystem pg_upgrade preserves statistics B-tree Skip Scan UUIDv7 support Virtual generated columns OAuth 2.0 authentication JSON_TABLE support Better logical replication (DDL)
17	Sep 26, 2024	17.9	Nov 2029	Enhanced logical replication Improved vacuum performance Better monitoring Incremental backup improvements
16	Sep 14, 2023	16.13	Nov 2028	Logical replication from standbys Parallel VACUUM improvements Improved query planner
15	Oct 13, 2022	15.17	Nov 2027	MERGE command Compression (lz4, zstd) Row-level security enhancements
14	Sep 30, 2021	14.22	Nov 2026	Schema-level privileges Parallel query enhancements JSON improvements
13	Sep 24, 2020	13.23	Nov 2025	Parallel vacuum Incremental sorting Logical replication enhancements

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 Backup and Restore Using pg_dumpall

PostgreSQLBackup Strategies, Database Automation, Database Backup, Database Management, Database Migration, Database Restore, DevOps, Disaster Recovery, Environment Replication, Full Database Backup, Linux PostgreSQL, Open Source Database, pg_dump vs pg_dumpall, pg_dumpall, PostgreSQL, PostgreSQL 15, PostgreSQL administration, PostgreSQL Backup, PostgreSQL Cluster Backup, PostgreSQL Restore, PostgreSQL Tablespaces, PostgreSQL Utilities, psql, SQL Scripting

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

A. Drop Existing Databases (Optional)
B. Restore Full Backup
C. Verification

9. Restore to Another Host (Different Tablespace Paths)

A. Copy Backup File
B. Extract Tablespace/Database Info
C. Create Tablespace Directories on Target
D. Create Tablespaces in PostgreSQL
E. Optional: Backup on Target Host
F. Restore on Target Host
G. Final Verification

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=#

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

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: Open Source Database

Install PostgreSQL 17.4 from Source code

Install PostgreSQL 17.4
Using Source Code

Environment

Install Dependencies

Create postgres User

Download PostgreSQL Source

Extract Source

Configure Build

Compile Source

Install PostgreSQL

Install PostgreSQL Contrib (Extensions)

Set Environment Variables

Validation 1 — pg_config

Initialize Database Cluster

Start PostgreSQL Instance

Validation

Next Steps

Rajasekhar Amudala

PostgreSQL

PostgreSQL DBA Step by Step Learning

PostgreSQL Versions & Features

PostgreSQL Versions & Key Features (as of April 2026)

PostgreSQL Backup and Restore Using pg_dumpall

PostgreSQL Backup and Restore Using pg_dumpall and psql

Backup:

Restore:

PSQL

PostgreSQL DBA Step by Step Learning