Date: Thu, 8 Feb 2024 17:21:29 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: pg_basebackup --wal-method=fetch
Message-ID: <ZcVT6e9AU9XgP/37@tamriel.snowman.net>
References: 
 <CANzqJaDR-LZn2jQKOzb10-jXOdGrDtgf9=99_kKzLN3+0pWfAA@mail.gmail.com>
 <CABUevEy38dnyRaJNgbextVVWjC72K-v0EUO+W00L7+ZfqVoqVA@mail.gmail.com>
 <CANzqJaB7m2V+uTvu1rOY-2toye1ZtS3xp-GeZ9=bxO+r93wHow@mail.gmail.com>
 <ZcVKf0vByWDMVH5M@tamriel.snowman.net>
 <CANzqJaADTqhZXGZ8gxTj_0SdQ85rowLnuXHA6FKs19ir28x_3w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="buBDo3PYekOKmvob"
Content-Disposition: inline
In-Reply-To: 
 <CANzqJaADTqhZXGZ8gxTj_0SdQ85rowLnuXHA6FKs19ir28x_3w@mail.gmail.com>
User-Agent: Mutt/2.1.4 (2021-12-11)
Archived-At: 
 <https://www.postgresql.org/message-id/ZcVT6e9AU9XgP/37%40tamriel.snowman.net>
Precedence: bulk


--buBDo3PYekOKmvob
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Ron Johnson (ronljohnsonjr@gmail.com) wrote:
> On Thu, Feb 8, 2024 at 4:41=E2=80=AFPM Stephen Frost <sfrost@snowman.net>=
 wrote:
> > * Ron Johnson (ronljohnsonjr@gmail.com) wrote:
> > > On Thu, Feb 8, 2024 at 12:48=E2=80=AFPM Magnus Hagander <magnus@hagan=
der.net>
> > wrote:
> > > > On Thu, Feb 8, 2024, 17:05 Ron Johnson <ronljohnsonjr@gmail.com>
> > wrote:
> > > >> (PG 14, if it matters.)
> > > >>
> > > >> What's the purpose of fetch mode, as opposed to streaming mode?  Is
> > it a
> > > >> legacy of bygone days that just hasn't been deprecated, or is there
> > > >> something I don't understand from reading
> > > >> https://www.postgresql.org/docs/14/app-pgbasebackup.html?
> > > >
> > > > Your backup can go to a single file with it, which it can't do in
> > > > streaming. Which means it can also be sent through a pipe.
> > >
> > > But isn't the whole purpose of pg_basebackup (running it on Node B, w=
hen
> > > the database instance is Node A)?
> >
> > Something seems missing from this question?
>=20
> The word "streaming".
> Should be "But isn't streaming the whole purpose of pg_basebackup"?

I'm a bit confused on this point still as if the whole purpose of
pg_basebackup is to be streaming ... then we should be defaulting to
fetch mode still?

> I use PgBackRest, though, and can't imagine single-threading any
> reasonably-sized database.  In fact, one of the tasks on my mental TODO
> list is to research how to use PgBackRest to initialize a replica instance
> prior to starting Streaming Replication.

I use pgbackrest too. ;)  That said, one of the longest poles in the
tent when it comes to dealing with backups is compression- and there are
tools like pigz that allow you to multi-thread a single stream across
many cores, so it isn't necessarily the case that using fetch mode or
pg_basebackup generally makes everything have to be completely
single-process.  Of course, pgbackrest has a lot of other features that
make it a great tool to use.

In terms of using pgbackrest to initialize a replica ... that's
basically running 'pgbackrest restore --type=3Dstandby'?  There's really
not much more to it than that.  pgbackrest will set up the restored
system to replay from the WAL in the archive, you'd just need to
configure primary_conninfo so that the replica will attempt to connect
to the primary once it's caught up with all of the WAL in the archive.

> > > It also needs one connection instead of two to the server, if that's
> > > > limited.
> > >
> > > It's 2024, not 2011.  Who can't spare an extra connection?
> >
> > Changing max_wal_senders requires a database-wide restart, so..
>=20
> To not have some wiggle room is poor planning

Doesn't change reality though.

> > If so, why?  If that's not the angle,
> > then what is?  Would you suggest some better documentation of the
> > option?  I'm sure a proposal to improve the docs would be welcome, if
> > there's something confusing about them and this option.
>=20
> A hint as to the use-case for the non-default "streaming" option would be
> enlightening.

I mentioned a couple of them above and there is an example of streaming
in the documentation today:

######
To create a backup of a single-tablespace local database and compress
this with bzip2:

$ pg_basebackup -D - -Ft -X fetch | bzip2 > backup.tar.bz2
######

Would it make things more clear if this was an example that sent data to
a tape device instead of through bzip2..?

Thanks!

Stephen

--buBDo3PYekOKmvob
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmXFU+YACgkQ7WyKOINH
ZFUzQw/5Abbw/F8bKc7yHJ0OdhZ/I7qg665CQ3aLejjH6QFwFN97Xm+aHytruq7K
iqDTtrMJ4y7hpLHm+gXoc2vYM16nhDvJ4IF3LCA8N2rMN7NMFE4N9JnQHmnlvu3/
EHi+Nsx42Wvq8xx2H2VyaZTSqjChGqPVKx7SArUuoEAJ6ypKj69DEceTG0dn1POG
ehAW0lnhu57NbmSgeTVr/4hBVjtTFv86vWuMoTVj0yDmoRYxRF0VjhLAQN6UfElq
LNO7NiBAr8YTqSv7DcSSE0ncq61r3s4/qghHd9quJAbcj1Hr/fKgZLoAgCIzeNyE
etg+mmRLo59kTg/os0nw2rK6orZs6kYTAwuxiXDkEfU4SO8+yVdXcQSMpi985IUV
tDs0Zw+69nyb7a6JKc8zu5pTc/7My3JuwE0+/0+2lvwtnQQJJ05EVlZIK2/iqc5u
Q09TBYF0QsyBesV76fk369GkhCX1udoDKUtybj0hL/C1GAATPhltVUbr8JkUt/2W
uVlqWfwty/D0C/+wieZDNZ1H2BXxXTZ3/XXfpvEL1CTaZg+KUdF5+xbpe9rqMLwf
M5iNiRvhSSyp6o7wH38oMH0FofSgBbxX+Z44Q7JriH/aaal5tKlVta+LctyfFgxg
cCQfzRzpNhX7XLhl+yU1vA6i4SN+9JFL0eOPZo9SIWYuNA7Nt54=
=P7Pc
-----END PGP SIGNATURE-----

--buBDo3PYekOKmvob--