0

I have a master-replica PostgreSQL 9.5 setup.

I'm trying to reinitialize the replica by doing the following:

  • Stop replica service ( /etc/init.d/postgresql stop )
  • Clear data directory ( rm -rf /path/to/postgresql/data )
  • Start pg_basebackup ( sudo pg_basebackup -h master-host -D /path/to/postgresql/data/ --checkpoint=fast -X stream -R -P -v -U replication )
  • Start replica service ( /etc/init.d/postgresql start )

Some details:

  • It takes the pg_basebackup process to complete around 45mins.
  • I'm using -X stream to keep up with the changes happening on master while the backup is taking place.
  • When the process has finished, the data directory in the replica is ~2GB smaller than the master's (743GB vs 745GB).

When starting the service, these are the output logs:

[1869] LOG:  database system is shut down
[2996] LOG:  database system was interrupted; last known up at 2024-03-18 20:48:03 UTC
[2996] LOG:  entering standby mode
[2996] LOG:  redo starts at 1820/41000098
[2999] [unknown]@[unknown] LOG:  incomplete startup packet
[2996] LOG:  consistent recovery state reached at 1820/4A8CB950
[3001] LOG:  started streaming WAL from primary at 1820/4B000000 on timeline 1
[3004] postgres@template1 FATAL:  the database system is starting up
[3008] postgres@template1 FATAL:  the database system is starting up

The service is started but any interaction with it results in the fatal error: database system is starting up

Am I missing something in the reinitialisation of the replica? What is causing this behaviour?

Aaron Ullal
  • 111
  • 2
  • Check this one Hot Backup Error: the database system is starting up – mbielsa Mar 19 '24 at 08:12
  • 2
    LOG: entering standby mode means that standby_mode = on is specified in recovery.conf, but hot_standby in postgresql.conf didn't seem to be enabled. In those ancient days of pg9.5, this setting was not enabled by default. – Melkij Mar 19 '24 at 11:15
  • 2
    @LaurenzAlbe I know, in fact, I am the author of this patch. But the question mentions version 9.5. – Melkij Mar 19 '24 at 15:56
  • @Melkij thank you for your comment. Quick question: from my understanding the master needs to specify wal_level=hot_standby and the slave needs to have hot_stanbdy=on. Is there a reason why those settings are not on by default? For example how would the replica work if the master specified wal_level=archive? – Aaron Ullal Mar 20 '24 at 08:44
  • To answer this question, I need to write a relatively big historical excursion about the evolution of databases in 2000-2015. Briefly about the reason, “whoever needs it will configure it himself, but everyone who has not changed the configuration will not have to pay for it”. For example wal_level > minimal costs some additional server resources. Now both hot_standby=on and wal_level=replica by default. it's your database stuck in the distant past. – Melkij Mar 20 '24 at 09:21
  • @Melkij awesome! Thank you so much – Aaron Ullal Mar 21 '24 at 07:38

0 Answers0