Automatic Database Management

Very nice. As a guy who has never used neither the Terminal nor Cron, I’m glad to say I was able to replicate your step-by-step guide, and achieve the same results. So thanks to have made it that clear.

But I have a question. Playing a little bit with the database manager for testing purposes, I have created then deleted several test databases. And noticed one thing. If I have deleted a previous database called ‘test’ (using the ‘-‘ button at the bottom of the screen), Resolve won’t allow me to use the name ‘test’ anymore, giving this error message “database ‘test’ already exists QPSQL: Unable to create query”. So, is there a place to manage the data more deeply, or I simply won’t be able to use the database names I’ve randomly picked for my tests ? By the way, I’m wondering if it is not the reason you’re using number for databases name (‘606’), because when you restore a database you have to change the name to avoid replicate (so you increment the database number) ?

Thanks again for this cool Insight.

Hey Dan, Thanks for this easy to follow guide. Just wondering if running/saving a resolve project during this process can cause any issues? At the moment my current resolve database is around 4.5gb, so it takes a little while to backup.

Hey Matt,

I normally back mine up around 3am so I can’t say I know for definite. My gut instinct is that it could slow your machine down temporarily.

Have you thought about starting a new Resolve database? We tend to start a new DB here at Smoke and Mirrors every 6 months or so to avoid massive DBs.

You can still easily access the old DB via the DB manager but it just means its not actively been written or read from.

The other option is to use the script without cron and run it at the end of the day manually? You could keep it on your desktop and run it when it feels like the best time to.

Hope this helps!

D

Hey Christophe,

Very good question. If you notice in Resolve when you hit the minus button it actually says disconnect rather than delete database. The database will live on the background which is why Resolve is having issues.

I haven’t thought about this in too much detail as I normally only restore DB’s when things have gone wrong!

Let me do a little bit of digging into the PostgreSQL world and I’m sure I can find out a way of deleting DBs forever!

D

Hey Dan, Thanks for the tip. Yeah I really need to start a new DB.
I think that idea of running it manually will suite me personally better, I’ll pop it in the scripts menu in the menu bar and run it whenever I do my other backups :-).

You the man!

Thank you Dan. The problem is real because when restoring a corrupted database, the DB name HAS to be changed (otherwise Resolve tells that the database already exists, and indeed minus sign says ‘disconnect’ and not ‘delete’). This means that when a DB is restored (and thus its named changed), well, one has to remember to update the terminal script that won’t work anymore (wrong name). Thanks again for digging into this. 😉

Glad it helps man! Hopefully it comes in useful someday!

Very good thoughts sir. I think I should update the insight to account for this.

I normally give it a new name like Resolve_2_OCT_2014 when building a new database. Resolve two is my machine at S&M and then always add a month and year to the end.

Updating the script is probably the bit I forget about the most to be honest! It normally happens a few weeks after we change database and I remember at 3am in a panic haha!

Thanks for putting this together, Dan. I realize that this is now over three years old, but I’ve been looking for some help in automating backups for PostgreSQL databases.

For Resolve 14.0.0.078 on macOS 10.12.6, I found Igor Riđanović’s Python script, and it works, but it just creates .sqlc files, and I don’t know what to do with those. I realize there’s a way to restore those .sqlc files with pgAdmin III, but I don’t have any other experience with PostgreSQL database management, and don’t really understand where to start. What I like about your method here is that your bash script generates .backup files, which can easily be imported with Resolve’s database manager again on a totally new boot disk with a totally fresh installation of PostgreSQL.

There are a couple things that have changed in three years, though:
–cron is deprecated in macOS Sierra, and instead, Apple recommends scheduling via launchd. Creating a properly formatted XML file with a plist extension and putting it in ~/Library/LaunchAgents isn’t too difficult, but it’s quite different than cron.
-Resolve now uses PostgreSQL 9.5, instead of 8.4, so I’m curious as to whether the specific paths from this insight will still work.

So, how are you automating your PostgreSQL database backups now? Have you switched to launchd? I for one would really appreciate a new insight on the topic.

For anyone who finds this comment, but is looking for a modern tool for macOS Sierra and Resolve 14, I’ve created a template launchd agent and bash script: https://github.com/sethgoldin/backup-resolve-postgresql

Seth this is great! I’ve pinged @ML_DanMoran:disqus about this. Also I’ve pinged contributor Joey D’Anna as scripting is right in his wheel house!

I’ve updated this to make it much, much easier–one very easy script that creates, loads, and starts launchd user agents for backups AND optimizations. I developed it on macOS 10.12.6, but I’ll test it with 10.13 soon, and will soon port it to Linux!

https://github.com/sethgoldin/davinci-resolve-postgresql-workflow-tools