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 1t1mGc-000K7Y-Pe for pgsql-general@arkaria.postgresql.org; Fri, 18 Oct 2024 12:39:43 +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 1t1mGa-003t1z-7K for pgsql-general@arkaria.postgresql.org; Fri, 18 Oct 2024 12:39:40 +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 1t1mGZ-003t0T-JK for pgsql-general@lists.postgresql.org; Fri, 18 Oct 2024 12:39:40 +0000 Received: from mail-yb1-xb32.google.com ([2607:f8b0:4864:20::b32]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t1mGW-001XBn-QS for pgsql-general@lists.postgresql.org; Fri, 18 Oct 2024 12:39:38 +0000 Received: by mail-yb1-xb32.google.com with SMTP id 3f1490d57ef6-e2903a48ef7so2075611276.2 for ; Fri, 18 Oct 2024 05:39:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1729255176; x=1729859976; 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=vy+3x2uIiBbO5mufTH2PWIWt8JHGF5SwALONZzqgQ2A=; b=IYv4lWDkcmCbtMqMpCf08fboiOR1/DMVrv9zgfH54Fi9T5urJ3KOL3AEZDLh3wZPOf R4wAv4oyvFVW/7dM5XizypQOjZfImSRWOMAHv2ri3DC07rlU1W7ZHbcFme8d+8ekZ5gh z+0QXxoUweuVrDUuff0qdhcCE60irpGH64aMnnUJBpIRwfIlnVPs4+j7TApsHKs6wJ8r EYkq/TMaDYaJ6eL5jgAUA8y02kSbjs6KoIcOufAZSKWrOpsDGshSnl1DP1dgFevTsr+b LAP27UpxBbxbSzoMKIH3nI9UlGZBtkPQ5/BPxQsAca7k0Y4tiR9HQoBffRMdakCmaYQD QB2Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729255176; x=1729859976; 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=vy+3x2uIiBbO5mufTH2PWIWt8JHGF5SwALONZzqgQ2A=; b=Bw5E0nwyGFJhXmzbfaLHmiTmKoH3vXr8yDvxl+zgBjIEBRqkMFwpDwQPZs0Gq2fjBX /Hc5+Vd7xLlW9waxWw8Cp4tT+0HmchxUKAhrS5G9Xb8mSWhvZkWUawVY8o5tqUoMLYYV F8MCi6Y26EvxsvUBcsEXMPW178SziN++lu3WQZ5oz2dB+Iagb3uAEW9MWbIpgr3tkzc5 /B4v8XXuauC0SVOJOwpmOvLeFoxDaA5l/faEfvJHX/d+mf1ZxLmcdYqdPNaPupenPbPc 9Bxn9gU3/P4Gg9xubWSC64glfKZjtUecCByhl6h+8HUFZH94a7F9ARVNvn1nYwZgy0W+ yQWg== X-Gm-Message-State: AOJu0Ywgwp8/NM0HCuuuICzUyBvPF/LHTnBBEQoIis/F+aPuuqOJmPLw xuFqLCuFENtIiW1V+ej0iw+vMBVJZBX8AHE5nkO/0xxIuENvbqUkcVAC6kBC79iXL/40ezjKojn 9qoLhh2ytH6FUdOTzkec88MOXpQw= X-Google-Smtp-Source: AGHT+IHEBFesYJwCA4QoqtZWhcMEyBHYc2YpTAj9+291LZpQl8iGliEXNCHUugNdfB+mLQia/NXu1NtBnexW7l0PLvs= X-Received: by 2002:a05:6902:1105:b0:e29:3bfb:2fd4 with SMTP id 3f1490d57ef6-e2bb143564amr2359980276.26.1729255176035; Fri, 18 Oct 2024 05:39:36 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Andy Hartman Date: Fri, 18 Oct 2024 08:39:24 -0400 Message-ID: Subject: Re: Backup To: Asad Ali Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000007e66b40624bf981d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007e66b40624bf981d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM Andy Hartman wrote: > 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 suggestion= s >> that should help you achieve compressed, efficient backups without runni= ng >> 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 y= our >> 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.d= ump >> 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 dat= a. >> >> *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 feature= s >> 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 ea= sy >> to use and built-in with PostgreSQL, it is primarily designed for physic= al >> 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 eithe= r >> 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 proc= ess. >> >> I hope this helps you create smaller and quicker backups for your >> PostgreSQL database. Let me know if you have any questions or need furth= er >> 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 st= ore >>> 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 >>> >>> --0000000000007e66b40624bf981d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I would like to use the closest thing to = a mssql backup...

How do I install=C2=A0pgbackrest after downloading=C2=A0 a= nd UnZip?



On Thu, Oct 17, 2024 at 6:28=E2=80=AFAM A= ndy Hartman <hartman60home@gm= ail.com> wrote:
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

=
--0000000000007e66b40624bf981d--