PostgreSQL
This Protected Item type backs up PostgreSQL databases at the logical (SQL) level using pg_dump. Each selected database is saved as a separate .sql file in the snapshot. Data is streamed directly into Comet's compression, encryption, and deduplication engine without spooling the dump to local disk.
Databases are backed up one at a time. Each database has a consistent snapshot, but the databases are not all captured at the same point in time. This Protected Item does not provide continuous Write-Ahead Log (WAL) archiving or point-in-time recovery between backups.
Requirements
Install the PostgreSQL client tools on the device running Comet Backup, even if the PostgreSQL server is on another machine. Comet requires pg_dump and there is no built-in fallback if it is missing.
Use client tools compatible with the source server. Comet rejects a pg_dump version older than the server's major version. PostgreSQL does not guarantee that a dump can be restored to an older server version. See the PostgreSQL pg_dump documentation for compatibility details.
The backup account must be able to connect to the selected databases and read all the data being backed up. Backing up cluster roles and tablespaces also requires a superuser account and pg_dumpall.
Connection details
Select PostgreSQL when creating the Protected Item. Enter the server hostname or IP address, port (default 5432), username, and password in the Authentication settings.
"Connect to database" specifies an existing database used to list databases and check the server version. It defaults to postgres. Change it if the account cannot connect to postgres. This setting does not select which databases to back up.
Use "Test Connection" to check the connection before selecting databases. A successful connection test does not check that the client tools are installed or that the account can read every selected database.
SSL / TLS
Enable "Use SSL protocol" to encrypt the connection. You can supply paths to a server CA file and a client certificate and private key. These files must be accessible on the device running the job.
Without a custom CA file, Comet verifies the server certificate and hostname. With a custom CA file, it verifies the certificate against that CA without checking the hostname.
"Allow self-signed certificate" disables server certificate verification for Test Connection, database browsing, and direct PostgreSQL restores. During backup, pg_dump and pg_dumpall can still verify the server certificate against a root.crt file in the PostgreSQL client's default location, even with this option enabled. A connection test can therefore succeed while the backup fails certificate verification.
For verified TLS with PostgreSQL client tools older than version 16, supply a server CA file or install a root certificate in the PostgreSQL client's default location. Client tools version 16 or newer can use the system trust store.
Selecting databases
Use the plus button to choose individual databases. The dropdown also lets you add a database by name or select "Add all database items". Selecting all databases includes new databases in future backups.
The database browser and all-databases selection exclude template databases and databases that do not allow connections.
Comet skips databases whose names begin with . or -, contain /, \, =, or control characters, or consist only of whitespace. Each skipped database produces a warning in the job log. Dots elsewhere in a name are supported.
Custom pg_dump
Comet searches standard PostgreSQL installation locations and then the device's PATH for the client tools. To select a specific installation, set "Override path to pg_dump" to the full executable path, for example C:\Program Files\PostgreSQL\18\bin\pg_dump.exe or /usr/lib/postgresql/18/bin/pg_dump.
"Extra parameters" accepts additional space-separated pg_dump arguments. Quoted arguments containing spaces are not supported. Keep the plain SQL output format for direct PostgreSQL restores, and do not add --create. Comet ignores -f / --file arguments because output must be streamed into the backup.
Roles, privileges, and tablespaces
Enable "Back up roles and privileges" to include cluster-wide roles, their privileges, and tablespaces in a .globals.sql file. Comet uses pg_dumpall --globals-only, looking for pg_dumpall beside the selected pg_dump before searching other installation locations. The backup fails if this option is enabled and pg_dumpall cannot be found.
Database dumps preserve object ownership and tablespace placement. The required roles and tablespaces must exist on the target before restoring databases. See Restoring roles and tablespaces.
Integrations can also attach a device-local file using the METADATA_FILE EngineProps setting. Its contents are stored as .metadata and can be retrieved with a file restore. Direct PostgreSQL restores skip both .metadata and .globals.sql.
Restore
You can restore all databases or select individual .sql files from the snapshot.
Restore directly to PostgreSQL
Choose the "PostgreSQL" restore type and enter the target server's connection settings. Comet prefills these from the current Protected Item settings where available. "Connect to database" must name an existing database on the target and defaults to postgres.
Comet restores each database using its original name and creates it if it does not exist. The restore account needs permission to create missing databases and to drop, create, and assign ownership of the restored objects. Direct restores do not require psql or pg_restore on the device.
Restoring drops and recreates objects included in the backup, replacing their current data. Objects that were not in the backup remain in the target database. For example, a table created after the backup remains, but new rows in a backed-up table are lost.
Check the job log and restored data after the restore. A failure in one database can leave it partially restored while other databases continue, and the overall job can still report success.
Restoring roles and tablespaces
If the target lacks required roles or tablespaces, restore .globals.sql using the "Files and Folders" restore type first. Review the script before applying it with psql as a PostgreSQL administrator. Tablespace definitions contain absolute paths from the source server; adjust these for the target server and prepare the directories before running the script. Then restore the databases.
Restore SQL files
Choose "Files and Folders" to recover the .sql files to disk for manual import with psql. Create the target database first and restore any required roles and tablespaces before importing its SQL file. These are plain SQL scripts, not pg_restore archives.