From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mario Mahovlić <mariomahovlic@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Very long standby database startup after doing pg_basebackup
Date: Thu, 22 Mar 2018 08:55:21 +0100
Message-ID: <1521705321.2459.24.camel@cybertec.at> (raw)
In-Reply-To: <CAG9x_sT2mgYF8PU+4o3hMXsqDT4SQj2ms2tWB69Qek8wbdrxOg@mail.gmail.com>
References: <CAG9x_sT2mgYF8PU+4o3hMXsqDT4SQj2ms2tWB69Qek8wbdrxOg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, mariomahovlic@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Very long standby database startup after doing pg_basebackup
In-Reply-To: <1521705321.2459.24.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox