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.