The SAS® Viya® 3.5 Backup Service might create *_pg_dump.dmp as a plain-text format file


Overview

For deployments that use sas-backup-agent version 2.31.0 and have not configured the SAS Backup service to use postgresBackupAdditionalOptions, the default backup creates *_pg_dump.dmp in the wrong format. (The SAS Backup service is not configured by default to use postgresBackupAdditionalOptions.)

When the SAS Viya Backup service performs the default backup, the *_pg_dump.dmp file is created as a plain-text file instead of a custom-format compressed file. (The *_pg_dump.dmp file is the output file for pg_dump.) However, a plain-text format *_pg_dump.dmp file is not in a format that pg_restore can use.

The *_pg_dump.dmp file is saved in the shared vault for the backupID. The file might be named something similar to SharedServices_pg_dump.dmp or <provider|tenant>_schema_pg_dump.dmp in a multitenant deployment.

Note that the problem described in this SAS KB article does not cause the backup to have a failed status. The backup completes successfully, and the size of the backup might be larger than expected. In addition, if you then try to restore the backup, the status for restore job is displayed by the Backup service as having completed successfully. However, no content is restored from the backup to SAS® Infrastructure Data Server.  

If you view the contents of the pg_dump restore log for the BackupID restored, a pg_restore error similar to the following occurs: 

pg_restore: error: input file appears to be a text format dump. Please use psql.

For Backup_ID restored, the pg_dump restore log is saved in the sharedVault of the following locations:

<sharedVault>/<Backup_ID>/__default__/postgres/restore/restore_ID_SharedServices_pg_dump.dmp_logon_restore.log
<sharedVault>/<Backup_ID>/__default__/postgres/restore/restore_ID_SharedServices_pg_dump.dmp_restore.log

When the Backup service performs a default backup, the *_pg_dump.dmp file should be a custom-format compressed file, which is a format that pg_restore can be use. However, for deployments that use sas-backup-agent 2.31.0, the "--format custom" default option is not picked up when you have not set postgresBackupAdditionalOptions in the SAS Backup service configuration.

Determine Your sas-backup-agent Version

To determine the version of sas-backup-agent that your deployment uses, run the following command in the sas_viya_playbook directory:

ansible consul,pgpoolc,rabbitmq,sas_casserver_primary,sas_casserver_secondary -m command -a "rpm -q sas-backup-agent"

For sas-backup-agent-2.31.0, the output resembles the following:

sas-backup-agent-2.31.0-20240816.1723798849902.x86_64

Determine the *_pg_dump.dmp File Format

To determine whether the *_pg_dump.dmp file is in plain-text format, the file command returns ASCII text similar to the following:

[sasinstall@sasviya ~]$ file /sharedvault/2025-09-29T10_29_57_484-0400/__default__/postgres/SharedServices_pg_dump.dmp

/sharedvault/2025-09-29T10_29_57_484-0400/__default__/postgres/SharedServices_pg_dump.dmp: ASCII text

The fix for this issue reinstitutes the default behavior for the Backup service, which is to create the pg_dump output file *_pg_dump.dmp as a compressed file that pg_restore can use.

Note: This issue is fixed in backup-agent version 2.31.2.

Workaround

There is currently no known workaround for this issue that enables you to restore the default backup when the *_pg_dump.dmp is in plain-text format. The workaround below corrects the format for only any future default backups that you create when you still use the backup-agent version 2.31.0. 

Edit the Backup service configuration using SAS® Environment Manager to specify the format for the pg_dump output file to be format custom. See SAS® Viya® 3.5 Administration: Backup and Restore: Manage the Backup Configuration and Schedule for additional information about editing a backup configuration.

  1. Log on to SAS Environment Manager a SAS administrator. In a multi-tenant deployment, log on as a provider administrator.
  2. In the left pane, click Configuration.
  3. Select View ► Basic services. Then, select Backup service from the list.
  4. Edit the sas.deploymentbackup configuration.
  5. When the Edit sas.deploymentbackup Configuration page is displayed, scroll down until you find the postgresBackupAdditionalOptions option. Then, set the value to -F c and click Save.
    Set the postgresBackupAdditionalOptions option to "-F c" and click Save.

  6. The option should be set in the Backup service configuration.
    The postgresBackupAdditionalOptions option is set to "-F c" on the Edit sas.deploymentbackup Configuration page.

  7. Next, restart all instances of deploymentBackup and backup-agent services.   

    sudo systemctl restart sas-viya-deploymentBackup-default

    sudo systemctl restart sas-viya-backup-agent-default

  8. Then, run a default backup. 

    When the default backup completes, make sure that the output format for the *_pg_dump.dmp file is now compressed.   

    A PostgreSQL custom database dump should be displayed that is similar to the following:

    [sasinstall@sasviya ~]$ file /sharedvault/2025-09-29T10_55_25_374-0400/__default__/postgres/SharedServices_pg_dump.dmp

    /sharedvault/2025-09-29T10_55_25_374-0400/__default__/postgres/SharedServices_pg_dump.dmp: PostgreSQL custom database dump - v1.14-0