Re: Setting up replication

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 <oliver(at)agilechilli(dot)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