Postgres Dump

Run pg_dump and save dump to a temporary file
Join our community

Overview

This node runs the pg_dump command-line utility to create a dump (backup) of a PostgreSQL database and saves it to a temporary file. It is useful for automating database backups, migrating data between environments, or archiving database states within an n8n workflow.

Typical use cases include:

  • Scheduling regular backups of a PostgreSQL database.
  • Exporting database contents before performing destructive operations.
  • Migrating data by exporting from one environment and importing into another.

The node outputs the path to the generated dump file, which can then be used by subsequent nodes for storage, transfer, or further processing.

Properties

Name Meaning
Host The hostname or IP address of the PostgreSQL server to connect to (default: "localhost")
Port The port number on which the PostgreSQL server is listening (default: 5432)
Database The name of the PostgreSQL database to dump
Username The username to authenticate with the PostgreSQL server
Password The password corresponding to the username (input is masked for security)

Output

The node outputs JSON data containing a single field:

  • filePath: A string representing the full path to the temporary SQL dump file created by pg_dump. This file contains the plain-text SQL commands needed to recreate the database schema and data.

No binary data is output directly by this node; instead, the dump file path can be used by other nodes to read or transfer the backup file.

Example output JSON:

{
  "filePath": "/tmp/pgdump-1687000000000.sql"
}

Dependencies

  • Requires the pg_dump utility to be installed and accessible in the system's PATH where n8n is running.
  • Uses environment variable PGPASSWORD to securely pass the database password to pg_dump.
  • Relies on Node.js child_process.exec to execute the shell command.
  • Temporary files are stored in the OS temporary directory.

Troubleshooting

  • Common issues:

    • pg_dump not found: Ensure PostgreSQL client tools are installed and pg_dump is in the PATH.
    • Authentication failures: Verify that the host, port, username, password, and database name are correct.
    • Permission denied writing to temp directory: Check that n8n has write permissions to the OS temp folder.
    • Large databases may take time to dump; consider workflow timeouts.

  • Error messages:

    • Errors returned by pg_dump will be surfaced as execution errors. The message usually includes details such as authentication failure, connection refused, or invalid database.
    • If the command fails, the node throws an error with the underlying message from pg_dump.

Links and References

Discussion