DEV Community

Aravin
Aravin

Posted on

Backup Concepts

#postgres #tutorial #cloud #database

This is Part 6 of the BigAnimal in PostgreSQL series.

👉 Previous: Access Model

In EDB BigAnimal, backups are fully managed, but we can view, configure, and trigger them manually if needed. Here’s how you can take and manage backups step-by-step

1. Open Backup Section

  • Go to the BigAnimal Console
  • Click Clusters → Select your cluster
  • From the top tabs, choose Backups

2. View Backup Configuration

On the Backups page, will see:

  • Automatic backup schedule — usually daily (default retention: 7–30 days)
  • Last backup status — Success, Failed, or In progress
  • Retention policy
  • Backup type — Full or Incremental

BigAnimal automatically performs continuous backups using WAL archiving + snapshots (depending on the cloud provider).

3. Take Manual Backup

To trigger an on-demand backup, follow these steps:

  1. Go to Clusters → Backups
  2. Click Create Backup
  3. Choose:
    • Backup type: Full
    • Description (optional)
  4. Click Start Backup

The status will appear as “Running” until the backup process is complete.

4. CLI Method (Optional)

If we use the BigAnimal CLI, we can trigger a manual backup like this:

biganimal backup create --cluster-id <cluster_id> --description "Manual backup before upgrade"
Enter fullscreen mode Exit fullscreen mode

To check progress:

biganimal backup list --cluster-id <cluster_id>
Enter fullscreen mode Exit fullscreen mode

5. Restore from Backup

To restore (Point-in-Time or Full):

biganimal cluster restore --cluster-id <cluster_id> --backup-id <backup_id>
Enter fullscreen mode Exit fullscreen mode

We can also do this via the Console → Backups → Restore.

6. Verify Backups

In the Console → Backups tab, you can view:

  • Last backup time & duration
  • Result (Success/Failure)
  • Backup ID
  • Size
  • Restore option

Table Backup and Restore

In PostgreSQL / EDB BigAnimal, backups are typically cluster- or database-level, but you can absolutely take a specific table backup and restore it using standard PostgreSQL tools like pg_dump and psql — even inside BigAnimal.

1. Using pg_dump (Recommended) 

pg_dump -h <hostname> -p 5432 -U <username> -d <database_name> -t <schema_name.table_name> -Fc -f table_backup.dump
Enter fullscreen mode Exit fullscreen mode

Restore the Table Backup

Use pg_restore to restore into the same or another database.

pg_restore -h <hostname> -p 5432 -U <username> -d <database_name> -t <schema_name.table_name> table_backup.dump
Enter fullscreen mode Exit fullscreen mode

If the table already exists, you can:

  • Drop it before restore, or
  • Use the --clean flag to automatically drop & recreate it: 
pg_restore --clean -t public.orders -d salesdb orders_table_backup.dump
Enter fullscreen mode Exit fullscreen mode

2. Export as SQL File (Plain Text) 

pg_dump -h <hostname> -U <username> -d <database_name> -t public.orders -Fp -f orders_table_backup.sql
Enter fullscreen mode Exit fullscreen mode

Then restore:

psql -h <hostname> -U <username> -d <database_name> -f orders_table_backup.sql
Enter fullscreen mode Exit fullscreen mode

This method is simpler but slower for very large tables.

3. Using EDB BigAnimal Console

BigAnimal’s web console does not yet support per-table backup —
But We can:

  • Connect to your cluster via pgAdmin or psql
  • Run the above pg_dump command from your local terminal
  • Store the .dump file securely (e.g., S3 or Azure Blob)

Optional: Backup with Data Filter
We can even export only certain rows:

pg_dump -h mycluster.biganimal.io -U edb -d salesdb \
  -t public.orders --data-only \
  --where="order_date >= '2025-01-01'" \
  -Fc -f recent_orders.dump
Enter fullscreen mode Exit fullscreen mode

Verification
To verify what’s inside the dump file:

pg_restore -l table_backup.dump
Enter fullscreen mode Exit fullscreen mode

This lists the contents (schema, table, indexes, etc.).

Top comments (0)