Hi,
We currently have an app with the database on the same server as the app itself. I'd like to transition to a system where
1) in the short term, the db replicates to a different server. This will allow us to take the daily pg_dump backups from the replica rather than the primary server. They're currently slowing down the system too much as they run.
2) in the medium term, switch the replica to be the primary and connect to that from the app, i.e. app and db will be on separate servers, letting us resource each appropriately. A 3rd server can then be used to replicate to for backup purposes.
3) in the long run, depending on demand that also gives us the option of scaling the db horizontally e.g. with a distributed db like Citus.
Are there any suggestions / good walkthroughs of how to do number 1? There are many options!
All I know so far is we can probably use streaming replication as I can make sure the PostgreSQL versions on each server are the same.
One thing I'm wondering is how often should a base backup be taken? Also should we set up everything manually with scripts or use a 3rd party backup tool like barman?
Any suggestions appreciated.
Oliver