Re: Setting up replication - Mailing list pgsql-general

core ref:

PostgreSQL: Documentation: 13: Part III. Server Administration

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

How to setup Postgres 13 WAL streaming replication on Ubuntu 18.04 - Highgo Software Inc.

some other references.

dhamaniasad/awesome-postgres: A curated list of awesome PostgreSQL software, libraries, tools and resources, inspired by awesome-mysql (github.com)

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://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))

Implement backup with Barman. This tutorial is part of a multipage… | by Sylvain | coderbunker | Medium

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

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