Setting up replication

core ref:
PostgreSQL: Documentation: 13: Part III. Server Administration
<https://www.postgresql.org/docs/13/admin.html&gt;
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/&gt;
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/&gt;

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&gt;

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/&gt;
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&gt;)
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/&gt;)
Implement backup with Barman. This tutorial is part of a multipage… | by
Sylvain | coderbunker | Medium
<https://medium.com/coderbunker/implement-backup-with-barman-bb0b44af71f9&gt;

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&gt;

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@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

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@gmail.com> wrote: