Tag Archives: PostgreSQL Database

Install EDB Postgres Advanced Server 17 on Linux 8

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

Install EDB Postgres Advanced Server 17 on Linux 8

Table of Contents

1. Setup EDB repository 

--Logon https://www.enterprisedb.com/repos and select --->  RHEL8 x86_64 then ----> EDB Postgres Advanced Server  ---> 17

[root@lxtrdedbpsgv01 ~]# curl -1sSLf 'https://downloads.enterprisedb.com/PMuvhbPsvEt9Rd4ORs5gnUT6z1L4P85R/enterprise/setup.rpm.sh' | sudo -E bash
Executing the  setup script for the 'enterprisedb/enterprise' repository ...

   OK: Checking for required executable 'curl' ...
   OK: Checking for required executable 'rpm' ...
   OK: Detecting your OS distribution and release using system methods ...
 ^^^^: ... Detected/provided for your OS/distribution, version and architecture:
 >>>>:
 >>>>: ... distro=ol  version=8.7  codename=8.7  arch=x86_64
 >>>>:
   OK: Importing 'enterprisedb/enterprise' repository GPG keys into rpm ...
   OK: Checking for available package manager (DNF/Microdnf/YUM/Zypper) ...
 ^^^^: ... Detected package manager as 'dnf'
   OK: Checking for dnf dependency 'dnf-plugins-core' ...
   OK: Checking if upstream install config is OK ...
   OK: Fetching 'enterprisedb/enterprise' repository configuration ...
   OK: Installing 'enterprisedb/enterprise' repository via dnf ...
  RUN: Updating the dnf cache to fetch the new repository metadata ...Importing GPG key 0x9F1EF813:
 Userid     : "Cloudsmith Package (enterprisedb/enterprise) <support@cloudsmith.io>"
 Fingerprint: 31A4 CF09 0B3A E265 F131 58DE E71E B082 9F1E F813
 From       : https://downloads.enterprisedb.com/PMuvhbPsvEt9Rd4ORs5gnUT6z1L4P85R/enterprise/gpg.E71EB0829F1EF813.key
Importing GPG key 0x9F1EF813:
 Userid     : "Cloudsmith Package (enterprisedb/enterprise) <support@cloudsmith.io>"
 Fingerprint: 31A4 CF09 0B3A E265 F131 58DE E71E B082 9F1E F813
 From       : https://downloads.enterprisedb.com/PMuvhbPsvEt9Rd4ORs5gnUT6z1L4P85R/enterprise/gpg.E71EB0829F1EF813.key
Importing GPG key 0x9F1EF813:
 Userid     : "Cloudsmith Package (enterprisedb/enterprise) <support@cloudsmith.io>"
 Fingerprint: 31A4 CF09 0B3A E265 F131 58DE E71E B082 9F1E F813
 From       : https://downloads.enterprisedb.com/PMuvhbPsvEt9Rd4ORs5gnUT6z1L4P85R/enterprise/gpg.E71EB0829F1EF813.key
   OK: Updating the dnf cache to fetch the new repository metadata ...
   OK: The repository has been installed successfully - You're ready to rock!

[root@lxtrdedbpsgv01 ~]#

[root@lxtrdedbpsgv01 ~]# dnf repolist | grep -i enterprisedb
enterprisedb-enterprise        enterprisedb-enterprise
enterprisedb-enterprise-noarch enterprisedb-enterprise-noarch
enterprisedb-enterprise-source enterprisedb-enterprise-source
[root@lxtrdedbpsgv01 ~]#

2. Install EDB Postgres Advanced Server 17

[root@lxtrdedbpsgv01 ~]# dnf -y install edb-as17-server
..
Dependencies resolved.
========================================================================================================================================================================================================================================
 Package                                                         Architecture                                 Version                                               Repository                                                     Size
========================================================================================================================================================================================================================================
Installing:
 edb-as17-server                                                 x86_64                                       17.11.0-1.el8                                         enterprisedb-enterprise                                        12 k
Installing dependencies:
 edb-as17-server-client                                          x86_64                                       17.11.0-1.el8                                         enterprisedb-enterprise                                       2.2 M
 edb-as17-server-contrib                                         x86_64                                       17.11.0-1.el8                                         enterprisedb-enterprise                                       831 k
 edb-as17-server-core                                            x86_64                                       17.11.0-1.el8                                         enterprisedb-enterprise                                       7.4 M
 edb-as17-server-docs                                            x86_64                                       17.11.0-1.el8                                         enterprisedb-enterprise                                        21 k
 edb-as17-server-ecpg                                            x86_64                                       17.11.0-1.el8                                         enterprisedb-enterprise                                       416 k
 edb-as17-server-libs                                            x86_64                                       17.11.0-1.el8                                         enterprisedb-enterprise                                       652 k
 edb-as17-server-plperl                                          x86_64                                       17.11.0-1.el8                                         enterprisedb-enterprise                                        76 k
 edb-as17-server-plpython3                                       x86_64                                       17.11.0-1.el8                                         enterprisedb-enterprise                                       110 k
 edb-as17-server-pltcl                                           x86_64                                       17.11.0-1.el8                                         enterprisedb-enterprise                                        49 k
 edb-libicu66                                                    x86_64                                       66.1-3.el8                                            enterprisedb-enterprise                                       9.2 M
 tcl                                                             x86_64                                       1:8.6.8-2.el8                                         ol8_baseos_latest                                             1.1 M
Installing weak dependencies:
 beacon-agent                                                    x86_64                                       1.401.5-1                                             enterprisedb-enterprise                                       115 M

Transaction Summary
========================================================================================================================================================================================================================================
Install  13 Packages

Total download size: 137 M
Installed size: 412 M
Downloading Packages:
(1/13): edb-as17-server-17.11.0-1.el8.x86_64.rpm                                                                                                                                                        9.1 kB/s |  12 kB     00:01
(2/13): edb-as17-server-client-17.11.0-1.el8.x86_64.rpm                                                                                                                                                 1.0 MB/s | 2.2 MB     00:02
(3/13): edb-as17-server-contrib-17.11.0-1.el8.x86_64.rpm                                                                                                                                                448 kB/s | 831 kB     00:01
(4/13): edb-as17-server-docs-17.11.0-1.el8.x86_64.rpm                                                                                                                                                    17 kB/s |  21 kB     00:01
(5/13): edb-as17-server-core-17.11.0-1.el8.x86_64.rpm                                                                                                                                                   2.8 MB/s | 7.4 MB     00:02
(6/13): edb-as17-server-ecpg-17.11.0-1.el8.x86_64.rpm                                                                                                                                                   273 kB/s | 416 kB     00:01
(7/13): edb-as17-server-libs-17.11.0-1.el8.x86_64.rpm                                                                                                                                                   433 kB/s | 652 kB     00:01
(8/13): edb-as17-server-plperl-17.11.0-1.el8.x86_64.rpm                                                                                                                                                  54 kB/s |  76 kB     00:01
(9/13): edb-as17-server-plpython3-17.11.0-1.el8.x86_64.rpm                                                                                                                                               72 kB/s | 110 kB     00:01
(10/13): edb-as17-server-pltcl-17.11.0-1.el8.x86_64.rpm                                                                                                                                                  50 kB/s |  49 kB     00:00
(11/13): tcl-8.6.8-2.el8.x86_64.rpm                                                                                                                                                                     1.3 MB/s | 1.1 MB     00:00
(12/13): beacon-agent-1.401.5_linux_amd64.rpm                                                                                                                                                           6.9 MB/s | 115 MB     00:16
(13/13): edb-libicu66-66.1-3.el8.x86_64.rpm                                                                                                                                                             1.1 MB/s | 9.2 MB     00:08
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                                                   8.2 MB/s | 137 MB     00:16
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                                                                                1/1
  Installing       : edb-libicu66-66.1-3.el8.x86_64                                                                                                                                                                                1/13
  Running scriptlet: edb-libicu66-66.1-3.el8.x86_64                                                                                                                                                                                1/13
  Installing       : edb-as17-server-libs-17.11.0-1.el8.x86_64                                                                                                                                                                     2/13
  Running scriptlet: edb-as17-server-libs-17.11.0-1.el8.x86_64                                                                                                                                                                     2/13
  Installing       : edb-as17-server-ecpg-17.11.0-1.el8.x86_64                                                                                                                                                                     3/13
  Installing       : edb-as17-server-client-17.11.0-1.el8.x86_64                                                                                                                                                                   4/13
  Running scriptlet: edb-as17-server-client-17.11.0-1.el8.x86_64                                                                                                                                                                   4/13
  Installing       : edb-as17-server-contrib-17.11.0-1.el8.x86_64                                                                                                                                                                  5/13
  Installing       : tcl-1:8.6.8-2.el8.x86_64                                                                                                                                                                                      6/13
  Running scriptlet: tcl-1:8.6.8-2.el8.x86_64                                                                                                                                                                                      6/13
  Installing       : edb-as17-server-docs-17.11.0-1.el8.x86_64                                                                                                                                                                     7/13
  Installing       : beacon-agent-1.401.5-1.x86_64                                                                                                                                                                                 8/13
  Running scriptlet: edb-as17-server-core-17.11.0-1.el8.x86_64                                                                                                                                                                     9/13
  Installing       : edb-as17-server-core-17.11.0-1.el8.x86_64                                                                                                                                                                     9/13
  Running scriptlet: edb-as17-server-core-17.11.0-1.el8.x86_64                                                                                                                                                                     9/13
  Installing       : edb-as17-server-plperl-17.11.0-1.el8.x86_64                                                                                                                                                                  10/13
  Installing       : edb-as17-server-plpython3-17.11.0-1.el8.x86_64                                                                                                                                                               11/13
  Installing       : edb-as17-server-pltcl-17.11.0-1.el8.x86_64                                                                                                                                                                   12/13
  Installing       : edb-as17-server-17.11.0-1.el8.x86_64                                                                                                                                                                         13/13
  Running scriptlet: edb-as17-server-17.11.0-1.el8.x86_64                                                                                                                                                                         13/13
  Verifying        : beacon-agent-1.401.5-1.x86_64                                                                                                                                                                                 1/13
  Verifying        : edb-as17-server-17.11.0-1.el8.x86_64                                                                                                                                                                          2/13
  Verifying        : edb-as17-server-client-17.11.0-1.el8.x86_64                                                                                                                                                                   3/13
  Verifying        : edb-as17-server-contrib-17.11.0-1.el8.x86_64                                                                                                                                                                  4/13
  Verifying        : edb-as17-server-core-17.11.0-1.el8.x86_64                                                                                                                                                                     5/13
  Verifying        : edb-as17-server-docs-17.11.0-1.el8.x86_64                                                                                                                                                                     6/13
  Verifying        : edb-as17-server-ecpg-17.11.0-1.el8.x86_64                                                                                                                                                                     7/13
  Verifying        : edb-as17-server-libs-17.11.0-1.el8.x86_64                                                                                                                                                                     8/13
  Verifying        : edb-as17-server-plperl-17.11.0-1.el8.x86_64                                                                                                                                                                   9/13
  Verifying        : edb-as17-server-plpython3-17.11.0-1.el8.x86_64                                                                                                                                                               10/13
  Verifying        : edb-as17-server-pltcl-17.11.0-1.el8.x86_64                                                                                                                                                                   11/13
  Verifying        : edb-libicu66-66.1-3.el8.x86_64                                                                                                                                                                               12/13
  Verifying        : tcl-1:8.6.8-2.el8.x86_64                                                                                                                                                                                     13/13

Installed:
  beacon-agent-1.401.5-1.x86_64                edb-as17-server-17.11.0-1.el8.x86_64        edb-as17-server-client-17.11.0-1.el8.x86_64   edb-as17-server-contrib-17.11.0-1.el8.x86_64   edb-as17-server-core-17.11.0-1.el8.x86_64
  edb-as17-server-docs-17.11.0-1.el8.x86_64    edb-as17-server-ecpg-17.11.0-1.el8.x86_64   edb-as17-server-libs-17.11.0-1.el8.x86_64     edb-as17-server-plperl-17.11.0-1.el8.x86_64    edb-as17-server-plpython3-17.11.0-1.el8.x86_64
  edb-as17-server-pltcl-17.11.0-1.el8.x86_64   edb-libicu66-66.1-3.el8.x86_64              tcl-1:8.6.8-2.el8.x86_64

Complete!
[root@lxtrdedbpsgv01 ~]#

[root@lxtrdedbpsgv01 ~]# rpm -qa | grep edb-as17
edb-as17-server-libs-17.11.0-1.el8.x86_64
edb-as17-server-plperl-17.11.0-1.el8.x86_64
edb-as17-server-docs-17.11.0-1.el8.x86_64
edb-as17-server-client-17.11.0-1.el8.x86_64
edb-as17-server-pltcl-17.11.0-1.el8.x86_64
edb-as17-server-contrib-17.11.0-1.el8.x86_64
edb-as17-server-core-17.11.0-1.el8.x86_64
edb-as17-server-17.11.0-1.el8.x86_64
edb-as17-server-ecpg-17.11.0-1.el8.x86_64
edb-as17-server-plpython3-17.11.0-1.el8.x86_64
[root@lxtrdedbpsgv01 ~]#
[root@lxtrdedbpsgv01 ~]# /usr/edb/as17/bin/psql --version
psql (EnterpriseDB) 17.11.0
[root@lxtrdedbpsgv01 ~]#

3. Check and reset EDB user password

[root@lxtrdedbpsgv01 ~]# id enterprisedb
uid=972(enterprisedb) gid=972(enterprisedb) groups=972(enterprisedb)
[root@lxtrdedbpsgv01 ~]# getent passwd enterprisedb
enterprisedb:x:972:972:EDBAS User:/var/lib/edb:/bin/bash
[root@lxtrdedbpsgv01 ~]#
[root@lxtrdedbpsgv01 ~]# passwd enterprisedb
Changing password for user enterprisedb.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@lxtrdedbpsgv01 ~]#

4. Change ownership for mount points

[root@lxtrdedbpsgv01 ~]# chown -R enterprisedb:enterprisedb /edb*
[root@lxtrdedbpsgv01 ~]#
[root@lxtrdedbpsgv01 ~]# df -h /edb/*
Filesystem      Size  Used Avail Use% Mounted on
/dev/sdf1       100G  746M  100G   1% /edb/pgArch
/dev/sdd1       100G  746M  100G   1% /edb/pgBackup
/dev/sdb1       100G  746M  100G   1% /edb/pgData
/dev/sde1       100G  746M  100G   1% /edb/pgBin
/dev/sdc1       100G  746M  100G   1% /edb/tsdata
[root@lxtrdedbpsgv01 ~]#

5. Setup custom Bin directory (Optional)

[root@lxtrdedbpsgv01 ~]# ls -l /usr/edb/as17/
total 36
drwxr-xr-x. 2 root root  4096 Aug 22 03:49 bin
-r--r--r--. 1 root root 15716 Aug 12 12:26 edb-as17-server_license.txt
drwxr-xr-x. 3 root root  8192 Aug 22 03:49 lib
drwxr-xr-x. 9 root root  4096 Aug 22 03:49 share
[root@lxtrdedbpsgv01 ~]#
[root@lxtrdedbpsgv01 ~]# cp -rp /usr/edb/as17 /edb/pgBin
[root@lxtrdedbpsgv01 ~]# 
[root@lxtrdedbpsgv01 ~]# ls -l /edb/pgBin/as17/
total 32
drwxr-xr-x. 2 root root  4096 Aug 22 03:49 bin
-r--r--r--. 1 root root 15716 Aug 12 12:26 edb-as17-server_license.txt
drwxr-xr-x. 3 root root  4096 Aug 22 03:49 lib
drwxr-xr-x. 9 root root  4096 Aug 22 03:49 share
[root@lxtrdedbpsgv01 ~]#
[root@lxtrdedbpsgv01 ~]# chown -R enterprisedb:enterprisedb /edb/pgBin/*
[root@lxtrdedbpsgv01 ~]# ls -l /edb/pgBin/as17/
total 32
drwxr-xr-x. 2 enterprisedb enterprisedb  4096 Aug 22 03:49 bin
-r--r--r--. 1 enterprisedb enterprisedb 15716 Aug 12 12:26 edb-as17-server_license.txt
drwxr-xr-x. 3 enterprisedb enterprisedb  4096 Aug 22 03:49 lib
drwxr-xr-x. 9 enterprisedb enterprisedb  4096 Aug 22 03:49 share
[root@lxtrdedbpsgv01 ~]#


One caution: simply copying /usr/edb/as17 can miss RPM-managed symlinks, configuration files, or dependencies outside that directory. Also, future dnf update operations will update /usr/edb/as17, but will not automatically update /edb/pgBase. So after an EDB RPM upgrade, you would need to copy the new binaries again. 
rsync -av /usr/edb/as17/ /edb/pgBin/as17

6. Configure environment variables

[root@lxtrdedbpsgv01 ~]# su - enterprisedb
[enterprisedb@lxtrdedbpsgv01 ~]$
[enterprisedb@lxtrdedbpsgv01 ~]$ cat .bash_profile

export PGHOME=/edb/pgBin/as17
export PGDATA=/edb/pgData/as17/data
export PATH=$PGHOME/bin:$PATH

[enterprisedb@lxtrdedbpsgv01 ~]$ . .bash_profile
[enterprisedb@lxtrdedbpsgv01 ~]$ which pg_ctl
/edb/pgBin/as17/bin/pg_ctl
[enterprisedb@lxtrdedbpsgv01 ~]$ which psql
/edb/pgBin/as17/bin/psql
[enterprisedb@lxtrdedbpsgv01 ~]$ which pg_basebackup
/edb/pgBin/as17/bin/pg_basebackup
[enterprisedb@lxtrdedbpsgv01 ~]$ which pg_rewind
/edb/pgBin/as17/bin/pg_rewind
[enterprisedb@lxtrdedbpsgv01 ~]$
[enterprisedb@lxtrdedbpsgv01 ~]$ pg_ctl --version
pg_ctl (EnterpriseDB) 17.11.0
[enterprisedb@lxtrdedbpsgv01 ~]$ psql --version
psql (EnterpriseDB) 17.11.0
[enterprisedb@lxtrdedbpsgv01 ~]$

7. Initialize the database

[enterprisedb@lxtrdedbpsgv01 ~]$ which initdb
/edb/pgBin/as17/bin/initdb
[enterprisedb@lxtrdedbpsgv01 ~]$
[enterprisedb@lxtrdedbpsgv01 ~]$ initdb --version
initdb (EnterpriseDB) 17.11.0
[enterprisedb@lxtrdedbpsgv01 ~]$
[enterprisedb@lxtrdedbpsgv01 ~]$ which edb-as-17-setup
/edb/pgBin/as17/bin/edb-as-17-setup
[enterprisedb@lxtrdedbpsgv01 ~]$

[enterprisedb@lxtrdedbpsgv01 ~]$ ls -ld /edb/pgData/as17/data
ls: cannot access '/edb/pgData/as17/data': No such file or directory
[enterprisedb@lxtrdedbpsgv01 ~]$
[enterprisedb@lxtrdedbpsgv01 ~]$ mkdir -p /edb/pgData/as17/data
[enterprisedb@lxtrdedbpsgv01 ~]$
[enterprisedb@lxtrdedbpsgv01 ~]$ chmod 700 /edb/pgData/as17/data
[enterprisedb@lxtrdedbpsgv01 ~]$ ls -ld /edb/pgData/as17/data
drwx------. 2 enterprisedb enterprisedb 6 Aug 22 06:16 /edb/pgData/as17/data
[enterprisedb@lxtrdedbpsgv01 ~]$

[enterprisedb@lxtrdedbpsgv01 ~]$ /edb/pgBin/as17/bin/initdb -D /edb/pgData/as17/data
The files belonging to this database system will be owned by user "enterprisedb".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.
Transparent data encryption is disabled.

fixing permissions on existing directory /edb/pgData/as17/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
creating edb sys ... ok
loading edb contrib modules ...
edb_redwood_bytea.sql
edb_redwood_date.sql
dbms_alert_public.sql
dbms_alert.plb
dbms_assert_public.sql
dbms_assert.plb
dbms_job_public.sql
dbms_job.plb
dbms_lob_public.sql
dbms_lob.plb
dbms_output_public.sql
dbms_output.plb
dbms_pipe_public.sql
dbms_pipe.plb
dbms_rls_public.sql
dbms_rls.plb
dbms_sql_public.sql
dbms_sql.plb
dbms_utility_public.sql
dbms_utility.plb
dbms_aqadm_public.sql
dbms_aqadm.plb
dbms_aq_public.sql
dbms_aq.plb
dbms_profiler_public.sql
dbms_profiler.plb
dbms_random_public.sql
dbms_random.plb
dbms_redact_public.sql
dbms_redact.plb
dbms_lock_public.sql
dbms_lock.plb
dbms_scheduler_public.sql
dbms_scheduler.plb
dbms_crypto_public.sql
dbms_crypto.plb
dbms_mview_public.sql
dbms_mview.plb
dbms_session_public.sql
dbms_session.plb
xmltype_public.sql
xmltype.plb
dbms_xmldom_public.sql
dbms_xmldom.plb
dbms_privilege_capture_public.sql
dbms_privilege_capture.plb
edb_bulkload.sql
edb_gen.sql
edb_objects.sql
edb_redwood_casts.sql
edb_redwood_nls.sql
edb_redwood_strings.sql
edb_redwood_views.sql
utl_encode_public.sql
utl_encode.plb
utl_http_public.sql
utl_http.plb
utl_file.plb
edb_ht_public.sql
edb_ht.plb
utl_tcp_public.sql
utl_tcp.plb
utl_smtp_public.sql
utl_smtp.plb
utl_mail_public.sql
utl_mail.plb
utl_url_public.sql
utl_url.plb
utl_raw_public.sql
utl_raw.plb
edb_gen_redwood.sql
waitstates.sql
installing extension edb_dblink_libpq ... ok
installing extension edb_dblink_oci ... ok
snap_tables.sql
snap_functions.sql
dblink_ora.sql
sys_stats.sql
ok
finalizing initial databases ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /edb/pgBin/as17/bin/pg_ctl -D /edb/pgData/as17/data -l logfile start

[enterprisedb@lxtrdedbpsgv01 ~]$

8. Start PostgreSQL Service

[enterprisedb@lxtrdedbpsgv01 ~]$ /edb/pgBin/as17/bin/pg_ctl -D /edb/pgData/as17/data -l /tmp/postgresql.log start
waiting for server to start.... done
server started
[enterprisedb@lxtrdedbpsgv01 ~]$

[enterprisedb@lxtrdedbpsgv01 ~]$
[enterprisedb@lxtrdedbpsgv01 ~]$ ps -ef | grep postgres
enterpr+   42936       1  0 06:25 ?        00:00:00 /edb/pgBin/as17/bin/edb-postgres -D /edb/pgData/as17/data
enterpr+   42937   42936  0 06:25 ?        00:00:00 postgres: logger
enterpr+   42938   42936  0 06:25 ?        00:00:00 postgres: checkpointer
enterpr+   42939   42936  0 06:25 ?        00:00:00 postgres: background writer
enterpr+   42941   42936  0 06:25 ?        00:00:00 postgres: walwriter
enterpr+   42942   42936  0 06:25 ?        00:00:00 postgres: autovacuum launcher
enterpr+   42943   42936  0 06:25 ?        00:00:00 postgres: dbms_aq launcher
enterpr+   42944   42936  0 06:25 ?        00:00:00 postgres: logical replication launcher
enterpr+   43022   41905  0 06:28 pts/0    00:00:00 grep --color=auto postgres
[enterprisedb@lxtrdedbpsgv01 ~]$

[enterprisedb@lxtrdedbpsgv01 ~]$ pstree -p 42936
edb-postgres(42936)─┬─edb-postgres(42937)
                    ├─edb-postgres(42938)
                    ├─edb-postgres(42939)
                    ├─edb-postgres(42941)
                    ├─edb-postgres(42942)
                    ├─edb-postgres(42943)
                    └─edb-postgres(42944)
[enterprisedb@lxtrdedbpsgv01 ~]$
[enterprisedb@lxtrdedbpsgv01 ~]$ ls -ltr /edb/pgData/as17/data
total 76
drwx------. 2 enterprisedb enterprisedb     6 Aug 22 06:24 pg_twophase
drwx------. 2 enterprisedb enterprisedb     6 Aug 22 06:24 pg_tblspc
drwx------. 2 enterprisedb enterprisedb     6 Aug 22 06:24 pg_stat_tmp
drwx------. 2 enterprisedb enterprisedb     6 Aug 22 06:24 pg_snapshots
drwx------. 2 enterprisedb enterprisedb     6 Aug 22 06:24 pg_serial
drwx------. 2 enterprisedb enterprisedb     6 Aug 22 06:24 pg_replslot
drwx------. 2 enterprisedb enterprisedb     6 Aug 22 06:24 pg_notify
drwx------. 4 enterprisedb enterprisedb    36 Aug 22 06:24 pg_multixact
drwx------. 2 enterprisedb enterprisedb     6 Aug 22 06:24 pg_dynshmem
drwx------. 2 enterprisedb enterprisedb     6 Aug 22 06:24 pg_commit_ts
-rw-------. 1 enterprisedb enterprisedb     3 Aug 22 06:24 PG_VERSION
-rw-------. 1 enterprisedb enterprisedb 38410 Aug 22 06:24 postgresql.conf
-rw-------. 1 enterprisedb enterprisedb    88 Aug 22 06:24 postgresql.auto.conf
-rw-------. 1 enterprisedb enterprisedb  2640 Aug 22 06:24 pg_ident.conf
-rw-------. 1 enterprisedb enterprisedb  5711 Aug 22 06:24 pg_hba.conf
drwx------. 2 enterprisedb enterprisedb    18 Aug 22 06:24 pg_xact
drwx------. 2 enterprisedb enterprisedb    18 Aug 22 06:24 pg_subtrans
drwx------. 4 enterprisedb enterprisedb   109 Aug 22 06:24 pg_wal
drwx------. 6 enterprisedb enterprisedb    46 Aug 22 06:24 base
drwx------. 4 enterprisedb enterprisedb    68 Aug 22 06:24 pg_logical
drwx------. 2 enterprisedb enterprisedb     6 Aug 22 06:25 dbms_pipe
drwx------. 2 enterprisedb enterprisedb    48 Aug 22 06:25 log
-rw-------. 1 enterprisedb enterprisedb    46 Aug 22 06:25 current_logfiles
-rw-------. 1 enterprisedb enterprisedb    62 Aug 22 06:25 postmaster.opts
drwx------. 2 enterprisedb enterprisedb     6 Aug 22 06:25 pg_stat
-rw-------. 1 enterprisedb enterprisedb    88 Aug 22 06:25 postmaster.pid
drwx------. 2 enterprisedb enterprisedb  4096 Aug 22 06:27 global
[enterprisedb@lxtrdedbpsgv01 ~]$


[enterprisedb@lxtrdedbpsgv01 ~]$ psql -d postgres -c "SELECT version();"
                                                                     version
-------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.11 (EnterpriseDB Advanced Server 17.11.0) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-28), 64-bit
(1 row)

[enterprisedb@lxtrdedbpsgv01 ~]$
[enterprisedb@lxtrdedbpsgv01 ~]$ psql -d postgres
psql (17.11.0)
Type "help" for help.

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

postgres=#
postgres=# \conninfo
You are connected to database "postgres" as user "enterprisedb" via socket in "/tmp" at port "5444".
postgres=#
postgres=# \du
                                     List of roles
         Role name         |                         Attributes
---------------------------+------------------------------------------------------------
 aq_administrator_role     | No inheritance, Cannot login                              +
                           | Profile default
 capture_admin             | No inheritance, Cannot login                              +
                           | Profile default
 edb_createdirectory_admin | No inheritance, Cannot login                              +
                           | Profile default
 edb_dropdirectory_admin   | No inheritance, Cannot login                              +
                           | Profile default
 enterprisedb              | Superuser, Create role, Create DB, Replication, Bypass RLS+
                           | Profile default

postgres=# ALTER USER enterprisedb PASSWORD 'oracle';
ALTER ROLE
postgres=# 
postgres=# \duS
                                      List of roles
          Role name          |                         Attributes
-----------------------------+------------------------------------------------------------
 aq_administrator_role       | No inheritance, Cannot login                              +
                             | Profile default
 capture_admin               | No inheritance, Cannot login                              +
                             | Profile default
 edb_createdirectory_admin   | No inheritance, Cannot login                              +
                             | Profile default
 edb_dropdirectory_admin     | No inheritance, Cannot login                              +
                             | Profile default
 enterprisedb                | Superuser, Create role, Create DB, Replication, Bypass RLS+
                             | Profile default
 pg_checkpoint               | Cannot login                                              +
                             | Profile default
 pg_create_subscription      | Cannot login                                              +
                             | Profile default
 pg_database_owner           | Cannot login                                              +
                             | Profile default
 pg_execute_server_program   | Cannot login                                              +
                             | Profile default
 pg_maintain                 | Cannot login                                              +
                             | Profile default
 pg_monitor                  | Cannot login                                              +
                             | Profile default
 pg_read_all_data            | Cannot login                                              +
                             | Profile default
 pg_read_all_settings        | Cannot login                                              +
                             | Profile default
 pg_read_all_stats           | Cannot login                                              +
                             | Profile default
 pg_read_server_files        | Cannot login                                              +
                             | Profile default
 pg_signal_backend           | Cannot login                                              +
                             | Profile default
 pg_stat_scan_tables         | Cannot login                                              +
                             | Profile default
 pg_use_reserved_connections | Cannot login                                              +
                             | Profile default
 pg_write_all_data           | Cannot login                                              +
                             | Profile default
 pg_write_server_files       | Cannot login                                              +
                             | Profile default

postgres=#

postgres=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
(1 row)

postgres=# \dnS
            List of schemas
        Name        |       Owner
--------------------+-------------------
 information_schema | enterprisedb
 pg_catalog         | enterprisedb
 pg_temp_0          | enterprisedb
 pg_toast           | enterprisedb
 pg_toast_temp_0    | enterprisedb
 public             | pg_database_owner
 sys                | enterprisedb
(7 rows)

postgres=# \dnS+
                                                  List of schemas
        Name        |       Owner       |           Access privileges            |           Description
--------------------+-------------------+----------------------------------------+----------------------------------
 information_schema | enterprisedb      | enterprisedb=UC/enterprisedb          +|
                    |                   | =U/enterprisedb                        |
 pg_catalog         | enterprisedb      | enterprisedb=UC/enterprisedb          +| system catalog schema
                    |                   | =U/enterprisedb                        |
 pg_temp_0          | enterprisedb      |                                        |
 pg_toast           | enterprisedb      |                                        | reserved schema for TOAST tables
 pg_toast_temp_0    | enterprisedb      |                                        |
 public             | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
                    |                   | =U/pg_database_owner                   |
 sys                | enterprisedb      | enterprisedb=UC/enterprisedb          +| sys schema
                    |                   | =U/enterprisedb                        |
(7 rows)

postgres=#
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you
Rajasekhar Amudala
Email: br8dba@gmail.com
LinkedIn: linkedin.com/in/rajasekhar-amudala

Types of Shutdown in PostgreSQL

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

Types of Shutdown in PostgreSQL

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

Difference Between Reload and Restart

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

 

Smart Shutdown (SIGTERM):

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

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


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

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

Fast Shutdown (SIGINT – Default):

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

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

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

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

Immediate Shutdown (SIGQUIT):

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

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


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

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

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

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

Backup and Restore on Same Host Using pg_basebackup

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

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

Table of Contents

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

1. Environment

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

2. Verify Existing DB Setup

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

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

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

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

dell=#

3. Pre-requisites

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

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

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

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

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

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

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

4. Take Backup

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

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

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

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

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

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

-- OR --

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

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

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

5. Prepare & Restore

5.1 Stop PostgreSQL and Clean Directories

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

5.2 Remove all from Data & WAL directories

-- Remove all from PGDATA directory

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

-- Remove all from WAL directory 

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

5.3 Restore Data and WAL

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

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

6. Start PostgreSQL

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

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

7. Final Verification

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

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

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

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

dell=#

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

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

pg_dump & pg_restore

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

PostgreSQL pg_dump and pg_restore Guide

Table of Contents
___________________________________________________________________________________________________

Backup:

0. pg_dump / pg_restore –help
1. Backup Output Formats
2. Full Database Backup

3. Schema Level Backup
4. Table Level Backup
5. Object Level Backup (PostgreSQL v17 Feature)

Restore:

6. Pre-requisites for Restore
7. Full Database Restore
8. Schema Level Restore

9. Table Level Restore

___________________________________________________________________________________________________

0. pg_dump/pg_restore –help

Click to expand pg_dump –help
[postgres@pg17 ~]$ pg_dump --help
pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -j, --jobs=NUM               use this many parallel jobs to dump
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  -Z, --compress=0-9           compression level for compressed formats
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  --no-sync                    do not wait for changes to be written safely to disk
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -b, --blobs                  include large objects in dump
  -B, --no-blobs               exclude large objects in dump
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 include commands to create database in dump
  -e, --extension=PATTERN      dump the specified extension(s) only
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -n, --schema=PATTERN         dump the specified schema(s) only
  -N, --exclude-schema=PATTERN do NOT dump the specified schema(s)
  -O, --no-owner               skip restoration of object ownership in
                               plain-text format
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in plain-text format
  -t, --table=PATTERN          dump the specified table(s) only
  -T, --exclude-table=PATTERN  do NOT dump the specified table(s)
  -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
  --enable-row-security        enable row security (dump only content user has
                               access to)
  --exclude-table-data=PATTERN do NOT dump data for the specified table(s)
  --extra-float-digits=NUM     override default setting for extra_float_digits
  --if-exists                  use IF EXISTS when dropping objects
  --include-foreign-data=PATTERN
                               include data of foreign tables on foreign
                               servers matching PATTERN
  --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-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --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
  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts
  --section=SECTION            dump named section (pre-data, data, or post-data)
  --serializable-deferrable    wait until the dump can run without anomalies
  --snapshot=SNAPSHOT          use given snapshot for the dump
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -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 no database name is supplied, then the PGDATABASE environment
variable value is used.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
[postgres@pg17 ~]$
Click to expand pg_restore –help
[postgres@pg17 ~]$ pg_restore --help
pg_restore restores a PostgreSQL database from an archive created by pg_dump.

Usage:
  pg_restore [OPTION]... [FILE]

General options:
  -d, --dbname=NAME        connect to database name
  -f, --file=FILENAME      output file name (- for stdout)
  -F, --format=c|d|t       backup file format (should be automatic)
  -l, --list               print summarized TOC of the archive
  -v, --verbose            verbose mode
  -V, --version            output version information, then exit
  -?, --help               show this help, then exit

Options controlling the restore:
  -a, --data-only              restore only the data, no schema
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 create the target database
  -e, --exit-on-error          exit on error, default is to continue
  -I, --index=NAME             restore named index
  -j, --jobs=NUM               use this many parallel jobs to restore
  -L, --use-list=FILENAME      use table of contents from this file for
                               selecting/ordering output
  -n, --schema=NAME            restore only objects in this schema
  -N, --exclude-schema=NAME    do not restore objects in this schema
  -O, --no-owner               skip restoration of object ownership
  -P, --function=NAME(args)    restore named function
  -s, --schema-only            restore only the schema, no data
  -S, --superuser=NAME         superuser user name to use for disabling triggers
  -t, --table=NAME             restore named relation (table, view, etc.)
  -T, --trigger=NAME           restore named trigger
  -x, --no-privileges          skip restoration of access privileges (grant/revoke)
  -1, --single-transaction     restore as a single transaction
  --disable-triggers           disable triggers during data-only restore
  --enable-row-security        enable row security
  --if-exists                  use IF EXISTS when dropping objects
  --no-comments                do not restore comments
  --no-data-for-failed-tables  do not restore data of tables that could not be
                               created
  --no-publications            do not restore publications
  --no-security-labels         do not restore security labels
  --no-subscriptions           do not restore subscriptions
  --no-table-access-method     do not restore table access methods
  --no-tablespaces             do not restore tablespace assignments
  --section=SECTION            restore named section (pre-data, data, or post-data)
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -h, --host=HOSTNAME      database server host or socket directory
  -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 restore

The options -I, -n, -N, -P, -t, -T, and --section can be combined and specified
multiple times to select multiple objects.

If no input file name is supplied, then standard input is used.

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

1. Backup Output Formats

A. Plain Text Format
# Using -Fp explicitly:
pg_dump -U postgres -d your_db_name -Fp -f /path/to/your_db.sql

# Shell redirect
pg_dump -U postgres -d your_db_name > /path/to/your_db.sql

B. Tar Format
pg_dump -U postgres -d your_db_name -Ft -f /path/to/your_db.tar

C. Directory Format and Parallel Backup
-- Parallel backup is only supported with -Fd
-- pg_dump will create the directory if it doesn't exist.

# Without parallelism
pg_dump -U postgres -d your_db_name -Fd -f /path/to/backup_dir/

# With parallelism
pg_dump -U postgres -d your_db_name -Fd -j8 -f /path/to/backup_dir/

D. Custom Format (Compressed binary format by default)
pg_dump -U postgres -d your_db_name -Fc -f /path/to/your_db.dump


2. Full Database Backup


A. Plain Text Format

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -v > /pgBackup/pgsql15/backup/blpua/database_blpua_full.sql 2> /pgBackup/pgsql15/backup/blpua/log/database_blpua_full.log &

-- OR --

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -v -Fp -f /pgBackup/pgsql15/backup/blpua/database_blpua_full1.sql > /pgBackup/pgsql15/backup/blpua/log/database_blpua_full1.log 2>&1 &

-- OR -- 

[postgres@pg17 ~]$ pg_dump -U postgres -d blpua | split -b 1G - /pgBackup/pgsql15/backup/blpua/database_blpua_split.dmp
[postgres@pg17 ~]$
[postgres@pg17 ~]$ ls -lrth /pgBackup/pgsql15/backup/blpua/database_blpua_s*
-rw-r--r--. 1 postgres postgres 1.0G Sep 30 11:03 /pgBackup/pgsql15/backup/blpua/database_blpua_split.dmpaa
-rw-r--r--. 1 postgres postgres 650M Sep 30 11:03 /pgBackup/pgsql15/backup/blpua/database_blpua_split.dmpab
[postgres@pg17 ~]$

-- OR --

[postgres@pg17 ~]$ nohup sh -c "pg_dump -U postgres -d blpua -v | gzip > /pgBackup/pgsql15/backup/blpua/database_blpua.gz" > /pgBackup/pgsql15/backup/blpua/log/database_blpua.log 2>&1 &

-- OR --

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -v > /pgBackup/pgsql15/backup/blpua/database_blpua.sql 2> /pgBackup/pgsql15/backup/blpua/log/database_blpua.log & gzip /pgBackup/pgsql15/backup/blpua/database_blpua.sql


B. Tar Format

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -v -Ft -f /pgBackup/pgsql15/backup/blpua/database_blpua.tar > /pgBackup/pgsql15/backup/blpua/log/blpua_tar_dump.log 2>&1 &


C. Directory Format

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -v -Fd -j4 -f /pgBackup/pgsql15/backup/blpua_dir > /pgBackup/pgsql15/backup/blpua/log/blpua_dump.log 2>&1 &


D. Custome Format

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -Fc -v -f /pgBackup/pgsql15/backup/blpua/database_blpua_$(date +%Y_%m_%d).dmp > /pgBackup/pgsql15/backup/blpua/log/database_blpua_$(date +%Y_%m_%d).log 2>&1 &
[postgres@pg17 ~]$ 
[postgres@pg17 ~]$ ls -lrth /pgBackup/pgsql15/backup/blpua/database_blpua*.dmp
-rw-r--r--. 1 postgres postgres 402M Sep 29 04:58 /pgBackup/pgsql15/backup/blpua/database_blpua_2025_09_29.dmp

[postgres@pg17 ~]$ ls -lrth /pgBackup/pgsql15/backup/blpua/log/database_blpua*.log
-rw-r--r--. 1 postgres postgres 2.5K Sep 29 04:58 /pgBackup/pgsql15/backup/blpua/log/database_blpua_2025_09_29.log
[postgres@pg17 ~]$


3. Schema Level Backup

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -n blp -Fc -v -f /pgBackup/pgsql15/backup/blpua/schema_blp_blpua_$(date +%Y_%m_%d).dmp > /pgBackup/pgsql15/backup/blpua/log/schema_blp_blpua_$(date +%Y_%m_%d).log 2>&1 &
[1] 5472
[postgres@pg17 ~]$

[postgres@pg17 ~]$ jobs -l
[1]+  5472 Running                 nohup pg_dump -U postgres -d blpua -n blp -Fc -v -f /pgBackup/pgsql15/backup/blpua/schema_blp_blpua_$(date +%Y_%m_%d).dmp > /pgBackup/pgsql15/backup/blpua/log/schema_blp_blpua_$(date +%Y_%m_%d).log 2>&1 &
[postgres@pg17 ~]$

[postgres@pg17 ~]$ jobs -l
[1]+  5472 Done                    nohup pg_dump -U postgres -d blpua -n blp -Fc -v -f /pgBackup/pgsql15/backup/blpua/schema_blp_blpua_$(date +%Y_%m_%d).dmp > /pgBackup/pgsql15/backup/blpua/log/schema_blp_blpua_$(date +%Y_%m_%d).log 2>&1
[postgres@pg17 ~]$


[postgres@pg17 ~]$ ls -lrth /pgBackup/pgsql15/backup/blpua/schema_blp_blpua*
-rw-r--r--. 1 postgres postgres 402M Sep 30 02:09 /pgBackup/pgsql15/backup/blpua/schema_blp_blpua_2025_09_30.dmp
[postgres@pg17 ~]$ ls -lrth /pgBackup/pgsql15/backup/blpua/log/schema_blp_blpua*
-rw-r--r--. 1 postgres postgres 2.5K Sep 30 02:09 /pgBackup/pgsql15/backup/blpua/log/schema_blp_blpua_2025_09_30.log
[postgres@pg17 ~]$


4. Table Level Backup

[postgres@pg17 ~]$ nohup pg_dump -U postgres -d blpua -t blp.employees -Fc -v -f /pgBackup/pgsql15/backup/blpua/table_blp_employees_$(date +%Y_%m_%d).dmp > /pgBackup/pgsql15/backup/blpua/log/table_blp_employees_$(date +%Y_%m_%d).log 2>&1 &
[1] 5652
[postgres@pg17 ~]$ 

[postgres@pg17 ~]$ ls -lrth /pgBackup/pgsql15/backup/blpua/table_blp_employees_*
-rw-r--r--. 1 postgres postgres 62M Sep 30 02:22 /pgBackup/pgsql15/backup/blpua/table_blp_employees_2025_09_30.dmp
[postgres@pg17 ~]$ ls -lrth /pgBackup/pgsql15/backup/blpua/log/table_blp_employees_*
-rw-r--r--. 1 postgres postgres 2.0K Sep 30 02:22 /pgBackup/pgsql15/backup/blpua/log/table_blp_employees_2025_09_30.log
[postgres@pg17 ~]$


5. Object Level Backup (PostgreSQL v17 Feature)

++ filter is only for pg_dump, not pg_restore

[postgres@lxicbpgdsgv01 ~]$ cat include_tables.par
include table demo.table_1
include table demo.table_2
include table demo.table_3
[postgres@lxicbpgdsgv01 ~]$


[postgres@lxicbpgdsgv01 ~]$ pg_dump -d testdb_source --filter=include_tables.par > include_tables.sql

[postgres@lxicbpgdsgv01 ~]$ ls -lrth include_tables.sql
-rw-r--r--. 1 postgres postgres 4.5K Sep 30 22:19 include_tables.sql
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ cat include_tables.sql | grep -i "CREATE TABLE"
CREATE TABLE demo.table_1 (
CREATE TABLE demo.table_2 (
CREATE TABLE demo.table_3 (
[postgres@lxicbpgdsgv01 ~]$


[postgres@lxicbpgdsgv01 ~]$ cat exclude_tables.par
exclude table demo.table_1
exclude table demo.table_2
exclude table demo.table_3
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ pg_dump -d testdb_source --filter=exclude_tables.par > exclude_tables.sql
[postgres@lxicbpgdsgv01 ~]$ ls -ltr exclude_tables.sql
-rw-r--r--. 1 postgres postgres 10931 Sep 30 22:23 exclude_tables.sql
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ cat exclude_tables.sql | grep -i "CREATE TABLE"
CREATE TABLE demo.table_10 (
CREATE TABLE demo.table_4 (
CREATE TABLE demo.table_5 (
CREATE TABLE demo.table_6 (
CREATE TABLE demo.table_7 (
CREATE TABLE demo.table_8 (
CREATE TABLE demo.table_9 (
[postgres@lxicbpgdsgv01 ~]$


6. Pre-requisites for restore

postgres=# CREATE TABLESPACE TEST_TB LOCATION '/pgTb/pgsql15/test';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE TEST_IX LOCATION '/pgIx/pgsql15/test';
CREATE TABLESPACE
postgres=#
postgres=# CREATE DATABASE TEST TABLESPACE TEST_TB;
CREATE DATABASE
postgres=#

[postgres@pg17 blpua]$ scp *.dmp postgres@192.168.2.32:/pgBackup/pgsql15/backup/test
postgres@192.168.2.32's password:
database_blpua_2025_09_29.dmp                           100%  402MB  39.9MB/s   00:10
schema_blp_blpua_2025_09_30.dmp                         100%  402MB  43.8MB/s   00:09
table_blp_employees_2025_09_30.dmp                      100%   61MB  47.0MB/s   00:01
[postgres@pg17 blpua]$


7. Full Database Restore

++ The source database uses two custom tablespaces: blpua_tbs01 and blpua_ixtbs01.
++ On the target system, I have two different tablespaces: test_tb and test_ix.
++ Unlike Oracle, PostgreSQL does not provide a direct equivalent of REMAP_TABLESPACE.
++ Therefore, to simplify the restore and ensure all objects are placed into the default tablespace (test_tb).
++ Using --no-owner --no-tablespaces in pg_restore — even if not specified, data still gets written to the default database tablespace. These options just help avoid errors in the log.
++ This ensures that all objects are restored to the default tablespace of the target database, regardless of their original tablespace assignments.
++ Later, we can move all indexes to the desired target tablespace using the ALTER INDEX ... SET TABLESPACE command.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# ALTER INDEX blp.idx_employee_name SET TABLESPACE test_ix;  <--  This command physically relocates the index to the test_ix tablespace.
ALTER INDEX
test=#


[postgres@lxtrdpgdsgv01 ~]$ nohup pg_restore -U postgres -d test --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/database_blpua_2025_09_29.dmp > /pgBackup/pgsql15/backup/test/log/restore_blpua_test_$(date +%Y_%m_%d).log 2>&1 &
[1] 5300
[postgres@lxtrdpgdsgv01 ~]$

[postgres@lxtrdpgdsgv01 ~]$ cat /pgBackup/pgsql15/backup/test/log/restore_blpua_test_2025_09_30.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "blp"
pg_restore: creating TYPE "blp.address"
pg_restore: creating TYPE "blp.employment_status"
pg_restore: creating DOMAIN "blp.positive_integer"
pg_restore: creating FUNCTION "blp.get_salary(integer)"
pg_restore: creating FUNCTION "blp.log_update()"
pg_restore: creating PROCEDURE "blp.raise_salary(integer, numeric)"
pg_restore: creating SEQUENCE "blp.emp_id_seq"
pg_restore: creating TABLE "blp.employees"
pg_restore: creating MATERIALIZED VIEW "blp.emp_summary"
pg_restore: creating SEQUENCE "blp.employees_id_seq"
pg_restore: creating SEQUENCE OWNED BY "blp.employees_id_seq"
pg_restore: creating VIEW "blp.high_paid_employees"
pg_restore: creating TABLE "blp.metrics"
pg_restore: creating TABLE "blp.metrics_high"
pg_restore: creating SEQUENCE "blp.metrics_id_seq"
pg_restore: creating TABLE "blp.metrics_low"
pg_restore: creating TABLE "blp.metrics_mid"
pg_restore: creating TABLE "blp.metrics_rest"
pg_restore: creating TABLE "blp.sales"
pg_restore: creating TABLE "blp.sales_2021"
pg_restore: creating TABLE "blp.sales_2022"
pg_restore: creating TABLE "blp.sales_2023"
pg_restore: creating TABLE "blp.sales_2024"
pg_restore: creating SEQUENCE "blp.sales_id_seq"
pg_restore: creating TABLE "blp.test_data"
pg_restore: creating SEQUENCE "blp.test_data_id_seq"
pg_restore: creating SEQUENCE OWNED BY "blp.test_data_id_seq"
pg_restore: creating TABLE ATTACH "blp.metrics_high"
pg_restore: creating TABLE ATTACH "blp.metrics_low"
pg_restore: creating TABLE ATTACH "blp.metrics_mid"
pg_restore: creating TABLE ATTACH "blp.metrics_rest"
pg_restore: creating TABLE ATTACH "blp.sales_2021"
pg_restore: creating TABLE ATTACH "blp.sales_2022"
pg_restore: creating TABLE ATTACH "blp.sales_2023"
pg_restore: creating TABLE ATTACH "blp.sales_2024"
pg_restore: creating DEFAULT "blp.employees id"
pg_restore: creating DEFAULT "blp.test_data id"
pg_restore: processing data for table "blp.employees"
pg_restore: processing data for table "blp.metrics_high"
pg_restore: processing data for table "blp.metrics_low"
pg_restore: processing data for table "blp.metrics_mid"
pg_restore: processing data for table "blp.metrics_rest"
pg_restore: processing data for table "blp.sales_2021"
pg_restore: processing data for table "blp.sales_2022"
pg_restore: processing data for table "blp.sales_2023"
pg_restore: processing data for table "blp.sales_2024"
pg_restore: processing data for table "blp.test_data"
pg_restore: executing SEQUENCE SET emp_id_seq
pg_restore: executing SEQUENCE SET employees_id_seq
pg_restore: executing SEQUENCE SET metrics_id_seq
pg_restore: executing SEQUENCE SET sales_id_seq
pg_restore: executing SEQUENCE SET test_data_id_seq
pg_restore: creating CONSTRAINT "blp.employees employees_pkey"
pg_restore: creating CONSTRAINT "blp.metrics metrics_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_high metrics_high_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_low metrics_low_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_mid metrics_mid_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_rest metrics_rest_pkey"
pg_restore: creating CONSTRAINT "blp.sales sales_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2021 sales_2021_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2022 sales_2022_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2023 sales_2023_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2024 sales_2024_pkey"
pg_restore: creating CONSTRAINT "blp.test_data test_data_pkey"
pg_restore: creating INDEX "blp.idx_employee_name"
pg_restore: creating INDEX ATTACH "blp.metrics_high_pkey"
pg_restore: creating INDEX ATTACH "blp.metrics_low_pkey"
pg_restore: creating INDEX ATTACH "blp.metrics_mid_pkey"
pg_restore: creating INDEX ATTACH "blp.metrics_rest_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2021_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2022_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2023_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2024_pkey"
pg_restore: creating TRIGGER "blp.employees employee_update_trigger"
pg_restore: creating MATERIALIZED VIEW DATA "blp.emp_summary"
[postgres@lxtrdpgdsgv01 ~]$


8. Schema Level Restore

++ pg_restore does not support schema remapping like Oracle’s REMAP_SCHEMA.
++ I want to restore schema blp --> trd. 
++ Option A: Restore Dump as it is, Then Rename <--- Best for simplicity
++ Option B: Split Dump into Schema + Data, Modify Both <--- This is more complex, but possible if you must use schema remapping.
++ This is schema dump file : /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
++ Using --no-owner --no-tablespaces in pg_restore — even if not specified, data still gets written to the default database tablespace. These options just help avoid errors in the log.


A. To Restore schema from own schema’s backup

postgres=# CREATE TABLESPACE ORCL_TB LOCATION '/pgTb/pgsql15/orcl';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE ORCL_IX LOCATION '/pgIx/pgsql15/orcl';
CREATE TABLESPACE
postgres=#
postgres=# CREATE DATABASE ORCL TABLESPACE ORCL_TB;
CREATE DATABASE
postgres=#

-- With NO parallelism


[postgres@lxtrdpgdsgv01 ~]$ nohup pg_restore -U postgres -d orcl --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp > /pgBackup/pgsql15/backup/test/log/restore_blpua_test_$(date +%Y_%m_%d).log 2>&1 &
[1] 7344
[postgres@lxtrdpgdsgv01 ~]$

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

orcl=#


[postgres@lxtrdpgdsgv01 ~]$ cat /pgBackup/pgsql15/backup/test/log/restore_blpua_test_2025_09_30.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "blp"
pg_restore: creating TYPE "blp.address"
pg_restore: creating TYPE "blp.employment_status"
pg_restore: creating DOMAIN "blp.positive_integer"
pg_restore: creating FUNCTION "blp.get_salary(integer)"
pg_restore: creating FUNCTION "blp.log_update()"
pg_restore: creating PROCEDURE "blp.raise_salary(integer, numeric)"
pg_restore: creating SEQUENCE "blp.emp_id_seq"
pg_restore: creating TABLE "blp.employees"
pg_restore: creating MATERIALIZED VIEW "blp.emp_summary"
pg_restore: creating SEQUENCE "blp.employees_id_seq"
pg_restore: creating SEQUENCE OWNED BY "blp.employees_id_seq"
pg_restore: creating VIEW "blp.high_paid_employees"
pg_restore: creating TABLE "blp.metrics"
pg_restore: creating TABLE "blp.metrics_high"
pg_restore: creating SEQUENCE "blp.metrics_id_seq"
pg_restore: creating TABLE "blp.metrics_low"
pg_restore: creating TABLE "blp.metrics_mid"
pg_restore: creating TABLE "blp.metrics_rest"
pg_restore: creating TABLE "blp.sales"
pg_restore: creating TABLE "blp.sales_2021"
pg_restore: creating TABLE "blp.sales_2022"
pg_restore: creating TABLE "blp.sales_2023"
pg_restore: creating TABLE "blp.sales_2024"
pg_restore: creating SEQUENCE "blp.sales_id_seq"
pg_restore: creating TABLE "blp.test_data"
pg_restore: creating SEQUENCE "blp.test_data_id_seq"
pg_restore: creating SEQUENCE OWNED BY "blp.test_data_id_seq"
pg_restore: creating TABLE ATTACH "blp.metrics_high"
pg_restore: creating TABLE ATTACH "blp.metrics_low"
pg_restore: creating TABLE ATTACH "blp.metrics_mid"
pg_restore: creating TABLE ATTACH "blp.metrics_rest"
pg_restore: creating TABLE ATTACH "blp.sales_2021"
pg_restore: creating TABLE ATTACH "blp.sales_2022"
pg_restore: creating TABLE ATTACH "blp.sales_2023"
pg_restore: creating TABLE ATTACH "blp.sales_2024"
pg_restore: creating DEFAULT "blp.employees id"
pg_restore: creating DEFAULT "blp.test_data id"
pg_restore: processing data for table "blp.employees"
pg_restore: processing data for table "blp.metrics_high"
pg_restore: processing data for table "blp.metrics_low"
pg_restore: processing data for table "blp.metrics_mid"
pg_restore: processing data for table "blp.metrics_rest"
pg_restore: processing data for table "blp.sales_2021"
pg_restore: processing data for table "blp.sales_2022"
pg_restore: processing data for table "blp.sales_2023"
pg_restore: processing data for table "blp.sales_2024"
pg_restore: processing data for table "blp.test_data"
pg_restore: executing SEQUENCE SET emp_id_seq
pg_restore: executing SEQUENCE SET employees_id_seq
pg_restore: executing SEQUENCE SET metrics_id_seq
pg_restore: executing SEQUENCE SET sales_id_seq
pg_restore: executing SEQUENCE SET test_data_id_seq
pg_restore: creating CONSTRAINT "blp.employees employees_pkey"
pg_restore: creating CONSTRAINT "blp.metrics metrics_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_high metrics_high_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_low metrics_low_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_mid metrics_mid_pkey"
pg_restore: creating CONSTRAINT "blp.metrics_rest metrics_rest_pkey"
pg_restore: creating CONSTRAINT "blp.sales sales_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2021 sales_2021_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2022 sales_2022_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2023 sales_2023_pkey"
pg_restore: creating CONSTRAINT "blp.sales_2024 sales_2024_pkey"
pg_restore: creating CONSTRAINT "blp.test_data test_data_pkey"
pg_restore: creating INDEX "blp.idx_employee_name"
pg_restore: creating INDEX ATTACH "blp.metrics_high_pkey"
pg_restore: creating INDEX ATTACH "blp.metrics_low_pkey"
pg_restore: creating INDEX ATTACH "blp.metrics_mid_pkey"
pg_restore: creating INDEX ATTACH "blp.metrics_rest_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2021_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2022_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2023_pkey"
pg_restore: creating INDEX ATTACH "blp.sales_2024_pkey"
pg_restore: creating TRIGGER "blp.employees employee_update_trigger"
pg_restore: creating MATERIALIZED VIEW DATA "blp.emp_summary"
[postgres@lxtrdpgdsgv01 ~]$


--- OR ---

-- With parallelism 4

nohup pg_restore -U postgres -d orcl -j4 --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp > /pgBackup/pgsql15/backup/test/log/restore_blpua_test_$(date +%Y_%m_%d).log1 2>&1 &

[postgres@lxtrdpgdsgv01 ~]$ ps -ef | grep pg_restore
postgres    7089    5102  0 05:49 pts/0    00:00:00 pg_restore -U postgres -d orcl -j4 --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
postgres    7091    7089  2 05:49 pts/0    00:00:00 pg_restore -U postgres -d orcl -j4 --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
postgres    7092    7089  3 05:49 pts/0    00:00:00 pg_restore -U postgres -d orcl -j4 --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
postgres    7093    7089  2 05:49 pts/0    00:00:00 pg_restore -U postgres -d orcl -j4 --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
postgres    7094    7089  2 05:49 pts/0    00:00:00 pg_restore -U postgres -d orcl -j4 --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
postgres    7126    5736  0 05:49 pts/1    00:00:00 grep --color=auto pg_restore
[postgres@lxtrdpgdsgv01 ~]$


B. To Restore only a specific schema from Full Database Backup

Step 1: Create schema 

gebua=# CREATE SCHEMA IF NOT EXISTS blp;

Step 2: Restore Objects and Data

nohup pg_restore -U postgres -d gebua -n blp --no-owner --no-tablespaces -v /pgBackup/pgsql15/backup/test/database_blpua_2025_09_29.dmp > /pgBackup/pgsql15/backup/test/log/restore_blpua_test_$(date +%Y_%m_%d).log_full 2>&1 &


9. Table Level Restore


A. Restore Table from own table’s backup

[postgres@lxtrdpgdsgv01 ~]$ pg_restore -l /pgBackup/pgsql15/backup/test/table_blp_employees_2025_09_30.dmp
;
; Archive created at 2025-09-30 02:22:21 EDT
;     dbname: blpua
;     TOC Entries: 13
;     Compression: -1
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 15.13
;     Dumped by pg_dump version: 15.13
;
;
; Selected TOC Entries:
;
229; 1259 92805 TABLE blp employees postgres
228; 1259 92804 SEQUENCE blp employees_id_seq postgres
4338; 0 0 SEQUENCE OWNED BY blp employees_id_seq postgres
4181; 2604 92808 DEFAULT blp employees id postgres
4331; 0 92805 TABLE DATA blp employees postgres
4339; 0 0 SEQUENCE SET blp employees_id_seq postgres
4183; 2606 92812 CONSTRAINT blp employees employees_pkey postgres
4184; 1259 92824 INDEX blp idx_employee_name postgres
4185; 2620 92829 TRIGGER blp employees employee_update_trigger postgres
[postgres@lxtrdpgdsgv01 ~]$


Step 1: Create schema 

postgres=# \c edpua
You are now connected to database "edpua" as user "postgres".
edpua=# CREATE SCHEMA IF NOT EXISTS blp;
CREATE SCHEMA
edpua=#


Step 2: Restore Objects and Data

[postgres@lxtrdpgdsgv01 ~]$ pg_restore -U postgres -d edpua --no-tablespaces -v /pgBackup/pgsql15/backup/test/table_blp_employees_2025_09_30.dmp
pg_restore: connecting to database for restore
pg_restore: creating TABLE "blp.employees"
pg_restore: creating SEQUENCE "blp.employees_id_seq"
pg_restore: creating SEQUENCE OWNED BY "blp.employees_id_seq"
pg_restore: creating DEFAULT "blp.employees id"
pg_restore: processing data for table "blp.employees"
pg_restore: executing SEQUENCE SET employees_id_seq
pg_restore: creating CONSTRAINT "blp.employees employees_pkey"
pg_restore: creating INDEX "blp.idx_employee_name"
pg_restore: creating TRIGGER "blp.employees employee_update_trigger"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4185; 2620 92829 TRIGGER employees employee_update_trigger postgres
pg_restore: error: could not execute query: ERROR:  function blp.log_update() does not exist
Command was: CREATE TRIGGER employee_update_trigger AFTER UPDATE ON blp.employees FOR EACH ROW EXECUTE FUNCTION blp.log_update();


pg_restore: warning: errors ignored on restore: 1
[postgres@lxtrdpgdsgv01 ~]$

++ PostgreSQL does not have a --ignore-errors flag, The above erros ignorable.


B. Restore Single Table data-only from Schema backup

edpua=# TRUNCATE TABLE blp.employees;
TRUNCATE TABLE
edpua=#

[postgres@lxtrdpgdsgv01 ~]$ pg_restore -U postgres -d edpua --data-only -t employees -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
pg_restore: connecting to database for restore
pg_restore: processing data for table "blp.employees"
[postgres@lxtrdpgdsgv01 ~]$

edpua=# select count(*) from blp.employees;
 5000000  <-----

edpua=#


C. Restore Single Table from Schema backup

Step 1: Create schema 

edpua=# CREATE SCHEMA blp;

Step 2: Restore Objects and Data

[postgres@lxtrdpgdsgv01 ~]$ pg_restore -U postgres -d edpua --clean -t employees -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
pg_restore: connecting to database for restore
pg_restore: dropping TABLE employees
pg_restore: creating TABLE "blp.employees"
pg_restore: processing data for table "blp.employees"
[postgres@lxtrdpgdsgv01 ~]$
[postgres@lxtrdpgdsgv01 ~]$ psql
psql (15.14)
Type "help" for help.

postgres=# \c edpua
You are now connected to database "edpua" as user "postgres".
edpua=# select count(*) from blp.employees;
  count
---------
 5000000 <----
(1 row)

edpua=#


D. Restore Single Table from Schema backup (add both -n and -t)

[postgres@lxtrdpgdsgv01 ~]$ pg_restore -U postgres -d edpua --clean -n blp -t employees -v /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
pg_restore: connecting to database for restore
pg_restore: dropping TABLE employees
pg_restore: creating TABLE "blp.employees"
pg_restore: processing data for table "blp.employees"
[postgres@lxtrdpgdsgv01 ~]$
[postgres@lxtrdpgdsgv01 ~]$ psql
psql (15.14)
Type "help" for help.

postgres=# \c edpua
You are now connected to database "edpua" as user "postgres".
edpua=# select count(*) from blp.employees;
  count
---------
 5000000 <-----
(1 row)

edpua=#


E. Restoring Using a Plain SQL File (COPY-based)

Step 1: Extracted table data from custom-format dump

[postgres@lxtrdpgdsgv01 ~]$ pg_restore -U postgres -n blp -t employees -f /pgBackup/pgsql15/backup/test/blp_employees.sql /pgBackup/pgsql15/backup/test/schema_blp_blpua_2025_09_30.dmp
[postgres@lxtrdpgdsgv01 ~]$
[postgres@lxtrdpgdsgv01 ~]$ ls -lrth /pgBackup/pgsql15/backup/test/blp_employees.sql
-rw-r--r--. 1 postgres postgres 210M Sep 30 09:17 /pgBackup/pgsql15/backup/test/blp_employees.sql
[postgres@lxtrdpgdsgv01 ~]$

[postgres@lxtrdpgdsgv01 ~]$ head -50 /pgBackup/pgsql15/backup/test/blp_employees.sql
--
-- PostgreSQL database dump
--

\restrict TthhVsS8JvVxrplYNApzHUalZpygXUqFg2pIvbSH8GTkbJ6WBNyW0JLnuuvkPdt

-- Dumped from database version 15.13
-- Dumped by pg_dump version 15.13

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: employees; Type: TABLE; Schema: blp; Owner: postgres
--

CREATE TABLE blp.employees (
    id integer NOT NULL,
    name text,
    salary numeric(10,2),
    hired_on date
);


ALTER TABLE blp.employees OWNER TO postgres;

--
-- Data for Name: employees; Type: TABLE DATA; Schema: blp; Owner: postgres
--

COPY blp.employees (id, name, salary, hired_on) FROM stdin;
1       Employee_1      70969.47        2005-06-13
2       Employee_2      94341.15        2006-12-09
3       Employee_3      101657.57       2012-07-05
4       Employee_4      54701.92        2010-01-05
5       Employee_5      104229.43       2001-06-28
6       Employee_6      95842.57        2010-10-06
7       Employee_7      50618.08        2015-06-02
[postgres@lxtrdpgdsgv01 ~]$

Step 2: Restore using psql command

postgres=# \c edpua
You are now connected to database "edpua" as user "postgres".
edpua=#
edpua=# drop table blp.employees;
DROP TABLE
edpua=# exit
[postgres@lxtrdpgdsgv01 ~]$


psql -U postgres -d edpua < /pgBackup/pgsql15/backup/test/blp_employees.sql

[postgres@lxtrdpgdsgv01 ~]$ psql -U postgres -d edpua < /pgBackup/pgsql15/backup/test/blp_employees.sql
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 5000000
[postgres@lxtrdpgdsgv01 ~]$

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

postgres=# \c edpua
You are now connected to database "edpua" as user "postgres".
edpua=# SELECT COUNT(*) FROM blp.employees;
  count
---------
 5000000 <-----
(1 row)

edpua=#

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/