Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1Nkt-00G2G6-Tc for pgsql-general@arkaria.postgresql.org; Thu, 17 Oct 2024 10:29:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t1Nks-002xDX-7v for pgsql-general@arkaria.postgresql.org; Thu, 17 Oct 2024 10:29:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1NkS-002rV9-0F for pgsql-general@lists.postgresql.org; Thu, 17 Oct 2024 10:28:52 +0000 Received: from mail-yb1-xb33.google.com ([2607:f8b0:4864:20::b33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t1NkP-001Ldt-BN for pgsql-general@lists.postgresql.org; Thu, 17 Oct 2024 10:28:51 +0000 Received: by mail-yb1-xb33.google.com with SMTP id 3f1490d57ef6-e290d48d3f7so622509276.3 for ; Thu, 17 Oct 2024 03:28:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1729160928; x=1729765728; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=OdrhCbQmQkGlohwX1byt2tdfCWXt0jYDZcCJmBuVTfo=; b=VslPoMOJMS4hABKwrD+fgRegCigiQvCfEAt3rP1bjrD44Kx9vfoTGXWR63dAZ2Y6Eo 9qpWrzS8NNtYnXla3KfmSM6sKCcZdVbbddyEpTti4uNLT5qtlmZoZghXHWQSDs/oq63O OqK0o+w3CMe5ned0M+g7GW+KcXjRl/5L8KVaXH0hzHrvTrgjQKEPWOLVHzQyPM+JbVFx wYNHTu0+ISyp/G4YFRwT4Hktf1oSfcChYu81Xp5Oxxw5W96LmI1I3Wfn33/c3AYSRPr5 GVRjGmz7WO6IMlk1+9uzuEUoG49ltApdEqp5Ut6++EK2PsXYaQIquZo8L9iCgWLRPT05 lJeg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729160928; x=1729765728; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=OdrhCbQmQkGlohwX1byt2tdfCWXt0jYDZcCJmBuVTfo=; b=cyhCekLHWe4GpIEINKFPPQdMP85NHVHRihlh/21UxwR4cDfLrrK7eaBeeh0nHn5sJc AeXMdmKCjJ8Yrngv/K3590DiyYVgMjliSvuX8Qya9Wnus4xNTH05GaIDUoYUnAajapCS MgAuOltEctrkHvYOZt4AqkqvW2xmFE//qBs7XjZLX/Pqq1XUeAtPWWc/jRJNJ2ay//JI 5AYS7CFMciUB9fU7nw7HNl22rVInxKKK6HMooDYnuzExD/fSnt3ZsUTVtz7d99lYIl8y iPpXYMDsnpPP9pit0Nrul98WdXr0EgdKUE4/gOO8vQO4wSSIQOrYuwM7JO1JP5eZvpb5 ZRjA== X-Gm-Message-State: AOJu0YyEGx1jtRpKapguL2E9YAkGSg1GvIYjp0KH9QF0jCzpm2ieoS+r Q99ljF9G1x4Lgqj+HqWr83dP+ZuloRD7eczeFltoS3k99+dZ0NBgYelWOp2CWcTTh+T4N48qsTX J6qd0CBwekiONWlp9XpPvQ1O1pDo= X-Google-Smtp-Source: AGHT+IHSYEbqvXshSoKnBdUZksM2XCOZWiTonl1MXy/bH1w16FH1RQi75OEelkXAMl1CgPHXo/SVnTr32n658ibBVj4= X-Received: by 2002:a05:6902:2413:b0:e25:e3d1:1492 with SMTP id 3f1490d57ef6-e2931b00e96mr13046302276.7.1729160928386; Thu, 17 Oct 2024 03:28:48 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Andy Hartman Date: Thu, 17 Oct 2024 06:28:37 -0400 Message-ID: Subject: Re: Backup To: Asad Ali Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000e5679e0624a9a61b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000e5679e0624a9a61b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable This is great, thank you so much! On Thu, Oct 17, 2024 at 12:47=E2=80=AFAM Asad Ali = 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 runnin= g > 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 yo= ur > 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 splittin= g > 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.du= mp > 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.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=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 eas= y > to use and built-in with PostgreSQL, it is primarily designed for physica= l > backups (base backups) and doesn't offer as many advanced features such a= s > 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 mon= th > 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 proce= ss. > > I hope this helps you create smaller and quicker backups for your > PostgreSQL database. Let me know if you have any questions or need furthe= r > assistance! > > Best regards, > > Asad Ali > > > On Thu, Oct 17, 2024 at 12:38=E2=80=AFAM 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 sto= re >> 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 >> >> --000000000000e5679e0624a9a61b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
This is great, thank you so much!=C2=A0

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

Hi Andy,

I hope you'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.

1. Use Custom Format with Compression
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's multiple CPUs by using parallel jobs:

pg_dump -Fc -Z 9 -j 4 --blobs -f /p= ath/to/backup/file.dump your_database_name
  • -Fc: Custom format (supports com= pression and flexible restore options).
  • -Z 9: Maximum compression level (0-9 scale).<= /span>
  • -j 4: Numb= er of parallel jobs (adjust based on CPU cores).
  • --blobs: Includes large objects (imp= ortant for your images).
This approach should gi= ve you a smaller backup file with faster performance.

2. Splittin= g Backups into Parts
If you'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:

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 ToolsIf you need additional compression, you can pipe the pg_dump output throug= h an external compression tool like gzip:

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

This should f= urther reduce the size of your backups.

4.= Consider Alternative Backup Methods
- Explore other backup m= ethods like `pgBackRest`= or `WAL-E`. These tools are specifically designed for PostgreSQ= L backups and offer features like incremental backups and point-in-time rec= overy
pgbackrest --stanza=3Dyour-database --type=3Dfull --compress-type=3D= zst --compress-level=3D6 --process-max=3D4 backup

=
- You can use pg_ba= sebackup 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't offer as many advanced features such as incremental backups, so= phisticated compression, or parallelism.

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

<= div>pg_baseba= ckup -D /path/to/backup/directory -F t -z -Z 9 -P -X stream
  • -D: The destination directory for the backup.
  • -F t: Speci= fies 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 consisten= t (important for recovery).
pg_basebackup -D /backups/db_backup -F t -z -= Z 9 -P -X stream
This command will take a full physical ba= ckup 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_n= ame

6. Monitoring Disk Usage &=C2=A0 Backup Performance
Finally, it'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
Use monitoring tools to track the performance of your b= ackups. 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 question= s 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 wo= rld. I'm looking for suggestions on DB backups. I currently have a DB u= sed to store Historical information that has images it's currently arou= nd 100gig.=C2=A0

I'm looking to take a monthly backup as I archi= ve a month of data at a time. I am looking for it to be compressed and have= a machine that has multiple=C2=A0cpu's=C2=A0and ample memory.

= Suggestions on things I can try ?=C2=A0=C2=A0
I did a pg_dump usi= ng 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 obvious= ly=C2=A0add more

looking for the quickest and smallest backup file o= utput...

Thanks again for help\suggestions=C2=A0

=
--000000000000e5679e0624a9a61b--