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
