Resolving PostgreSQL 12 Startup Issues After pg_basebackup in Master-Slave Replication

Описание: Discover essential steps to fix the `Cannot start PostgreSQL 12` error after executing pg_basebackup for replication on CentOS. This guide simplifies the process for smooth database operations.
---
This video is based on the question https://stackoverflow.com/q/65105063/ asked by the user 'David YK Han' ( https://stackoverflow.com/u/7107563/ ) and on the answer https://stackoverflow.com/a/65105346/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Cannot start Postgresql 12 after pg_basebackup (replication)

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Troubleshooting PostgreSQL 12 Startup Issues After pg_basebackup

Setting up PostgreSQL 12 replication can be a challenging task, especially when using tools like pg_basebackup to create a slave server. Recently, one user encountered a frustrating issue where PostgreSQL refused to start after the base backup process. If you're in a similar situation, this guide will provide clarity and structured steps to resolve this problem.

Understanding the Problem

The scenario unfolds as follows:

Two database servers are set up for master-slave replication.

After completing various setup steps, the slave server fails to start services, resulting in a critical error.

The error logs indicated that PostgreSQL attempted to start but failed, hinting at configuration issues or changes needed due to updates in versioning.

Key Steps to Resolve the Issue

The root cause of the issue primarily stems from changes introduced in PostgreSQL 12 regarding replication configuration. To resolve the startup failure, here are the structured steps to follow:

1. Update Your Configuration Files

In versions prior to PostgreSQL 12, users would typically utilize a specific file called recovery.conf for recovery configurations. However, PostgreSQL 12 has moved away from this method. Instead:

Modify postgresql.conf or postgresql.auto.conf:

Transfer all necessary recovery configuration parameters from recovery.conf into either of these configuration files.

2. Create the Standby Signal File

To inform PostgreSQL that it should start in recovery mode, create a file named standby.signal in your data directory, which is typically located at /var/lib/pgsql/12/data.

Commands:

[[See Video to Reveal this Text or Code Snippet]]

3. Start the Slave Server

Once the configuration is complete and the standby signal file is created, attempt to start your PostgreSQL server again:

[[See Video to Reveal this Text or Code Snippet]]

4. Verify the Logging

If the server starts successfully, it's important to monitor the logs for any issues:

Check the PostgreSQL logs to ensure stability by running:

[[See Video to Reveal this Text or Code Snippet]]

5. Final Checks

Ensure firewalls and other network configurations allow traffic between the master and slave nodes.

Test and verify the replication process after starting the slave server to ensure that it is functioning correctly.

Additional Notes

The removal of the standby_mode parameter is essential to note; for setting a standby node, only the standby.signal is required.

Using recovery.signal can indicate recovery mode that ends after a certain point, ensuring that PostgreSQL transitions to the regular operation mode seamlessly.

By following these steps, you should be able to troubleshoot and resolve the startup issues encountered with PostgreSQL 12 after executing the pg_basebackup command. Don’t hesitate to seek further assistance if problems persist, as community support can provide additional insight and experience-based solutions.

Stay connected for more tips and tricks on managing PostgreSQL effectively!