Postgres Pro
Downloads
Home   >   mailing lists

Re: pg_restore - cannot to restore blobs in dictionary format from older pg dumps - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: pg_restore - cannot to restore blobs in dictionary format from older pg dumps
Date
Msg-id CAFj8pRAWx7Dfu6B=cSK_6VPDAPrKL9uktBjXEgbruyxM_JPQrw@mail.gmail.com
Whole thread Raw
In response to pg_restore - cannot to restore blobs in dictionary format from older pg dumps  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses Re: pg_restore - cannot to restore blobs in dictionary format from older pg dumps
List pgsql-hackers
Tree view
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

pgsql-hackers by date:

Previous
From: Robert Treat
Date:
Subject: Re: [PATCH] Re: Proposal to Enable/Disable Index using ALTER INDEX
Next
From: Pavel Stehule
Date:
Subject: Re: pg_restore - cannot to restore blobs in dictionary format from older pg dumps