Thread: pg_dump
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
On Monday, August 12, 2024, Wasim Devale <wasimd60@gmail.com> wrote:
Please let me know if the below option persists while using pg_dump command:-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.Please let me know any work around this.
Write your own copy command to export that table’s contents. See the —snapshot option.
David J.
Am 12.08.24 um 21:09 schrieb Wasim Devale: > 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 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
Attachment
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
On Tue, 13 Aug, 2024, 12:48 am Holger Jakobs, <holger@jakobs.com> wrote:
Am 12.08.24 um 21:09 schrieb Wasim Devale:
> 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
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
Please let me know your inputs.
On Tue, 13 Aug, 2024, 11:52 am Wasim Devale, <wasimd60@gmail.com> 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.
--exclude-table-data=pg_toast.pg_toast_10176226
Thanks,
WasimOn Tue, 13 Aug, 2024, 12:48 am Holger Jakobs, <holger@jakobs.com> wrote:Am 12.08.24 um 21:09 schrieb Wasim Devale:
> 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
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
Wasim Devale <wasimd60@gmail.com> writes: > 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 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
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, <tgl@sss.pgh.pa.us> wrote:
Wasim Devale <wasimd60@gmail.com> writes:
> 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
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
"Free your mind, and pg_dump will follow." Maybe En Vogue, but probably not.
On Tue, Aug 13, 2024 at 2:43 AM Wasim Devale <wasimd60@gmail.com> wrote:
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, <tgl@sss.pgh.pa.us> wrote:Wasim Devale <wasimd60@gmail.com> writes:
> 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
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
Death to America, and butter sauce.
Iraq lobster!
Oops... should have been "free your mind, and COPY will follow."
On Tue, Aug 13, 2024 at 2:43 AM Wasim Devale <wasimd60@gmail.com> wrote:
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, <tgl@sss.pgh.pa.us> wrote:Wasim Devale <wasimd60@gmail.com> writes:
> 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
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
Death to America, and butter sauce.
Iraq lobster!
On Monday, August 12, 2024, Wasim Devale <wasimd60@gmail.com> wrote:
Then what might be the solution for it if I am only concerned about using pg_dump
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.