3

In a tar dump

$ tar -tf dvdrental.tar 
toc.dat
2163.dat
...
2189.dat
restore.sql

After extraction

$ file *
2163.dat:    ASCII text
...
2189.dat:    ASCII text
restore.sql: ASCII text, with very long lines
toc.dat:     PostgreSQL custom database dump - v1.12-0
  1. What is the purpose of restore.sql?

  2. toc.dat is binary, but I can open it and it looks like a sql script too. How different are between the purposes of restore.sql and toc.dat?

    The following quote from the document does't answer my question:

    with one file for each table and blob being dumped, plus a so-called Table of Contents file describing the dumped objects in a machine-readable format that pg_restore can read.

  3. Since a tar dump contains restore.sql besides the .dat files, what is the difference between the sql script files restore.sql and toc.dat in a tar dump and a plain dump (which has only one sql script file)?

Thanks.

Tim
  • 1
  • 141
  • 372
  • 590

2 Answers2

6

  • restore.sql is not used by pg_restore. See this comment from src/bin/pg_dump/pg_backup_tar.c:

     *  The tar format also includes a 'restore.sql' script which is there for
 *  the benefit of humans. This script is never used by pg_restore.

  • toc.dat is the table of contents. It contains commands to create and drop each object in the dump and is used by pg_restore to create the objects. It also contains COPY statements that load the data from the *.dat file.

    You can extract the table of contents in human-readable form with pg_restore -l, and you can edit the result to restore only specific objects with pg_restore -L.

  • The <number>.dat files are the files containing the table data, they are used by the COPY statements in toc.dat and restore.sql.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks. Why does `toc.dat` look like SQL script containing `CREATE DATABASE` and other SQL commands , if it is table of contents? – Tim Jun 20 '18 at 16:00
  • After reading the code, it turns out that my answer was quite wrong. See the updated answer. – Laurenz Albe Jun 21 '18 at 06:25
  • Thanks again. Both `restore.sql` and `toc.dat` are SQL scripts, and what is the difference between them? – Tim Jun 21 '18 at 12:43
  • One is used by PostgreSQL, one is nice for you if you want to extract the dump with `tar` and restore it with `psql`. – Laurenz Albe Jun 21 '18 at 13:02
  • Which one is for which? toc.dat for postgresql, and restore.sql for psql? – Tim Jun 21 '18 at 13:04
  • See my answer: *`toc.dat` is the table of contents. It contains commands to create and drop each object in the dump and is used by `pg_restore` to create the objects* and *The tar format also includes a `restore.sql` script which is there for the benefit of humans. This script is never used by `pg_restore`.* – Laurenz Albe Jun 21 '18 at 13:33
  • Thank you. (1) What is the difference between `restore.sql` and the output of extracting `toc.dat` in human-readable form with `pg_restore -l`? (2) Who will use `restore.sql` and when? – Tim Jun 28 '18 at 16:30
  • Use your imagination :^) Someone may want to untar the dump and load it with `psql`. – Laurenz Albe Jun 28 '18 at 17:17
  • This is a directory format dump, if you cd to the directory with toc.dat and *.dat.gz files, "pg_restore -F d -l ." (omit the quotes and mind the .) will give you a list which can prove to you whether you should restore the data using the pg_restore directory format (or not). – Jan Oct 13 '21 at 20:05
1

This looks a script to restore the data to PostgresQL. the script was created using pg_dump.

If you'd like to restore, please have a look at pg_restore.

The dat files contain the data to be restored in those \copy commands in the sql script.

the toc.dat file is not referenced inside the sql file. if you try to peek inside using cat toc.dat|strings you'll find that it contains data very similar to the sql file, but with a few more internal ids.

I think it might have been intended to work without the SQL at some point, but that's not how it's working right now. see the code to generate toc here.

Ereli
  • 965
  • 20
  • 34