Backup Database Script

Development
1

Hey hey fellow AYON enthusiasts,

TLDR;
I want to share my backup and restore scripts for my AYON database because I encountered some issues with the official method.

I learned that the v flag in docker dompose down -v doesn’t stand for verbose the hard way. As a result I lost my database volumes… Thankfully I had backups! However, after restoring, I encountered this error when trying to publish assets in Maya:

  ayon_api.exceptions.FailedOperations: Operation "..." failed with data:
  {
      "type": "update",
      "entityType": "product",
      "entityId": "...",
      "data": {
          "productBaseType": "model"
      }
  }
  Detail: column "product_base_type" of relation "products" does not exist.

The Problem

After investigating, I found two issues that occur after restoring a database backup:

  1. Missing product_base_type Column

The product_base_type column in the products table (within each project_* schema) is apparently created dynamically by the AYON server at some point after the database schema is initially set up. When you restore from a backup that was created before this column existed, or when the column wasn’t included in the dump, you get the error above.

  1. PostgreSQL Sequence Desync (creation_order_seq)

This is a classic PostgreSQL restore issue. Each project schema has multiple sequences for generating creation_order values:

  • folders_creation_order_seq
  • products_creation_order_seq
  • versions_creation_order_seq
  • representations_creation_order_seq
  • tasks_creation_order_seq
  • workfiles_creation_order_seq
  • … and more

When you restore a backup using pg_dump (plain SQL format), the sequences are restored with their last_value from the time of backup. However, after the restore, PostgreSQL doesn’t automatically update these
sequences based on the actual maximum values in the tables. This can cause:

  • Duplicate key errors
  • Silent failures
  • Unexpected behavior when creating new entities

The Solution

I (which means that claude code helped a lot…) wrote two bash scripts to handle automated backups and proper restoration with automatic repair:

ayon_backup.sh

  • Weekly full database backups (Sundays)
  • Daily per-project backups using the Makefile’s dump target
  • Automatic cleanup with configurable retention
  • Lock mechanism to prevent concurrent runs
  • Backup validation

restore_backup.sh

  • Restore full or project-specific backups
  • Automatic database repair after restore:
    • Adds missing product_base_type column to all project schemas
    • Populates it with the value from product_type for existing products
    • Resets all creation_order_seq sequences to MAX(creation_order) + 1
  • Dry-run mode to preview changes
  • Can also repair an existing database without restoring (useful if you already restored and have issues)

Usage

  # Repair current database (no restore)
  ./restore_backup.sh --repair

  # Dry-run to see what would be fixed
  ./restore_backup.sh --repair --dry-run

  # Restore full backup + automatic repair
  ./restore_backup.sh --full backups/20250201_full_backup.sql.gz

  # Restore single project + automatic repair
  ./restore_backup.sh --project backups/20250201_myproject.sql.gz

  # List available backups
  ./restore_backup.sh --list

I’m sharing these scripts with the community. Use at your own risk - no warranty! Please review and adapt the paths to your setup before using.

Hope this helps someone else who runs into the same problem!

Cheers,
Daniel

ayon_backup.sh.txt (5.3 KB)
restore_backup.sh.txt (12.8 KB)

1 Like
2

Cool thanks, probably not as convenient, but I recently added dump entire and restore entire to the manage scripts in ayon docker… if this ever gets merged…Add dump-entire and restore-entire convenience functions to the helper scripts by nebukadhezer · Pull Request #39 · ynput/ayon-docker · GitHub maybe it is worth to get it in there too ?

1 Like
3

Hello. Running make setup after restoring from a backup will trigger database migration script, that supports all database changes from ayon 1.0.0 to this day :slight_smile:

2 Likes
4

Hey there, good to know – wish I had known that before going down the rabbit hole :sweat_smile: Maybe it’s worth adding to the documentation? Couldn’t find it in AYON Server Provisioning - AYON

1 Like