PostgreSQL Database (Server Edition) Backup Procedure

Post Reply
Al_M
Posts: 23
Joined: 21 Jun 19 14:09
Location: Kansas City, USA

PostgreSQL Database (Server Edition) Backup Procedure

Post by Al_M » 17 Jun 20 21:39

I have upgraded from the single-user to the Server Edition for PostgreSQL. (reference this topic --> Moving to Server Edition)

I am up and running with a server node which hosts the PostgreSQL database (using Postgre.app) and PSu (client) application on a MacPro.
Also have a MacBook Pro with just the PSu client software that connects to the MacPro database server.

Great. Now I need to come up with a backup strategy. In the single-user edition this was fairly simple. Just backup (make copies) of the Catalog folder (or just the catalog files: photosupreme.cat.db and photosupreme.thumbs.db). Easy peasy.
This was automated for me since I use Apple's Time machine (backup #1). Also replicated onto my Synology NAS (backup #2) and Dropbox (backup #3). And finally bootable system backup using SuperDuper (backup #4). Yes I'm that paranoid.

Now in the server edition, things seem a bit more complicated.
  • 1. Is the Backup function in PSu not enough in the server edition?

    It seems to generate the same files as in the single-user edition, except the actual db file is much smaller in size. My single-user catalog backup file was around 1.67 GB, while the server edition catalog backup file is now 181 MB. About 10x smaller. I believe the difference is attributable to the fact the server file is actually a text file while the single-user file was a SQLite db file.
  • 2. What would be the difference between using the PSu Backup function versus a tool like the pgAdmin Backup or the native pgdump command in PostgreSQL command line interface?
  • 3. Any thoughts to other tools used? I have seen SQL database management and backup tools in the net such as Barman, SQLBak and others. They seem like overkill to me. These tools seem like they are designed for much larger corporate database installations. I don't want to complicate the issue.
  • 4. Anyone using the Postgre server version willing to share their backup procedure/strategy?
Right now I just want to get started with a proven backup/restore procedure. Then I would like to investigate how to automate it for unattended (scheduled) backups.
Photo Supreme V5 Server Edition (build 2971)
PostgreSQL 12
macOS 10.15.4 (Catalina)
Mac Pro (Late 2013), 3.0 GHz 8-Core Xeon E5, 64 GB DDR3
MacBook Pro (Mid 2015), 2.5 GHz Quad-Core i7, 16 GB DDR3L

Hert
Posts: 6216
Joined: 13 Sep 03 7:24

Re: PostgreSQL Database (Server Edition) Backup Procedure

Post by Hert » 18 Jun 20 8:33

As a hobby photographer and user of PSU myself, I'm also using the PostgreSQL version of Photo Supreme Server. I have my database installed on a simple home Windows "Server" (an Intel NUC 8gen, i3, running Win10 Pro). The database is being backed up to an external hard drive with this batch file.

Code: Select all

@ECHO OFF
SET PGPASSWORD=<<my postgres password here>>
"C:\Program Files\PostgreSQL\10\bin\pg_dump.exe" --host localhost --port 5432 --dbname photosupreme --file="D:\Backups\PostgreSQL\photosupreme.backup" --encoding "UTF8" --clean --create --blobs --format custom --username=postgres
In Windows Task Scheduler, I've scheduled this batch to run every night at 2am. Another scheduled task backs up the backup of the catalog database to an off-site location in the cloud.

NOTE; I'm not backing up the thumbs database because when disaster happens, I can always rebuild the thumbs/previews database. That takes away the need for me to backup the thumbs database.
PS. My needs can differ from yours, for example if the thumbs/previews would take weeks to rebuild due to high volumes then I would backup the thumbs too.
This is a User-to-User forum which means that users post questions here for other users.
Feature requests, change suggestions, or bugs can be logged in the ticketing system

Al_M
Posts: 23
Joined: 21 Jun 19 14:09
Location: Kansas City, USA

Re: PostgreSQL Database (Server Edition) Backup Procedure

Post by Al_M » 18 Jun 20 13:55

Hert,

Thanks for the information. Since I'm on a Mac, I will have to modify the details, but use a similar method.

While using the pgAdmin tool to test making backups, I discovered that if you hit the "details" button, it will give you the command that it generated (using the options you previously set).

In my case, I used all defaults and this is what was generated:

Code: Select all

/Applications/pgAdmin 4.app/Contents/SharedSupport/pg_dump --file "/Users/almartire/Documents/PSu PostgreSQL dbBAKs/photosupreme_dbBAK_061720_2.sql" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs "photosupreme"
I will use this command as the basis for my automated backup (script).

But, can you answer this question: What is the difference between using the PSu Backup function versus a tool like the pgAdmin Backup or the native pgdump command in PostgreSQL?

Assuming they are equivalent (in that either could be used to restore the database in case of disaster), one advantage I see to using the pg_dump (pgAdmin) route is that you can automate it, where I see no easy way to automate the built-in PSu Backup tool. Unless you were to implement such options into the tool... :D Maybe a feature request???

Another advantage of the pg_dump command is that you can specify many options. If you are experienced enough to know what they do.
This could actually be a disadvantage for an inexperienced used, such as myself. In the pg_dump documentation, I counted no less than 50 options that could be set in the execution of the command.

For what it's worth, I agree about rebuilding the thumbnail database. I'm currently at about 40,000+ images and rebuilding the thumbnails took about 10-15 minutes (at 2560 resolution), when I migrated from single-user to the server edition. Not a big deal for me.
Photo Supreme V5 Server Edition (build 2971)
PostgreSQL 12
macOS 10.15.4 (Catalina)
Mac Pro (Late 2013), 3.0 GHz 8-Core Xeon E5, 64 GB DDR3
MacBook Pro (Mid 2015), 2.5 GHz Quad-Core i7, 16 GB DDR3L

Hert
Posts: 6216
Joined: 13 Sep 03 7:24

Re: PostgreSQL Database (Server Edition) Backup Procedure

Post by Hert » 18 Jun 20 14:02

What is the difference between using the PSu Backup function versus a tool like the pgAdmin Backup or the native pgdump command in PostgreSQL?
While the end result is the same: a backup file is created, the Photo Supreme backup uses its own backup routine and the PostgreSQL backup uses its own backup format. They are not compatible. But since the PG backup is native, it works a multitude faster than the PSU backup. That for me is reason enough.
This is a User-to-User forum which means that users post questions here for other users.
Feature requests, change suggestions, or bugs can be logged in the ticketing system

Post Reply