What is the purpose of the sql script file in a tar dump?

Question

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.

Answer 1

• 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.

Answer 2

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.