Re: Shortest offline window on database migration

[Fabio Pardi]

On 5/30/19 5:08 PM, Haroldo Kerry wrote:
> Hello,
> 
> We are migrating our PostgreSQL 9.6.10 database (with streaming
> replication active) to a faster disk array.
> We are using this opportunity to enable checksums, 


I would stay away from performing 2 big changes in one go.


regards,

fabio pardi

Re: improve wals replay on secondary

[Fabio Pardi]

On 5/29/19 10:39 AM, Mariel Cherkassky wrote:
> Is there any messages that indicates that the secondary replayed a
> specific wal ? "restored 0..." means that the restore_command
> succeeded but there isnt any proof that it replayed the wal.

I believe that the message "restored log file..' is an acknowledgement
that the file has been restored and the WAL file applied. At least
that's what i know and understand from the source code.



> 
> My theory regarding the issue : 
>  It seems, that my customer stopped the db 20 minutes after the clone
> have finished.  During those 20 minutes the secondary didnt get enough
> wal records (6 wal files) so it didnt reach the max_wal_size. My
> checkpoint_timeout is set to 30minutes, therefore there wasnt any
> checkpoint. As a result of that the secondary didnt reach a restart
> point. Does that sounds reasonable ?
> 

It could be, but i do not see the point here.

> So basically, if I clone a small primary db, the secondary would reach a
> restart point only if it reached a checkpoint (checkpoint_timeout or
> max_wal_size). However, I have cloned many small dbs and saw the it
> takes a sec to start the secondary (which means that restartpoint was
> reached). So what am I missing ?
> 

The restart point is reached in relation to checkpoints.

But when all available WAL files have been applied, you should be able
to connect to your standby, regardless to when the last checkpoint
occurred, if your standby allows that (hot_standby = on).




regards,

fabio pardi

> ‫בתאריך יום ד׳, 29 במאי 2019 ב-11:20 מאת ‪Fabio Pardi‬‏
> <‪f.pa...@portavita.eu ‬‏>:‬
> 
> 
> 
> On 5/29/19 9:20 AM, Mariel Cherkassky wrote:
> > First of all thanks Fabio.
> > I think that I'm missing something : 
> > In the next questionI'm not talking about streaming replication,rather
> > on recovery : 
> >
> > 1.When the secondary get the wals from the primary it tries to replay
> > them correct ? 
> 
> 
> correct
> 
> >
> > 2. By replaying it just go over the wal records and run them in the
> > secondary ?
> >
> 
> correct
> 
> > 3.All those changes are saved in the shared_buffer(secondary) or the
> > changed are immediately done on the data files blocks ?
> >
> 
> the changes are not saved to your datafile yet. That happens at
> checkpoint time.
> 
> > 4.The secondary will need a checkpoint in order to flush those changes
> > to the data files and in order to reach a restart point ?
> >
> 
> yes
> 
> > So, basically If I had a checkpoint during the clone, the secondary
> > should also have a checkpoint when I recover the secondary right ?
> >
> 
> correct. Even after being in sync with master, if you restart Postgres
> on standby, it will then re-apply the WAL files from the last
> checkpoint.
> 
> In the logfile of the standby, you will see as many messages reporting
> "restored log file" as many WAL files were produced since the last
> checkpoint
> 
> Hope it helps to clarify.
> 
> regards,
> 
> fabio pardi
> >
> > ‫בתאריך יום ג׳, 28 במאי 2019 ב-13:54 מאת ‪Fabio Pardi‬‏
> > <‪f.pa...@portavita.eu 
> >‬‏>:‬
> >
> >     Hi Mariel,
> >
> >     please keep the list posted. When you reply, use 'reply all'. That
> >     will maybe help others in the community and you might also get
> more
> >     help from others.
> >
> >     answers are in line here below
> >
> >
> >
> >     On 28/05/2019 10:54, Mariel Cherkassky wrote:
> >     > I have pg 9.6, repmgr version 4.3 .
> >     > I see in the logs that wal files are restored : 
> >     > 2019-05-22 12:35:12 EEST  60942  LOG:  restored log file
> >     "0001377B00DB" from archive
> >     > that means that the restore_command worked right ? 
> >     >
> >
> >     right
> >
> >     > According to the docs :
> >     > "In standby mode, a restartpoint is also triggered
> >     if checkpoint_segments log segments have been replayed since last
> >     restartpoint and at least one checkpoint record has been replayed.
> >     Restartpoints can't be performed more frequently than
> checkpoints in
> >     the master because restartpoints can only be performed at
> checkpoint
> >     records"
> >     > so maybe I should decrease max_wal_size or even
> checkpoint_timeout
> >     to force a restartpoint ? 
> >     > During this gap (standby clone) 6-7 wals were generated on
> the primary
> >     >
> >
> >
> >     From what you posted earlier, you should in any case have hit a
> >     checkpoint every 30 minutes. (That was also the assumption in the
> >     previous messages. If that's not happening, then i would really
>

Re: Sv: JIT in PostgreSQL 12 ?

[Justin Pryzby]

On Wed, May 29, 2019 at 10:48:19AM +0200, Andreas Joseph Krogh wrote:
> På onsdag 29. mai 2019 kl. 10:02:50, skrev Tobias Gierke 
> 
> Hi,
> 
>  Browsing the PostgreSQL 12 release notes I noticed that JIT is now
>  enabled by default. Having not followed PostgreSQL development closely -
>  does this mean that compilation results are now getting cached and
>  compilation is no longer executed separately for each worker thread in a
>  parallel query ?

Thanks for starting the conversation.

> I don't know, but just want to chime in with my experience 
> with PG-12 and JIT: Execution-time is still way worse then JIT=off for your 
> queries so we'll turn JIT=off until we can mesure performance-gain.

That's also been my consistent experience and conclusion.

I gather the presumption has been that JIT will be enabled by default..
..but perhaps this is a "Decision to Recheck Mid-Beta" (?)

Justin