Automatic Database Management

Automatic Database Management

October 14, 2014

Tired of manually backing up your Resolve? Using some handy scripts Dan shows you how to fully automate the process


We all have the best intentions when it comes to backing up our systems and making sure our work is safe but the reality is that we’re human and eventually our good intentions will ware off and the backups will slow down and stop.

So why not automate your backups!

This is aimed at Resolve users but you MUST be using the PostgreSQL database option.

The reason for this is the database allows access on a script level and we’re going to automate this.

This is an advanced insight!

This insight is based on setting up a shell script using cron. I’m going to try and explain as I go but you will need to be comfortable using the terminal to make this work.

TEST TEST TEST!

Make sure you test this system before you rely on it.

I use this system and only this for my backups but we’ve tested it multiple times and it works for us. Please do the same! We’re going to start by building a shell script that backups your database and then learn how to automate that.

Resources

Check out the video insight below to find out how I set up my auto backup and then use the resources below as needed.

Member Content

Sorry... the rest of this content is for members only. You'll need to login or sign up to continue (we hope you do!).

Membership options
Member Login

Comments

12 thoughts on “Automatic Database Management”

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

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

      1. 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. 😉

        1. 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!

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

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

      1. 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!

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

Leave a Reply

Hundreds of Free Tutorials

Get full access to our entire library of 900+ color tutorials for an entire week!


Start Your Free Trial
Loading...