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 ) id 1t1AS9-00F14b-0T for pgsql-general@arkaria.postgresql.org; Wed, 16 Oct 2024 20:17:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t1AS6-009gK8-L0 for pgsql-general@arkaria.postgresql.org; Wed, 16 Oct 2024 20:17:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1AS6-009gJz-51 for pgsql-general@lists.postgresql.org; Wed, 16 Oct 2024 20:17:02 +0000 Received: from cloud.gatewaynet.com ([185.90.37.94]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t1AS3-001EuA-32 for pgsql-general@lists.postgresql.org; Wed, 16 Oct 2024 20:17:01 +0000 Content-Type: multipart/alternative; boundary="------------JEGIeELuaPa2DJZ0fWFY73cm" Message-ID: <7b130a5e-af5c-479b-a9fe-4fa0c25d2817@cloud.gatewaynet.com> Date: Wed, 16 Oct 2024 23:16:53 +0300 MIME-Version: 1.0 Subject: Re: Backup To: pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Achilleas Mantzios In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------JEGIeELuaPa2DJZ0fWFY73cm Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Στις 16/10/24 23:02, ο/η Ron Johnson έγραψε: > On Wed, Oct 16, 2024 at 4:00 PM Achilleas Mantzios > wrote: > > Στις 16/10/24 22:55, ο/η Ron Johnson έγραψε: > >> On Wed, Oct 16, 2024 at 3:37 PM Andy Hartman >> 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 , and butter sauce. > Don't boil me, I'm still alive. > crustacean! --------------JEGIeELuaPa2DJZ0fWFY73cm Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


Στις 16/10/24 23:02, ο/η Ron Johnson έγραψε:
On Wed, Oct 16, 2024 at 4:00 PM Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com> wrote:

Στις 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.

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!
--------------JEGIeELuaPa2DJZ0fWFY73cm--