Feature request: Add dump and restore endpoints to support primitive backups

[ruggi99]

Chore

Describe the chore

Feature request: Add dump and restore endpoints to backup and restore database. Useful for self-hosting, given that Supabase Backup is not available. Maybe useful for users not using Supabase. Also can be used in Supabase Free Tier.

Initial thoughts:

• Install and use pg_dump and pg_restore in Docker. No additional dependencies when installed in studio.
• Add env var to know if running in docker
• Endpoints should handle files and strings.
• dump returns sql string or starts downloading a file
• restore accepts an upload of a file or a body of a POST request.

Additional context

To be discussed if dump and restore are in scope with the scope of this package (personally I think they are)

[soedirgo]

I think this is a bit out of scope - while backup & restore are part of managing Postgres (which is what pg-meta aims to do), pg-meta's interface is not sufficient to do the job effectively. Imagine e.g. having a 1GB database, and downloading the dump as a multi-gb JSON blob in one HTTP request. Not good.

A dedicated tool is better served for this, e.g. using the pg_* tools directly or mounting the data directory in a separate volume.