Creating A Shared Database – A Huge Workflow Enhancement For A Small Facility

Creating A Shared Database – A Huge Workflow Enhancement For A Small Facility

July 3, 2015
Robbie Carman C.S.I.

In this Insight learn what gear you'll need and how to properly setup a shared database in DaVInci Resolve.


Editors Note 6/5/17: Since version 12.5.2 DaVinci Resolve has shipped with a newer version of PostGresSQL (9.5.4) Any references to PostGres 8.4 in the steps below should be replaced with 9.5. Also PostGresSQL is not installed by default anymore. In the DaVinci Resolve installer you must eplicitly choose to install it.  If you forgot to, you can simply rerun the installer.  Finally, if a PostGres Data folder is not found first launch Resolve and create a local PostGresSQL database – this will create the nessasary data folder


Easily Share A Resolve Database & Gain Streamlined Workflows

As you probably know the database is at the heart of DaVinci Resolve.

Databases store everything about a project, your preferences, and perhaps most importantly your actual corrections and grades.

There is a lot of information out there on best practices for SQL and Disk Databases – how to connect, backup, etc. – indeed, Team Mixing Light has produced a lot of training on databases including nearly a whole chapter dedicated to them in our Deep Insights Training.

But if you work in a facility with multiple Resolve workstations or if you’re an independent colorist with multiple computers there is a compelling case to be made for having a shared database.

While nearly all the info I’ll share in this Insight is in the Resolve config guides or user manual, it’s hard to find it all in one place and in layman’s terms.

As a small facility owner/operator I can tell you that using a shared database has a been a huge, gigantic, even colossal benefit to our grading workflows.

A shared database not only makes moving from room to room simple, it unlocks some advanced Resolve features such as timeline collaboration and the upcoming remote rendering in Resolve 12.

Configuring a shared database server & SQL database is very straight forward, but also a little tricky if you haven’t done it before so that’s what I want to share with you in this Insight.

What Is A Shared Database?

For those of you who have been using DaVinci Resolve for years, you’re probably very familiar with the PostGreSQL database environment.

An SQL database is a standard way of storing data – your bank probably uses thousands of them to store user and financial data.

Resolve uses a database to store information about your user, the project, preferences and of course, the actual corrections you apply to any given shot.

Resolve used to only work with this type of database.  While many users and facilities still use an SQL database for Resolve, it seems like increasingly BlackMagic is trying to steer users away from SQL databases or at least make them harder to create.

A few versions ago the Resolve team implemented Disk Databases, which function more like a project folder with all the database goodness contained within.

These databases are of course very popular, function pretty much the same (files are visible) and are now the default type of database that is created when you install Resolve.

BlackMagic seems to prefer Disk Databases so much, that Resolve 12 by default, won’t even install the PostgreSQL environment need to create a SQL database!

The big problem with Disk Databases?

Well, without some trickery with file sharing services or shared storage they’re not shareable.

You might have noticed if you’ve created an SQL database that unlike a Disk Database that has a file path to the location of the database, an SQL database has an IP.

By default, this is an internal IP, meaning it’s local to your machine and points to the PostgreSQL database server on your local machine.

However, because an SQL database uses an IP, it means that the database can be shared or accessed from other machines in your facility or even outside your office!

To be clear – by default an SQL database does run local to your particular machine running Resolve and is not shared.

But, as I’ll show you a little later, it’s easy with some pretty simple steps to setup any SQL database to be shared.

Why Use A Shared Database?

My facility has multiple machines running DaVinci Resolve.

We have a ‘hero’ suite with all the bells & whistles, a smaller suite but similarly equipped to the big room, and a couple assist stations.

My biggest issue with disk or local SQL databases is that if someone started a project on computer 1, but then I need the project on computer 2, I’d have to export the project from computer 1, import it into the local database on computer 2, and relink, etc.

This is an ok way to work, but sooner or later this shuttling back and forth and having to manage multiple versions of the same project becomes very tiresome and potentially dangerous – are you working on the right version of the project?

So why do I used a shared database?

With a shared SQL database, every Resolve computer can point to the SAME database, & can access the same version of the project, without the export/import problems or the hassles of managing multiple versions of the same project.

Also with a proper VPN setup, this database can even be accessed remotely.

For example, often I’ll come home to have dinner with my family but realize that I could get a jump on the next day by working on a project after the kids are tucked into bed.

Because I have a shared database, I can VPN into my office network and I can access the same database and same projects I use at work from my iMac in my home studio!

bmd collaborative
A shared database allows these good looking folks in a really nice looking studio to work in a collaborative fashion with DaVinci Resolve ©BlackMagic Design

 

Besides a simple convenience issue, using a shared SQL database is how you can leverage Resolve’s collaboration features, which allow multiple colorists, editors and assistants to not only work on the same project at the same time, but actually be working on the same timeline at the same time.

A shared database will also make the upcoming Resolve 12 feature ‘remote rendering’ possible.

What About The Media?

Oh right, the media!

Obviously a Resolve database in whatever form is everything BUT the media.

While shared databases can absolutely be used in any setup with multiple computers running Resolve, a shared database tends to work best in an environment with shared storage.

If you setup a shared database using the steps below but don’t have shared storage that’s ok, but you need to be aware of a few things:

As long as you have media that a project uses locally on each machine you can still get all the benefits of using a shared database, but you might have to relink media on each machine and of course you’ve duplicated, triplicated or even more ‘cated’ your media which is not all that efficient – but will work.

I’ve heard of people getting pretty creative with file sharing services like DropBox – locating media on DropBox, and syncing that media to a drive called the same thing on each of the Resolve computers.  I haven’t tried this myself, but I would imagine it would work.

In my facility, we have a true SAN that allows all the computers that use the shared database to also point to the same location for the media that any project uses.

tz8
A SAN like this base TZ8 unit from Small Tree (the 10Ge SAN my facility uses) lets you share media easily in a shared database environment

 

With both the database setup and shared storage, I can get on any computer in the facility and access the database, project, user settings and so on just like I would running locally.

For example, I might start in the morning in the hero room, but then someone else is being supervised with clients and would like to use that room, no problem!

I’ll simply log off, go to another room, log back into the database, open my project and everything is right where I left it.

What Do You Need To Run A Shared SQL Database?

Chances are you probably already have everything you need to run a shared SQL database.  Let’s break the components down:

  • Full Version Of Resolve – To get started running a shared SQL database, it’s important to note that you need the full version DaVinci Resolve on the computer you wish to access the shared database.  Resolve Lite (as of version 11) doesn’t support shared databases (but you can use Lite to setup the database server – more on that in moment).
  • A Computer To Be The Database Server – This can be any of the machines you already run Resolve on, but many facilities (including mine) choose to dedicate a separate computer to act as the database server. Instead of a new computer, what I did was use the same computer we were running our FTP server on. For my shop, this is 2012 i7 Mac Mini. Just keep in mind, I’m not sure how to set up the database server on a Windows/Linux box.  I’m sure BlackMagic Resolve support can help with that.
  • Fast Networking – Gigabit Ethernet is standard on pretty much every computer you can buy these days, but make sure all of your computers are actually communicating with each other over Gigabit and that you have good Cat 5e or Cat 6 cables. Also be sure you have a good Gigabit switch, for some time I had a cheap 48 port NetGear that was causing lag in a shared database environment, I upgraded a much better managed switch and haven’t had any problems since.
  • Static IP – The computer you use as the database server should have a permanent static IP on your network and should not be using DHCP to get an IP. Having the same IP lets you configure the database server once and essentially forget about it.
  • PostGres SQL – On the machine you intended to use as the database server you’ll need to install PostGreSQL. The easiest way to do that is to run the DaVinci Resolve (or Lite) installer and make sure PostGres is installed.  Just remember, the database server computer doesn’t have to be able to run Resolve – although you might want it to. I installed the full version of Resolve on the Mac Mini we use so I can setup new shared databases, and do any database management directly from that computer. I just borrow a dongle from another machine when I need to do those things.
  • DaVinci Resolve Config/User Guide – Unless you’re a PostGreSQL guru you probably don’t know the Terminal commands you need to turn on the database server and configure it to share SQL databases. Page 25 of the Mac config guides details what you need to do and this information is also in the user manual, but I’ll save you the hassle of finding the info by detailing the steps below.
MacMini
A Mac Mini even moderately configured makes a fantastic Resolve database server.

 

Getting the gear together to use a shared SQL database is pretty easy.  Again, I want to mention the computer you use doesn’t have to be an amazing machine because all you’re really doing is throwing metadata around, but in my experience a machine like a Mac Mini or even MacBook Pro with a decent amount of RAM and fast disk (SSD) will work really well.

Configuring The Server

After installing PostgreSQL via the Resolve installer, you’ll need to do some configuration using the Terminal app to properly configure the server.

If you’re intimated by the Terminal get some help!

Because you’ll be in ‘SUDO’ mode there is potential that you can screw things up, but as long as you follow the steps that I’ll list here or from the configuration guide you should be fine (please type in don’t copy these commands):

  • Launch the Terminal (Applications > Utilities > Terminal)
  • In the Terminal type sudo su – postgres
  • Enter your admin password for the computer you’re using.  You’ll see the Terminal prompt change to postgres$
  • Next, just in case you do screw something up you’ll want to create a backup of the current SQL config by typing
    cp/Library/PostgreSQL/8.4/data/pg_ hba.conf/Library/PostgreSQL/8.4/data/pg_hba.conf.backup
  • With the backup complete, next you need to configure the database server to use the static IP for the computer your using (and a range of possible addresses) to do this type in: echo “host all all 10.0.70.00/24 md5” >> /Library/PostgreSQL/8.4/data/pg_hba. conf  Keep in mind with above string you’ll replace the IP with actual IP of the computer you’re using. Make sure you keep the /24 md5 part. This simply specifies that the 4th number in the IP can be any number. Also notice I didn’t put in the actual set of last numbers in the IP? I’ve found adding.00 to more reliable.
  • To have the change you’ve made in the Terminal active open the PostgreSQL application folder (Applications > PostgreSQL 8.4) and double-click the Stop Server app.  After confirmation that the server has stopped double click the Start Server app.

That’s the hard part!  Almost done.

It’s a good idea to make sure that the SQL server is properly configured. To do that head back over to the Terminal and enter the following commands:

  • sudo su – postgres enter your admin password
  • cd /Library/PostgreSQL/8.4/data/ this will change the active directory.
  • cat pg_hba.conf  this will load the file called pg_hba.conf file which is the configuration file for the database server.  You’ll get a readout that shows various configuration settings. What you’re looking for is that besides the default 127.0.0.1 address that the IP address you entered in the steps above is listed.  If it is, you’ve properly configured the server!

The Resolve user manual contains more trouble shooting commands for doing things like deleting or restoring a previous .conf file if you should happen to screw up!

Create & Connect To A Shared Database

Now that you’ve setup the database server, the next thing to do is to actually create a shared database.

You can do this from any computer in your facility running Resolve, but since you’re probably already on computer that is the database server, simply plug in your dongle and launch DaVinci Resolve.

  • After the application boots up on the login screen, click the database button in the lower right to setup a new database.
  • In the database window click the + (plus) button to create a new database.
  • Just like you normally do give the database a name, and label.
  • Make sure in the database driver pull-down you choose QPSQL.
  • In the IP field enter the IP of the database server.  In my example from above that would be 10.0.70.218
  • Create the database
shared database
After configuring the database server you can create or connect to a new an existing database by entering the database server IP.

 

If everything worked as it should, the database should appear in the list of databases, double click on the database to make it active (connect to it).

At this point, you can pull the dongle from the database server and put it back on the machine it belongs to.

On any other computer (remember you can create a new shared database from any computer as long as you use the IP of the server) launch Resolve and go to the database list.

In the upper right corner click the the menu and choose Connect.  Enter all the same information – name, label and IP and click connect.

You should now have access to the shared database on any computer you want!

The Mac, Windows, Linux File Path Conundrum

You have your shared database working, shared storage is humming and things can’t be better!

Well, there is one thing that I’ve encountered recently even with all this perfection that nearly killed my otherwise great setup.

For years we’ve been a Mac only facility. Because of this, every computer had the same file path to shared storage which looked something like:

/Volumes/SAN/

All media as well as cache files, gallery stills and so on pointed to this location.

No matter what computer I worked on, media automatically connected, cache files worked and connected no matter what computer they were created from and stills and saved corrections worked as they should.

Well, then I decided to get fancy and add a kick ass windows Machine, and dual purpose that computer as a Linux Resolve setup too.

That’s when I got really frustrated.

Different operating systems of course use different file paths so the same SAN on Windows mounted as:

S:\

On Linux this was different still:

/Volumes/Media/SAN

So when moving from machine to machine I had to manually relink media, and because the file paths were different, if I had cached something in one place and now opened that project on a different machine the file path was now different – I lost those cache files!

That last bit more than anything was HUGELY frustrating.

If I had spent 30 min to cache a timeline I had to repeat that 30min caching on another computer.

Luckily, I have some pretty smart friends.

Good pal and friend of Mixing Light Juan Salvo turned me on to a way to fix this problem that was right in front of me all along!

Mapped Mounts!

The best way to think of mapped mounts is as a file path substitution.

If on the Mac you have the path /Volumes/SAN you could add as Mapped Mount the Windows equivalent  S:\ (or what ever it is on your machine).

In similar fashion, you can enter the Mac file path on a Windows Machine.

Configuring this ‘translation’ is simple.

  • Launch Resolve and open the Resolve Preferences
  • On the Media Storage tab locate the native mount point /Volumes/SAN for example and then double click in the column labeled Mapped Mount and enter the Windows or Linux equivalent.
  • Repeat this process on other computers in your shop.
mappedmount
The default map mount for my SAN S:\ on Windows is mapped to /Volumes/tz8 on the Mac/Linux. TZ8 is the name of the SAN.

 

With this Mount Mapping in place I can go back to the way it was prior to have different operating systems in the mix, and just like my previous Mac only setup, I don’t have to relink, or re-cache files.

This simple fix has been a huge overall workflow enhancement.

Pheww. Let Me Get Back To Part 2 Of The Z840 Review

Ok, now that I have all this database stuff out of my system, I’ll get back to part 2 of my z840 review.

As a teaser, for the most part, I’ve been LOVING the z840 & Windows 8.1 is actually pretty good too, but I have some issues that I’ll detail in part 2.

But overall, for running Resolve the z840 does indeed feel just like running a Mac, which I know a lot of you are concerned about.

More soon!

Any questions on database setup or Mapped Mounts please used the comments below!

– Robbie

P.S. Dan way back at the very start of Mixing Light (Insight 12!) did an quick Insight on creating a shared database. His direct method of editing the pg_hba.conf and replacing the default 127.0.0.1/32  to 0.0.0.0/0 should still work, but I prefer adding a new IP range (as detailed above) instead of replacing the local IP.

Comments