Skip to main content
  • EN

INTRODUCTION

 

As you know, the latest version of Veeam Backup & Replication, that is 12.1.2.172, was released last May 21.
Among the various new features and security patches, the aspect I would like to elaborate on is this:


Security & Compliance Analyzer
A new backup infrastructure check was added to ensure that the PostgreSQL instance hosting the configuration database has been configured with the recommended settings. These settings can be applied with the Set-VBRPSQLDatabaseServerLimits cmdlet.


This new check just verifies that the recommended settings for PosgreSQL are actually applied to our backup server.

But what does it mean in detail?

 

INFRASTRUCTURE CHECK DETAILS


We know that since version 12 of VBR, the database that is installed by default is PostgreSQL and no longer the Microsoft SQL Express.


So when installing the product, if you choose to keep the default DB and have it installed during the wizard, Veeam will install PostgreSQL on the same VBR server.
During this operation, Veeam will edit the file C:\Program Files\PostgreSQL\15\data\posgresql.conf setting the recommended parameters for its DB.


So, if after a standard installation like the one described above we run the Security & Compliance Analyzer tool, we notice that the new check on PosgreSQL gives us a positive result.

 

So when should the Set-VBRPSQLDatabaseServerLimits command be applied ?

 

USE CASES

 

There are basically two cases:

 

  • PostgreSQL is installed manually, thus not automatically during Veeam deployment.
  • CPU and/or RAM resources of the server on which PostgreSQL is installed are changed.

 

How to use the Set-VBRPSQLDatabaseServerLimits command depending on the case scenarios?


There are essentially three ways:

 

  1. Modify a local PostgreSQL instance: from the Veeam powershell, run the Set-VBRPSQLDatabaseServerLimits command and restart PostgreSQL services to make the change effective
     
  2. Modify a remote PostgreSQL instance already used by Veeam: from the Veeam powershell, run the command, with the appropriately set parameters, Set-VBRPSQLDatabaseServerLimits -OSType <String> -CPUCount <Int32> -RamGb <Int32> and restart the PostgreSQL services to make the change effective
     

     

  3. Modify a remote PostgreSQL instance not yet used by Veeam: from the Veeam powershell, run the command, with the appropriately set parameters, Set-VBRPSQLDatabaseServerLimits -OSType <String> -CPUCount <Int32> -RamGb <Int32> -DumpToFile <String> and restart the PostgreSQL services to make the change effective.

 

Specifically, OS type refers to the OS version of the server where PostgreSQL is installed, CPUCount and RamGb are the CPU and RAM parameters of the server where PostgreSQL is installed, DumpToFile is the path where a file with a .sql extension containing the recommended settings will be generated.

 

Example: Set-VBRPSQLDatabaseServerLimits -OSType <String> -CPUCount 16 -RamGb 30 -DumpToFile "C:\file.sql"


It is not explicitly specified in the official guide (you can see only “run it” in the PS output), but obviously in the third mode the file with the optimized parameters must be “uploaded” to the PostgreSQL server.

 

To pass these parameters to the PostgreSQL instance you can use, for example:

  • PgAdmin's query tool
  • The psql command line


In all cases these operations will go to modify, via the ALTER SYSTEM command, the C:\Program Files\PostgreSQL\15\data\posgresql.auto.conf file, which is read each time the C:\ProgramFiles\posgresql.conf file is and effectively overwrites the parameters of the existing settings.

 

REFERENCES

 

https://helpcenter.veeam.com/docs/backup/powershell/set-vbrpsqldatabaseserverlimits.html?ver=120

https://www.postgresql.org/docs/current/config-setting.html

Enjoy! 💚

I saw this after upgrading and it is a great tool to ensure optimization of PostgreSQL.  Great write-up Marco. 👍

Nice writeup Marco...thanks for sharing!

Nice post @marco_s! I get some many environments with high CPU levels about postgre process.


All of them comes from a V12 update and migration of SQL server to postgree.

The only thing that I still do not understand is what limits the VBRPSQLDatabaseServerLimits set?

It takes all the CPU and RAM of the server?

Thanks to share this info! I have to update my procedure

The only thing that I still do not understand is what limits the VBRPSQLDatabaseServerLimits set?

It takes all the CPU and RAM of the server?

Hi @wolff.mateus ,

if I am not wrong, the command takes the maximum configured in the OS of CPU and RAM and sets from those values the corresponding parameters proportionally.

Specifically, the parameters that change should be:


max_worker_processes (CPU)
max_parallel_workers_per_gather (CPU)
max_parallel_workers (CPU) 
max_parallel_maintenance_workers (CPU)
effective_cache_size (RAM) 
maintenance_work_mem (RAM)
work_mem (RAM)

To be clear, if the OS has 8CPU and 16 GB, the following two commands will give the same result:

  • Set-VBRPSQLDatabaseServerLimits
  • Set-VBRPSQLDatabaseServerLimits -OSType Windows -CPUCount 8 -RamGb 16

Thanks for that explanation Marco as I always wondered the same thing myself.

The only thing that I still do not understand is what limits the VBRPSQLDatabaseServerLimits set?

It takes all the CPU and RAM of the server?

Hi @wolff.mateus ,

if I am not wrong, the command takes the maximum configured in the OS of CPU and RAM and sets from those values the corresponding parameters proportionally.

Specifically, the parameters that change should be:


max_worker_processes (CPU)
max_parallel_workers_per_gather (CPU)
max_parallel_workers (CPU) 
max_parallel_maintenance_workers (CPU)
effective_cache_size (RAM) 
maintenance_work_mem (RAM)
work_mem (RAM)

To be clear, if the OS has 8CPU and 16 GB, the following two commands will give the same result:

  • Set-VBRPSQLDatabaseServerLimits
  • Set-VBRPSQLDatabaseServerLimits -OSType Windows -CPUCount 8 -RamGb 16

Very good Marco to know that Marco. Thanks!

Hello everyone, I have a question, before apply the command is it possible to modify the values of the CPU and RAM as we want or is it better to use the basic command without adjusting the values ?

 

Hello everyone, I have a question, before apply the command is it possible to modify the values of the CPU and RAM as we want or is it better to use the basic command without adjusting the values ?

 

If you understand how Postgres uses resources you can adjust them or just leave the command to do it for you.  I always just let it do the adjustments.

Thanks @marco_s!

Thank you for this information.  Postgres is new to me in general but obviously with the default being Postgres now, this is a new world for me and I hadn’t considered the optimizations for this.  However, if I’m understanding this correctly, it appears that this would only be really affected if the resources of the VBR server were changed, or if you need to tweak things down instead of using the max resources, correct?

Hi Derek, in case of new installation, the command should be run if you install Postgres manually or if you go to change OS resources later. Of course you can also decide to customize the parameters, but i would assume that the ones Veeam sets are the correct/optimized ones.

I think the command should also be run when upgrading to 12.1.2, as it is a specific check introduced in this version.

Hi Derek, in case of new installation, the command should be run if you install Postgres manually or if you go to change OS resources later. Of course you can also decide to customize the parameters, but i would assume that the ones Veeam sets are the correct/optimized ones.

I think the command should also be run when upgrading to 12.1.2, as it is a specific check introduced in this version.

Thank you for the confirmation!

I am just now getting to this on my 2 VBR servers and one additional note to keep in mind → to be able to run the command, MFA needs to temporarily be disabled it appears:

 

I am just now getting to this on my 2 VBR servers and one additional note to keep in mind → to be able to run the command, MFA needs to temporarily be disabled it appears:

 

You can also run it as an account you have marked as a “Service Account” but you need to use the Run As for Powershell with that account for it to work.

Thanks Chris...that is indeed the case. Appreciate the assist! 😊

Comment


Terms & Conditions