Search code examples
postgresqldump

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

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.

Solution

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