Support bootstrapping spilo cluster from the external PostgreSQL database

[alexeyklyukin]

There is a valid use case of moving already running databases to spilo without doing an expensive dump/restore. In order to support it, we can just pretend that an external PostgreSQL data service is part of the spilo cluster, acting as a leader, with TTL that never expires. Once the cluster is started, spilo nodes will choose replica roles (as the leader is already taken) and will continue to act as replicas until the leader key is explicitly removed by spilo (since it cannot expire on its own).

In order to support this use case, the following parameters should be changed:

• a new primary_conninfo parameter should be added to the yaml configuration for Patroni.
• a global 'standby_mode' flag should be adedd to the DCS at /service/cluster_name/, inidicating that the cluster is containing a non-Patroni managed leader, while all the spilo nodes are running as replicas. This flag would change some operations, i.e. replication slots creation/removal.

From the user point-of-view, the spilo cluster should be configured with the primary_conninfo, pointing to the external PostgreSQL database.
This connection string should also allow normal (non-replication) superuser connections, and the pg_hba.conf on the cluster should be configured to permit such connections from the addresses that would belong to the spilo cluster.

The changes required in the spilo workflow are the following:

• When the first patroni node joins the new (non-initialized cluster), it would set the following keys right after acquiring the initialize one:
  • /service/cluster_name/member/external_$random_suffix with the connection string to the external service (primary_conninfo parameter)
  • /service/cluster_name/leader with the value "external_$random_suffix"\
  • /service/cluster_name/leader/optime with the value 0 (since the leader is not supposed to update the key, but it's still required for some code inside Patroni)
  • /service/cluster_name/stanby_mode to signal that the cluster is running in a special mode.
• When the other spilo nodes join the DCS and observe the standby_mode flag, they start sleeping, periodically checking until this flag is gone. This would prevent cloning an external source multiple times.
• The active patroni will connect to the master and create a new replication slot. Afterwards, it will clone the data from the master and will run the streaming replication.
• At the point the DBA decides the cluster should be separated from the external PostgreSQL node, it would remove the leader key and the member entry, corresponding to that key, also cleaning the replication slot created in the external PostgreSQL node. At this point, the active spilo cluster will come up, taking the leader role (unconditionally, by observing the standby_mode flag) and remove the standby_mode flag, so that other spilo members can start building itself from the leader. Note that the attempt to remove that key before the active spilo replica becomes available should be ignored.
  That command should be built into the spilo comamand-line client, as it's not possible to find the promotion point automatically (for instance, we might stream current changes from the external master to spilo until a certain scheduled maintaince window, where the spilo cluster will be set as a master).

If the active spilo node dies while the standby_mode is still set, another node should notice it and take its place.

[alexeyklyukin]

The particular plan did not work out (*): after playing with the standby mode flag we've decided the interactions with the standby mode flag and the decision on when to clear this flag (given that the "primary standby" node may just crash or shut down) were too complex to manage them automatically, so instead, we would rely on a client to set the keys in DCS in order for the existing cluster to point elsewhere, and clear those keys when we are done. The user setting up the external master would also be responsible for creating replication slots or setting up WAL archiving.

Of course, we need the client support and documented process on how to set things up, which will be a part of this task.

• https://github.com/zalando/patroni/tree/feature/copy_existing_cluster

[lasomethingsomething]

@alexeyklyukin Hi! Any movement on this since Oct 2015? LMK if we can close.

[alexeyklyukin]

We might still need it in some form or another. Let's keep the ticket open for now.

[CyberDem0n]

I believe #252 solves this issue

[lasomethingsomething]

@alexeyklyukin Given @CyberDem0n's last comment, can we close now?