Migrating VBR to PostgreSQL [Part 1]

  • 26 February 2023
  • 2 comments
  • 1276 views

Userlevel 5
Badge +2

Part 1 of the small series is about to install a PostgreSQL server on a Debian system.

Ok let’s start.

 

Step 1 Install the postgres package with the package manager of the distribution.

You have to have PostgreSQL at least in version 14!

 

Because this is a Debian system I had to enable the official repos from PostgreSQL:

root@postgres:~# echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" >> /etc/apt/sources.list.d/postgres.list

root@postgres:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

 

I normally use aptitude, but you can for sure use apt-get as well.

root@postgres:~# aptitude update

root@postgres:~# aptitude install postgresql

 

Step 2 To enable PostgreSQL to use encrypted passwords we have to uncomment the password_encryption option in the configuration file

root@postgres:~# vi /etc/postgresql/15/main/postgresql.conf

password_encryption = scram-sha-256         # md5 or scram-sha-256

and restart the PostgreSQL server:

root@postgres:~# systemctl restart postgresql

 

Step 3 We now create a new database and a user which we can use in the Veeam migration afterwards to connect to the database.

For this step we switch to the postgres user:

root@postgres:~# su - postgres

 

With the command psql we can manage the databases, users, tables and so on:

postgres@postgres:~$ psql

psql (15.2 (Debian 15.2-1.pgdg110+1))

Type "help" for help.

postgres=#

 

Step 3.1 We create the database first:

postgres=# CREATE DATABASE veeambackup;

 

and verify it:

postgres=# \l
                                                  List of databases
    Name     |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   
-------------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | 
 template0   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
             |          |          |             |             |            |                 | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
             |          |          |             |             |            |                 | postgres=CTc/postgres
 veeambackup | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =Tc/postgres
(4 rows

 

Step 3.2 Then we create the user veeam with a password:

postgres=# CREATE USER veeam WITH SUPERUSER PASSWORD 'Veeam123#';

 

and again verify it:

postgres=# \du


The veeam user needs to be superuser to install extensions during the migration.


Step 4 (back to root) To make sure the server is listening also to external connections and not to localhost only, change the configuration file again:

postgres@postgres:~$

logout

root@postgres:~# vi /etc/postgresql/15/main/postgresql.conf

listen_addresses = '*'


Additionally, you can create your own SSL certificate and change the path in the same configuration file.


Step 5 To give permission only to the Veeam server to connect to the database with the desired user, edit the file access policy configuration file:

root@postgres:~# vi /etc/postgresql/15/main/pg_hba.conf

add the following line:

host all veeam 10.1.1.50/32 scram-sha-256


Explanation:

  • host defines that we want to allow connections from an external host (hostssl seems not to be supported by veeam for the moment)

  • all is to allow all databases

  • veeam is the user which is allowed to connect

  • ip address or subnet is the host (or subnet) which will be allowed to connect 

  • the last option is the used authentication method.

 

Step 6 Restart the daemon again:

root@postgres:~# systemctl restart postgresql

 

Step 7 You can easily check if the PostgreSQL server now listens to external connections:

root@postgres:~# ss -tulpen |grep 5433

tcp LISTEN 0 244 0.0.0.0:5433 0.0.0.0:* users:(("postgres",pid=1011,fd=5)) uid:106 ino:15278 sk:8 cgroup:/system.slice/system-postgresql.slice/postgresql@15-main.service <->

tcp LISTEN 0 244 [::]:5433 [::]:* users:(("postgres",pid=1011,fd=6)) uid:106 ino:15279 sk:9 cgroup:/system.slice/system-postgresql.slice/postgresql@15-main.service v6only:1 <->

 

 

In Part 2 we will talk about migrating the Veeam VBR installation to use this external PostgreSQL server.


2 comments

Userlevel 7
Badge +20

This is a great article and series. It will definitely help those looking to migrate to PostgreSQL.  Thanks for sharing this. 👍

Userlevel 5
Badge +2

I forgot to mention the reasons to migrate to PostgreSQL:

  • Internal team which has already knowhow with PostgreSQL
  • Already existing best practises for configurations with PostgreSQL (HA, Tuning)
  • License cost and limitations (10GB database size, CPU limitations, buffer cache)

And also open tasks that I want to clarify:

  • Superuser permissions really used?
    • Can the extensions be created manually?
    • Is database owner and all privileges not enough?
  • File access policy needs the term all for the database and not just the name of the database
  • Is HA with PostgreSQL supported?

Comment