PostgreSQL® Database Maintenance

Update Database Schema

If the following warning is shown: "Database schema is 1.1, PostgreSQL-Backend already supports version 1.2", updating the database schema is recommended.

When the warning "Database schema is 1.1, PostgreSQL-Backend only supports 1.2" is shown, using a newer version of PostgreSQL®-Backend is required.

The first number is the major version and second number the minor version. If the minor versions do not match, the update is optional. If the major versions do not match, the update is mandatory!

Upgrade your database version by executing the update script: <WinCC OA Installation>/data/NGA/PostgreSQL/sql/update_database_platform -tv <1.1> e.g. WinCC OA Installation/data/NGA/PostgreSQL/sql/update_database_windows.ps1 -tv 1.2

Full DB Backup and Restore

The NGA Database Backup feature is an operation to backup (and if required, restore) the complete database(s) used by NGA and is therefore specific to the DB used InfluxDB®, PostgreSQL® and SQLServer® .

Note:

The backups are based on online backup features of the underlying databases and do not require a shutdown of the database and / or the WinCC OA project. Note that the performance of the database will be reduced while the backup is active.

There is no WinCC OA panel to perform the backup / restore operation and instead sample scripts (cmd or shell scripts) are provided for this purpose.

These are scripts that do a full backup of the current state of the database. These can be used when data is lost for some reason:

For backup and restore of the InfluxDB®, use the commands described in the chapter InfluxDB® Database Maintenance.
For backup and restore of the PostgreSQL®, use the commands described in the chapter PostgreSQL® Commands.
For backup and restore of the MS SQL®, use the commands described in the chapter MS SQL® Database Maintenance.

For backup and restore of the InfluxDB®, use the commands described in the chapter InfluxDB® Database Maintenance.
For backup and restore of the MS SQL®, use the commands described in the chapter MS SQL® Database Maintenance.

Other WinCC OA Backup Options

The WinCC OA "Online Backup" feature is to recreate a WinCC OA project in case of data loss or other fatal situations (hardware fault, unwanted manual deletion of data, ...). The online backup is a complete backup of a WinCC OA project. Note that this backup does not include the history database.
The Segment backup integrated in the NGA (see Extended Settings - Database-specific Configuration) can be used for restoration of historic data which has already been deleted due to retention settings ("lifetime" of data).

PostgreSQL® Commands

The backup commands are available for PostgreSQL®. The command can be used for cmd and sh. This chapter describes the PostgreSQL® commands.

CAUTION:

To use PostgreSQL® backup commands, you must set the environment variables "PGPASSWORD" and "PGDATA". For the environment variables, see the PostgreSQL® documentation https://www.postgresql.org/docs/13/libpq-envars.html

PostgreSQL®: backupPostgreSQL.cmd [[command] [argument]] <PostgreSQL-backup-path>


Command	Description
--host	Host of the database (default is 127.0.0.1).
--port	Port of the database (default is 5432).
--user	User of the database (default is "postgres") .
--max-backup-count	Number of backups kept (default is 3).

PostgreSQL®: restorePostgreSQL.cmd [[command] [argument]] <PostgreSQL-backup-path>


Command	Description
--backup	The backup folder name (e.g. 20211021-133236) inside <PostgreSQL-backup-path> (default is the newest backup).
--promote	Promotes database to primary database if "true" is passed (default is "true").
--silent	The user will not be asked to confirm that the current database will be overwritten. This command is a switch and does not require a parameter.