public inbox for [email protected]help / color / mirror / Atom feed
Re: In-order pg_dump (or in-order COPY TO) 4+ messages / 2 participants [nested] [flat]
* Re: In-order pg_dump (or in-order COPY TO) @ 2025-08-27 14:16 Tom Lane <[email protected]> 2025-08-27 14:31 ` Re: In-order pg_dump (or in-order COPY TO) Ron Johnson <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Tom Lane @ 2025-08-27 14:16 UTC (permalink / raw) To: Dimitrios Apostolou <[email protected]>; +Cc: Adrian Klaver <[email protected]>; [email protected] Dimitrios Apostolou <[email protected]> writes: > Dump is from PostgreSQL 16, it's pg_dump writing to stdout: > pg_dump -v --format=custom --compress=none --no-toast-compression --serializable-deferrable db_name | borg create ... Don't use --format=custom (and not -v either). That causes pg_dump to include the OIDs and pg_dump object IDs of all the tables and other objects, which will all be different in a dump from the new server. The actual data contents of the tables should be the same, but apparently the differences in the entry headers are enough to mislead borgbackup. You might be well advised to manually examine the data you are stuffing into borgbackup. Right now we seem to be operating on hypotheses, not facts, about what that looks like and how it's different between your old and new server. regards, tom lane ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: In-order pg_dump (or in-order COPY TO) 2025-08-27 14:16 Re: In-order pg_dump (or in-order COPY TO) Tom Lane <[email protected]> @ 2025-08-27 14:31 ` Ron Johnson <[email protected]> 2025-08-27 14:42 ` Re: In-order pg_dump (or in-order COPY TO) Tom Lane <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Ron Johnson @ 2025-08-27 14:31 UTC (permalink / raw) To: pgsql-generallists.postgresql.org <[email protected]> On Wed, Aug 27, 2025 at 10:16 AM Tom Lane <[email protected]> wrote: > Dimitrios Apostolou <[email protected]> writes: > > Dump is from PostgreSQL 16, it's pg_dump writing to stdout: > > > pg_dump -v --format=custom --compress=none --no-toast-compression > --serializable-deferrable db_name | borg create ... > > Don't use --format=custom (and not -v either). That causes pg_dump to > include the OIDs and pg_dump object IDs of all the tables and other > objects, That's interesting. Why? (Since isn't it supposed to be Bad to rely on OIDs?) -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: In-order pg_dump (or in-order COPY TO) 2025-08-27 14:16 Re: In-order pg_dump (or in-order COPY TO) Tom Lane <[email protected]> 2025-08-27 14:31 ` Re: In-order pg_dump (or in-order COPY TO) Ron Johnson <[email protected]> @ 2025-08-27 14:42 ` Tom Lane <[email protected]> 2025-08-27 15:12 ` Re: In-order pg_dump (or in-order COPY TO) Ron Johnson <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Tom Lane @ 2025-08-27 14:42 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]> Ron Johnson <[email protected]> writes: > On Wed, Aug 27, 2025 at 10:16 AM Tom Lane <[email protected]> wrote: >> Don't use --format=custom (and not -v either). That causes pg_dump to >> include the OIDs and pg_dump object IDs of all the tables and other >> objects, > That's interesting. Why? (Since isn't it supposed to be Bad to rely on > OIDs?) -v in a text-format dump includes that data for debugging purposes: -- -- TOC entry 1401 (class 1255 OID 16499) -- Name: fipshash(text); Type: FUNCTION; Schema: public; Owner: postgres -- (The "TOC entry" comment line wouldn't be there without -v.) Then custom format has to store the same info so that pg_restore can produce this identical text output on demand. Yeah, this is all pretty historical, but nobody wants to change it at this point. regards, tom lane ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: In-order pg_dump (or in-order COPY TO) 2025-08-27 14:16 Re: In-order pg_dump (or in-order COPY TO) Tom Lane <[email protected]> 2025-08-27 14:31 ` Re: In-order pg_dump (or in-order COPY TO) Ron Johnson <[email protected]> 2025-08-27 14:42 ` Re: In-order pg_dump (or in-order COPY TO) Tom Lane <[email protected]> @ 2025-08-27 15:12 ` Ron Johnson <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Ron Johnson @ 2025-08-27 15:12 UTC (permalink / raw) To: pgsql-generallists.postgresql.org <[email protected]> On Wed, Aug 27, 2025 at 10:42 AM Tom Lane <[email protected]> wrote: > Ron Johnson <[email protected]> writes: > > On Wed, Aug 27, 2025 at 10:16 AM Tom Lane <[email protected]> wrote: > >> Don't use --format=custom (and not -v either). That causes pg_dump to > >> include the OIDs and pg_dump object IDs of all the tables and other > >> objects, > > > That's interesting. Why? (Since isn't it supposed to be Bad to rely on > > OIDs?) > > -v in a text-format dump includes that data for debugging purposes: > > -- > -- TOC entry 1401 (class 1255 OID 16499) > -- Name: fipshash(text); Type: FUNCTION; Schema: public; Owner: postgres > -- > > (The "TOC entry" comment line wouldn't be there without -v.) > Then custom format has to store the same info so that pg_restore > can produce this identical text output on demand. > Ah, so the culprit is "-v". I like using -v, redirecting it to a log file (more info is almost always better), but then I rarely use pg_dump, and never pipe it to de-duplicators. (ExaGrid is supposed to deduplicate, but that's not going to stop me from using pgbackrest, compression and encryption; PCI auditors care about that, not deduplication.) -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-08-27 15:12 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-08-27 14:16 Re: In-order pg_dump (or in-order COPY TO) Tom Lane <[email protected]> 2025-08-27 14:31 ` Ron Johnson <[email protected]> 2025-08-27 14:42 ` Tom Lane <[email protected]> 2025-08-27 15:12 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox