[V12] Migrate MS SQL to PostgreSQL issue

Yes, it seems that the help document is missing this part - should be a note at the top of the page!

@Stabz  in the guide it is assumed that it is necessary to have already installed and configured the postgreSQL instance

O.o fresh release

Ok, that’s what I thought thanks for the confirmation. I ll look how to configure it :)
 

Link State wrote:

 

@Stabz  in the guide it is assumed that it is necessary to have already installed and configured the postgreSQL instance

 

O.o fresh release

 

I was not sure about it, Veeam could have implement the installation during the migration process 
then I have to look how to configure PostgreSQL properly in Windows now :)

Becareful with the version 15.2 I don’t know if Veeam v12 is compatible :)

Stabz wrote:

Link State wrote:

 

@Stabz  in the guide it is assumed that it is necessary to have already installed and configured the postgreSQL instance

 

O.o fresh release

 

then I have to look how to configure PostgreSQL properly in Windows now :)

Becareful with the version 15.2 I don’t know if Veeam v12 is compatible :)

Sure m8 @Stabz ,

That's why I made the face O_o

very similar to the installation of an MSSQL

guide 

Install and Configure PostgreSQL on Windows | Windows OS Hub (woshub.com)

Install PostgreSQL (postgresqltutorial.com)

Set Up a PostgreSQL Database on Windows (microfocus.com)

Don’t forget to choose the best answer here.

😅

There is another option.

1. Backup configuration from the existing VBR.

2. Create a new VM and install VBR12 with PostgreSQL.

      3. Restore the existing configuration to the new VBR. Select Migrate on the configuration restore mode.

I feel the guude itself is sufficient. But this is where bloggers like us are to come in to complement the help document. In no distant time, we will work on a guide.

I’m curious. 

Which version does Veeam recommend for installation? I am assuming that it is the one that comes with the install media. 

However, it would be great to know if we can easily upgrade to a newer version if installing patches for example. 

I know I tried with the latest 15.2 version on a Linux box and it seemed to work fine with no issues.

CarySun wrote:

There is another option.

1. Backup configuration from the existing VBR.

2. Create a new VM and install VBR12 with PostgreSQL.

      3. Restore the existing configuration to the new VBR. Select Migrate on the configuration restore mode.

 

Hello @CarySun , you r right !

I think that for the majority of my clients, the migration will be done on MS SQL. For clients wishing to switch to PSSQL, I would recommend migrating to a new server. This would potentially allow for updating the OS, as well as avoiding any traces of MS SQL and potential security risks.

But I know than I ll have the case to upgrade to V12 and change database to PSSQL so I have to test it. :D

Was having this odd issue as well. After inplace upgrade from V11a to V12, previous SQL Express. Trying to migrate to POSTGRES.

• Installed Postgres 15.1 from the ISO X:\Redistr\x64\PostgreSQL
• Ran the Restore Configuration (Restore Configuration) from windows.
• C:\Program Files\Common Files\Veeam\Backup and Replication\DBConfig\veeam.backup.configuration.restore.exe
• Ran the Restore option, Switched to Native authentication username: Postgres \ password
• Changed the hostname from FQDN to Localhost and it worked. (Localhost:5432)
• Note: You can just launch the Restore Configuration utility from the VBR Menu, Configuration Utility, Restore.

Update: (2/24/2023)

• You can also choose to install the Stack Builder application during the Postgres install.
• With that application you can install the Database Drivers (PSQLODBC (64bit))
• Which saves the executable locally and you can manually run the installer for the driver.
• In windows go to ODBC 64 Data Source Adminstrator.
• Add PostgreSQL ODBC Driver (Unicode) 
• Configure and test database authentication with this tool. 

 Let me explain why you saw this message. I got the same in our lab. So we analyzed the issue and found the reason.

DISCLAIMER: 127.0.0. 1 is not written correctly on purpose. The security rules in the community doesn’t allow me to write it as one “word”.
 

The problem is the default configuration in "C:\Program Files\PostgreSQL\15\data\pg_hba.conf".
This file pg_hba.conf defines which client and how this client can connect to the PostgreSQL server.

You can read more about it here: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

Default settings are, that only Localhost can connect:

# IPv4 local connections:
host    all    all    127.0.0. 1/32    scram-sha-256

# IPv6 local connections:
host    all    all    ::1/128         scram-sha-256

Now there are different ways Veeam will connect to the database on local host. It depends on what you provide as the server name in our wizard:

• HOSTNAME, which resolves to a IPv4 address -> Veeam uses it’s IPv4 address to connect to the PostgreSQL server
• HOSTNAME, which resolves to a IPv6 address -> Veeam uses it’s IPv6 address to connect to the PostgreSQL Server
• IPv4 address of the backup server -> Veeam uses it’s IPv4 address to connect to the PostgreSQL server
• 127.0.0. 1 -> Veeam connects internally as Localhost
• Localhost -> Veeam connects internally as Localhost

When you use HOSTNAME or the ipv4 address, Veeam won’t be able to connect because the source IPv4 and IPv6 address are not in the pg_hba.conf. They simply are not allowed to connect.

What can you do:

• Use Localhost or 127.0.0. 1 as the server address
• Configure the pg_hba.conf to allow your IPv4 or IPv6 address and use the hostname of the server

The following IP addresses are just an example. You must use the ones from your backup server.

# IPv4 local connections:
host    all    all    172.30.30.31/32                                scram-sha-256

# IPv6 local connections:
host    all    all    fda7:01ef:2c85:ca84:aaaa:aaaa:aaaa:aaaa/128    scram-sha-256

Best,

Fabian

Mildur wrote:

 Let me explain why you saw this message. I got the same in our lab. So we analyzed the issue and found the reason.

DISCLAIMER: 127.0.0. 1 is not written correctly on purpose. The security rules in the community doesn’t allow me to write it as one “word”.
 

 

The problem is the default configuration in "C:\Program Files\PostgreSQL\15\data\pg_hba.conf".
This file pg_hba.conf defines which client and how this client can connect to the PostgreSQL server.

You can read more about it here: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

Default settings are, that only Localhost can connect:

# IPv4 local connections:
host    all    all    127.0.0. 1/32    scram-sha-256

# IPv6 local connections:
host    all    all    ::1/128         scram-sha-256

Now there are different ways Veeam will connect to the database on local host. It depends on what you provide as the server name in our wizard:

• HOSTNAME, which resolves to a IPv4 address -> Veeam uses it’s IPv4 address to connect to the PostgreSQL server
• HOSTNAME, which resolves to a IPv6 address -> Veeam uses it’s IPv6 address to connect to the PostgreSQL Server
• IPv4 address of the backup server -> Veeam uses it’s IPv4 address to connect to the PostgreSQL server
• 127.0.0. 1 -> Veeam connects internally as Localhost
• Localhost -> Veeam connects internally as Localhost

 

When you use HOSTNAME or the ipv4 address, Veeam won’t be able to connect because the source IPv4 and IPv6 address are not in the pg_hba.conf. They simply are not allowed to connect.

What can you do:

• Use Localhost or 127.0.0. 1 as the server address
• Configure the pg_hba.conf to allow your IPv4 or IPv6 address and use the hostname of the server

The following IP addresses are just an example. You must use the ones from your backup server.

# IPv4 local connections:
host    all    all    172.30.30.31/32                                scram-sha-256

# IPv6 local connections:
host    all    all    fda7:01ef:2c85:ca84:aaaa:aaaa:aaaa:aaaa/128    scram-sha-256

Best,

Fabian

Thanks for this explanation Mildur.  It will help when we start looking at PG servers to move to.

If we’re currently running Veeam V12 with Microsoft SQL - If I spin up a new server and install a fresh copy of Veeam V12 - Can I import the backup config from my old server to the new one all while switching to Postgres at the same time? I’ll even keep the servers hostname the same if that helps with the config restore. I imagine this would be the cleanest way to switch to get rid of any traces of MSQL?

Thoughts? 

Yes, you can if the configuration backup was taken from an original server which was on V12.

Make sure that the new server has the most current build of Veeam Backup & Replication.

https://www.veeam.com/kb4420

Best,

Fabian

@Mildur Is there any benefit to making the switch to Postgres SQL? Our current SQL database is well under 10GB.

Well, it’s not only the database size. With PostgreSQL you won’t have any restriction related to CPU and Memory use. SQL Express has those limits: https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2022?view=sql-server-ver16#scale-limits

I recently write a blog post on Veeam blog with a step-by-step procedure about how to migrate the config database to PostgreSQL: https://www.veeam.com/blog/switch-sql-server-postgresql-veeam.html

Hello guys, 

1st post ;)

Just ran today in this issue. v12, SQL DB Full, unable to migrate to Postgresql because it isn’t installed.

Manual install of v15.3.2 is ok for information, and migration ok.

It would be appreciate that this migration guide gives recommandations on security recommandations of postgresql service account management, to have an account with minimum roles and rights, and SQL uninstallation. 

Ronan.

Hi Ronan, this might be of help: https://www.veeam.com/blog/switch-sql-server-postgresql-veeam.html