public inbox for [email protected]
help / color / mirror / Atom feedRe: Backup
4+ messages / 4 participants
[nested] [flat]
* Re: Backup
@ 2024-10-16 20:02 Ron Johnson <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Ron Johnson @ 2024-10-16 20:02 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
On Wed, Oct 16, 2024 at 4:00 PM Achilleas Mantzios <
[email protected]> wrote:
> Στις 16/10/24 22:55, ο/η Ron Johnson έγραψε:
>
> On Wed, Oct 16, 2024 at 3:37 PM 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
>>
>
> Step 1: redesign your DB to *NOT* use large objects. It's an old, slow
> and unmaintained data type. The data type is what you should use.
>
> You mean bytea I guess. As a side note, (not a fan of LOs), I had the
> impression that certain drivers such as the JDBC support streaming for LOs
> but not for bytea? It's been a while I haven't hit the docs tho.
>
Our database is stuffed with images in bytea fields. The Java application
uses JDBC and handles them just fine.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Backup
@ 2024-10-16 20:16 Achilleas Mantzios <[email protected]>
parent: Ron Johnson <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Achilleas Mantzios @ 2024-10-16 20:16 UTC (permalink / raw)
To: [email protected]
Στις 16/10/24 23:02, ο/η Ron Johnson έγραψε:
> On Wed, Oct 16, 2024 at 4:00 PM Achilleas Mantzios
> <[email protected]> wrote:
>
> Στις 16/10/24 22:55, ο/η Ron Johnson έγραψε:
>
>> On Wed, Oct 16, 2024 at 3:37 PM 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
>>
>>
>> Step 1: redesign your DB to *NOT* use large objects. It's an
>> old, slow and unmaintained data type. The data type is what you
>> should use.
> You mean bytea I guess. As a side note, (not a fan of LOs), I had
> the impression that certain drivers such as the JDBC support
> streaming for LOs but not for bytea? It's been a while I haven't
> hit the docs tho.
>
>
> Our database is stuffed with images in bytea fields. The Java
> application uses JDBC and handles them just fine.
Likewise, but the "streaming" part is still not clear to me, unless one
reads the newest JDBC source. Lots of problems due to image explosion,
java app heap space exhaustion and the like.
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> crustacean!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Backup
@ 2024-10-16 21:48 Peter J. Holzer <[email protected]>
parent: Ron Johnson <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Peter J. Holzer @ 2024-10-16 21:48 UTC (permalink / raw)
To: [email protected]
On 2024-10-16 16:02:24 -0400, Ron Johnson wrote:
> On Wed, Oct 16, 2024 at 4:00 PM Achilleas Mantzios <
> [email protected]> wrote:
> Στις 16/10/24 22:55, ο/η Ron Johnson έγραψε:
> On Wed, Oct 16, 2024 at 3:37 PM Andy Hartman <[email protected]>
> wrote:
[...]
>
> Step 1: redesign your DB to NOT use large objects. It's an old, slow
> and unmaintained data type. The data type is what you should use.
>
> You mean bytea I guess. As a side note, (not a fan of LOs), I had the
> impression that certain drivers such as the JDBC support streaming for LOs
> but not for bytea? It's been a while I haven't hit the docs tho.
>
>
> Our database is stuffed with images in bytea fields. The Java application uses
> JDBC and handles them just fine.
Images are usually small enough (a few MB) that they don't need to be
streamed.
I don't think bytea can be streamed in general. It's just like text, you
write and read the whole thing at once.
If you have data which is too large for that and want to store it in
bytea fields, you'll probably have to chunk it yourself (which you
probably have to anyway because for me "so large it has to be streamed"
implies "at least possibly larger than 1 GB").
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Backup
@ 2024-10-16 22:59 Andy Hartman <[email protected]>
parent: Peter J. Holzer <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Andy Hartman @ 2024-10-16 22:59 UTC (permalink / raw)
To: [email protected]
I'm on Ver16 and yes Our database has image in a bytea field.
Running on Win22 box...
On Wed, Oct 16, 2024 at 5:49 PM Peter J. Holzer <[email protected]> wrote:
> On 2024-10-16 16:02:24 -0400, Ron Johnson wrote:
> > On Wed, Oct 16, 2024 at 4:00 PM Achilleas Mantzios <
> > [email protected]> wrote:
> > Στις 16/10/24 22:55, ο/η Ron Johnson έγραψε:
> > On Wed, Oct 16, 2024 at 3:37 PM Andy Hartman <
> [email protected]>
> > wrote:
> [...]
> >
> > Step 1: redesign your DB to NOT use large objects. It's an old,
> slow
> > and unmaintained data type. The data type is what you should
> use.
> >
> > You mean bytea I guess. As a side note, (not a fan of LOs), I had the
> > impression that certain drivers such as the JDBC support streaming
> for LOs
> > but not for bytea? It's been a while I haven't hit the docs tho.
> >
> >
> > Our database is stuffed with images in bytea fields. The Java
> application uses
> > JDBC and handles them just fine.
>
> Images are usually small enough (a few MB) that they don't need to be
> streamed.
>
> I don't think bytea can be streamed in general. It's just like text, you
> write and read the whole thing at once.
>
> If you have data which is too large for that and want to store it in
> bytea fields, you'll probably have to chunk it yourself (which you
> probably have to anyway because for me "so large it has to be streamed"
> implies "at least possibly larger than 1 GB").
>
> hp
>
> --
> _ | Peter J. Holzer | Story must make more sense than reality.
> |_|_) | |
> | | | [email protected] | -- Charles Stross, "Creative writing
> __/ | http://www.hjp.at/ | challenge!"
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-10-16 22:59 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-16 20:02 Re: Backup Ron Johnson <[email protected]>
2024-10-16 20:16 ` Achilleas Mantzios <[email protected]>
2024-10-16 21:48 ` Peter J. Holzer <[email protected]>
2024-10-16 22:59 ` Andy Hartman <[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