Subject: pg_dump

From: Wasim Devale <[email-masked]>
Date: Tue, 13 Aug 2024 00:39:50 +0530

Hi All

Please let me know if the below option persists while using pg_dump command:

-t table_name --exclude-column column_name

I have a table with a column having bytea data type (BLOBS). I just want to
exclude this in the dump file as it is of 99GB but I want the other data in
the same table which is of only 22MB.

Please let me know any work around this.

I used this option in pg_dump but that didn't work:

--exclude-table-data = table_name

PG version 12.19

Thanks,
Wasim

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 12 Aug 2024 12:16:17 -0700

> -t table_name --exclude-column column_name

No, there is no option to dump a partial table, rows or columns.

>
> I just want to exclude this in the dump file as it is of 99GB but I want
> the other data in the same table which is of only 22MB.
>

Write your own copy command to export that table’s contents.  See the
—snapshot option.

David J.

---

From: Holger Jakobs <[email-masked]>
Date: Mon, 12 Aug 2024 21:18:41 +0200

Am 12.08.24 um 21:09 schrieb Wasim Devale:
> I have a table with a column having bytea data type (BLOBS). I just 
> want to exclude this in the dump file as it is of 99GB but I want the 
> other data in the same table which is of only 22MB.
>
No, pg_dump can only dump complete tables.

An alternative would be exporting the result of a select command via 
COPY or \copy to a file, which can easily be read again with COPY or \copy

COPY is an SQL command and has to be used by a superuser because it 
writes to (or reads from) a file on the server.
\copy is a psql command and thus can only be executed in psql (not any 
other client) and by any user, because it writes to (or reads from) a 
file on the client computer.

Kind Regards,

Holger

-- 

Holger Jakobs, Bergisch Gladbach

---

From: Wasim Devale <[email-masked]>
Date: Tue, 13 Aug 2024 11:52:17 +0530

I did one thing, we can exclude the toast table in the pg_dump command that
has the BLOBS data for a particular table.

--exclude-table-data=pg_toast.pg_toast_10176226

Thanks,
Wasim

---

From: Wasim Devale <[email-masked]>
Date: Tue, 13 Aug 2024 11:52:49 +0530

Please let me know your inputs.

On Tue, 13 Aug, 2024, 11:52 am Wasim Devale, <[email-masked]> wrote:

> I did one thing, we can exclude the toast table in the pg_dump command
> that has the BLOBS data for a particular table.
>

---

From: Tom Lane <[email-masked]>
Date: Tue, 13 Aug 2024 02:33:20 -0400

> --exclude-table-data=pg_toast.pg_toast_10176226

Utterly pointless.  pg_dump does not dump toast tables as such;
it's only concerned with their parent "regular" tables.  It will
fetch data from the regular tables, and it's not concerned with
the server-side implementation detail that some of that data
might be coming out of a toast table.

			regards, tom lane

---

From: Wasim Devale <[email-masked]>
Date: Tue, 13 Aug 2024 12:12:41 +0530

Then what might be the solution for it if I am only concerned about using
pg_dump

On Tue, 13 Aug, 2024, 12:03 pm Tom Lane, <[email-masked]> wrote:

> that

---

From: Ron Johnson <[email-masked]>
Date: Tue, 13 Aug 2024 08:04:37 -0400

"Free your mind, and *pg_dump* will follow."  Maybe *En Vogue*, but
probably not.

-- 
Death to America, and butter sauce.
Iraq lobster!

---

From: Ron Johnson <[email-masked]>
Date: Tue, 13 Aug 2024 08:05:33 -0400

Oops... should have been "free your mind, and COPY will follow."

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 13 Aug 2024 05:35:02 -0700

> Then what might be the solution for it if I am only concerned about using

Create a new table to house the bytea data, copy the bytea data to it, drop
the bytea column from the existing table.

David J.