Create Dump Dialog Help

• SQL With External Data – SQL-based format. It creates two files – main (*.sqlex) with smaller data values and external data file (*.sqldata) for placing large data values. Note, the external data file may not be created in case there are no large data values in the database.

• Custom – Create custom-format archive.
• Directory – Create a directory 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.
• Tar – Create a tar-format archive.

Options: Queries

• Include CREATE DATABASE statement – If ON then the CREATE DATABASE statement will be added to the dump.
• Clean before restore – Clean (drop) database objects prior to outputting the commands for creating them.

• Use INSERT commands – Dump data as INSERT commands (rather than COPY).
  • Use column inserts – Dump data as INSERT commands with explicit column names. Note, this option makes dump loading slower

    (INSERT INTO TABLE_NAME (column_name, ...) VALUES ...)

  • Multiple rows in INSERT statement – Place multiple data rows in each INSERT statement. It makes dump loading faster.

    (INSERT INTO TABLE_NAME (column_name, ...) VALUES ( ...row1... ), ( ...row2... ) )

  • New INSERT statment every [N] rows – Limit the number of rows per INSERT statement. It helps to avoid dumping too long INSERT commands which can be harder to transmit over the network.

Options: Save

• Owner – Dump commands to set ownership of objects.
• Privilege – Dump of access privileges.
• Tablespace – Dump commands to select tablespaces.
• Unlogged table data – Dump the contents of unlogged tables.

Options: Misc

• Disable triggers – Include commands to temporarily disable triggers on the target tables while the data is reloaded.
• Disable '$' quoting – Disable the use of dollar quoting for function bodies.
• Use SET SESSION AUTHORIZATION – USE SQL-standard SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to determine object ownership.
• With OID's – Dump object identifiers (OIDs) as part of the data for every table.
• Verbose – Output detailed object comments and start/stop times to the dump file, and progress messages to standard error.
• Force double quotes on identifiers – Force quoting of all identifiers.

Dump utility version – version of the pg_dump to create a dump. The drop-down menu allows the user to manage versions of the pg_dump utility. Detailed description of version management can be found here.

• Include CREATE DATABASE statement – If ON then the CREATE DATABASE statement will be added to the dump.
• Drop before restore – Clean (drop) database objects prior to outputting the commands for creating them.
• Lock tables for read – Add commands to lock tables to dump. Only the session that holds the lock can access the table.
• Surround the INSERT statements with statements to disable and enable keys – Disable keys before inserting records.
• Perform inserts in transaction – Add BEGIN/COMMIT statements for records of each dumped table.

• Multiple rows in INSERT statement – Place multiple data rows in each INSERT statement. It makes dump loading faster.

  (INSERT INTO TABLE_NAME (column_name, ...) VALUES ( ...row1... ), ( ...row2... ) )

  • New statement every – These options help to avoid dumping too long INSERT commands which may be harder to transmit over the network.
    • [N] Rows – Limit the number of rows per INSERT statement.
    • [S] KiB of data – Limit the size of data per INSERT statement.

• Write REPLACE statements rather than INSERT statements – REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

• Include CREATE DATABASE statement – If ON then the CREATE DATABASE statement will be added to the dump.
• Drop before restore – Clean (drop) database objects prior to outputting the commands for creating them.
• Perform inserts in transaction – Add BEGIN/COMMIT statements for records of each dumped table.

• Multiple rows in INSERT statement – Place multiple data rows in each INSERT statement. It makes dump loading faster.

  (INSERT INTO TABLE_NAME (column_name, ...) VALUES ( ...row1... ), ( ...row2... ) )

  • New statement every – These options help to avoid dumping too long INSERT commands.
    • [N] Rows – Limit the number of rows per INSERT statement.
    • [S] KiB of data – Limit the size of data per INSERT statement.

• Write REPLACE statements rather than INSERT statements – REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

* Start new batch every – These options help to avoid dumping too long batches which may be harder to transmit over the network.

• [N] Rows – Limit the number of rows per batch.
• [S] KiB of data – Limit the size of data per batch.

• Write REPLACE statements rather than INSERT statements – REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

• Drop before restore – Clean (drop) database objects prior to outputting the commands for creating them.
• Perform inserts in transaction – Add BEGIN/COMMIT statements for records of each dumped table.
  • [S] KiB of data – Limit the size of data per INSERT statement.

SQL With External Data options.

• Maximum data size to inline in the SQL file – All values exceeding this limit will be written to an external file.
• Put string data to external file – If ON, string values may be placed to an external file (in addition to binary values).