Set-VBOPSQLDatabaseServerLimits

Short Description

Modifies settings of the PostgreSQL instance.

Syntax

This cmdlet provides parameter sets that allow you to:

Modify settings of the PostgreSQL instance automatically.

Set-VBOPSQLDatabaseServerLimits [-DumpToFile <String>] [<CommonParameters>]

Modify settings of the PostgreSQL instance manually.

Set-VBOPSQLDatabaseServerLimits -OSType <String> -CPUCount <Int32> -RamGb <Int32> [-DumpToFile <String>] [<CommonParameters>]

Detailed Description

This cmdlet allows you to modify settings for the PostgreSQL instance. Use this cmdlet to extend hardware resources of the PostgreSQL instance where the Veeam Backup for Microsoft 365 configuration database is located.

Depending on your configuration, you can use the following parameter sets:

Automatic modification. Use this parameter set if you installed the PostgreSQL instance and the Veeam Backup for Microsoft 365 server on the same machine, and the PostgreSQL instance is used as the Veeam Backup for Microsoft 365 configuration database.
Manual modification. Use this parameter set in the following cases:

If the PostgreSQL instance is already installed on a remote machine and it is used by Veeam Backup for Microsoft 365 as the configuration database. In this case, you must specify the OS of the machine where the PostgreSQL database installed, number of CPU cores and amount of memory in GB.
If the PostgreSQL instance is installed on a remote machine and currently is not used by Veeam Backup for Microsoft 365 as the configuration database. In this case, you must create a dump file with the necessary settings and specify a path to this file. Also, you must specify the OS of the machine where the PostgreSQL database installed, number of CPU cores, and amount of memory in GB.

To modify settings, you need to enter the necessary parameters with new values. The parameters that you omit will remain unchanged.

Set-VBOPSQLDatabaseServerLimits Important

Consider the following:

If you set PostgreSQL database as the configuration database when you install Veeam Backup for Microsoft 365, the necessary resource capacity will be configured during Veeam Backup for Microsoft 365 installation.
If the CPU or RAM resources are changed after Veeam Backup for Microsoft 365 installation, you must run this cmdlet again to adjust hardware resources of the PostgreSQL instance.
You must restart the PostgreSQL service after you run this cmdlet.

Parameters

Parameter	Description	Type	Required	Position	Accept Pipeline Input
DumpToFile	Specifies a path to a dump file. The cmdlet will save a dump SQL file on a machine where the PostgreSQL instance is installed.	String	False	Named	False
OSType	Specifies the OS of the machine where the PostgreSQL instance is installed: Windows Linux	String	True	Named	False
CPUCount	Specifies a number of CPU cores that you want to assign to a machine where the PostgreSQL instance is installed.	Int32	True	Named	False
RamGb	Specifies amount of memory in GB that you want to assign to a machine where the PostgreSQL instance is installed.	Int32	True	Named	False

This cmdlet supports Microsoft PowerShell common parameters. For more information on common parameters, see the About CommonParameters section of Microsoft Docs.

Output Object

None.

Examples

Set-VBOPSQLDatabaseServerLimits Example 1. Modifying Local PostgreSQL Instance

This command automatically modifies settings of the PostgreSQL instance. The PostgreSQL instance and the Veeam Backup for Microsoft 365 server are installed on the same machine, and the PostgreSQL instance is used as the Veeam Backup for Microsoft 365 configuration database.

Set-VBOPSQLDatabaseServerLimits

Set-VBOPSQLDatabaseServerLimits Example 2. Modifying Remote PostgreSQL Instance

This command manually modifies settings of the PostgreSQL instance. The PostgreSQL instance is already installed on a remote machine and it is used by Veeam Backup for Microsoft 365 as the configuration database.

Set-VBOPSQLDatabaseServerLimits -OSType Windows -CPUCount 16 -RamGb 30

Set-VBOPSQLDatabaseServerLimits Example 3. Modifying Remote PostgreSQL Instance and Creating Dump File

This command manually modifies settings of the PostgreSQL instance. The PostgreSQL instance is already installed on a remote machine and currently it is not used by Veeam Backup for Microsoft 365 as the configuration database.

Set-VBOPSQLDatabaseServerLimits -OSType Windows -CPUCount 8 -RamGb 16 -DumpToFile "c:\temp\PSqlLimitConfig.sql"

The following is the example of a dump file:

ALTER SYSTEM SET max_connections = '3000';

ALTER SYSTEM SET effective_cache_size = '<dynamically calculated value>';

ALTER SYSTEM SET checkpoint_completion_target = '0.9';

ALTER SYSTEM SET wal_buffers = '16MB';

ALTER SYSTEM SET default_statistics_target = '100';

ALTER SYSTEM SET random_page_cost = '1.1';

ALTER SYSTEM SET max_worker_processes = '<dynamically calculated value>';

ALTER SYSTEM SET max_parallel_workers_per_gather = '<dynamically calculated value>';

ALTER SYSTEM SET max_parallel_workers = '<dynamically calculated value>';

ALTER SYSTEM SET max_parallel_maintenance_workers = '<dynamically calculated value>';

ALTER SYSTEM SET min_wal_size = '2GB';

ALTER SYSTEM SET max_wal_size = '8GB';

ALTER SYSTEM SET hash_mem_multiplier = '2';

ALTER SYSTEM SET log_temp_files = '10MB';

ALTER SYSTEM SET log_lock_waits = 'on';

ALTER SYSTEM SET join_collapse_limit = '8';

ALTER SYSTEM SET geqo_threshold = '10';

ALTER SYSTEM SET log_line_prefix = '%m [%p] %q[user=%u,db=%d,app=%a] ';

ALTER SYSTEM SET jit = 'off';

ALTER SYSTEM SET log_autovacuum_min_duration = '60s';

ALTER SYSTEM SET log_min_duration_sample = '1000';

ALTER SYSTEM SET log_statement_sample_rate = '0.01';

ALTER SYSTEM SET log_rotation_age = '10d';

ALTER SYSTEM SET shared_buffers = '<dynamically calculated value>';

ALTER SYSTEM SET maintenance_work_mem = '<dynamically calculated value>';

ALTER SYSTEM SET work_mem = '<dynamically calculated value>';