Very long standby database startup after doing pg_basebackup

From: Mario Mahovlić <mariomahovlic(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Very long standby database startup after doing pg_basebackup
Date: 2018-03-21 12:55:21
Message-ID: CAG9x_sT2mgYF8PU+4o3hMXsqDT4SQj2ms2tWB69Qek8wbdrxOg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello,

We are running 2 nodes (primary and standby) of PostgreSQL 9.6 with
asynchronous streaming replication set up.
We use repmgr 3.3.2 for managing replication/failover, and we are
experiencing long database startup after cloning the primary node.

We use repmgrs standby clone command and it executes pg_basebackup as
follows:
INFO: executing: '/usr/lib/postgresql/9.6/bin/pg_basebackup -l "repmgr base
backup" -D /var/lib/postgresql/data -h *NODE_IP* -p *PORT* -U *USER* -c
fast -X stream '

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?

Thanks in advance for any help you might provide, kind regards.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message dbx postgres 2018-03-21 17:20:38 psql error
Previous Message Andrew Foster 2018-03-20 16:57:30 Re: Newbie question