public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andy Hartman <[email protected]>
To: Asad Ali <[email protected]>
Cc: [email protected]
Subject: Re: Backup
Date: Fri, 18 Oct 2024 08:39:24 -0400
Message-ID: <CAEZv3co3spuFH6JWB7fwxjobZA2CfiBxjx59kPvBLrmm_ro_0A@mail.gmail.com> (raw)
In-Reply-To: <CAEZv3cpDuCNg=vN_Q=ogk7ems0fho3xTWoCjLhZr47yM5u77KQ@mail.gmail.com>
References: <CAEZv3cqct1WFb9KYmYa5kjdxfVK8JEFdzbJ5bRL6b4r+XR2dug@mail.gmail.com>
<CAJ9xe=sdH2-kgM0+bMH8Oe_4=npPwetue+ukz9MXDTZfJ_poUg@mail.gmail.com>
<CAEZv3cpDuCNg=vN_Q=ogk7ems0fho3xTWoCjLhZr47yM5u77KQ@mail.gmail.com>
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 <[email protected]>
wrote:
> This is great, thank you so much!
>
> On Thu, Oct 17, 2024 at 12:47 AM Asad Ali <[email protected]> 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 <[email protected]>
>> 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
>>>
>>>
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Backup
In-Reply-To: <CAEZv3co3spuFH6JWB7fwxjobZA2CfiBxjx59kPvBLrmm_ro_0A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox