PostgreSQL® Database Maintenance

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_upgradeTo1.3.sql

upgrades the schema to version 1.3.

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 version 1.3, 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_upgradeTo1.3.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).

Create a Backup

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!
```
For the DB Admin managed PostgreSQL® Database backend we recommend the same configuration. This must be set by the database administrator in the corresponding file:

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

or Linux: /var/lib/pgsql/data/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.

Restore the backup

Stop the database
```
 pg_ctl stop -D -m f
```
Rename the following database file directory: <wincc oa project directory>db/wincc_oa/localdb/postgresql/16/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.

Execute the command for a managed database - see PostgreSQL® Database Overview

tar -xf <db_target_backup_directory>/base.tar.gz -C <db_file_target_directory>
tar -xf <db_target_backup_directory>/pg_wal.tar.gz -C <db_file_target_directory>/pg_wal

Execute the following two commands if you use a remote database:
```
pg_ctl start -D <db_file_target_directory>
```
```
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

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.