Migration to Postgresql

[Jabi]

Hi,

I have a customer with an old SQL Server (Express).
Instead of Upgrading MSSQL i would like to migriate to Postgres.
Is there an official best practice or a how to guide ?

thanks

[Mildur]

Hi Jabi

May I ask, how big is the infrastructure you want to migrate? How many VMs are protected by this VBR server?
Does this customer has a Veeam Enterprise Manager involved in his infrastructure?

For scalability and reliability purposes, we recommend to stay on MSSQL for now as the configuration database. If the customer still want to switch, migration steps are documented in our Userguide:
VBR: https://helpcenter.veeam.com/docs/backu ... ml?ver=120
EM: https://helpcenter.veeam.com/docs/backu ... ql&ver=120

If you manually install the PostgreSQL server, please also check out this command to optimize your PostgreSQL configuration for Veeam:
https://helpcenter.veeam.com/docs/backu ... ts&ver=120

Best,
Fabian

[Jabi]

Hi,

Not that big, 20 VMs
Hardend Repo, TapeServer etc

No EM

[Mildur]

Thanks.
I don't see any issues with such environments.

1) Install a support PostgreSQL server (locally or remote): https://helpcenter.veeam.com/docs/backu ... ml?ver=120
2) Use the link to migrate the VBR configuration database: https://helpcenter.veeam.com/docs/backu ... ml?ver=120
3) Use the PowerShell command to optimize the PostgreSQL configuration for VBR: https://helpcenter.veeam.com/docs/backu ... ts&ver=120

Please give us a feedback how the migration process worked for your customer.

Best,
Fabian

[hexadecimal]

I've seen a lot of talk about migrating away from MSSQL to PostgreSQL. Are there any significant performance benefits resulting in the migration for those backing up up to 50 VMs, fully utilizing vbr (surebackup, cloud, sobr, etc.) and employing VDRO. Or is it simply to move away from the MS SQL db platform to an open source platform?

[Jabi]

What is the minimum Version for PostgreSql as Veeam ConfigDB ?

[Mildur]

Hello Hexadecimal

The main reason I see that customer want to migrate their database is because of the MSSQL Express size 10GB limitation.
If you use MSSQL as the database server, VBR environments which protects a lot of objects (VMs, Agents, large File To Tape Job, ...) could have a bigger database than 10GB and so required a MSSQL standard license to overcome the 10GB limits of MSSQL Express.
With PostgreSQL, you don't have that 10GB limit. Going to PostgreSQL can save you license costs.

Best,
Fabian

[Mildur]

What is the minimum Version for PostgreSql as Veeam ConfigDB ?

It's in the system requirements I shared in step 1:
https://helpcenter.veeam.com/docs/backu ... ments.html

SQL Database
Local or remote installation of the following versions of PostgreSQL:
PostgreSQL 14
PostgreSQL 15 (PostgreSQL 15.1 is included in the setup)

[lienkristian]

I have a customer with Enterprise Manager and aprox 200 protected vm's in multiple domains. using S3 repo, StoreOnce and local storage for the repositories. Does this migration require to reenter all the passwords for the users used? And how do we convert the enterprise manager since the two need to be on the same technology and not one MSSQL and one PostgreSQL

[Mildur]

Hello Kristian

Does this migration require to reenter all the passwords for the users used?

Reentering passwords after a configuration restore was never required if your configuration backup was encrypted.

And how do we convert the enterprise manager since the two need to be on the same technology and not one MSSQL and one PostgreSQL

I provided a link in my first answer:
EM: https://helpcenter.veeam.com/docs/backu ... ql&ver=120

Best,
Fabian

[Jabi]

soory for asking again, is there a way to change the database without uninstalling and reinstalling vbr ?

[Egor Yakovlev]

is there a way to change the database without uninstalling and reinstalling vbr ?

Absolutely!
Links to documentation, provided by Fabian as an answer to the first post in this thread, have all the steps to migrate configuration database from one engine to another for the current VBR installation without need of reinstall.
Long story short, when restoring backup server configuration in "Migrate" mode, on a step "Target Database" you will now be able to choose a PostgreSQL server.

/Cheers!

[allankjaer]

Is the a sizing guide/guideline, for when to use SQL Express vs SQL Standard vs Postgresqql with v12?

[dave_lub]

Can you upgrade EM to V12 with postgresql and let B&R servers on v11?

[Egor Yakovlev]

Is the a sizing guide/guideline, for when to use SQL Express vs SQL Standard vs Postgresqql with v12?

You can get MS database recommendations at Design VBR Database section of best practices guide.
General rule of thumb for PostgreSQL vs MS SQL is "few thousand workloads". For anything below, PostgreSQL as default will suite you well, removing any possible obstacle met with MS SQL Express.

[Gostev]

For V12 GA build, our QA is reasonably confident with the usage of PostgreSQL in environments with up to 1000 machines per job and up to 5000 machines total (per backup server). These are not final scalability limits by any means, it was just a good sane target for the initial release. From here on it will be an iterative process of them gradually increasing test environments size, uncovering new PostgreSQL bottlenecks and having R&D address them. Plus learning specific customer scenarios and corner cases where we did not expect PostgreSQL might struggle. We've been through this exact iterative process with the SQL Server before, which is why we're playing it safe with PostgreSQL for a start as well.

[sebastian]

I tried to move EM to PostgreSQL but when I try to import my backup i get an error message:

"The configuration backup file is valid.

Failed to connect to PostgreSQL server localhost:5432. GSS/SSPI authentication but IntegratedSecurity not enabled"

Any hints about this? EM Service runs at local system service.
I tried running the service with my local user, also no success.

Edit: Nevermind, when /login:<value> /password:<value> is omitted, everything works fine

[Jabi]

Absolutely!
Links to documentation, provided by Fabian as an answer to the first post in this thread, have all the steps to migrate configuration database from one engine to another for the current VBR installation without need of reinstall.
Long story short, when restoring backup server configuration in "Migrate" mode, on a step "Target Database" you will now be able to choose a PostgreSQL server.

/Cheers!

ok have to try that, was confused bei the statement, once you connected postgresdb to your veram instance, …

[spiritie]

2/2 of our VBR servers which has been migrated to Postgres are spamming logs with all kinds of different issues and one server has been spamming Veeam logs with 100MB/s for over a week now, while the other is going crazy on tmp DB files. Would urge others to stay away for now.

[Gostev]

Actually it can't be that bad either because PostgreSQL was the database installed by default since the very first V12 beta well over a year ago. Because of this decision, most of the V12 testing was naturally done on PostgreSQL. And as I already mentioned, the only reservation our QA has at this time is the environment size.

So most likely most of those issues you're seeing are not related to the PostgreSQL usage, rather they are some usual teething issues of a major release (e.g. too much logging in some corner cases).

[alexander sh]

2/2 of our VBR servers which has been migrated to Postgres are spamming logs with all kinds of different issues and one server has been spamming Veeam logs with 100MB/s for over a week now, while the other is going crazy on tmp DB files. Would urge others to stay away for now.

@spiritie have you opened a support case for these issues regarding Postgres? I'd be happy to take a look.

[spiritie]

@alexander sh

We have 2 cases open already, RnD is looking at it but thanks.

[spiritie]

Veeam should really link the:
https://helpcenter.veeam.com/docs/backu ... ml?ver=120

Inside the article of:
https://helpcenter.veeam.com/docs/backu ... ml?ver=120

We've missed the step on our migrations and only stumbled into it because of this forum post.

[Egor Yakovlev]

Passed to documentation team.
Thanks for feedback!

[jveerd1]

Can you share some more details on the automatic customization of the postgresql settings? How does this work? Which parameters are used in this case?

[Egor Yakovlev]

You can use "Set-VBRPSQLDatabaseServerLimits -DumpToFile C:\temp\sql.txt" to see all parameters and it's values.
/Cheers!

[William Bohn]

Is it best to upgrade to v12 first and leave it on MSSQL and then do the database conversion to PostgreSQL or switch to the new database as you upgrade to V12?

[Mildur]

You must upgrade first to V12.
Migration to PostgreSQL cannot be done as a part of the backup server upgrade to V12.

Best,
Fabian