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

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

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

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

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