MIME-Version: 1.0
References: <CAEZv3cqct1WFb9KYmYa5kjdxfVK8JEFdzbJ5bRL6b4r+XR2dug@mail.gmail.com>
In-Reply-To: <CAEZv3cqct1WFb9KYmYa5kjdxfVK8JEFdzbJ5bRL6b4r+XR2dug@mail.gmail.com>
From: Asad Ali <asadalinagri@gmail.com>
Date: Thu, 17 Oct 2024 09:46:56 +0500
Message-ID: <CAJ9xe=sdH2-kgM0+bMH8Oe_4=npPwetue+ukz9MXDTZfJ_poUg@mail.gmail.com>
Subject: Re: Backup
To: Andy Hartman <hartman60home@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000003b6390624a4e123"
Archived-At: <https://www.postgresql.org/message-id/CAJ9xe%3DsdH2-kgM0%2BbMH8Oe_4%3DnpPwetue%2Bukz9MXDTZfJ_poUg%40mail.gmail.com>
Precedence: bulk

--00000000000003b6390624a4e123
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Andy,

I hope you're doing well. Based on your inquiry about PostgreSQL backups
for your 100GB historical database with images, here are some suggestions
that should help you achieve compressed, efficient backups without running
into storage issues.

*1. Use Custom Format with Compression*
A more efficient option would be to use the custom format (-Fc) with
compression. You can also adjust the compression level and make use of your
machine's multiple CPUs by using parallel jobs:

pg_dump -Fc -Z 9 -j 4 --blobs -f /path/to/backup/file.dump
your_database_name

   - -Fc: Custom format (supports compression and flexible restore options)=
.
   - -Z 9: Maximum compression level (0-9 scale).
   - -j 4: Number of parallel jobs (adjust based on CPU cores).
   - --blobs: Includes large objects (important for your images).

This approach should give you a smaller backup file with faster performance=
.

*2. Splitting Backups into Parts*
If you're concerned about running out of storage space, consider splitting
the backup by table or schema, allowing more control over the backup size:

pg_dump -Fc --table=3Dmy_large_table -f /path/to/backup/my_large_table.dump
your_database_name
pg_dump -Fc --table=3Dother_table -f /path/to/backup/other_table.dump
your_database_name

This can be helpful when you archive different tables or sections of data.

*3. External Compression Tools*
If you need additional compression, you can pipe the pg_dump output through
an external compression tool like gzip:

pg_dump -Fc --blobs your_database_name | gzip > /path/to/backup/file.dump.g=
z

This should further reduce the size of your backups.

*4. Consider Alternative Backup Methods*
- Explore other backup methods like `*pgBackRest` or `WAL-E`*. These tools
are specifically designed for PostgreSQL backups and offer features like
incremental backups and point-in-time recovery
pgbackrest --stanza=3Dyour-database --type=3Dfull --compress-type=3Dzst
--compress-level=3D6 --process-max=3D4 backup

- You can use *pg_basebackup* for PostgreSQL backups, but it has
limitations compared to tools like pgBackRest. While pg_basebackup is easy
to use and built-in with PostgreSQL, it is primarily designed for physical
backups (base backups) and doesn't offer as many advanced features such as
incremental backups, sophisticated compression, or parallelism.

However, it does support basic compression and can be used for full backups=
.

pg_basebackup -D /path/to/backup/directory -F t -z -Z 9 -P -X stream

   - -D: The destination directory for the backup.
   - -F t: Specifies the tar format for the backup, which is required for
   compression.
   - -z: Compresses the output.
   - -Z 9: Compression level (0=E2=80=939, where 9 is the highest).
   - -P: Shows the progress of the backup.
   - -X stream: Includes the WAL files needed to make the backup consistent
   (important for recovery).

pg_basebackup -D /backups/db_backup -F t -z -Z 9 -P -X stream
This command will take a full physical backup of the database, compress the
output using gzip, and store the backup in a tarball.

*5. Automating Backups*
Since you need monthly backups, I recommend automating this process with a
cron job. For example, you can set this up to run on the 1st of every month
at 2 AM:

0 2 1 * * /usr/bin/pg_dump -Fc -Z 9 -j 4 --blobs -f
/path/to/backup/file.dump your_database_name

*6. Monitoring Disk Usage & * *Backup Performance*
Finally, it's important to monitor your available storage. You can either
ensure you have enough free space or consider moving older backups to
external or cloud storage to free up space.
Use monitoring tools to track the performance of your backups. This will
help you identify any potential bottlenecks and optimize the backup process=
.

I hope this helps you create smaller and quicker backups for your
PostgreSQL database. Let me know if you have any questions or need further
assistance!

Best regards,

Asad Ali


On Thu, Oct 17, 2024 at 12:38=E2=80=AFAM Andy Hartman <hartman60home@gmail.=
com>
wrote:

> I am very new to Postgres and have always worked in the mssql world. I'm
> looking for suggestions on DB backups. I currently have a DB used to stor=
e
> Historical information that has images it's currently around 100gig.
>
> I'm looking to take a monthly backup as I archive a month of data at a
> time. I am looking for it to be compressed and have a machine that has
> multiple cpu's and ample memory.
>
> Suggestions on things I can try ?
> I did a pg_dump using these parms
> --format=3Dt --blobs lobarch
>
> it ran my device out of storage:
>
> pg_dump: error: could not write to output file: No space left on device
>
> I have 150gig free on my backup drive... can obviously add more
>
> looking for the quickest and smallest backup file output...
>
> Thanks again for help\suggestions
>
>

--00000000000003b6390624a4e123
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><br></div><div>Hi Andy,</div><div><br></div><div>

I hope you&#39;re doing well. Based on your inquiry about PostgreSQL backup=
s for your 100GB historical database with images, here are some suggestions=
 that should help you achieve compressed, efficient backups without running=
 into storage issues.

</div><div><br></div><div><b>1. Use Custom Format with Compression</b><br>A=
 more efficient option would be to use the custom format (-Fc) with compres=
sion. You can also adjust the compression level and make use of your machin=
e&#39;s multiple CPUs by using parallel jobs:<br><br><font color=3D"#ffffff=
" style=3D"background-color:rgb(0,0,0)">pg_dump -Fc -Z 9 -j 4 --blobs -f /p=
ath/to/backup/file.dump your_database_name</font></div><div><ul><li><span s=
tyle=3D"background-color:rgb(255,255,255)">-Fc: Custom format (supports com=
pression and flexible restore options).</span></li><li><span style=3D"backg=
round-color:rgb(255,255,255)">-Z 9: Maximum compression level (0-9 scale).<=
/span></li><li><span style=3D"background-color:rgb(255,255,255)">-j 4: Numb=
er of parallel jobs (adjust based on CPU cores).</span></li><li><span style=
=3D"background-color:rgb(255,255,255)">--blobs: Includes large objects (imp=
ortant for your images).</span></li></ul></div><div>This approach should gi=
ve you a smaller backup file with faster performance.<br><br><b>2. Splittin=
g Backups into Parts</b><br>If you&#39;re concerned about running out of st=
orage space, consider splitting the backup by table or schema, allowing mor=
e control over the backup size:</div><div><br><span style=3D"background-col=
or:rgb(0,0,0)"><font color=3D"#ffffff">pg_dump -Fc --table=3Dmy_large_table=
 -f /path/to/backup/my_large_table.dump your_database_name<br>pg_dump -Fc -=
-table=3Dother_table -f /path/to/backup/other_table.dump your_database_name=
</font></span></div><div><br>This can be helpful when you archive different=
 tables or sections of data.<br><br><b>3. External Compression Tools</b><br=
>If you need additional compression, you can pipe the pg_dump output throug=
h an external compression tool like gzip:<br><br><font color=3D"#ffffff" st=
yle=3D"background-color:rgb(0,0,0)">pg_dump -Fc --blobs your_database_name =
| gzip &gt; /path/to/backup/file.dump.gz</font></div><div><br>This should f=
urther reduce the size of your backups.</div><div><br></div><div><div><b>4.=
 Consider Alternative Backup Methods</b></div><div>- Explore other backup m=
ethods like <span style=3D"background-color:rgb(255,255,0)">`<u>pgBackRest`=
 or `WAL-E`</u></span>. These tools are specifically designed for PostgreSQ=
L backups and offer features like incremental backups and point-in-time rec=
overy<br></div><div><font color=3D"#ffffff" style=3D"background-color:rgb(0=
,0,0)">pgbackrest --stanza=3Dyour-database --type=3Dfull --compress-type=3D=
zst --compress-level=3D6 --process-max=3D4 backup<br></font></div><div><br>=
</div><div>- You can use <u style=3D"background-color:rgb(255,255,0)">pg_ba=
sebackup</u> for PostgreSQL backups, but it has limitations compared to too=
ls like pgBackRest. While pg_basebackup is easy to use and built-in with Po=
stgreSQL, it is primarily designed for physical backups (base backups) and =
doesn&#39;t offer as many advanced features such as incremental backups, so=
phisticated compression, or parallelism.<br><br>However, it does support ba=
sic compression and can be used for full backups.<br></div><div><br></div><=
div><font color=3D"#ffffff" style=3D"background-color:rgb(0,0,0)">pg_baseba=
ckup -D /path/to/backup/directory -F t -z -Z 9 -P -X stream</font></div><di=
v><ul><li>-D: The destination directory for the backup.</li><li>-F t: Speci=
fies the tar format for the backup, which is required for compression.</li>=
<li>-z: Compresses the output.</li><li>-Z 9: Compression level (0=E2=80=939=
, where 9 is the highest).</li><li>-P: Shows the progress of the backup.</l=
i><li>-X stream: Includes the WAL files needed to make the backup consisten=
t (important for recovery).</li></ul></div><font color=3D"#ffffff" style=3D=
"background-color:rgb(0,0,0)">pg_basebackup -D /backups/db_backup -F t -z -=
Z 9 -P -X stream</font></div><div>This command will take a full physical ba=
ckup of the database, compress the output using gzip, and store the backup =
in a tarball.<br><br><b>5. Automating Backups</b><br>Since you need monthly=
 backups, I recommend automating this process with a cron job. For example,=
 you can set this up to run on the 1st of every month at 2 AM:<br><br><font=
 color=3D"#ffffff" style=3D"background-color:rgb(0,0,0)">0 2 1 * * /usr/bin=
/pg_dump -Fc -Z 9 -j 4 --blobs -f /path/to/backup/file.dump your_database_n=
ame</font></div><div><br><b>6. Monitoring Disk Usage &amp;=C2=A0</b>

<b>Backup Performance</b><br>Finally, it&#39;s important to monitor your av=
ailable storage. You can either ensure you have enough free space or consid=
er moving older backups to external or cloud storage to free up space.=C2=
=A0=C2=A0</div><div>Use monitoring tools to track the performance of your b=
ackups. This will help you identify any potential bottlenecks and optimize =
the backup process.<br><br>I hope this helps you create smaller and quicker=
 backups for your PostgreSQL database. Let me know if you have any question=
s or need further assistance!<br></div><div><br></div><div>Best regards,</d=
iv><div><br></div><div>Asad Ali</div><br></div><br><div class=3D"gmail_quot=
e"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Oct 17, 2024 at 12:38=E2=
=80=AFAM Andy Hartman &lt;<a href=3D"mailto:hartman60home@gmail.com">hartma=
n60home@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">I am ve=
ry new to Postgres and have always worked in the mssql world. I&#39;m looki=
ng for suggestions on DB backups. I currently have a DB used to store Histo=
rical information that has images it&#39;s currently around 100gig.=C2=A0<b=
r><br>I&#39;m looking to take a monthly backup as I archive a month of data=
 at a time. I am looking for it to be compressed and have a machine that ha=
s multiple=C2=A0cpu&#39;s=C2=A0and ample memory.<div><br>Suggestions on thi=
ngs I can try ?=C2=A0=C2=A0</div><div>I did a pg_dump using these parms<br>=
--format=3Dt --blobs lobarch<br><br>it ran my device out of storage:<br><br=
>pg_dump: error: could not write to output file: No space left on device<br=
><br>I have 150gig free on my backup drive... can obviously=C2=A0add more<b=
r><br>looking for the quickest and smallest backup file output...<br><br>Th=
anks again for help\suggestions=C2=A0<br><br></div></div></div></div>
</blockquote></div>

--00000000000003b6390624a4e123--