Automated backup of a PostgreSQL database?

Right now I have different workstations running disk databases with separate rsync cron jobs to back up each individual database regularly. I'm considering acquiring some more dongles and switching to a PostgreSQL database for project sharing across workstations.

I'm wondering if there's an automatic way of backing up the PostgreSQL database, perhaps with a cron job, without having to manually click a button in Resolve's GUI whenever I remember to do so. How can I best accomplish this?

I use this across three machines sharing a database

http://postgresql-backup.com

I have my database on a Windows machine.

Hello.
I have written a simple script for automating daily backups of a Davinci Resolve Studio PSQL dabatase. The tool is written for Mac, Windows and Linux.
No SQL database proficiencies are required to use the tool; just willingness to edit a few paths in a configuration file. The tool can be found here on GitHub:

https://github.com/walter-arrighetti/ResolveDB_backup

It runs a full dump of the PostgreSQL database each night at 2 o'clock, compresses it (7-Zip for Windows, zipped-tar otherwise) and copies/moves them to a separate folder (e.g. a network redundant volume).
Each day of the week the archive will overwrite the archive from the same day of the previous week (for a total retention time of one week).
It can be modified by editing variables at the begninning of the code; almost no scripting capabilities required, but the tool must be installed as the full superuser account ('root' on Linux/macOS, 'Administrator' on Windows).

I use this across three machines sharing a database

http://postgresql-backup.com

I have my database on a Windows machine.

Hi,

I"m trying out Postgresql-Backup Free version to backup a shared database on a virtual Windows 7.

The backup goes fine but I'm having a hard time restoring it in DV14.2. Are there any specific setting that are required in order for the backup to work with DR?

Many thanks!
Boris

What’s going wrong when you try to restore?


Sent from my iPhone using Tapatalk

What’s going wrong when you try to restore?


Sent from my iPhone using Tapatalk

What I do is change the *.sql to *.backup so that DR Project Server would see it. When trying to restore it I get DR crashing.

Im pretty sure this is not the right way of doing it so any advice would be appreciated.

Many thanks,
Boris

You need to restore it in the pgAdmin software - not the resolve software.

You need to restore it in the pgAdmin software - not the resolve software.

I have tried using "pgAdmin" but can't figure out how to do it.

I'd really appreciate it if you could help me with that.

Thanks,
Boris

https://stackoverflow.com/questions/187 ... ng-pgadmin

Boris, it might be easier for you to use Igor's tool for Windows: http://liftgammagain.com/forum/index.ph ... tool.8466/

With Igor's tool, you should be able to just change the .sqlc extension to .backup for Resolve's GUI.

Thanks a lot, guys! Managed to do it using pgAdmin.

Best,
Boris

My original suggestion was quite old, if i was to redo this I would probably use Igor's method too.

I recently switched over to using PostgreSQL database on windows and found that the software recommended by Paul is excellent.

I have 3 workstations running off the same database (on W10). I did an exploration of automated backup systems and did not have the knowledge or patience to work with pgAdmin, to script this or use some of the available scripting methods from Igor et al.

I independently arrived at using SQLBackupAndFTP - which is the product recommended by Paul. There is a free version without the email capability, but the paid lite version is like $30 + a $20 maintenance subscription.

It allows you to run backups on a schedule and emails you on completion. It will do the restore for you which I have tested without issue. The trick is to make a new database in the resolve database manager and restore to that database.

As someone who was deeply involved with PostgreSQL for about 2 decades: There is really no need at all to purchase any additional software or use pgAdmin. PG has everything you could ever need build-in, just read its very good documentation and get a basic understand of its BC/DR options.

Next to periodic full dumps in custom format, consider PITR and log streaming to a hot/cold standby for availability.

Thanks. The point for me, and likely others, is that I definitely don't have 20 years of Postgres experience and unfortunately don't have the time to develop the kind of expertise that you demonstrate here.

you lost me at "a basic understand of its BC/DR options."

And:

"Next to periodic full dumps in custom format, consider PITR and log streaming to a hot/cold standby for availability."

This all just sounds intriguing, but it also sounds a bit like a foreign language to me.

$50 vs the time to figure out the intricacies of PostgreSQL and scripting at my hourly - the $50 is a far more economical solution.

- J

It is hilarious that this thread got bumped back into view for me.

For anyone who finds this thread, know that I was never quite satisfied with the backup tools I found, so I ended up scripting my own: https://github.com/sethgoldin/davinci-r ... flow-tools

For Mac and Linux, those shell scripts integrate with the OS system tools and will set into motion automatic backups and automatic optimizations.

As long as you are confident that your backups and restores work and will keep working, all is good.

A lazy alternate is to just use file system snapshots. PG is fine with that on any platform. VSS on Windows, and LVM on Linux.

So you could also just use Marcium Reflect Free or Veeam Agent Free and Backup your entire system, PG included.

Hey, Peter. Yes. I did a bunch of testing and have backed up and restored successfully on a couple machines.

While there is no replacement for the kind of knowledge you would have, I sleep better an night with this solution than I ever did with disk databases and manual .drp and database backups.

It is hilarious that this thread got bumped back into view for me.

For anyone who finds this thread, know that I was never quite satisfied with the backup tools I found, so I ended up scripting my own: https://github.com/sethgoldin/davinci-r ... flow-tools

For Mac and Linux, those shell scripts integrate with the OS system tools and will set into motion automatic backups and automatic optimizations.

Hi Seth. We tried to run your script with a DaVinci Resolve 17 postgresql database, but we couldn't get it to work. We got a backup file with 0 bytes. We're running Catalina, so I don't know if the issue you mentioned with Bigsur in github could also be happening here.
If we could share some light it would be great.

[mention]joanareis08 [/mention] DM me with some more specific information and I can try to help.


Sent from my iPhone using Tapatalk

Hey Seth, You're the man! Thanks ahead of time for creating this script. By chance have you tested this with version 18 databases yet?

There is a bunch of PostgreSQL backup software you can find n the internet. Most are Windows and Linux-based.
https://sqlbackupandftp.com/postgresql-backup

This software can backup and restore and in a specific schedule. It doesn't need to stop the service.

A lazy alternate is to just use file system snapshots. PG is fine with that on any platform. VSS on Windows, and LVM on Linux.

It's not safe to snapshot a running Postgres server. As long as it's fully shut down you ought to be okay, though.

[mention]CUBuffskier [/mention]2.0 beta is out for Resolve 18 and PostgreSQL 13: https://github.com/sethgoldin/davinci-r ... g/2.0-beta


Sent from my iPhone using Tapatalk

It is hilarious that this thread got bumped back into view for me.

Hi Seth. We tried to run your script with a DaVinci Resolve 17 postgresql database, but we couldn't get it to work. We got a backup file with 0 bytes. We're running Catalina, so I don't know if the issue you mentioned with Bigsur in github could also be happening here.
If we could share some light it would be great.

.

That script is only meant for Resolve project server running at PostgreSQL 13 and not v 9.5... if you started with project server at Resolve 17 or older, then your server is running PostgreSQL 9.5, you will have to upgrade for this to work.

I did, and it works like a charm.