postgres
postgres copied to clipboard
supabase
Allow replication in pg_hba for pg_basebackup
What kind of change does this PR introduce?
Add an entry in pg_hba.conf to allow replication connections. This allows fast andeasy backups using pg_basebackup (pg_basebackup -D /tmp/mybasebackup -U postgres -h 127.0.0.1)
What is the current behavior?
Trying to create backups with pg_basebackup fails due to the lack of permissions for replication ("all" does not match replication, as the comment in pg_hba.conf states)
What is the new behavior?
Backups using pg_basebackup can be created without authentication inside the container and with password authentication outside the container
Additional context
Creating backups with a classic dump can be challenging with all the features supabase uses in Postgres. Basebackups copy the whole data directory of the Postgres instance and can easily (and fast!) be restored by copying it back.
@cmantsch is this for use with a self-hosted setup? We'll likely want to provide a mechanism to easily supply your own pg_hba file, I think..
Note that you can always use the CLI to take a dump https://supabase.com/docs/guides/platform/migrating-and-upgrading-projects#backup-your-old-database
Yes, this is for self-hosted.
Supabase CLI DB dumps don't work really well for me as it seems to miss some things that was custom added to the db besides supabase. That resulted in broken supabase installations. Basebackup is rock-solid for me and also way faster than taking a normal dump. Restoring a 1GB db takes less than 30 seconds and that even includes stopping and starting the docker stack.
Yeah I think we'll want to solve for this by allowing custom pg_hba files for self-hosted setups, rather than including this in the default. fyi @sweatybridge
Supabase CLI DB dumps don't work really well for me as it seems to miss some things that was custom added to the db besides supabase.
Could you elaborate on the sort of custom things that are being missed? Are there any github issues discussing this problem?
Hey, yes this would be super helpful. We're also using pg_basebackup for more efficient backups and this was a small pain to setup.
What we did was we just overwrote the base file, by mounting our own configuration on-top.
@darora simply allowing for pg_basebackup to be used is nothing really custom, please consider this PR again:
It's simply the standard and most efficient backup mechanism for PostgreSQL. While pgdump and pgdumpall create logical backups (read: SQL files) just fine, those are quite inefficient, especially when restored large databases.
pg_basebackup allows to backup and restore even large databases in minutes since it's binary backup.
