Restore Projects with PSQL

Hello there,

recently, we switched from local databases to PSQL, to enable collaborative workflows for our editors.
PSQL is running in version 13.x on Debian 11 and there are no big issues whatsoever.

There is one thing though, where i feel we still need some improvement and that is the way we handle database rollbacks, in case a user deleted something in a project for example.
When that happens, i am able to rollback the Database to pretty much any point in time, due to the backup system we have in place but unfortunately that means rolling back the entire database cluster instead of just one particular project.

Is there any way to easily restore just one particular resolve project from a database backup instead of the entire database or even the entire cluster?

I tried to do some digging on my own and the table SM_Project seems to contain all the projects of that particular database but i didn't go any further than that.

Some of you might probably suggest setting up automatic project backups within resolve but to my knowledge they are not working for shared projects and to be honest, they always (seem to) have an impact on the user experience, especially when they are created frequently. (resolve slowing down, loading circle, that appears to the end-user as resolve having crashed, etc.)

Thank you in advance for any help or ideas you can provide.

[Dwaine Maggart]

Correct that Project Backups do not work in Collab mode.

Not aware of a way to do project level restores from a PSQL backup. Not saying it's impossible, just don't know of a way to do it.

We generally advise people working on important projects to make Project Export drps at the end of each workday. This would be one way to manage this.

Hello Dwaine,

thanks for your reply.
Unfortunately Project Exports on a daily basis won`t suffice all the time.
For example, when my colleague creates a DRP at the end of her working day and for some reason she deletes a timeline or several bins in the middle, or near the end of her next working day,
(already happened before) a lot of work would be lost by just relying on daily project exports.

With the workflow we have in place, we are actually able to use Postgresql's WAL to make point in time recoveries, which work fine for us. The limitation unfortunately is, they recover the entire database cluster or a specific database which is not always what you want.

I am willing to play around and invest some time in finding out a way to restore single projects in a PSQL environment by leveraging pgadmin or the command line.

This is just me throwing out the idea and its probably not working but just to have somewhere to start. The table SM_Project i mentioned before doesn't contain all the data/rows that are relevant for one particular project, or does it? I assume its not as easy as restoring the row in the table SM_Project that represents the project you wanna recover, from a database backup, to effectively get a project restore, right?
I am just guessing here but i would think there are a lot more tables involved that build the entire project. Is my way of thinking correct?

Thank you.

To build something like that you'd need someone who understands PSQL really well. They'd need to reverse engineer the schema. This is just not practical and it would likely only last as long as the next major Resolve update.

I understand your pain. We face the exact same issue. What we do is use multiple databases. We back them up four times a day. That way if we need to pull something from a backup we don't need to restore hundreds of current projects but only a subset of those. It's not ideal but it's managable.

You could look into automating DRP export. The only thing is that that would tie up one instance of Resolve on the LAN. It could be a headless machine though. You can use this as a starting point: https://github.com/IgorRidanovic/DaVinc ... rtProjects

Hello Igor,

thanks you Igor.
The automatic DRP export leveraging the API is a good idea. I would like give it a try as an additional "safety net" if you will.

I will set up an old workstation of us as a dedicated machine to handle the automatic DRP exports.

Have you ever considered PostgreSQLs PITR functionality for your backup needs?
pgbackrest is an amazing tool to create backups and LOG ship the WALs to a NAS share for example.
(or on S3 for offsite backups if thats what you re looking for)

With that, you could create a backup schedule creating full backups on the weekend and differential/incremental backups within the regular working days and WAL replay to a specific point in time whenever you need to rollback the DB cluster.

That in conjuction with pg_dumps seems like a good backup workflow for me.
The automatic DRP exports are still pretty interesting because they could be imported back by our editors instead of relying on me or any other IT person to get the job done.

@Igor

Allright. I tried your script from Github, added parenthesis to the print statements so that they are according to python 3.6 syntax and added a path in the script file.

Unfortunately everytime i start the script, i get the following error:

AttributeError: 'NoneType' object has no attribute 'GetProjectManager'

It doesn`t matter if i execute the script from within resolve or if i start it externally.

Entering commands from your youtube video directly into the console works fine though.
(

)

This seems to be a limitation of the free resolve version i am currently using on this client, am i correct?

Thank you.

I don’t think it’s the Resolve version.
Rather, the application object sounds like it’s not set up which probably means the script isn’t running in the right environment.

I haven’t tried this recently but I recall setting up the right environment is not as trivial as one might hope.


Sent from my iPhone using Tapatalk

You are probably talking about setting up the environment variables so that the API can be properly loaded, right? I might have overlooked something but i pretty much followed this guide here:

https://deric.github.io/DaVinciResolve-API-Docs/

All the env variables are set up properly in windows and echo %VARIABLE% returns the correct path for
RESOLVE_SCRIPT_API and RESOLVE_SCRIPT_LIB.

I came to the conclusion that its a limitation of using the free version of resolve due to this thread.

viewtopic.php?f=21&t=121798

Its in regards to ResolveV16 but i assume things haven`t changed with scripting in V18.
Just wanted to get this confirmed so i know that i might have to purchase another license for what i wanna achieve.

Thank you.

No, it should work in the free Resolve as well. The resolve object is conveniently instantiated for you by Resolve since maybe version 16. The script should work from the script dropdown menu if you remove these two lines:

Code: Select all

from python_get_resolve import GetResolve
resolve = GetResolve()

Thanks Igor. It now works from within Resolve.
Unfortunately i can`t find a way to switch between different databases and from the API documentation,
it seems there isn't one. I can switch to a subfolder within a database (OpenFolder(folderName) and export DRP files from there but eventually the goal was to export projects from several databases automatically.

This was recently added: SetCurrentDatabase()

Thanks for all the valuable information. That helps a lot.
The preferable solution clearly is creating database dumps (we are doing this too, of course) but being able to re-import earlier version of your current project is super convenient for our editors.

I will experiment with it a little bit and if it works fine, i would probably buy another license to eventually be able to run the script externally with resolve running headless and the script running via the task scheduler in windows.

Thanks again.

I'm toying with an idea to setup a seat of Resolve on a VM just to keep iterating databases and projects and saving DRPs. Restoring databases from dumps is hard for people to wrap their heads around. Though it ahs saved us big time on several occasions.