MIME-Version: 1.0
References: <CAEZv3cqct1WFb9KYmYa5kjdxfVK8JEFdzbJ5bRL6b4r+XR2dug@mail.gmail.com>
In-Reply-To: <CAEZv3cqct1WFb9KYmYa5kjdxfVK8JEFdzbJ5bRL6b4r+XR2dug@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 16 Oct 2024 15:55:30 -0400
Message-ID: <CANzqJaBfaOvbjJYYCPPC-JKFEw=pZTGkfwL3+-t2fjP7B_9U+g@mail.gmail.com>
Subject: Re: Backup
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006f55ba06249d74c6"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBfaOvbjJYYCPPC-JKFEw%3DpZTGkfwL3%2B-t2fjP7B_9U%2Bg%40mail.gmail.com>
Precedence: bulk

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

On Wed, Oct 16, 2024 at 3:37=E2=80=AFPM Andy Hartman <hartman60home@gmail.c=
om>
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 stor=
e
> 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.

Step 2: show us the "before" df output, the whole pg_dump command, and the
"after" df output when it fails. "du -c --max-depth=3D0 $PGDATA/base" also
very useful.

And tell us what version you're using.

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

--0000000000006f55ba06249d74c6
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 3:37=E2=80=AFPM A=
ndy Hartman &lt;<a href=3D"mailto:hartman60home@gmail.com">hartman60home@gm=
ail.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div di=
r=3D"ltr">I am very new to Postgres and have always worked in the mssql wor=
ld. I&#39;m looking for suggestions on DB backups. I currently have a DB us=
ed to store Historical information that has images it&#39;s currently aroun=
d 100gig.=C2=A0<br><br>I&#39;m looking to take a monthly backup as I archiv=
e 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>S=
uggestions on things I can try ?=C2=A0=C2=A0</div><div>I did a pg_dump usin=
g these parms<br>--format=3Dt --blobs lobarch<br><br>it ran my device out o=
f 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 obviousl=
y=C2=A0add more<br><br>looking for the quickest and smallest backup file ou=
tput...<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 typ=
e.=C2=A0 The data type is what you should use.<div><br></div><div>Step 2: s=
how us the &quot;before&quot; df output, the whole pg_dump command, and the=
 &quot;after&quot; df output when it fails. &quot;du -c --max-depth=3D0 $PG=
DATA/base&quot; also very useful.</div><div><br></div><div>And tell us what=
 version you&#39;re using.<br clear=3D"all"><div><br></div><span class=3D"g=
mail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signat=
ure"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&=
#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; crustacea=
n!</div></div></div></div></div></div></div>

--0000000000006f55ba06249d74c6--