autobase: Patroni "CRITICAL: system ID mismatch, node belongs to a different cluster"

Hey,

first of all thanks for this project! Really appreciate the time and effort.

I’m kinda stuck though, not really sure how to proceed. I’ve had a cluster running already, but since we’re still testing and I discovered some issues, I tore it down to redeploy it. The first deployment was on an older version of the ansible playbook though, so I’m not sure what changed. I compared my old version with the new and couldn’t really spot much of a difference.

Basis are Debian 12 VMs on Proxmox 8 deployed via Terraform, so they are absolutely identical.

The patroni master Node works apparently, but the Replicas won’t connect. According to the Logs they are in a different Cluster ID.

I’ve checked and tried the solution in #747 , but unfortunately it didn’t do the trick for me. After removing the configs and cluster, it couldn’t connect to postgresql anymore at all.

This is the output from the playbook:

TASK [patroni : Wait for port 8008 to become open on the host] *********************************************************************************************************************************************************************************************************************
fatal: [192.168.88.103]: FAILED! => {"changed": false, "elapsed": 120, "msg": "Timeout when waiting for 192.168.88.103:8008"}
fatal: [192.168.88.102]: FAILED! => {"changed": false, "elapsed": 120, "msg": "Timeout when waiting for 192.168.88.102:8008"}

And the logs on both Replica Hosts:

Sep 24 08:58:30 pgcn02 patroni[37244]: 2024-09-24 08:58:30,734 INFO: Selected new etcd server http://192.168.88.102:2379
Sep 24 08:58:30 pgcn02 patroni[37244]: 2024-09-24 08:58:30,739 INFO: No PostgreSQL configuration items changed, nothing to reload.
Sep 24 08:58:30 pgcn02 patroni[37244]: 2024-09-24 08:58:30,741 CRITICAL: system ID mismatch, node pgcn02 belongs to a different cluster: 7418131322725245191 != 7418131302586179729
Sep 24 08:58:30 pgcn02 systemd[1]: patroni.service: Main process exited, code=exited, status=1/FAILURE
Sep 24 08:58:30 pgcn03 patroni[37243]: 2024-09-24 08:58:30,843 INFO: Selected new etcd server http://192.168.88.101:2379
Sep 24 08:58:30 pgcn03 patroni[37243]: 2024-09-24 08:58:30,847 INFO: No PostgreSQL configuration items changed, nothing to reload.
Sep 24 08:58:30 pgcn03 patroni[37243]: 2024-09-24 08:58:30,849 CRITICAL: system ID mismatch, node pgcn03 belongs to a different cluster: 7418131322725245191 != 7418131302059765907
Sep 24 08:58:30 pgcn03 systemd[1]: patroni.service: Main process exited, code=exited, status=1/FAILURE

This is the relvant part in my inventory (I think):

# PostgreSQL nodes
[master]
192.168.88.101

[replica]
192.168.88.102
192.168.88.103

[postgres_cluster:children]
master
replica

I’ve replaced the cluster completely twice now, changing the cluster name in between installs, but to no avail. Not sure what else you need to troubleshoot, just let me know which logs or config you need.

Thanks in advance!

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 21 (10 by maintainers)

Most upvoted comments

Thanks, this seems to work fine now! Updated the patroni main.yml and ran the restore like before and it went through just fine and pretty quick as well.

For my understanding: You changed the handling of the leader node so it now runs a check to see if postgres is (still) up, then stop it if true on Lines 704-727? Then added further checks for starting it back up.

One last question, now that restore is working fine: What is the best way to recover in case of a complete loss of the cluster? I’ll backup the nodes in general as well, so I can restore those, but I wanna cover all my bases before going into production. You never know what kind of catastrophic failure may happen 😉

I’ve had issues recreating the cluster nodes after deleting them on purpose, because it complained either about postgres not existing (when running with PITR command) or that the stanza already exist if creating a new cluster. Setting the bootstrap method to pgbackrest also did not work (I forgot the error tbh). But this might’ve been connected to this former issue, because this seems to be the correct way from my understanding. Or did I get this wrong and there’s another, correct way?

Thanks! I also just noticed I forgot to add another error message that might or might not be helpful. Wenn running the restore, it will succeed on the replicas, but on the master node it fails with:

"2024-10-28 12:07:46.168 P00  ERROR: [038]: unable to restore while PostgreSQL is running", "                                    HINT: presence of 'postmaster.pid' in '/var/lib/postgresql/16/main' indicates PostgreSQL is running.", "                                    HINT: remove 'postmaster.pid' only if PostgreSQL is not running.", "2024-10-28 12:07:46.168 P00   INFO: restore command end: aborted with exception [038]"]}

I then have to stop postgres & Remove the postmaster.pid manually before I can run the playbook again to attempt a restore. Then it’ll run untill it times out (or sometimes not) on the master Node.

Not sure what to make of that, but it seems connected since it should stop postgres like it does on the replicas to then restore.

this week I plan to try to reproduce the problem and suggest an improvement as part of the PR.

I’m not sure why, as my ansible knowledge isn’t that deep yet, but it seems the pg_ctl_timeout is ignored because ansible adds a newline at the end of the command:

changed: [192.168.88.102] => {
    "changed": true,
    "cmd": "/usr/lib/postgresql/16/bin/pg_ctl start -D /var/lib/postgresql/16/main -o '-c hot_standby=off' -w -t 180\n",
    "delta": "0:00:00.240277",
    "end": "2024-10-11 07:42:01.847932",
    "invocation": {
        "module_args": {
            "_raw_params": "/usr/lib/postgresql/16/bin/pg_ctl start -D /var/lib/postgresql/16/main -o '-c hot_standby=off' -w -t 180\n",
            "_uses_shell": true,
            "argv": null,
            "chdir": null,
            "creates": null,
            "executable": "/bin/bash",
            "removes": null,
            "stdin": null,
            "stdin_add_newline": true,
            "strip_empty_ends": true
        }
    },

So it’s like before, it finished restoring on the replicas, but get’s stuck at the master node. No ansible output whatsoever and even with timeout set to 180 Seconds it waits much longer than that.

Add the minio domain in the etc_hosts variable.

It’s a FQDN with proper DNS set up, the error came only due to me not adding the repo1-s3-uri-style-Option.

Think I got it working now, at least the bucket is now getting populated with archives on an archive run as well as no more errors in the logs.

The error was sitting in front of the terminal after all.

Thanks for your time and patience!