Re: Setting up replication

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(dot)github(at)gmail(dot)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 <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
>