Backup

On 10/16/24 21:37, Andy Hartman 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
store 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=t --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 

You didn't specify the Postgres version - that matters, because older
pg_dump versions (before PG 16) do not support compression. Since PG 16
you can use either -Fc or -Fd (instead of the tar format), and it'll
compress the output using gzip.

Alternatively, you can use --compress=method:level (the supported
methods depend on how the packages were built, no idea what platform
you're on etc.). See

https://www.postgresql.org/docs/current/app-pgdump.html

If you're on older version, you should be able to write the dump to
standard output, and compress that way. Something like

pg_dump -Fc | gzip -c > compressed.dump.gz

However, be aware that pg_dump is more an export tool than a backup
suitable for large databases / quick recovery. It won't allow doing PITR
and similar stuff.

regards

--
Tomas Vondra

On Wed, Oct 16, 2024 at 3:37 PM 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 store
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=t --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

Step 1: redesign your DB to *NOT* use large objects. It's an old, slow and
unmaintained data type. The data type is what you should use.

Step 2: show us the "before" df output, the whole pg_dump command, and the
"after" df output when it fails. "du -c --max-depth=0 $PGDATA/base" also
very useful.

And tell us what version you're using.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!

Στις 16/10/24 22:55, ο/η Ron Johnson έγραψε:

On Wed, Oct 16, 2024 at 3:37 PM 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 store 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=t --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

Step 1: redesign your DB to *NOT* use large objects.  It's an old,
slow and unmaintained data type.  The data type is what you should use.

You mean bytea I guess. As a side note, (not a fan of LOs), I had the
impression that certain drivers such as the JDBC support streaming for
LOs but not for bytea? It's been a while I haven't hit the docs tho.

On Wed, Oct 16, 2024 at 3:52 PM Tomas Vondra <tomas@vondra.me> wrote:
[snip]

You didn't specify the Postgres version - that matters, because older
pg_dump versions (before PG 16) do not support compression. Since PG 16
you can use either -Fc or -Fd (instead of the tar format), and it'll
compress the output using gzip.

Not true. pg_dump has had built-in gzipping of directory format backups
since at least 9.6. Probably earlier.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!

On Wed, Oct 16, 2024 at 4:00 PM Achilleas Mantzios <
a.mantzios@cloud.gatewaynet.com> wrote:

Στις 16/10/24 22:55, ο/η Ron Johnson έγραψε:

On Wed, Oct 16, 2024 at 3:37 PM 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 store
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=t --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

Step 1: redesign your DB to *NOT* use large objects. It's an old, slow
and unmaintained data type. The data type is what you should use.

You mean bytea I guess. As a side note, (not a fan of LOs), I had the
impression that certain drivers such as the JDBC support streaming for LOs
but not for bytea? It's been a while I haven't hit the docs tho.

Our database is stuffed with images in bytea fields. The Java application
uses JDBC and handles them just fine.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!

Στις 16/10/24 23:02, ο/η Ron Johnson έγραψε:

On Wed, Oct 16, 2024 at 4:00 PM Achilleas Mantzios
<a.mantzios@cloud.gatewaynet.com> wrote:

Στις 16/10/24 22:55, ο/η Ron Johnson έγραψε:

On Wed, Oct 16, 2024 at 3:37 PM 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 store 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=t --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

Step 1: redesign your DB to *NOT* use large objects.  It's an
old, slow and unmaintained data type.  The data type is what you
should use.

You mean bytea I guess. As a side note, (not a fan of LOs), I had
the impression that certain drivers such as the JDBC support
streaming for LOs but not for bytea? It's been a while I haven't
hit the docs tho.

Our database is stuffed with images in bytea fields.  The Java
application uses JDBC and handles them just fine.

Likewise, but the "streaming" part is still not clear to me, unless one
reads the newest JDBC source. Lots of problems due to image explosion,
java app heap space exhaustion and the like.

On 10/16/24 12:52, Tomas Vondra wrote:

On 10/16/24 21:37, Andy Hartman wrote:

I am very new to Postgres and have always worked in the mssql world. I'm

You didn't specify the Postgres version - that matters, because older
pg_dump versions (before PG 16) do not support compression. Since PG 16
you can use either -Fc or -Fd (instead of the tar format), and it'll
compress the output using gzip.

Oh, it goes back further then that:

https://www.postgresql.org/docs/7.1/app-pgdump.html

"-F format, --format=format

Format can be one of the following:

p

output a plain text SQL script file (default)
t

output a tar archive suitable for input into pg_restore. Using
this archive format allows reordering and/or exclusion of schema
elements at the time the database is restored. It is also possible to
limit which data is reloaded at restore time.
c

output a custom archive suitable for input into pg_restore.
This is the most flexible format in that it allows reordering of data
load as well as schema elements. This format is also compressed by default.

"

Alternatively, you can use --compress=method:level (the supported
methods depend on how the packages were built, no idea what platform
you're on etc.). See

https://www.postgresql.org/docs/current/app-pgdump.html

If you're on older version, you should be able to write the dump to
standard output, and compress that way. Something like

pg_dump -Fc | gzip -c > compressed.dump.gz

However, be aware that pg_dump is more an export tool than a backup
suitable for large databases / quick recovery. It won't allow doing PITR
and similar stuff.

regards

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2024-10-16 16:02:24 -0400, Ron Johnson wrote:

On Wed, Oct 16, 2024 at 4:00 PM Achilleas Mantzios <
a.mantzios@cloud.gatewaynet.com> wrote:
Στις 16/10/24 22:55, ο/η Ron Johnson έγραψε:
On Wed, Oct 16, 2024 at 3:37 PM Andy Hartman <hartman60home@gmail.com>
wrote:

[...]

Step 1: redesign your DB to NOT use large objects.  It's an old, slow
and unmaintained data type.  The data type is what you should use.

You mean bytea I guess. As a side note, (not a fan of LOs), I had the
impression that certain drivers such as the JDBC support streaming for LOs
but not for bytea? It's been a while I haven't hit the docs tho.

Our database is stuffed with images in bytea fields.  The Java application uses
JDBC and handles them just fine.

Images are usually small enough (a few MB) that they don't need to be
streamed.

I don't think bytea can be streamed in general. It's just like text, you
write and read the whole thing at once.

If you have data which is too large for that and want to store it in
bytea fields, you'll probably have to chunk it yourself (which you
probably have to anyway because for me "so large it has to be streamed"
implies "at least possibly larger than 1 GB").

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

I'm on Ver16 and yes Our database has image in a bytea field.

Running on Win22 box...

On Wed, Oct 16, 2024 at 5:49 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

Hi,
you can refer the following link where many backup methods are mentioned
according to your need.

Backups in PostgreSQL. In PostgreSQL, different types of… | by Usman Khan |
Aug, 2024 | Medium
<https://medium.com/@usman.khan9805/backups-in-postgresql-2fca57c2872b&gt;

On Thu, 17 Oct 2024 at 00:37, Andy Hartman <hartman60home@gmail.com> wrote:

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=my_large_table -f /path/to/backup/my_large_table.dump
your_database_name
pg_dump -Fc --table=other_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.gz

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=your-database --type=full --compress-type=zst
--compress-level=6 --process-max=4 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–9, 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 AM Andy Hartman <hartman60home@gmail.com>
wrote:

This is great, thank you so much!

On Thu, Oct 17, 2024 at 12:47 AM Asad Ali <asadalinagri@gmail.com> wrote:

I would like to use the closest thing to a mssql backup...

How do I install pgbackrest after downloading and UnZip?

On Thu, Oct 17, 2024 at 6:28 AM Andy Hartman <hartman60home@gmail.com>
wrote:

On 10/18/24 05:39, Andy Hartman wrote:

I would like to use the closest thing to a mssql backup...

How do I install pgbackrest after downloading  and UnZip?

Read the docs?:

https://pgbackrest.org/user-guide.html#installation

--
Adrian Klaver
adrian.klaver@aklaver.com

Thanks.. I'm on a Windows platform using PG.

On Fri, Oct 18, 2024 at 10:45 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/18/24 08:34, Andy Hartman wrote:

Thanks.. I'm on a Windows platform using PG.

AFAIK pgBackRest does not run on Windows:

https://github.com/pgbackrest/pgbackrest/issues/2431

--
Adrian Klaver
adrian.klaver@aklaver.com

On 10/18/24 05:39, Andy Hartman wrote:

I would like to use the closest thing to a mssql backup...

As in?

Searching on 'mssql backup' returns a lot of choices. You will need to
be more specific on what you want to achieve. Also be specific about
what versions of OS and Postgres you are using.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 10/18/24 09:52, Andy Hartman wrote:

Reply to list also
Ccing list.

Windows 22 and Postgres 16.4

Is that Windows Server 2022 or Windows 10/11 with the 22H2 update?

You also need to provide a more detailed description of what you want
the backup to do?

On Fri, Oct 18, 2024 at 12:04 PM Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 10/18/24 05:39, Andy Hartman wrote:

I would like to use the closest thing to a mssql backup...

As in?

Searching on 'mssql backup' returns a  lot of choices. You will need to
be more specific on what you want to achieve. Also be specific about
what versions of OS and Postgres you are using.

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

Windows Server 2022

I would like a backup process similar to what I'm used to using in the
mssql world if that's possible with Postgres... I will be loading monthly
archive data to Postgresql so that's why I probably only need once a backup
.. once loaded data is just viewed thru an app... no updates..

in mssql you create the .bak and easily restorable .... that's what I'm
looking for with Postgres

thanks for replies..

On Fri, Oct 18, 2024 at 1:00 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote: