Horton

Thread

Commits

  1. pg_restore: fix incompatibility with old directory-format dumps.

  2. Rearrange pg_dump's handling of large objects for better efficiency.

  1. pg_restore - cannot to restore blobs in dictionary format from older pg dumps

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-06-08T12:39:35Z

    Hi,

one customer reported an issue related probably to pg_restore and
dictionary format.

Inside PostgreSQL 11 I created one large object

I used pg_dump (version 11) and did dump a) dictionary format, b) tar format

I will try to restore these files in PostgreSQL 18 with pg_restore (version
18).

pavel@nemesis:~$ /usr/local/pgsql/master/bin/pg_restore -Ft testx.tar -l
;
; Archive created at 2025-06-08 14:14:08 CEST
;     dbname: postgres
;     TOC Entries: 7
;     Compression: none
;     Dump Version: 1.13-0
;     Format: TAR
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 11.22
;     Dumped by pg_dump version: 11.22
;
;
; Selected TOC Entries:
;
4034; 2613 16386 BLOB - 16386 pavel
4035; 0 0 BLOBS - BLOBS

pavel@nemesis:~$ /usr/local/pgsql/master/bin/pg_restore -Fd testx -l
;
; Archive created at 2025-06-08 14:14:16 CEST
;     dbname: postgres
;     TOC Entries: 7
;     Compression: gzip
;     Dump Version: 1.13-0
;     Format: DIRECTORY
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 11.22
;     Dumped by pg_dump version: 11.22
;
;
; Selected TOC Entries:
;
4034; 2613 16386 BLOB - 16386 pavel
4035; 0 0 BLOBS - BLOBS

the --list options without problems

When I try to restore blobs

using tar format (b) it is working without problems

but import from dictionary format fails with an error

pavel@nemesis:~$ LANG=C /usr/local/pgsql/master/bin/pg_restore -Fd testx -d
postgres
pg_restore: error: could not open large object TOC file "testx/4035.dat"
for input: No such file or directory

When I use a dump in dictionary format from pg 18, there is difference

instead of the blobs_4035.toc I have a file blobs.toc with the same content.

In the tar format, the differences between format pg18 and pg11 are the
same as in dictionary format, but pg_restore is able to read it correctly.

Probably it is not a critical bug, but it is very confusing for users, and
when it is working in tar format, then probably it should work in
dictionary format too.

Regards

Pavel

  2. Re: pg_restore - cannot to restore blobs in dictionary format from older pg dumps

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-06-08T18:54:34Z

    Hi

ne 8. 6. 2025 v 14:39 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

> Hi,
>
> one customer reported an issue related probably to pg_restore and
> dictionary format.
>
> Inside PostgreSQL 11 I created one large object
>
> I used pg_dump (version 11) and did dump a) dictionary format, b) tar
> format
>
> I will try to restore these files in PostgreSQL 18 with pg_restore
> (version 18).
>
> pavel@nemesis:~$ /usr/local/pgsql/master/bin/pg_restore -Ft testx.tar -l
> ;
> ; Archive created at 2025-06-08 14:14:08 CEST
> ;     dbname: postgres
> ;     TOC Entries: 7
> ;     Compression: none
> ;     Dump Version: 1.13-0
> ;     Format: TAR
> ;     Integer: 4 bytes
> ;     Offset: 8 bytes
> ;     Dumped from database version: 11.22
> ;     Dumped by pg_dump version: 11.22
> ;
> ;
> ; Selected TOC Entries:
> ;
> 4034; 2613 16386 BLOB - 16386 pavel
> 4035; 0 0 BLOBS - BLOBS
>
> pavel@nemesis:~$ /usr/local/pgsql/master/bin/pg_restore -Fd testx -l
> ;
> ; Archive created at 2025-06-08 14:14:16 CEST
> ;     dbname: postgres
> ;     TOC Entries: 7
> ;     Compression: gzip
> ;     Dump Version: 1.13-0
> ;     Format: DIRECTORY
> ;     Integer: 4 bytes
> ;     Offset: 8 bytes
> ;     Dumped from database version: 11.22
> ;     Dumped by pg_dump version: 11.22
> ;
> ;
> ; Selected TOC Entries:
> ;
> 4034; 2613 16386 BLOB - 16386 pavel
> 4035; 0 0 BLOBS - BLOBS
>
> the --list options without problems
>
> When I try to restore blobs
>
> using tar format (b) it is working without problems
>
> but import from dictionary format fails with an error
>
> pavel@nemesis:~$ LANG=C /usr/local/pgsql/master/bin/pg_restore -Fd testx
> -d postgres
> pg_restore: error: could not open large object TOC file "testx/4035.dat"
> for input: No such file or directory
>
> When I use a dump in dictionary format from pg 18, there is difference
>
> instead of the blobs_4035.toc I have a file blobs.toc with the same
> content.
>
> In the tar format, the differences between format pg18 and pg11 are the
> same as in dictionary format, but pg_restore is able to read it correctly.
>
> Probably it is not a critical bug, but it is very confusing for users, and
> when it is working in tar format, then probably it should work in
> dictionary format too.
>
> Regards
>
> Pavel
>
>
The problem is related to the commit a45c78e

I am not sure if this is correct fix, but it fixed this issue

diff --git a/src/bin/pg_dump/pg_backup_directory.c
b/src/bin/pg_dump/pg_backup_directory.c
index 21b00792a8a..16da4decbd0 100644
--- a/src/bin/pg_dump/pg_backup_directory.c
+++ b/src/bin/pg_dump/pg_backup_directory.c
@@ -415,7 +415,15 @@ _LoadLOs(ArchiveHandle *AH, TocEntry *te)
     * now there can be multiple.  We don't need to worry what version we
are
     * reading though, because tctx->filename should be correct either way.
     */
-   setFilePath(AH, tocfname, tctx->filename);
+
+   /*
+    * probably there is a bug in older releases (tested in Postgres 11).
+    * The filename is not correct - instead "blobs.toc" it is NNNN.dat.
+    */
+   if (AH->version < K_VERS_1_16)
+       setFilePath(AH, tocfname, "blobs.toc");
+   else
+       setFilePath(AH, tocfname, tctx->filename);

    CFH = ctx->LOsTocFH = InitDiscoverCompressFileHandle(tocfname,
PG_BINARY_R);

Regards

Pavel

  3. Re: pg_restore - cannot to restore blobs in dictionary format from older pg dumps

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-06-08T19:00:48Z

    Hi



> The problem is related to the commit a45c78e
>
> I am not sure if this is correct fix, but it fixed this issue
>
> diff --git a/src/bin/pg_dump/pg_backup_directory.c
> b/src/bin/pg_dump/pg_backup_directory.c
> index 21b00792a8a..16da4decbd0 100644
> --- a/src/bin/pg_dump/pg_backup_directory.c
> +++ b/src/bin/pg_dump/pg_backup_directory.c
> @@ -415,7 +415,15 @@ _LoadLOs(ArchiveHandle *AH, TocEntry *te)
>      * now there can be multiple.  We don't need to worry what version we
> are
>      * reading though, because tctx->filename should be correct either way.
>      */
> -   setFilePath(AH, tocfname, tctx->filename);
> +
> +   /*
> +    * probably there is a bug in older releases (tested in Postgres 11).
> +    * The filename is not correct - instead "blobs.toc" it is NNNN.dat.
>
+    */
> +   if (AH->version < K_VERS_1_16)
> +       setFilePath(AH, tocfname, "blobs.toc");
> +   else
> +       setFilePath(AH, tocfname, tctx->filename);
>
>     CFH = ctx->LOsTocFH = InitDiscoverCompressFileHandle(tocfname,
> PG_BINARY_R);
>
> Regards
>

It is a problem of PostgreSQL 11 - the pg_dump from PostgreSQL 12 sets
filename correctly,
and pg_restore doesn't need patching (in this case).

Regards

Pavel



>
> Pavel
>

  4. Re: pg_restore - cannot to restore blobs in dictionary format from older pg dumps

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-08T20:38:40Z

    Pavel Stehule <pavel.stehule@gmail.com> writes:
> It is a problem of PostgreSQL 11 - the pg_dump from PostgreSQL 12 sets
> filename correctly,
> and pg_restore doesn't need patching (in this case).

Yeah.  It looks like the actual filename was always "blobs.toc" before
v17, but prior to 548e50976 pg_backup_directory.c's _ArchiveEntry put
the wrong filename into tctx->filename.  That had no visible effect,
because _StartBlobs and _LoadBlobs ignored that field and hard-wired
the filename to use.  Then in a45c78e32 I made those functions rely on
tctx->filename instead of hard-wiring "blobs.toc", so they fail on
archives made by versions before 548e50976.

I think your patch is about the right thing to do, although I'm
inclined to check for K_VERS_1_14 which is closer to when the
fix was made.

			regards, tom lane

  5. Re: pg_restore - cannot to restore blobs in dictionary format from older pg dumps

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-08T21:09:20Z

    I wrote:
> I think your patch is about the right thing to do, although I'm
> inclined to check for K_VERS_1_14 which is closer to when the
> fix was made.

Pushed.  After thinking about how to rewrite the comment, I went
with the version test as you had it.  K_VERS_1_14 isn't really
better, since it's still not an exact match to when the behavior
changed.

			regards, tom lane

  6. Re: pg_restore - cannot to restore blobs in dictionary format from older pg dumps

    Pavel Stehule <pavel.stehule@gmail.com> — 2025-06-09T04:06:25Z

    ne 8. 6. 2025 v 23:09 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

> I wrote:
> > I think your patch is about the right thing to do, although I'm
> > inclined to check for K_VERS_1_14 which is closer to when the
> > fix was made.
>
> Pushed.  After thinking about how to rewrite the comment, I went
> with the version test as you had it.  K_VERS_1_14 isn't really
> better, since it's still not an exact match to when the behavior
> changed.
>

Thank you

Pavel


>
>                         regards, tom lane
>