Re: Very long standby database startup after doing pg_basebackup

Mario Mahovlić wrote:
> However, our issue is not with primary cloning, but with database startup after
> clone/basebackup is done. 
> Startup of standby database is successful but it takes around 6 hours, during
> which time all we get from DB is: FATAL:  the database system is starting up.
> We have noticed that this startup process uses only 1 cpu core on our machine
> which has 8.
> 
> Our database size is around 570GB, and we are using schema per tenant data
> distribution, so we have separate schemas for each of the users of our
> application, as it suits our needs well.
> Because of this our database has about 40000 schemas with 5 tables per each
> schema.
> We suspect the reason behind this long startup is amount of data and/or large
> number of schemas/tables on database, however we would like to confirm that somehow.
> 
> So our questions are following:
> What could be possible reasons for startup as long as this?
> How can we figure out what exactly does postgres startup process do all this time,
> is there any documentation on what exactly does postgres database do on startup as
> we have not found any info on this?
> Is there any way to speed up this startup process?
> Can we mitigate this startup time in cases when we do planned switchover for
> maintenance by using pg_rewind instead of pulling all data from primary again?
> So if we shut down the database gracefully and use pg_rewind will startup process
> be faster?

You could use "perf top" to see where the time is spent or have
a look at the PostgeSQL log file.

pg_basebackup creates a checkpoint when it starts, so checkpoints that happen
too rarely cannot be the problem.

Did you use "pg_basebackup -X stream" to copy the WAL files along with the data?
If yes, is there a lot of them?
If no, how do you restore the required WAL files?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com