Message-ID: <1521705321.2459.24.camel@cybertec.at>
Subject: Re: Very long standby database startup after doing pg_basebackup
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mario =?UTF-8?Q?Mahovli=C4=87?= <mariomahovlic@gmail.com>,
	pgsql-admin@lists.postgresql.org
Date: Thu, 22 Mar 2018 08:55:21 +0100
In-Reply-To: 
 <CAG9x_sT2mgYF8PU+4o3hMXsqDT4SQj2ms2tWB69Qek8wbdrxOg@mail.gmail.com>
References: 
	<CAG9x_sT2mgYF8PU+4o3hMXsqDT4SQj2ms2tWB69Qek8wbdrxOg@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit
Precedence: bulk

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