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 <hartman60h...@gmail.com> wrote: > This is great, thank you so much! > > On Thu, Oct 17, 2024 at 12:47 AM Asad Ali <asadalina...@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 <hartman60h...@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 >>> >>>
