public inbox for [email protected]
help / color / mirror / Atom feedRe: Backup
8+ messages / 2 participants
[nested] [flat]
* Re: Backup
@ 2024-10-18 12:39 Andy Hartman <[email protected]>
2024-10-18 14:45 ` Re: Backup Adrian Klaver <[email protected]>
2024-10-18 16:04 ` Re: Backup Adrian Klaver <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Andy Hartman @ 2024-10-18 12:39 UTC (permalink / raw)
To: Asad Ali <[email protected]>; +Cc: [email protected]
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
>>>
>>>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup
2024-10-18 12:39 Re: Backup Andy Hartman <[email protected]>
@ 2024-10-18 14:45 ` Adrian Klaver <[email protected]>
2024-10-18 15:34 ` Re: Backup Andy Hartman <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2024-10-18 14:45 UTC (permalink / raw)
To: Andy Hartman <[email protected]>; Asad Ali <[email protected]>; +Cc: [email protected]
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
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup
2024-10-18 12:39 Re: Backup Andy Hartman <[email protected]>
2024-10-18 14:45 ` Re: Backup Adrian Klaver <[email protected]>
@ 2024-10-18 15:34 ` Andy Hartman <[email protected]>
2024-10-18 15:55 ` Re: Backup Adrian Klaver <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Andy Hartman @ 2024-10-18 15:34 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Asad Ali <[email protected]>; [email protected]
Thanks.. I'm on a Windows platform using PG.
On Fri, Oct 18, 2024 at 10:45 AM Adrian Klaver <[email protected]>
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
> [email protected]
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup
2024-10-18 12:39 Re: Backup Andy Hartman <[email protected]>
2024-10-18 14:45 ` Re: Backup Adrian Klaver <[email protected]>
2024-10-18 15:34 ` Re: Backup Andy Hartman <[email protected]>
@ 2024-10-18 15:55 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Adrian Klaver @ 2024-10-18 15:55 UTC (permalink / raw)
To: Andy Hartman <[email protected]>; +Cc: Asad Ali <[email protected]>; [email protected]
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
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup
2024-10-18 12:39 Re: Backup Andy Hartman <[email protected]>
@ 2024-10-18 16:04 ` Adrian Klaver <[email protected]>
2024-10-18 17:00 ` Re: Backup Adrian Klaver <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2024-10-18 16:04 UTC (permalink / raw)
To: Andy Hartman <[email protected]>; Asad Ali <[email protected]>; +Cc: [email protected]
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
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup
2024-10-18 12:39 Re: Backup Andy Hartman <[email protected]>
2024-10-18 16:04 ` Re: Backup Adrian Klaver <[email protected]>
@ 2024-10-18 17:00 ` Adrian Klaver <[email protected]>
2024-10-18 18:25 ` Re: Backup Andy Hartman <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2024-10-18 17:00 UTC (permalink / raw)
To: Andy Hartman <[email protected]>; +Cc: pgsql-general
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
> <[email protected] <mailto:[email protected]>> 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
> [email protected] <mailto:[email protected]>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup
2024-10-18 12:39 Re: Backup Andy Hartman <[email protected]>
2024-10-18 16:04 ` Re: Backup Adrian Klaver <[email protected]>
2024-10-18 17:00 ` Re: Backup Adrian Klaver <[email protected]>
@ 2024-10-18 18:25 ` Andy Hartman <[email protected]>
2024-10-18 18:30 ` Re: Backup Adrian Klaver <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Andy Hartman @ 2024-10-18 18:25 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-general
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 <[email protected]>
wrote:
> 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
> > <[email protected] <mailto:[email protected]>> 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
> > [email protected] <mailto:[email protected]>
> >
>
> --
> Adrian Klaver
> [email protected]
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Backup
2024-10-18 12:39 Re: Backup Andy Hartman <[email protected]>
2024-10-18 16:04 ` Re: Backup Adrian Klaver <[email protected]>
2024-10-18 17:00 ` Re: Backup Adrian Klaver <[email protected]>
2024-10-18 18:25 ` Re: Backup Andy Hartman <[email protected]>
@ 2024-10-18 18:30 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Adrian Klaver @ 2024-10-18 18:30 UTC (permalink / raw)
To: Andy Hartman <[email protected]>; +Cc: pgsql-general
On 10/18/24 11:25 AM, Andy Hartman wrote:
> 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
>
Then pg_dump for database backup:
https://www.postgresql.org/docs/current/app-pgdump.html
and pg_dumpall for entire cluster backup:
https://www.postgresql.org/docs/current/app-pg-dumpall.html
> thanks for replies..
>
>
>
> On Fri, Oct 18, 2024 at 1:00 PM Adrian Klaver <[email protected]
> <mailto:[email protected]>> wrote:
>
> 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
> > <[email protected] <mailto:[email protected]>
> <mailto:[email protected]
> <mailto:[email protected]>>> 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
> > [email protected] <mailto:[email protected]>
> <mailto:[email protected] <mailto:[email protected]>>
> >
>
> --
> Adrian Klaver
> [email protected] <mailto:[email protected]>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-10-18 18:30 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-18 12:39 Re: Backup Andy Hartman <[email protected]>
2024-10-18 14:45 ` Adrian Klaver <[email protected]>
2024-10-18 15:34 ` Andy Hartman <[email protected]>
2024-10-18 15:55 ` Adrian Klaver <[email protected]>
2024-10-18 16:04 ` Adrian Klaver <[email protected]>
2024-10-18 17:00 ` Adrian Klaver <[email protected]>
2024-10-18 18:25 ` Andy Hartman <[email protected]>
2024-10-18 18:30 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox