无法启动PostgreSQL备库求助：双Ubuntu实例配置详情

Alright, let's walk through troubleshooting your failed standby PostgreSQL instance (willie) step by step. You've got two Ubuntu 14.04 servers running PostgreSQL 9.5.10, with omnipitr 1.3.3 set up, so let's focus on the most likely culprits here:

First, make sure the base backup you created with omnipitr on flanders is intact and properly copied to willie:

• Double-check the backup command you ran on flanders—something like omnipitr-backup start -D /var/lib/postgresql/9.5/main -U postgres should have generated a complete backup set.
• Verify the backup files on willie have the correct permissions: they should be owned by postgres:postgres with 700 permissions (run ls -l /var/lib/postgresql/9.5/main to confirm).
• Rule out file corruption by comparing MD5 hashes of backup files between flanders and willie. For example, run md5sum /path/to/backup/file on both servers—hashes should match exactly.

PostgreSQL 9.5 relies on a separate recovery.conf file (this was merged into postgresql.conf in v12) and requires specific settings to run as a standby:

postgresql.conf on willie #

Ensure these parameters are set correctly (align with flanders' config where needed):

• hot_standby = on (mandatory to enable read-only access on the standby)
• wal_level = hot_standby (must match the primary's setting—you said you configured this on flanders, so mirror it here)
• max_wal_senders (set to at least the same value as flanders to support replication)

recovery.conf on willie #

This file must exist in willie's data directory (/var/lib/postgresql/9.5/main) with these critical settings:

• standby_mode = 'on'
• primary_conninfo = 'host=flanders port=5432 user=postgres password=your_replication_password' (replace with your actual credentials—ensure this user has replication rights on flanders)
• restore_command = 'omnipitr-restore -D /var/lib/postgresql/9.5/main %f %p' (use the full path to omnipitr-restore if it's not in postgres's PATH, e.g., /usr/local/bin/omnipitr-restore)
• Also, confirm recovery.conf has strict permissions: postgres:postgres with 600 (run chmod 600 /var/lib/postgresql/9.5/main/recovery.conf if needed—PostgreSQL will reject it otherwise)

Logs are your most valuable tool here. On Ubuntu, willie's PostgreSQL logs live at /var/log/postgresql/postgresql-9.5-main.log.

• Pull the latest error entries with:

  sudo tail -n 50 /var/log/postgresql/postgresql-9.5-main.log
  

• Look for red flags like:
  • Permission denied errors (e.g., can't read backup files or recovery.conf)
  • WAL file missing messages (indicates incomplete backup or replication issues)
  • Connection failures to flanders (points to network/firewall or primary_conninfo mistakes)

Make sure the user specified in primary_conninfo (postgres, in your case) has replication rights on the primary:

• Log into flanders' psql as postgres: psql -U postgres
• Run this query to check replication status:

  SELECT rolname, rolreplication FROM pg_roles WHERE rolname = 'postgres';
  

• If rolreplication returns f, grant replication access with:

  ALTER ROLE postgres WITH REPLICATION;
  SELECT pg_reload_conf();
  

Ensure willie can reach flanders on port 5432 (PostgreSQL's default port):

• On willie, test connectivity with:

  nc -zv flanders 5432
  

• If the connection fails, check Ubuntu's UFW firewall on flanders:
  • Allow willie's IP to connect with:

    sudo ufw allow from <willie_server_ip> to any port 5432
    sudo ufw reload
    

Confirm omnipitr 1.3.3 is correctly installed and accessible to the postgres user on both servers:

• Verify the commands are in postgres's PATH:

  which omnipitr-backup
  which omnipitr-restore
  

• If they're not found, use the full path to the commands in your backup/restore scripts and recovery.conf.
• Double-check the version to ensure you're running 1.3.3:

  omnipitr-backup --version
  

内容的提问来源于stack exchange，提问作者Kim Stacks