DaVinci Resolve | How to backup, optimize and restore the Davinci Resolve database via commandline20 Apr 2014
DaVinci Resolve interally uses a PostgreSQL database to store projects and a plethora of other information. While there are options in the GUI to backup, optimize and restore the database - they are not very helpful for automated backup workflows in post production companies.
Moreover piping in commands into the PostgreSQL interactive terminal
psql can be quite difficult. Since more and more facilities using DaVinci Resolve move to one centralized database, which is shared among several grading suites and datalab seats, simple automated backup procedures are required to secure daily operations.
Resolve actually uses the database tools that come with PostgreSQL, so when you press for example the "Backup database" button the
pg_dump tool is invoked and creates a archive of your database. The created archive is even portable across different system architectures, therefore moving the entire project database from Mac to a more robust Linux system is very simple.
In this article we want to show you how to use the Mac OS Terminal to backup, optimize and restore your Resolve database. The following commands can be easily adapted to Windows or Linux Resolve databases installations as well.
Resolve should not run while you execute the following commands (for performance reasons)
Novice Terminal users should backup their database via GUI before working with the Terminal
pg_dump to store its entire database into one file.
First we change the directory to the location where the pg_dump and the other executable resides with
Then we call the application with ./pg_dump and several arguments. The call looks something like this:
./pg_dump --host localhost --username postgres resolvedbname --blobs --file ~/Desktop/davinci_resolve.backup --format=custom --verbose
The actual database is called resolvedbname in the upper call. If you don't know what the name of your database is, just open up Resolve and click on the database icon in the lower right corner of your user screen. The database manager will open up and show you all the available databases with their names and hosts.
The other arguments pg_dump needs are :
-- host The host, in this case this is "localhost", which is your local machine. You can also use the actual IP of localhost which is 127.0.0.1.
-- username The username, in this case this is "postgres", which is the default user of the Resolve database
-- file The file to store the database into, in this case the file is called "davinci_resolve.backup" and will be written to the Desktop folder of the current user.
custom format will create a archive, which is compatible with
pg_restore for efficient restore operations.
This will tell
pg_dump to output detailed object comments and start/stop times to the dump file, and also print progress messages to standard error.
If you want to automatically add the current date to the filename you can use the
date command inside the call with
./pg_dump --host localhost --username postgres resolvedbname --blobs --file ~/Desktop/davinci_`date "+%y_%m_%d_%H_%M"`.backup --format=custom --verbose
How to avoid the password prompt
If you run the above commands on your own,
pg_dump might ask you for a password, depending on how your PostgreSQL database is configured. This is the password of your PostgreSQL database, which is usually DaVinci.
For facilitating automated backups you can use the
no-passwordargument in combination with a
.pgpass file to avoid any password prompt. Usually Resolve creates this file by default.
.pgpass file resides in the home folder of the user calling the Terminal and is structured in the following way:
Usually PostgreSQL runs on port 5432. You can also utilize the wildcard capabilities with the asterix
* in the above notation to refer to any database name with
.pgpass file in your home folder and replace the entries with the correct values. Drawn from the above examples you can also run the following command which redirects the output of
echo to a automatically created
.pgpass file in your home folder.
echo "localhost:5432:resolvedbname:postgres:DaVinci" > ~/.pgpass
After the creation of the
.pgpass file, you have to set the file permissions with
chmod at least to 600. Otherwise PostgreSQL will ignore the file in any call. Simply run
chmod 600 ~/.pgpass
Et voilà! Now you can run
pg_dump with the
no-password argument like this:
pg_dump -h localhost --username postgres resolvedbname --blobs --file ~/Desktop/davinci_resolve.backup --format=custom --verbose --no-password
How to compress the pg_dump archive file
pg_dump is used with the
custom archival format, the created archive is compressed by default. To increase the compression you can use the
compress argument and set the value from 0 to 9. Keep in mind that the value of 9 will significantly increase execution time. In normal post production environments the database file size will be around just a few Gigabytes, so rather bare with the default compression.
./pg_dump -h localhost -U postgres resolvedbname --blobs -f ~/Desktop/davinci_resolve.backup --format=custom --verbose --compress=9
PostgreSQL databases can be optimized in various ways. The two standard procedures that should be done on a regular basis are
vacuumdb. Both applications reside in the same directory as
pg_dump. Furthermore important to mention is the
pgbench utility, although it will not be discussed in this article.
pgbench is very useful to determine and optimize overall database performance. It can be very useful when running a centralized DaVinci Resolve database with many attached clients and investigating hardware bottlenecks.
vacuumdb command optimizes your database by removing empty spaces and also creating statistics with the
analyze argument. PostgreSQL uses these statistics to plan and execute optimized queries to your database.
./vacuumdb --analyze --host localhost --username postgres resolvedbname --verbose --no-password
reindexdb command will optimize your database by reindexing the tables for optimal performance.
./reindexdb --host localhost --username postgres resolvedbname --no-password --echo
Keep in mind that having old or unused project in Resolve is considered bad practice. Storing your finished projects with "Save Project" and afterwards deleting them from Resolve will also result in continuous good database performance in combination with the mentioned commands.
If you want to restore the database from a previously created dump archive file, you can use
./pg_restore --host localhost --username postgres --single-transaction --clean --dbname=resolve fullpath_of_the_archivefile
WARNING : The current database named resolve will be overwritten