Thread: PgBackRest full backup first time : Verification

pgbackrest  backup  what all the directories and files  PgBackRest will backup to RepoServer when performing backup using PgBackRest.  ( Because of a limited infr ( N/W bandwidth 8Mbps between DB Server and RepoServer) .

1.   I am not sure the pgbackrest command finished output message in the console is true due to the following facts.

 i) .   My ILL - SSL VPN connection (8Mbps link) reset in between 3 to 4 times during the backup process span of 12 hours I reissued the pgbackrest backup command 3 to 4 time in this span)

 ii)  EPAS   installation dir   /data/edb/as16/data showing 4.5 G only, 

iii) /data/edb/as16/tablespace shows 149G  

iv)  /data  Dir shows  537 G    and

v)    /data/edb/as16/tablespace    149G   for     du  -h command  output.

Kindly share your thoughts does the   pgbackrest backup command ( for the first time run) performed as expected ..   (Which shows in command line as finished and subsequent reissue of the same command finishes in seconds ) 

I can't  restore back to the DB server right now to test it as it is a production server and down time granting is not immediately possible to test it ...

Thank you,

Krishane

[root@db1 data]# pwd
/data
[root@db1 data]# du -h

returns   537 G

and

 

[root@db1 data]# cd /data/edb/as16/data/
[root@db1 data]# pwd
/data/edb/as16/data
[root@db1 data]# du -h

Returns 4.5G

Query :  1  PgbackRest Initial  full backup  will copy  what ?   /data/edb/as16/data  dir  of size 4.5 G?  

or     /data  Dir of 537 G  ?  // I think this won't ?  

As my  pgbackrest command  issued initially finishes  with following output for info command

 # sudo -u postgres pgbackrest --stanza=Repo --log-level-console=info backup  //performed this 

&  once it finishes  I reissued this command two times again(in suspicion as  I left it overnight for the backup to complete) that's why I think 2 incr backups showing here. 

and 

[root@dbtest ~]# sudo -u postgres pgbackrest info
stanza: Repo
    status: ok
    cipher: aes-256-cbc

    db (current)
        wal archive min/max (16): 00000001000000830000005F/000000010000008700000019

        full backup: 20240829-105625F
            timestamp start/stop: 2024-08-29 18:53:32+05:30 / 2024-08-29 20:24:13+05:30
            wal start/stop: 000000010000008600000063 / 000000010000008600000074
            database size: 146.9GB, database backup size: 146.9GB
            repo1: backup size: 20.6GB

  ### Where this 146.9 GB    from  (  please find the file system du -h pasted bottom of this post)

        incr backup: 20240829-105625F_20240830-084303I
            timestamp start/stop: 2024-08-30 08:43:03+05:30 / 2024-08-30 08:50:46+05:30
            wal start/stop: 00000001000000870000000E / 000000010000008700000010
            database size: 148GB, database backup size: 4.7GB
            repo1: backup size: 170.4MB
            backup reference list: 20240829-105625F

        incr backup: 20240829-105625F_20240830-090729I
            timestamp start/stop: 2024-08-30 09:07:29+05:30 / 2024-08-30 09:12:50+05:30
            wal start/stop: 000000010000008700000015 / 000000010000008700000016
            database size: 148.1GB, database backup size: 1GB
            repo1: backup size: 13.2MB
            backup reference list: 20240829-105625F, 20240829-105625F_20240830-084303I
[root@dbtest ~]#

[root@db1 data]# pwd
/data

20K     ./pg_wal/archive_status
4.1G    ./pg_wal
1.5M    ./global
0       ./pg_commit_ts
0       ./pg_dynshmem
0       ./pg_notify
0       ./pg_serial
0       ./pg_snapshots
56K     ./pg_subtrans
0       ./pg_twophase
3.0M    ./pg_multixact/members
1.2M    ./pg_multixact/offsets
4.1M    ./pg_multixact
15M     ./base/1
15M     ./base/4
15M     ./base/5
15M     ./base/15355
15M     ./base/42613
28M     ./base/43102
0       ./base/pgsql_tmp
40K     ./base/44497
100M    ./base
0       ./pg_replslot
0       ./pg_tblspc
0       ./pg_stat
0       ./pg_stat_tmp
48M     ./pg_xact
0       ./pg_logical/snapshots
0       ./pg_logical/mappings
4.0K    ./pg_logical
280M    ./log
0       ./dbms_pipe
4.5G    .

[root@db1 data]# cd pg_tblspc/
[root@db1 pg_tblspc]# du -h
0       .
[root@db1 pg_tblspc]# ls -al
total 4
drwxr-xr-x.  2 enterprisedb enterprisedb  110 Jun 14 14:17 .
drwx------. 21 enterprisedb enterprisedb 4096 Aug 30 00:00 ..
lrwxrwxrwx.  1 enterprisedb enterprisedb   38 Jun 14 14:17 16388 -> /data/edb/as16/tablespace/ESS/SER/DAT
lrwxrwxrwx.  1 enterprisedb enterprisedb   38 Jun 14 14:17 16389 -> /data/edb/as16/tablespace/ESS/SER/IDX
lrwxrwxrwx.  1 enterprisedb enterprisedb   38 Jun 14 14:17 16390 -> /data/edb/as16/tablespace/ESS/GIS/DAT
lrwxrwxrwx.  1 enterprisedb enterprisedb   38 Jun 14 14:17 16391 -> /data/edb/as16/tablespace/ESS/GIS/IDX
lrwxrwxrwx.  1 enterprisedb enterprisedb   38 Jun 14 14:17 16392 -> /data/edb/as16/tablespace/ESS/RPT/DAT
lrwxrwxrwx.  1 enterprisedb enterprisedb   38 Jun 14 14:17 16393 -> /data/edb/as16/tablespace/ESS/RPT/IDX
lrwxrwxrwx.  1 enterprisedb enterprisedb   38 Jun 14 14:17 16394 -> /data/edb/as16/tablespace/ESS/TSP/DAT
lrwxrwxrwx.  1 enterprisedb enterprisedb   38 Jun 14 14:17 16395 -> /data/edb/as16/tablespace/ESS/TSP/IDX
[root@db1 pg_tblspc]# du -h
0       .
[root@db1 pg_tblspc]# pwd
/data/edb/as16/data/pg_tblspc

[root@db1]# cd /data/edb/as16/tablespace

[root@db1 tablespace]# pwd
/data/edb/as16/tablespace

.

[root@db1 tablespace]#du -h /data/edb/as16/tablespace/
22M     /data/edb/as16/tablespace/ESS/SER/DAT/PG_16_202307071/44494
121G    /data/edb/as16/tablespace/ESS/SER/DAT/PG_16_202307071/44496
0       /data/edb/as16/tablespace/ESS/SER/DAT/PG_16_202307071/pgsql_tmp
121G    /data/edb/as16/tablespace/ESS/SER/DAT/PG_16_202307071
121G    /data/edb/as16/tablespace/ESS/SER/DAT
0       /data/edb/as16/tablespace/ESS/SER/IDX/PG_16_202307071
0       /data/edb/as16/tablespace/ESS/SER/IDX
121G    /data/edb/as16/tablespace/ESS/SER
94M     /data/edb/as16/tablespace/ESS/GIS/DAT/PG_16_202307071/44495
40K     /data/edb/as16/tablespace/ESS/GIS/DAT/PG_16_202307071/44497
94M     /data/edb/as16/tablespace/ESS/GIS/DAT/PG_16_202307071
94M     /data/edb/as16/tablespace/ESS/GIS/DAT
0       /data/edb/as16/tablespace/ESS/GIS/IDX/PG_16_202307071
0       /data/edb/as16/tablespace/ESS/GIS/IDX
94M     /data/edb/as16/tablespace/ESS/GIS
15M     /data/edb/as16/tablespace/ESS/RPT/DAT/PG_16_202307071/44498
15M     /data/edb/as16/tablespace/ESS/RPT/DAT/PG_16_202307071
15M     /data/edb/as16/tablespace/ESS/RPT/DAT
0       /data/edb/as16/tablespace/ESS/RPT/IDX/PG_16_202307071
0       /data/edb/as16/tablespace/ESS/RPT/IDX
15M     /data/edb/as16/tablespace/ESS/RPT
28G     /data/edb/as16/tablespace/ESS/TSP/DAT/PG_16_202307071/44497
0       /data/edb/as16/tablespace/ESS/TSP/DAT/PG_16_202307071/pgsql_tmp
28G     /data/edb/as16/tablespace/ESS/TSP/DAT/PG_16_202307071
28G     /data/edb/as16/tablespace/ESS/TSP/DAT
0       /data/edb/as16/tablespace/ESS/TSP/IDX/PG_16_202307071
0       /data/edb/as16/tablespace/ESS/TSP/IDX
28G     /data/edb/as16/tablespace/ESS/TSP
149G    /data/edb/as16/tablespace/ESS
149G    /data/edb/as16/tablespace/
[root@db1 tablespace]#

It looks to me as though everything is working as expected. You took a full backup of your system, which was around 147GB - most of which is in a tablespace. It got compressed down to 20GB. You then took two incremental backups, which are by definition much smaller and take a short amount of time to run.