[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: Setting up replication
From: Oliver Kohll <oliver () agilechilli ! com>
Date: 2021-05-26 20:49:43
Message-ID: CAMS=m5KJt2Ao+SvJf2GZ94XFW7iTqs1AO_P=Ryhz_cbB+y3Jtg () mail ! gmail ! com
[Download RAW message or body]
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:
> 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@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
>
[Attachment #3 (text/html)]
<div dir="ltr"><div dir="ltr">That is helpful, thanks Vijay.<div><br></div><div>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.</div><div><br></div><div>Oliver</div></div><br><div class="gmail_quote"><div \
dir="ltr" class="gmail_attr">On Wed, 26 May 2021 at 20:37, Vijaykumar Jain <<a \
href="mailto:vijaykumarjain.github@gmail.com">vijaykumarjain.github@gmail.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div \
dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div \
dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div dir="ltr"><div \
dir="ltr">core ref:<br></div><div><a \
href="https://www.postgresql.org/docs/13/admin.html" target="_blank">PostgreSQL: \
Documentation: 13: Part III. Server Administration</a></div><div>although this is a \
lot verbose, but you would keep coming back to this to tune your \
setup.</div><div><br></div><div><br></div><div>to understand basic setups. some \
are</div><div dir="ltr"><a \
href="https://www.percona.com/blog/2019/10/11/how-to-set-up-streaming-replication-in-postgresql-12/" \
target="_blank">How to Set Up Streaming Replication in PostgreSQL 12 - Percona \
Database Performance Blog</a><br></div><div dir="ltr"><a \
href="https://www.highgo.ca/2021/02/03/how-to-setup-postgres-13-wal-streaming-replication-on-ubuntu-18-04/" \
target="_blank">How to setup Postgres 13 WAL streaming replication on Ubuntu 18.04 - \
Highgo Software Inc.</a><br></div><div dir="ltr"><br></div><div>some other \
references.</div><div dir="ltr"><a \
href="https://github.com/dhamaniasad/awesome-postgres" \
target="_blank">dhamaniasad/awesome-postgres: A curated list of awesome PostgreSQL \
software, libraries, tools and resources, inspired by awesome-mysql \
(github.com)</a><br></div><div dir="ltr"><br></div><div>a typical \
setup</div><div><br></div><div>Primary ---streaming replication ---> (Replica1, \
Replica2 ....)</div><div><br></div><div>Primary - writes</div><div>replica R1,R2 .... \
- reads ( depending on load can be put behind load balancer like haproxy and \
connection pooler pgbouncer)</div><div><a \
href="https://www.percona.com/blog/2018/10/02/scaling-postgresql-using-connection-poolers-and-load-balancers-for-an-enterprise-grade-environment/" \
target="_blank">Scaling PostgreSQL using Connection Poolers and Load Balancers for an \
Enterprise Grade environment - Percona Database Performance Blog</a><br></div><div><a \
href="https://tinyurl.com/f2zk76yc" target="_blank">https://tinyurl.com/f2zk76yc</a> \
(EDB link, but the link is too \
big)<br></div><div><br></div><div><br></div><div>backups:</div><div>vm snapshots ( \
optional )</div><div>physical disk backups. ( optional )</div><div>pg_dumpall from \
replica and save it to external storage daily. (<a \
href="https://www.postgresql.org/docs/13/app-pg-dumpall.html" \
target="_blank">PostgreSQL: Documentation: 13: pg_dumpall</a>)</div><div>barman \
(point in time recovery, can configure to save 7 days of WALs for point in time \
recovery ) on external server. (<a href="http://docs.pgbarman.org/release/2.12/" \
target="_blank">Barman Manual (pgbarman.org)</a>)</div><div><a \
href="https://medium.com/coderbunker/implement-backup-with-barman-bb0b44af71f9" \
target="_blank">Implement backup with Barman. This tutorial is part of a multipage… \
| by Sylvain | coderbunker | Medium</a><br></div><div><br></div><div>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.</div><div><br></div><div>Things can \
get more complex (or simpler) if you go with auto failover solutions \
</div><div>pg_auto_failover </div><div>patroni </div><div>enterprise solutions from \
EDB, cruncy etc .</div><div><br></div><div>this channel on youtube is pretty neat \
too. <a href="https://www.youtube.com/channel/UCnfO7IhkmJu_azn0WbIcV9A" \
target="_blank">Scaling Postgres - YouTube</a></div><div><br></div><div>I am not sure \
my reply is making it helpful or making it too loud for simple setups. \
</div><div>anyways :)</div><div><br></div></div></div></div></div></div></div></div></div></div></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, 26 May 2021 at 23:28, \
Oliver Kohll <<a href="mailto:oliver@agilechilli.com" \
target="_blank">oliver@agilechilli.com</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hi,<div><br></div><div>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</div><div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div>Are there any suggestions / good walkthroughs of how \
to do number 1? There are many options!</div><div><br></div><div>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.</div><div><br></div><div>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?</div><div><br></div><div>Any \
suggestions appreciated.</div><div><br></div><div>Oliver</div></div> \
</blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr"><div \
dir="ltr">Thanks,<div>Vijay</div><div>Mumbai, India</div></div></div> \
</blockquote></div><br clear="all"><div><br></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic