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
- Open Internet Information Services (IIS) Manager and stop the RecordingService website.
- Shut down a PostgreSQL database server
- 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 - Start PostgreSQL database server.
- 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