Adjusting PostgreSQL Instance Configuration

If you selected to use an already installed PostgreSQL instance at the Specify Database Engine and Instance step of the wizard, make sure that the instance configuration is sufficient for the Veeam Backup & Replication performance.

To adjust the configuration of an existing PostgreSQL instance, take the following steps after you install Veeam Backup & Replication:

  1. On a backup server, run the Set-VBRPSQLDatabaseServerLimits cmdlet. The cmdlet generates the necessary PostgreSQL configuration and saves it to a dump SQL file.

Set-VBRPSQLDatabaseServerLimits -OSType <String> -CPUCount <number of CPU cores> -RamGb <RAM in GB> -DumpToFile <file path>

For example:

Set-VBRPSQLDatabaseServerLimits -OSType Windows -CPUCount 16 -RamGb 32 -DumpToFile "C:\config.sql"

  1. On the machine with the PostgreSQL instance where you want to deploy the Veeam Backup & Replication configuration database, use the psql tool to apply the configuration from the dump file.

The tool is located in the PostgreSQL installation folder.

psgl -U <user> -f <file path>

For example:

psgl -U postgres -f "C:\config.sql"

After you apply the configuration from the dump file, all changes will be written into the postgressql.auto.conf file located in the PostgreSQL installation folder. This file is loaded when the service starts and takes precedence over the default PostgreSQL configuration file.

  1. Include the pg_stat_statements library to the PostgreSQL configuration. To add the library, you can manually edit the shared_preload_libraries option in the postgres.conf file.

Alternatively, you can do it by by executing the SQL code:

  1. Check the content of the shared_preload_libraries variable.

SELECT * FROM pg_settings
WHERE name = 'shared_preload_libraries';

  1. Add the pg_stat_statements library to the shared preloaded libraries.
  • If the shared_preload_libraries value is empty, assign pg_stat_statements to the shared_preload_libraries variable.

ALTER SYSTEM SET shared_preload_libraries = pg_stat_statements;

  • If the shared_preload_libraries value is not empty, add pg_stat_statements to the current value separated by comma.

ALTER SYSTEM SET shared_preload_libraries = <existing libraries>, pg_stat_statements;

  1. Restart the PostgreSQL service for the new configuration to take effect.
  2. Install the pg_stat_statements extension. The extension is used to analyze the PostgreSQL performance.

CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";

Page updated 1/24/2024

Page content applies to build 12.1.1.56