dat names generated by pg_dump

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.