Thread: dat names generated by pg_dump
Hi, I can associate these dat names with their source tables through a bunch of bash and vim manual operations, but I was wondering if there's any automated method (maybe some SQL query of some catalog table; pg_class didn't seem to have the relevant data) of making the association. If relevant, the source database is v8.4, but the backup was done by 9.6 on a separate server. $ ls -1 CDSLBXW/*dat | head CDSLBXW/8412.dat CDSLBXW/8414.dat CDSLBXW/8416.dat CDSLBXW/8418.dat CDSLBXW/8420.dat CDSLBXW/8422.dat CDSLBXW/8423.dat CDSLBXW/8425.dat CDSLBXW/8427.dat CDSLBXW/8428.dat Thanks -- Angular momentum makes the world go 'round.
On 09/02/2018 05:40 PM, Ron wrote: > Hi, > > I can associate these dat names with their source tables through a bunch > of bash and vim manual operations, but I was wondering if there's any > automated method (maybe some SQL query of some catalog table; pg_class > didn't seem to have the relevant data) of making the association. Some background would be helpful: 1) What is producing the *.dat files? 2) What is their structure? 3) What do the numbers refer to? > > If relevant, the source database is v8.4, but the backup was done by 9.6 > on a separate server. > > $ ls -1 CDSLBXW/*dat | head > CDSLBXW/8412.dat > CDSLBXW/8414.dat > CDSLBXW/8416.dat > CDSLBXW/8418.dat > CDSLBXW/8420.dat > CDSLBXW/8422.dat > CDSLBXW/8423.dat > CDSLBXW/8425.dat > CDSLBXW/8427.dat > CDSLBXW/8428.dat > > Thanks > -- Adrian Klaver adrian.klaver@aklaver.com
On 09/02/2018 08:41 PM, Adrian Klaver wrote: > On 09/02/2018 05:40 PM, Ron wrote: >> Hi, >> >> I can associate these dat names with their source tables through a bunch >> of bash and vim manual operations, but I was wondering if there's any >> automated method (maybe some SQL query of some catalog table; pg_class >> didn't seem to have the relevant data) of making the association. > > Some background would be helpful: > > 1) What is producing the *.dat files? pg_dump, as described in the Subject. > > 2) What is their structure? They're pg_dump files. > > 3) What do the numbers refer to? That's what I'm asking the list. > >> >> If relevant, the source database is v8.4, but the backup was done by 9.6 >> on a separate server. >> >> $ ls -1 CDSLBXW/*dat | head >> CDSLBXW/8412.dat >> CDSLBXW/8414.dat >> CDSLBXW/8416.dat >> CDSLBXW/8418.dat >> CDSLBXW/8420.dat >> CDSLBXW/8422.dat >> CDSLBXW/8423.dat >> CDSLBXW/8425.dat >> CDSLBXW/8427.dat >> CDSLBXW/8428.dat >> >> Thanks >> > > -- Angular momentum makes the world go 'round.
On 09/02/2018 07:07 PM, Ron wrote: > On 09/02/2018 08:41 PM, Adrian Klaver wrote: >> On 09/02/2018 05:40 PM, Ron wrote: >>> Hi, >>> >>> I can associate these dat names with their source tables through a >>> bunch of bash and vim manual operations, but I was wondering if >>> there's any automated method (maybe some SQL query of some catalog >>> table; pg_class didn't seem to have the relevant data) of making the >>> association. >> >> Some background would be helpful: >> >> 1) What is producing the *.dat files? > > pg_dump, as described in the Subject. Oops, missed that. > >> >> 2) What is their structure? > > They're pg_dump files. What is the full pg_dump command used? > >> >> 3) What do the numbers refer to? > > That's what I'm asking the list. > >> >>> >>> If relevant, the source database is v8.4, but the backup was done by >>> 9.6 on a separate server. >>> >>> $ ls -1 CDSLBXW/*dat | head >>> CDSLBXW/8412.dat >>> CDSLBXW/8414.dat >>> CDSLBXW/8416.dat >>> CDSLBXW/8418.dat >>> CDSLBXW/8420.dat >>> CDSLBXW/8422.dat >>> CDSLBXW/8423.dat >>> CDSLBXW/8425.dat >>> CDSLBXW/8427.dat >>> CDSLBXW/8428.dat >>> >>> Thanks >>> >> >> > -- Adrian Klaver adrian.klaver@aklaver.com
Ron <ronljohnsonjr@gmail.com> writes:
> I can associate these dat names with their source tables through a bunch of
> bash and vim manual operations, but I was wondering if there's any automated
> method (maybe some SQL query of some catalog table; pg_class didn't seem to
> have the relevant data) of making the association.
Those numbers are the "dump object IDs" generated by pg_dump. They don't
have any significance on the server side, and typically would vary from
one pg_dump run to another. You have to look at the dump TOC (table of
contents) to figure out what corresponds to what. For example,
$ pg_dump -Fd -f dumpd regression
$ ls -1 dumpd
6143.dat.gz
6144.dat.gz
6145.dat.gz
...
blob_3001.dat.gz
blobs.toc
toc.dat
$ pg_restore -l dumpd
;
; Archive created at 2018-09-02 22:14:48 EDT
...
6573; 2613 119655 BLOB - 119655 postgres
6574; 2613 3001 BLOB - 3001 postgres
6603; 0 0 COMMENT - LARGE OBJECT 3001 postgres
6247; 0 100933 TABLE DATA public a postgres
6212; 0 89417 TABLE DATA public a_star postgres
6180; 0 88516 TABLE DATA public abstime_tbl postgres
6218; 0 89444 TABLE DATA public aggtest postgres
6446; 0 121383 TABLE DATA public alter_table_under_transition_tables postgres
...
The numbers before the semicolons are the dump IDs. In particular
$ pg_restore -l dumpd | grep 6143
6143; 0 88018 TABLE DATA public int4_tbl postgres
so 6143.dat.gz contains the data for table public.int4_tbl. There
will only be separate files in the dump directory for TABLE DATA and
BLOB dump objects ... other stuff is just embedded in the toc.dat file.
regards, tom lane
On 09/02/2018 09:26 PM, Tom Lane wrote: > Ron <ronljohnsonjr@gmail.com> writes: >> I can associate these dat names with their source tables through a bunch of >> bash and vim manual operations, but I was wondering if there's any automated >> method (maybe some SQL query of some catalog table; pg_class didn't seem to >> have the relevant data) of making the association. > Those numbers are the "dump object IDs" generated by pg_dump. They don't > have any significance on the server side, and typically would vary from > one pg_dump run to another. You have to look at the dump TOC (table of > contents) to figure out what corresponds to what. For example, > > $ pg_dump -Fd -f dumpd regression > $ ls -1 dumpd > 6143.dat.gz > 6144.dat.gz > 6145.dat.gz > ... > blob_3001.dat.gz > blobs.toc > toc.dat > $ pg_restore -l dumpd > ; > ; Archive created at 2018-09-02 22:14:48 EDT > ... > 6573; 2613 119655 BLOB - 119655 postgres Thanks. That's exactly what I needed. -- Angular momentum makes the world go 'round.