PostgreSQL® Database Maintenance - DBAdmin-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;”

Create a Backup

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

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

  • Set the following parameters to ensure that the log file size is kept limited / constant in continuous operation in the corresponding file:

    Windows: <PG Installation path>/<PG version>/postgres.conf

    or Linux: /var/lib/pgsql/data/postgresql.conf:

    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!
  • Open the command shell on Windows or a terminal window on Linux.
  • 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

  • How you stop the database depends on how the database was installed and started.

    On Windows, the services panel (Windows start menu -> type in "Services") should be used to stop the database.

    On Linux systems, either systemctl or the service command should be used, depending on the distribution. If you are unsure, ask your database administrator.

  • Rename or remove the database configuration directory. The storage location depends on how the database was installed. If you are unsure, please contact your database administrator. In addition, archives with tablespace information must be unpacked into the correct directories; for each entry in the tablespace_map file, go to the directory named there, move or remove all PG_* subdirectories located there and unpack the archive with the appropriate name to this location. 
    tar -xf <db_target_backup_directory>/base.tar.gz -C <database configuration directory> tar -xf <db_target_backup_directory>/pg_wal.tar.gz -C <database configuration directory>/pg_wal
  • Create an empty new pgdata directory.
  • Open the command shell on Windows or a terminal window on Linux.
  • Start the server using a method suitable for the type of installation.

    Under Windows, the Services panel (Windows Start menu -> enter "Services") should be used to start the server.

    On Linux systems, either systemctl or the service command should be used, depending on the distribution. If you are unsure, ask your database administrator.

  • In any case, 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 is established 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

Note:
If you are using the default system installation of PostgreSQL®, then use systemctl instead of pg_ctl start.
Note:
pg_ctl is not included in the bin path under Debian. You must use the /lib/postgresql/$version$/bin/pg_ctl full path.