PostgreSQL® Database Maintenance - WinCC OA-managed Database

Upgrade the Schema

WinCC OA PostgreSQL® schema upgrade scripts are stored in the WinCC OA installation directory wincc_oa_path/data/NGA/PostgreSQL/sql/Migrations. Each upgrade script name contains the version of the target schema to which the WinCC OA database schema is to be upgraded, e.g. 
PSQL_upgradeTo<version>.sql
upgrades the schema to the specified version.
Note:
Make sure that you upgrade your PostgreSQL® schema version to the latest available version. You can find the available update scripts under wincc_oa_path\data\NGA\PostgreSQL\sql\Migrations. Update the schema in the right order, meaning from the lowest to the highest (latest) version.
To execute the upgrade script, simply call psql: 
psql -f <upgrade_script.sql> [-U user] [-h database_host] [-p database_port] <database_name>

For example, to upgrade the schema to the specified version, where the database server is installed on localhost with default port (5432), with the user (etm) and with the database name (winccoa), simply call:

psql -f PSQL_upgradeTo<version>.sql -U etm winccoa

You will then be asked for the password of the user `etm`. For more information about how to connect to the PostgreSQL® database, see (https://www.postgresql.org/docs/current/app-psql.html).

Note that the user "etm" must have replication rights, which are not granted by default. This is done, for example, by executing the command "ALTER ROLE etm REPLICATION;" in the database with the admin user account:
psql -U admin_user -h C:/Users/<username>/AppData/Local/.winccoa -p 15432 -c “ALTER ROLE etm REPLICATION;”
To use a local Unix socket connection, use this command for Windows: 
pg_basebackup -D <db_target_backup_directory> -U admin_user -h C:/Users/<username>/AppData/Local/.winccoa -p 15432 -Ft -z -c spread -R -P
and the following command for Linux: 
pg_basebackup -D <db_target_backup_directory> -U admin_user -h $HOME/.winccoa -p 15432 -Ft -z -c spread -R -P

Create a Backup

To backup your complete PostgreSQL® database, proceed as follows.

Note:
If you need to use the PostgreSQL® commands, call the commands from the WinCC OA product directory /bin/pgsql/bin.

Prerequisite: WinCC OA project is running and PostgreSQL® database is accessible.

  • In a "WinCC OA managed PostgreSQL" Database backend configuration, the following parameters are set to ensure that the log file size is kept limited / constant in continuous operation: 
    logging_collector = on
log_truncate_on_rotation = on
log_rotation_age = 1d
log_filename = '%A.log' # log file name pattern: creates a logfile with weekday name - will be overwritten after 7 days!

    This must be set by the database administrator in the corresponding file:

    <Project path>/db/wincc_oa/localdb/postgresql/<PostgreSQL Version>/pgdata/postgresql.conf

  • Open the command shell on Windows or a terminal window on Linux.
  • Change to the following WinCC OA product directory /bin/pgsql/bin. If an environment variable is set to this directory under Windows, this is not necessary.
  • Execute the following psql commands.
    pg_basebackup -D <db_target_backup_directory> [-U admin_user] [-h database_host] [-p database_port] -Ft -z -c spread -R -P
    You must authenticate yourself (user = "etm" + your defined password for standard project creation or the default password for a "Legacy project"). If the command is executed successfully, the output is "<size/size> kB (100 %), 1/1 tablespace.
    Note:
    The <db_target_backup_directory> must be empty. It is recommended to set up a "Date+Time" folder in the directory.
    CAUTION:
    The backup files are not encrypted. To prevent unauthorized access, ensure that the destination directory (db_target_backup_directory), especially a remote directory, is secure. Use BitLocker (Windows) or other encryption programs to encrypt the backup files.

Restore the backup

  • Use the database engineering panel to stop the database.
  • Rename or remove the database configuration directory: <wincc oa project directory>/db/wincc_oa/localdb/postgresql/<PostgreSQL Version>/pgdata and create an empty new pgdata directory.
  • Open the command shell on Windows or a terminal window on Linux.
  • Change to the following WinCC OA product directory <winccoa product directory>/<version>/bin/pgsql/bin.
    Note:
    Note that if an environment variable is set to this directory under Windows, this is not necessary. Note also that the target "database_directory" must be empty. You must first move or delete your old, corrupted database.
  • Open the command prompt on Windows or your preferred terminal on Linux and execute: 
    tar -xf <db_target_backup_directory>/base.tar.gz -C <wincc oa project directory>/db/wincc_oa/localdb/postgresql/<PostgreSQL Version>/pgdata tar -xf <db_target_backup_directory>/pg_wal.tar.gz -C <wincc oa project directory>/db/wincc_oa/localdb/postgresql/<PostgreSQL Version>/pgdata/pg_wal
  • Use database engineering panel to start the server.
  • Execute the following command to promote the database from standby to read-write mode:
    pg_ctl promote -D <database_directory>

If a database restore has become necessary and the NextGen Archiver has buffered the data from the failure, the buffers are automatically rewritten to the database after a new database connection and the buffers are resolved.

Note:
The database backup concept should be designed in such a way that WinCC OA can buffer all data for the period in which a restore point can be set up again. This means that the available space requirement for the buffer period is greater than the backup interval.

You can find all required commands in the readme file <winccoa product directory>/<version>data/NGA/PostgreSQL/sql>/readme.md