feat(postgresql): Add schema filter for pg_dump and pg_restore #368

New Issue

MrUnknownDE · 2026-04-05T16:16:18+02:00

MrUnknownDE commented

2026-04-05 16:16:18 +02:00

Originally created by @leonardocouy on 12/10/2025

Summary

Add an optional "Schemas" field to PostgreSQL database settings, allowing users to specify which schemas to include in backups and restores using a comma-separated list.

Motivation

When backing up managed PostgreSQL databases or databases with restricted internal schemas, the backup user may not have access to all schemas. This causes pg_dump to fail with permission errors like:

pg_dump: error: permission denied for schema internal_schema

By filtering backups to only include specific schemas (e.g., public), users can successfully backup their databases without permission errors.

My use case was to back up only the public and drizzle schemas from the Supabase DB, because this one doesn't allow backing up other internal schemas.

Changes

Backend

Migration: Add schemas column to postgresql_databases table
Model: Add Schemas field to PostgresqlDatabase struct
Backup: Modify buildPgDumpArgs() to append --schema flags
Restore: Modify pg_restore args to support --schema filtering

Frontend

Add "Schemas" input field with tooltip in database edit form
Display schemas in read-only database view
Increase restore modal width to accommodate new field

Usage

Setting the Schemas field to public,custom_schema generates:

# Backup
pg_dump ... --schema public --schema custom_schema

# Restore
pg_restore ... --schema public --schema custom_schema

Leave the field empty for full database backup/restore (default behavior).

Screenshots

Testing

Tested backup with schema filtering
Tested restore with schema filtering
Frontend builds without errors
Backend builds without errors

How to Test

If you'd like to test this feature, a pre-built Docker image is available:

docker pull leoflores/postgresus:latest

Then run it with your preferred configuration or build locally or replace the image in your existing Postgresus deployment.

*Originally created by @leonardocouy on 12/10/2025* ## Summary Add an optional "Schemas" field to PostgreSQL database settings, allowing users to specify which schemas to include in backups and restores using a comma-separated list. ## Motivation When backing up managed PostgreSQL databases or databases with restricted internal schemas, the backup user may not have access to all schemas. This causes `pg_dump` to fail with permission errors like: ``` pg_dump: error: permission denied for schema internal_schema ``` By filtering backups to only include specific schemas (e.g., `public`), users can successfully backup their databases without permission errors. My use case was to back up only the public and drizzle schemas from the Supabase DB, because this one doesn't allow backing up other internal schemas. ## Changes ### Backend - **Migration**: Add `schemas` column to `postgresql_databases` table - **Model**: Add `Schemas` field to `PostgresqlDatabase` struct - **Backup**: Modify `buildPgDumpArgs()` to append `--schema` flags - **Restore**: Modify `pg_restore` args to support `--schema` filtering ### Frontend - Add "Schemas" input field with tooltip in database edit form - Display schemas in read-only database view - Increase restore modal width to accommodate new field ## Usage Setting the Schemas field to `public,custom_schema` generates: ```bash # Backup pg_dump ... --schema public --schema custom_schema # Restore pg_restore ... --schema public --schema custom_schema ``` Leave the field empty for full database backup/restore (default behavior). ## Screenshots ### Schema field with tooltip when configuring backup <img width="520" height="311" alt="image" src="https://github.com/user-attachments/assets/88e7099c-f797-4145-bb65-129036457073" /> ### Schema field with tooltip in restore modal <img width="533" height="544" alt="image" src="https://github.com/user-attachments/assets/09799fe7-d14c-46be-8494-0623162fa347" /> ## Testing - [x] Tested backup with schema filtering - [x] Tested restore with schema filtering - [x] Frontend builds without errors - [x] Backend builds without errors ## How to Test If you'd like to test this feature, a pre-built Docker image is available: ```bash docker pull leoflores/postgresus:latest ``` Then run it with your preferred configuration or build locally or replace the image in your existing Postgresus deployment.

MrUnknownDE closed this issue

2026-04-05 16:16:18 +02:00

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github/databasus#368