OAProfessor

There is a backup tab in the NGA configuration for the database of your choice. But, this backup works only if the database is being managed by WinCC OA (i.e. started and stopped with each run of the project, not a service). Many customers do not want their database managed this way, they want to run the database as a service as expected and put the files on secondary drives or even on remote drives.

The good news, ETM has published the string to backup the most popular database: PostgresSQL. You will find various command line hints in this file: C:\Siemens\Automation\WinCC_OA\3.20\data\NGA\PostgreSQL\sql\readme.md. I have taken this concept and applied it to the way OA system calls work in CONTROL.

It is best practice to put various things in a library as string constants so that they are all in one place and can be reused without having to search for them. Some of the items might be:

string sPOSTGRESQLBACKUP = "M:/OnlineBackup/Database";  // where to backup
const string sPOSTGRESQLPASSWORD = "yourPassword";      // postgreSQL admin password
const string sPOSTGRESQLADMIN = "postgres";             // admin user, usually this string
const string sPOSTGRESQLSVR = "localhost";             // if database on same computer as OA
const string sPOSTGRESQLPORT = "5432";                 // linux may be different 15432
string sPOSTGRESQLDATA = "D:/PostgreSQLdata";          // database folder

If you want to create a unique backup each time that has a timestamp, try this sort of function:

{
  time t = getCurrentTime();
  int iRet;
  int iYear, iMonth, iDay, iHour, iMinute, iSecond;
  string sNewPath;

  iYear = year(t);
  iMonth = month(t);
  iDay = day(t);
  iHour = hour(t);
  iMinute = minute(t);
  iSecond = second(t);

  sprintf(sNewPath, "%d%02d%02d-%02d%02d%02d", iYear, iMonth, iDay, iHour, iMinute, iSecond);
}

The best way to run the backup would be for a timedfunc that ran every week or whatever your needs. You can also just setup a button to trigger this to run on the server. NOTE: you cannot run this on a client as it needs the local database to have the tools for the backup.

To run that command line, there is a bit of a trick. PostgreSQL requires that there is an environment variable called PGPASSSWORD setup for command line functions. You could setup this in the computer, but then the password would be easy for someone to find (OA library is a bit more secure, but you can use the OA encryption to create an encrypted string). To pass in an environment variable to the system call, you must use the special case of a mapping. Here is the working code:

{
  mapping m;
  int iRet;
  string sBackupFolder;

  sBackupFolder = "timebasedName";

  m = makeMapping("program", PVSS_BIN_PATH + "pgsql/bin/pg_basebackup.exe",
                  "arguments", makeDynString("-D",sPOSTGRESQLBACKUP +"/"+ sBackupFolder,
                                             "-U",sPOSTGRESQLADMIN,
                                             "-h", sPOSTGRESQLSVR,
                                             "-p", sPOSTGRESQLPORT,
                                             "-Xf", "-Ft", "-z", "-c", "spread", "-R", "-P", "-w"),
                  "env", makeMapping("PGPASSWORD",sPOSTGRESQLPASSWORD),
                  "stderr+", "log/PGbackupErr.log");
  iRet = system(m);
}

This assumes that you installed the database yourself, AND also installed the PosgreSQL client files in OA. Otherwise, you will need to adjust that path. The “env” is the magic that makes all this work!

Now you have your very own way to backup the PostgreSQL database anytime you need.