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

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

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