VFX.engineering Development for post-production and visual effects

DaVinci Resolve | How to backup, optimize and restore the Davinci Resolve database via commandline

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.

Warning

  • 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

Backup

Resolve calls 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 cd.

cd /Library/PostgreSQL/8.4/pgAdmin3.app/Contents/SharedSupport/

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.

-- format Using the custom format will create a archive, which is compatible with pg_restore for efficient restore operations.

-- verbose 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 date "+%y_%m_%d_%H_%M":

./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.

The .pgpass file resides in the home folder of the user calling the Terminal and is structured in the following way:

hostname:port:database:username:password

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

localhost:5432:*:username:DaVinci

Create the .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

When 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

Optimize

PostgreSQL databases can be optimized in various ways. The two standard procedures that should be done on a regular basis are reindexdb and 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.

The 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

Additionally the 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.

Restore

If you want to restore the database from a previously created dump archive file, you can use pg_restore:

./pg_restore --host localhost --username postgres --single-transaction --clean --dbname=resolve fullpath_of_the_archivefile

WARNING : The current database named resolve will be overwritten


Further reading