Re: 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(dot)stehule(at)gmail(dot)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(at)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(at)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(at)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