Back up and restore the PostgreSQL databases

The Recording Service and Process Intelligence databases contain valuable data. These PostgreSQL databases should be backed up regularly. It is highly important to backup databases before upgrading the Recording Service and Process Intelligence applications.

One of the following options can be used to back up your PostgreSQL database:

The following is an example of backing up and restoring Recording Service databases.

File system level backup

This backup strategy requires to directly copy the files that PostgreSQL uses to store the data in the database. It is highly recommended to back up your Recording Service and Process Intelligence databases using this method before the upgrade. You can use whatever method you prefer for doing file system backups, but first you have to stop the Recording Service website in IIS and shut down a database server in order to get a usable backup.

To do this:

  1. Open Internet Information Services (IIS) Manager and stop the RecordingService website.
  2. Shut down a PostgreSQL database server.

  3. Copy the data directory.

    The default path is: C:\Program Files\PostgreSQL\12\data

    If you are using SSD and HDD to store databases, copy all your PostgreSQL data directories.

  4. Start the PostgreSQL database server.
  5. Start the RecordingService website.

Restoring files

To restore your database, you can use whatever method you prefer for doing a file system restore, but first you have to stop the Recording Service website in IIS and shut down a database server in order to get a usable backup. For example, repeat the backup steps above, but delete the current folders and replace them with the folders from the backup.

SQL dump

The idea behind this dump method is to generate a file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump. PostgreSQL provides the utility program pg_dump for this purpose. It extracts a PostgreSQL database into a script file or other archive file.

The approach is recommended for daily and weekly backups.

You don't need to shut down a database server to get a usable backup. You can perform a backup procedure from any remote host that has access to the database. But the pg_dump utility does not operate with special permissions. It must only have read access to all tables that you want to back up, so in order to back up the entire database, you almost always have to run it as a database superuser. If you do not have sufficient privileges to back up the entire database, you can still back up portions of the database to which you do have access.

An important advantage of pg_dump over the file system level backup method is that the pg_dump output can generally be re-loaded into newer versions of PostgreSQL, whereas file-level backups and continuous archiving are both extremely server version specific. For more details, see the PostgreSQL documentation.

Example of command line to run on the same computer where the Recording Service database is installed:

Copy 
"C:\Program Files\PostgreSQL\12\bin\pg_dump.exe" -F d -Z 1 -U su -j 2 -f \\fileshare\backups\august_backup RFStorage

where:

  • RFStorage is the database used by the Recording Service.
  • su is the user with sufficient rights.

Restoring the dump

Dumps are restored using the pg_restore utility. This restores a PostgreSQL database from an archive file created by pg_dump. For more details, see the PostgreSQL documentation.

Example of command line to restore a dump:

Copy 
pg_restore -d RFStorage \\fileshare\backups\august_backup