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 <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1t1ABY-00F03A-F3
	for pgsql-general@arkaria.postgresql.org; Wed, 16 Oct 2024 19:59:56 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1t1ABW-009OkY-Hb
	for pgsql-general@arkaria.postgresql.org; Wed, 16 Oct 2024 19:59:54 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <a.mantzios@cloud.gatewaynet.com>)
	id 1t1ABW-009OkP-6d
	for pgsql-general@lists.postgresql.org; Wed, 16 Oct 2024 19:59:54 +0000
Received: from cloud.gatewaynet.com ([185.90.37.94])
	by magus.postgresql.org with esmtps  (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <a.mantzios@cloud.gatewaynet.com>)
	id 1t1ABU-001PdL-74
	for pgsql-general@lists.postgresql.org; Wed, 16 Oct 2024 19:59:53 +0000
Content-Type: multipart/alternative;
 boundary="------------cGeMLcyDxoNku7fsHB0LlS1e"
Message-ID: <f1a0c76b-c645-4530-b768-fea480eab56e@cloud.gatewaynet.com>
Date: Wed, 16 Oct 2024 22:59:47 +0300
MIME-Version: 1.0
Subject: Re: Backup
To: pgsql-general@lists.postgresql.org
References: <CAEZv3cqct1WFb9KYmYa5kjdxfVK8JEFdzbJ5bRL6b4r+XR2dug@mail.gmail.com>
 <CANzqJaBfaOvbjJYYCPPC-JKFEw=pZTGkfwL3+-t2fjP7B_9U+g@mail.gmail.com>
Content-Language: en-US
From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
In-Reply-To: <CANzqJaBfaOvbjJYYCPPC-JKFEw=pZTGkfwL3+-t2fjP7B_9U+g@mail.gmail.com>
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/f1a0c76b-c645-4530-b768-fea480eab56e%40cloud.gatewaynet.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------cGeMLcyDxoNku7fsHB0LlS1e
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


Στις 16/10/24 22:55, ο/η Ron Johnson έγραψε:
> On Wed, Oct 16, 2024 at 3:37 PM 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 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.
>
> 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=0 
> $PGDATA/base" also very useful.
>
> And tell us what version you're using.
>
> -- 
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> crustacean!
--------------cGeMLcyDxoNku7fsHB0LlS1e
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><br>
    </p>
    <div class="moz-cite-prefix">Στις 16/10/24 22:55, ο/η Ron Johnson
      έγραψε:<br>
    </div>
    <blockquote type="cite"
cite="mid:CANzqJaBfaOvbjJYYCPPC-JKFEw=pZTGkfwL3+-t2fjP7B_9U+g@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr">On Wed, Oct 16, 2024 at 3:37 PM Andy Hartman &lt;<a
            href="mailto:hartman60home@gmail.com" moz-do-not-send="true"
            class="moz-txt-link-freetext">hartman60home@gmail.com</a>&gt;
          wrote:<br>
        </div>
        <div class="gmail_quote">
          <blockquote class="gmail_quote"
style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
            <div dir="ltr">
              <div dir="ltr">
                <div dir="ltr">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. <br>
                  <br>
                  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.
                  <div><br>
                    Suggestions on things I can try ?  </div>
                  <div>I did a pg_dump using these parms<br>
                    --format=t --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 add more<br>
                    <br>
                    looking for the quickest and smallest backup file
                    output...<br>
                    <br>
                    Thanks again for help\suggestions <br>
                  </div>
                </div>
              </div>
            </div>
          </blockquote>
        </div>
        <div><br>
        </div>
        Step 1: redesign your DB to <b>NOT</b> use large objects.  It's
        an old, slow and unmaintained data type.  The data type is what
        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's been a while I haven't hit
    the docs tho.<br>
    <blockquote type="cite"
cite="mid:CANzqJaBfaOvbjJYYCPPC-JKFEw=pZTGkfwL3+-t2fjP7B_9U+g@mail.gmail.com">
      <div dir="ltr">
        <div><br>
        </div>
        <div>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=0 $PGDATA/base" also very useful.</div>
        <div><br>
        </div>
        <div>And tell us what version you're using.<br clear="all">
          <div><br>
          </div>
          <span class="gmail_signature_prefix">-- </span><br>
          <div dir="ltr" class="gmail_signature">
            <div dir="ltr">Death to &lt;Redacted&gt;, and butter sauce.
              <div>Don't boil me, I'm still alive.<br>
                <div>
                  <div>&lt;Redacted&gt; crustacean!</div>
                </div>
              </div>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
  </body>
</html>

--------------cGeMLcyDxoNku7fsHB0LlS1e--