Compatibility List

PostgreSQL compatibility list

Restrictive explanation

• To ensure the correctness of backup data, other backup software or scripts need to be closed before backup.
• Backup and restore PostgreSQL database on a per-instance basis.
• Archive mode for the database instance must be enabled before backup, and the archive command must be configured in the postgresql.conf configuration file.
• The listen_addresses option in the Postgresql.conf configuration file must include localhost.
• A user with superuser privileges must be used for backup and recovery.
• The password authentication method for socket connection should be set to md5 or scram-sha-256.
• PostgreSQL databases do not support configuration of archive commands with relative paths.

Preparation before backup

Network between Database and backup system

• Method 1: On the backup host, ping the backup system IP to test connectivity.
• Method 2: Use the backup system [System Settings] - [System Settings] - [System Tools] - [Network Tools], select "Ping", and test the network connectivity of the host domain name/IP.

Default ports Information

a. The database backup agent needs to use 2 service ports: 23100 and 23101. On the database server firewall, these 2 ports need to be opened for Vinchin backup server.

b. Confirm port connectivity: Use the backup system [System Settings] - [System Settings] - [System Tools] - [Network Tools], select "Telnet", and test the host domain name/IP network connectivity.

Preparation before database backup

1. Check the installation path and data directory of the PostgreSQL database.

   ps aux | grep postgre
   

   Modify the data directory, ensure the listen_addresses configuration item must include localhost.

2. Enable archiving.

   a. Use the postgres user to create the archive directory: /var/lib/pgsql/13/archivedir/

   b. View the PostgreSQL database data directory:

   wal_level = replica
   archive_mode = on
   archive_command='test ! -f /var/lib/pgsql/13/archivedir/%f && cp %p /var/lib/pgsql/13/archivedir/%f'
   

   Restart the database, manually switch the log to check if it is successful. Run the following command twice, insert some data to trigger archiving, check if archive logs are generated in the archive directory:

   select pg_switch_wal();
   

3. Check the password authentication method. The password-based authentication should be "md5" or "scram-sha-256" in the pg_hba.conf under the PostgreSQL data directory.

   View the pg_hba.conf file in the data directory:

   cd /var/lib/pgsql/13/data
   vi pg_hba.conf
   

   The password-based authentication should be "md5" or "scram-sha-256" in the pg_hba.conf under the PostgreSQL data directory.

Prerequisites for creating a backup job

• The backup database version is within the compatibility list, and the corresponding version client is installed. Perform application configuration in [Agent & Client].
• In the backup system [Agent & Client], confirm that the host status is online.

Preparation before restore

Recovery target

• Original Machine Recovery: Restore the database to the original host.
• Different Machine Recovery: Restore the database to another host.

Recovery mode

Restore to New Path

You must specify a custom port number to run the database instance and the port number should not be used by any other services on the database server.

You need to specify new directories for data and the archive log, these 2 directories should be empty and should not be any directory which is being used by any other services on the database server.

For the free storage space required, it must be 2 times more than the full restore point data size.

Preparation before recovery

• When executing a recovery task, the operating system user who installed the backup client must have read, write, and execute permissions on the recovery target directory.
• A user with Superuser privileges must be used for backup and recovery.
• Before recovery, confirm that the data directory has reserved space twice the size of the backup data.
• After restoring a PostgreSQL database by specifying a folder, it needs to be started by specifying the database directory. Starting with the service name will start the original database directory.

Select Restore mode

New Instance Recovery: New Instance Recovery is suitable for verifying backup data and restoring to a different machine.

Rollback Time: if you had selected archive log backup restore point, you are able to rollback PostgreSQL database state within the given time range.

If you disable rollback time it will by default restore to the latest time point of when the backup has been taken.