MIME-Version: 1.0
References: <CAEZv3cqct1WFb9KYmYa5kjdxfVK8JEFdzbJ5bRL6b4r+XR2dug@mail.gmail.com>
 <CANzqJaBfaOvbjJYYCPPC-JKFEw=pZTGkfwL3+-t2fjP7B_9U+g@mail.gmail.com> <f1a0c76b-c645-4530-b768-fea480eab56e@cloud.gatewaynet.com>
In-Reply-To: <f1a0c76b-c645-4530-b768-fea480eab56e@cloud.gatewaynet.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 16 Oct 2024 16:02:24 -0400
Message-ID: <CANzqJaCtWGeLNoVVmzJ6=S6XzptR_K6VByCtwCLRVXAHUk0fUw@mail.gmail.com>
Subject: Re: Backup
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000117ef606249d8d3f"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaCtWGeLNoVVmzJ6%3DS6XzptR_K6VByCtwCLRVXAHUk0fUw%40mail.gmail.com>
Precedence: bulk

--000000000000117ef606249d8d3f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, Oct 16, 2024 at 4:00=E2=80=AFPM Achilleas Mantzios <
a.mantzios@cloud.gatewaynet.com> wrote:

> =CE=A3=CF=84=CE=B9=CF=82 16/10/24 22:55, =CE=BF/=CE=B7 Ron Johnson =CE=AD=
=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
>
> On Wed, Oct 16, 2024 at 3:37=E2=80=AFPM Andy Hartman <hartman60home@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 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
>>
>
> 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 LO=
s
> 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.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> crustacean!

--000000000000117ef606249d8d3f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Oct 16, 2024 at 4:00=E2=80=AFPM A=
chilleas Mantzios &lt;<a href=3D"mailto:a.mantzios@cloud.gatewaynet.com">a.=
mantzios@cloud.gatewaynet.com</a>&gt; wrote:<br></div><div class=3D"gmail_q=
uote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex"><u></u>

 =20
   =20
 =20
  <div>
    <p>=CE=A3=CF=84=CE=B9=CF=82 16/10/24 22:55, =CE=BF/=CE=B7 Ron Johnson
      =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:<br></p>
    <blockquote type=3D"cite">
     =20
      <div dir=3D"ltr">
        <div dir=3D"ltr">On Wed, Oct 16, 2024 at 3:37=E2=80=AFPM Andy Hartm=
an &lt;<a href=3D"mailto:hartman60home@gmail.com" target=3D"_blank">hartman=
60home@gmail.com</a>&gt;
          wrote:<br>
        </div>
        <div class=3D"gmail_quote">
          <blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8=
ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
            <div dir=3D"ltr">
              <div dir=3D"ltr">
                <div dir=3D"ltr">I am very new to Postgres and have always
                  worked in the mssql world. I&#39;m looking for suggestion=
s
                  on DB backups. I currently have a DB used to store
                  Historical information that has images it&#39;s currently
                  around 100gig.=C2=A0<br>
                  <br>
                  I&#39;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=C2=A0cpu&#39;s=C2=A0and ample memory.
                  <div><br>
                    Suggestions on things I can try ?=C2=A0=C2=A0</div>
                  <div>I did a pg_dump using these parms<br>
                    --format=3Dt --blobs lobarch<br>
                    <br>
                    it ran my device out of storage:<br>
                    <br>
                    pg_dump: error: could not write to output file: No
                    space left on device<br>
                    <br>
                    I have 150gig free on my backup drive... can
                    obviously=C2=A0add more<br>
                    <br>
                    looking for the quickest and smallest backup file
                    output...<br>
                    <br>
                    Thanks again for help\suggestions=C2=A0<br>
                  </div>
                </div>
              </div>
            </div>
          </blockquote>
        </div>
        <div><br>
        </div>
        Step 1: redesign your DB to <b>NOT</b>=C2=A0use large objects.=C2=
=A0 It&#39;s
        an old, slow and unmaintained data type.=C2=A0 The data type is wha=
t
        you should use.</div>
    </blockquote>
    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&#39;s been a while I haven&#39;=
t hit
    the docs tho.</div></blockquote><div><br></div><div>Our database is stu=
ffed with images in bytea fields.=C2=A0 The Java application uses JDBC and =
handles them just fine.</div><div>=C2=A0</div></div><span class=3D"gmail_si=
gnature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><d=
iv dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t b=
oil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; crustacean!</div=
></div></div></div></div></div>

--000000000000117ef606249d8d3f--