PostgreSQL Databases Backup and Restore

The Recording Service and Timeline 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 Timeline applications.

We suggest you take one of the approaches to backing up your PostgreSQL database:

  • File system level backup
  • SQL dump

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

File System Level Backup

This backup strategy is to directly copy the files that PostgreSQL uses to store the data in the database. We highly recommend backup your Recording Service and Timeline 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.

Basic Steps

  1. Open Internet Information Services (IIS) Manager and stop the RecordingService website.
  2. Shut down a PostgreSQL database server
  3. Copy Data Directory
    The default path is: C:\Program Files\PostgreSQL\12\data
    Note. If you are using SSD and HDD to store databases, copy all your PostgreSQL data directories
  4. Start PostgreSQL database server.
  5. Start RecordingService website.

Restoring Files

To restore your database, you can use whatever method you prefer for doing 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, but in steps 3 and 3.a 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 backing up.

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. In particular, it must 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 previous backup method is that pg_dump's 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 detailed information, see https://www.postgresql.org/docs/12/app-pgdump.html.

Example of command line to run on the same computer where the Recording Service database is installed:
"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 - the name of the database used by the Recording Service.
  • su - user with sufficient rights.

Restoring the Dump

Dumps are restored using the pg_restore utility. It restores a PostgreSQL database from an archive file created by pg_dump.

For detailed information, see https://www.postgresql.org/docs/12/app-pgrestore.html

Example of command line to restore a dump is:
pg_restore -d RFStorage \\fileshare\backups\august_backup

22.09.2023 8:59:47

Usage of Cookies. In order to optimize the website functionality and improve your online experience ABBYY uses cookies. You agree to the usage of cookies when you continue using this site. Further details can be found in our Privacy Notice.