Re: Fwd: upgrade to PG11 on secondary fails (no initdb was launched)
Hi Bruce, On 6/14/19 5:30 AM, Bruce Momjian wrote: >> Also, I do not think it best practice (or perhaps not correct at all) to >> use '--size-only' > > --size-only is correct, as far as I know. > Maybe I am missing something, but I am of the opinion that --size-only should not be used when syncing database content (and probably in many other use cases where content can change over time). The reason is that db allocates blocks, 8K by default regardless from the content. Using --size-only, tells rsync to only check the size of the blocks. That is: if the block is present on the destination, and is the same size as the origin, then skip. I understand that in this thread we are contextualizing in a step by step procedure to create a new standby, but I have anyway a few remarks about it (and the documentation where it has been copied from) and I would be glad if you or somebody else could shed some light on it. *) It might happen in some corner cases that when syncing the standby, rsync dies and the DBA does not realize it. It will then start the master and some data gets modified. At the time the DBA realizes the issue on the standby, he will stop master and resume the sync. Changes happened on the master will then not be propagated to the standby if they happened on files already present on the standby. *) It might be a long shot because I do not have time now to reproduce the situation of the standby at that exact point in time, but I think that --size-only option is there probably to speed up operations. In that case I do not see a reason for it since the data folder on the standby is assumed to be empty regards, fabio pardi
wal_log_hints benchmarks
Hi, Maybe of some interest for the past, present and future community, I benchmarked the impact of wal_log_hints with and without wal_compression enabled. https://portavita.github.io/2019-06-14-blog_PostgreSQL_wal_log_hints_benchmarked/ comments are welcome. regards, fabio pardi
Re: Fwd: upgrade to PG11 on secondary fails (no initdb was launched)
On Fri, Jun 14, 2019 at 03:12:29PM +0200, Fabio Pardi wrote: > Hi Bruce, > > > On 6/14/19 5:30 AM, Bruce Momjian wrote: > > >> Also, I do not think it best practice (or perhaps not correct at all) to > >> use '--size-only' > > > > --size-only is correct, as far as I know. > > > > > Maybe I am missing something, but I am of the opinion that --size-only > should not be used when syncing database content (and probably in many > other use cases where content can change over time). > > The reason is that db allocates blocks, 8K by default regardless from > the content. > > Using --size-only, tells rsync to only check the size of the blocks. > That is: if the block is present on the destination, and is the same > size as the origin, then skip. The files are _exactly_ the same on primary and standby, so we don't need to check anything. Frankly, it is really only doing hard linking of the files. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: Fwd: upgrade to PG11 on secondary fails (no initdb was launched)
On Fri, Jun 14, 2019 at 10:39:40AM -0400, Bruce Momjian wrote: > On Fri, Jun 14, 2019 at 03:12:29PM +0200, Fabio Pardi wrote: > > Using --size-only, tells rsync to only check the size of the blocks. > > That is: if the block is present on the destination, and is the same > > size as the origin, then skip. > > The files are _exactly_ the same on primary and standby, so we don't > need to check anything. Frankly, it is really only doing hard linking > of the files. Here is the description from our docs: What this does is to record the links created by pg_upgrade's link mode that connect files in the old and new clusters on the primary server. It then finds matching files in the standby's old cluster and creates links for them in the standby's new cluster. Files that were not linked on the primary are copied from the primary to the standby. (They are usually small.) This provides rapid standby upgrades. Unfortunately, rsync needlessly copies files associated with temporary and unlogged tables because these files don't normally exist on standby servers. The primary and standby have to be binary the same or WAL replay would not work on the standby. (Yes, I sometimes forgot how this worked so I wrote it down in the docs.) :-) -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
Re: Fwd: upgrade to PG11 on secondary fails (no initdb was launched)
Greetings, * Bruce Momjian ([email protected]) wrote: > On Fri, Jun 14, 2019 at 10:39:40AM -0400, Bruce Momjian wrote: > > On Fri, Jun 14, 2019 at 03:12:29PM +0200, Fabio Pardi wrote: > > > Using --size-only, tells rsync to only check the size of the blocks. > > > That is: if the block is present on the destination, and is the same > > > size as the origin, then skip. > > > > The files are _exactly_ the same on primary and standby, so we don't > > need to check anything. Frankly, it is really only doing hard linking > > of the files. > > Here is the description from our docs: > >What this does is to record the links created by pg_upgrade's >link mode that connect files in the old and new clusters on the >primary server. It then finds matching files in the standby's old >cluster and creates links for them in the standby's new cluster. >Files that were not linked on the primary are copied from the >primary to the standby. (They are usually small.) This provides >rapid standby upgrades. Unfortunately, rsync needlessly copies >files associated with temporary and unlogged tables because these >files don't normally exist on standby servers. > > The primary and standby have to be binary the same or WAL replay would > not work on the standby. (Yes, I sometimes forgot how this worked so I > wrote it down in the docs.) :-) Right- this is *not* a general process for building a replica, this is specifically *only* for when doing a pg_upgrade and *everything* is shut down when it runs, and every step is checked to ensure that there are no errors during the process. Thanks! Stephen signature.asc Description: PGP signature
Re: Fwd: upgrade to PG11 on secondary fails (no initdb was launched)
Greetings, * Fabio Pardi ([email protected]) wrote: > I understand that in this thread we are contextualizing in a step by > step procedure to create a new standby, but I have anyway a few remarks > about it (and the documentation where it has been copied from) and I > would be glad if you or somebody else could shed some light on it. This is not a procedure for creating a new standby. To create a *new* standby, with the primary online, use pg_basebackup or another tool that knows how to issue the appropriate start/stop backup and takes care of the WAL. Thanks! Stephen signature.asc Description: PGP signature
