Subject: PgBackRest full backup first time : Verification

From: KK CHN <[email-masked]>
Date: Fri, 30 Aug 2024 09:58:38 +0530

List,

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 ~]#

For more information about the DB cluster file system  Find the  paste below
[root@db1 data]# pwd
/data

[root@db1 data]# cd /data/edb/as16/data
[root@db1 data]# pwd
/data/edb/as16/data
[root@db1 data]# du -h
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]#

---

From: Greg Sabino Mullane <[email-masked]>
Date: Fri, 30 Aug 2024 08:45:56 -0400

> repo1: backup size: 20.6GB

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.

> production server and down time granting is not immediately possible to

You do not have to restore to the same server or the same directory. You
can keep your production system running and do a test restore somewhere
else. Just make sure you specify --archive-mode=off (which prevents the WAL
from being shipped from the restored system to your existing production
repo)

[root@db1 data]# du -h
> returns   537 G

This is not relevant, as pgbackrest only cares about the Postgres data
directory (/data/edb/as16/data/)

This is where the rest of your backup size is coming from. Postgres and
pgbackrest consider this part of the data directory.

You really should spin up a test Postgres cluster and get very familiar
with how pgbackrest works, rather than continuing to flounder about on a
production system and rely on mailing lists to answer a bunch of questions
for you. While we can answer these questions, you will learn better from
experimenting and trying things out yourself on a non-prod system.

Cheers,
Greg