That is helpful, thanks Vijay.
I will wade in and give it a go. For some reason I had it in my head that it was a good idea to run pg_basebackup frequently, e.g. once a day, but it looks like it's only necessary once for the initial transfer to the replica.
Oliver
On Wed, 26 May 2021 at 20:37, Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> wrote:
core ref:although this is a lot verbose, but you would keep coming back to this to tune your setup.to understand basic setups. some aresome other references.a typical setupPrimary ---streaming replication ---> (Replica1, Replica2 ....)Primary - writesreplica R1,R2 .... - reads ( depending on load can be put behind load balancer like haproxy and connection pooler pgbouncer)https://tinyurl.com/f2zk76yc (EDB link, but the link is too big)backups:vm snapshots ( optional )physical disk backups. ( optional )pg_dumpall from replica and save it to external storage daily. (PostgreSQL: Documentation: 13: pg_dumpall)barman (point in time recovery, can configure to save 7 days of WALs for point in time recovery ) on external server. (Barman Manual (pgbarman.org))Ideally, i would always go with core docs, as many tutorials get stale, but i just mention to help get started quickly and then come back to core docs.Things can get more complex (or simpler) if you go with auto failover solutionspg_auto_failoverpatronienterprise solutions from EDB, cruncy etc .this channel on youtube is pretty neat too. Scaling Postgres - YouTubeI am not sure my reply is making it helpful or making it too loud for simple setups.anyways :)On Wed, 26 May 2021 at 23:28, Oliver Kohll <oliver@xxxxxxxxxxxxxxx> wrote: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 where1) 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--Thanks,VijayMumbai, India
