Thread: Re: To take backup of Postgresql Database without large objects
Hello,
Using PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit in Windows 10.
Trying to take backup of a database, using pg_dump, where one table contains bytea datatype, which I don't want to include in the backup.
My command was:
"E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username> --no-blobs -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
the backup includes the bytea field also.
I tried with
"E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username> -B -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
What wrong I'm doing? Couldn't figure it out. Any help is appreciated.
Happiness Always
BKR Sivaprakash
BKR Sivaprakash
On Fri, Apr 11, 2025 at 8:56 AM sivapostgres@yahoo.com <sivapostgres@yahoo.com> wrote:
Hello,Using PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit in Windows 10.
That's 11 patch releases behind current.
Trying to take backup of a database, using pg_dump, where one table contains bytea datatype, which I don't want to include in the backup.My command was:"E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username> --no-blobs -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6the backup includes the bytea field also.I tried withalso, which also included the bytea field."E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username> -B -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6What wrong I'm doing? Couldn't figure it out. Any help is appreciated.
The manual explicitly states what you're doing wrong.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On 4/11/25 05:55, sivapostgres@yahoo.com wrote: > Hello, > > Using PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit in > Windows 10. > > Trying to take backup of a database, using pg_dump, where one table > contains bytea datatype, which I don't want to include in the backup. > > My command was: > "E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username> > --no-blobs -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6 > > the backup includes the bytea field also. > > I tried with > "E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username> -B > -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6 > > also, which also included the bytea field. 1) Short version Short version bytea fields != large objects. 2) Long version From here: https://www.postgresql.org/docs/current/app-pgdump.html -B --no-large-objects --no-blobs (deprecated) Exclude large objects in the dump. When both -b and -B are given, the behavior is to output large objects, when data is being dumped, see the -b documentation. Where large objects are defined here: https://www.postgresql.org/docs/current/largeobjects.html > > What wrong I'm doing? Couldn't figure it out. Any help is appreciated. > > Happiness Always > BKR Sivaprakash > -- Adrian Klaver adrian.klaver@aklaver.com
I tried all the combinations, as mentioned in the document
"E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username> --no-blobs -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
"E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username> -B -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
"E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username> -B --no-blobs -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
--no-large-objects is not working.
I tried with all the other combinations. NO LUCK.
bytea field also included in the backup.
I tried taking backup using pg_dump of version 17 also, with no luck. [ Database version is 15 and the pg_dump version is 17 ].
Confirming this after restoring the backup file in a new database. The new database contains contents from bytea field also.
Either my command should be wrong or I'm missing something.
Happiness Always
BKR Sivaprakash
BKR Sivaprakash
On Friday 11 April, 2025 at 08:31:31 pm IST, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/11/25 05:55, sivapostgres@yahoo.com wrote:
> Hello,
>
> Using PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit in
> Windows 10.
>
> Trying to take backup of a database, using pg_dump, where one table
> contains bytea datatype, which I don't want to include in the backup.
>
> My command was:
> "E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username>
> --no-blobs -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
>
> the backup includes the bytea field also.
>
> I tried with
> "E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username> -B
> -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
>
> also, which also included the bytea field.
1) Short version
Short version bytea fields != large objects.
2) Long version
From here:
https://www.postgresql.org/docs/current/app-pgdump.html
-B
--no-large-objects
--no-blobs (deprecated)
Exclude large objects in the dump.
When both -b and -B are given, the behavior is to output large
objects, when data is being dumped, see the -b documentation.
Where large objects are defined here:
https://www.postgresql.org/docs/current/largeobjects.html
>
> What wrong I'm doing? Couldn't figure it out. Any help is appreciated.
>
> Happiness Always
> BKR Sivaprakash
>
--
Adrian Klaver
adrian.klaver@aklaver.com
> Hello,
>
> Using PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit in
> Windows 10.
>
> Trying to take backup of a database, using pg_dump, where one table
> contains bytea datatype, which I don't want to include in the backup.
>
> My command was:
> "E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username>
> --no-blobs -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
>
> the backup includes the bytea field also.
>
> I tried with
> "E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username> -B
> -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6
>
> also, which also included the bytea field.
1) Short version
Short version bytea fields != large objects.
2) Long version
From here:
https://www.postgresql.org/docs/current/app-pgdump.html
-B
--no-large-objects
--no-blobs (deprecated)
Exclude large objects in the dump.
When both -b and -B are given, the behavior is to output large
objects, when data is being dumped, see the -b documentation.
Where large objects are defined here:
https://www.postgresql.org/docs/current/largeobjects.html
>
> What wrong I'm doing? Couldn't figure it out. Any help is appreciated.
>
> Happiness Always
> BKR Sivaprakash
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Friday, April 11, 2025, sivapostgres@yahoo.com <sivapostgres@yahoo.com> wrote:
bytea field also included in the backup.
Bytea typed columns are completely separate things than large objects. You cannot exclude individual columns using pg_dump.
David J.
Thanks.
Then I've misunderstood large objects. Is there document to explain large objects?
We store images in bytea column.
Happiness Always
BKR Sivaprakash
BKR Sivaprakash
On Saturday 12 April, 2025 at 10:44:21 am IST, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Friday, April 11, 2025, sivapostgres@yahoo.com <sivapostgres@yahoo.com> wrote:
bytea field also included in the backup.
Bytea typed columns are completely separate things than large objects. You cannot exclude individual columns using pg_dump.
David J.
> On Apr 11, 2025, at 22:34, sivapostgres@yahoo.com wrote:
> Then I've misunderstood large objects. Is there document to explain large objects?
Large objects are a relatively old and now little-used feature of PostgreSQL that predates the bytea type:
https://www.postgresql.org/docs/current/largeobjects.html
As was mentioned, you cannot exclude individual columns with pg_dump; you have to exclude the entire table.
On 4/11/25 22:06, sivapostgres@yahoo.com wrote: > Either my command should be wrong or I'm missing something. This was explained in my post as quoted below. > > Happiness Always > BKR Sivaprakash > > On Friday 11 April, 2025 at 08:31:31 pm IST, Adrian Klaver > <adrian.klaver@aklaver.com> wrote: > > > On 4/11/25 05:55, sivapostgres@yahoo.com <mailto:sivapostgres@yahoo.com> > wrote: > > Hello, > > > > Using PostgreSQL 15.1, compiled by Visual C++ build 1914, 64-bit in > > Windows 10. > > > > Trying to take backup of a database, using pg_dump, where one table > > contains bytea datatype, which I don't want to include in the backup. > > > > My command was: > > "E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username> > > --no-blobs -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6 > > > > the backup includes the bytea field also. > > > > I tried with > > "E:\DBBackup\bin\pg_dump.exe" -h 192.168.1.1 -p 5432 -U <username> -B > > -F c -v -f "E:\DBBackup\demo6_110420251637.bak" demo6 > > > > also, which also included the bytea field. > > 1) Short version > > Short version bytea fields != large objects. > > 2) Long version > > From here: > > https://www.postgresql.org/docs/current/app-pgdump.html > <https://www.postgresql.org/docs/current/app-pgdump.html> > > -B > --no-large-objects > --no-blobs (deprecated) > > Exclude large objects in the dump. > > When both -b and -B are given, the behavior is to output large > objects, when data is being dumped, see the -b documentation. > > Where large objects are defined here: > > https://www.postgresql.org/docs/current/largeobjects.html > <https://www.postgresql.org/docs/current/largeobjects.html> > > > > > > > > > What wrong I'm doing? Couldn't figure it out. Any help is appreciated. > > > > Happiness Always > > BKR Sivaprakash > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Friday, April 11, 2025, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/11/25 22:06, sivapostgres@yahoo.com wrote:Either my command should be wrong or I'm missing something.
This was explained in my post as quoted below.
Yeah, the short version. Then you added a long version that just confused the issue. Why point out exclude blobs if you know they are using bytea?
David J.
Thanks for the clarification.
bytea != large object
Happiness Always
BKR Sivaprakash
BKR Sivaprakash
On Saturday 12 April, 2025 at 11:36:11 am IST, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Friday, April 11, 2025, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/11/25 22:06, sivapostgres@yahoo.com wrote:Either my command should be wrong or I'm missing something.
This was explained in my post as quoted below.
Yeah, the short version. Then you added a long version that just confused the issue. Why point out exclude blobs if you know they are using bytea?
David J.
On 4/11/25 23:05, David G. Johnston wrote: > On Friday, April 11, 2025, Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 4/11/25 22:06, sivapostgres@yahoo.com > <mailto:sivapostgres@yahoo.com> wrote: > > Either my command should be wrong or I'm missing something. > > > This was explained in my post as quoted below. > > > Yeah, the short version. Then you added a long version that just > confused the issue. Why point out exclude blobs if you know they are > using bytea? To show what --no-blobs is actually doing versus what the OP thought it was doing, along with link to large object docs to show they are not bytea. > > David J. > -- Adrian Klaver adrian.klaver@aklaver.com