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.git...@gmail.com> wrote: > core ref: > PostgreSQL: Documentation: 13: Part III. Server Administration > <https://www.postgresql.org/docs/13/admin.html> > although this is a lot verbose, but you would keep coming back to this to > tune your setup. > > > to understand basic setups. some are > How to Set Up Streaming Replication in PostgreSQL 12 - Percona Database > Performance Blog > <https://www.percona.com/blog/2019/10/11/how-to-set-up-streaming-replication-in-postgresql-12/> > How to setup Postgres 13 WAL streaming replication on Ubuntu 18.04 - > Highgo Software Inc. > <https://www.highgo.ca/2021/02/03/how-to-setup-postgres-13-wal-streaming-replication-on-ubuntu-18-04/> > > some other references. > dhamaniasad/awesome-postgres: A curated list of awesome PostgreSQL > software, libraries, tools and resources, inspired by awesome-mysql > (github.com) <https://github.com/dhamaniasad/awesome-postgres> > > a typical setup > > Primary ---streaming replication ---> (Replica1, Replica2 ....) > > Primary - writes > replica R1,R2 .... - reads ( depending on load can be put behind load > balancer like haproxy and connection pooler pgbouncer) > Scaling PostgreSQL using Connection Poolers and Load Balancers for an > Enterprise Grade environment - Percona Database Performance Blog > <https://www.percona.com/blog/2018/10/02/scaling-postgresql-using-connection-poolers-and-load-balancers-for-an-enterprise-grade-environment/> > 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 > <https://www.postgresql.org/docs/13/app-pg-dumpall.html>) > 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) > <http://docs.pgbarman.org/release/2.12/>) > Implement backup with Barman. This tutorial is part of a multipage… | by > Sylvain | coderbunker | Medium > <https://medium.com/coderbunker/implement-backup-with-barman-bb0b44af71f9> > > 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 > solutions > pg_auto_failover > patroni > enterprise solutions from EDB, cruncy etc . > > this channel on youtube is pretty neat too. Scaling Postgres - YouTube > <https://www.youtube.com/channel/UCnfO7IhkmJu_azn0WbIcV9A> > > I 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 <oli...@agilechilli.com> 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 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 >> > > > -- > Thanks, > Vijay > Mumbai, India >
