Thread

PgBackRest full backup first time : Verification

KK CHN <kkchn.in@gmail.com> — 2024-08-30T04:28:38Z

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

Re: PgBackRest full backup first time : Verification

Greg Sabino Mullane <htamfids@gmail.com> — 2024-08-30T12:45:56Z

>
> database size: 146.9GB, database backup size: 146.9GB
> 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.

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 ...
>

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/)

149G    /data/edb/as16/tablespace/ESS


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