Postgres replica node restarts with blank data directory

[the-redback]

Currently, when the replica node (pod) starts/restarts, it deletes its data directory, then takes pg_basebackup of primary node, then starts as standby server. ref: https://github.com/kubedb/postgres/blob/b0ed4c6ee2ab737001ef9fc2e3e1b1d48f6bfa24/hack/docker/postgres/9.6.7/scripts/replica/run.sh#L8

Though we can avoid this 'deleteion & pg_basebackup' for replica pod restarts, yet problem can arise if the replica node is unavailable for a long time, and some WAL objects are deleted after that. So, after becoming available, it will lack WAL files, so data loss can happen.

some reference regarding this topic.

• https://info.crunchydata.com/blog/wheres-my-replica-troubleshooting-streaming-replication-synchronization-in-postgresql

Though. replication_slot is a good alternative, it needs to be drop when coresponding replica node removes itself from cluster. The problem is, It's hard to determine which pod is terminating for good, and which one is restarting.

[the-redback]

cc: @stormmore

[the-redback]

When a previous master is becoming a new Standby, it may throw error due to conflict in timeline.
In this case, pg_rewind is a good tool to sync old master to new master, without deleting data directory.

• Scenario: http://hlinnaka.iki.fi/presentations/NordicPGDay2015-pg_rewind.pdf
• pg_rewind official doc: https://www.postgresql.org/docs/9.6/app-pgrewind.html

PG_REWIND will also help to find if any WAL is missing from primary or standby, to resume the standby.

[the-redback]

Why pg_rewind? refs:

• Resolves conflict of different timelines without taking full pg_basebackup. ref:
  1. http://hlinnaka.iki.fi/presentations/NordicPGDay2015-pg_rewind.pdf
  2. part(1 of 3) https://blog.2ndquadrant.com/introduction-to-pgrewind/
  3. part(2 of 3) https://blog.2ndquadrant.com/pgrewind-and-pg95/
  4. part(3 of 3) https://blog.2ndquadrant.com/back-to-the-future-part-3-pg_rewind-with-postgresql-9-6/

Why don't just pull all WAL file?

• Doesn't solve conflict between different timelines, (mostly, in failover scenario, where a standby node becomes primary)
  So, after pulling wal files, it is required to run pg_rewind.

pw_rewind. Possible error:

1. target server must be shut down cleanly
2. could not find previous WAL record at 0/30000F8
3. could not find common ancestor of the source and target cluster's timelines
4. target server needs to use either data checksums or "wal_log_hints = on"

[the-redback]

• Error 1: target server must be shut down cleanly

  Target database (localhost) must be shutdown cleanly to perform pg_rewind.
  So, check and if necessary, re-stop the database gracefully. Then, run pg_rewind again.

• Error 2: could not find previous WAL record at 0/30000F8

  If the server diverged from primary and the diverged WAL doesn't exist anymore,
  pg_rewind will throw an error similar to "could not find previous WAL record at 0/30000F8".
  We have to manually fetch WALs starting from the missing point.
  At this point, we will take pg_basebackup.
  TODO: for wal-g or other kind of wal-archiving, fetch missing WALs from archive storage (may be). Then, run pg_rewind again

• Error 3: could not find common ancestor of the source and target cluster's timelines

  Since 9.6, pg_rewind is very powerful to find common ancestor while running on non-promoted master.
  ref: https://www.enterprisedb.com/blog/pgrewind-improvements-postgresql-96
  Yet, if the error shows up, taking pg_basebackup is a must.

• Error 4: target server needs to use either data checksums or "wal_log_hints = on"

  In case of upgrade from previous database version, where 'wal_log_hints' was not turned on, this error may occur.
  But, will not occur again after adding 'wal_log_hints = on' on config file.
  We could have skipped here and manually pull WAL files so that this node can redo wal files.
  But, again, that will not resolve conflict between timelines.
  So, take base_backup and continue

• Error 5; The error is not any of these.

  In another scenario, pg_rewind is failing and the reason is not 'non-existing WAL' or 'no common ancestor'.
  The workaround could be deleting $PGDATA directory and taking pg_basebackup again.
  But, again the reason is not missing WAl. So, safely exit without processing further.

[Excds]

We're also experiencing this problem and getting the database wiped. We're running with one primary and two replicas.

[Excds]

I can now confirm that using WAL-G archiving does not prevent the data loss.

Setup: one primary and two replicas.

[the-redback]

I am going through a few blog posts to find a solution to this. These blogs might be a good help to learn new approaches:

1. https://severalnines.com/database-blog/top-pg-clustering-high-availability-ha-solutions-postgresql
2. https://hackernoon.com/postgresql-cluster-into-kubernetes-cluster-f353cde212de
3. https://portworx.com/ha-postgresql-kubernetes/
4. https://github.com/CrunchyData/postgres-operator
5. https://github.com/helm/charts/tree/master/stable/postgresql#production-and-horizontal-scaling
6. https://disaev.me/p/how-i-have-set-up-ha-postgresql-on-kubernetes/
7. https://engineering.bitnami.com/articles/create-a-production-ready-postgresql-cluster-bitnami-kubernetes-and-helm.html
8. https://github.com/sorintlab/stolon
9. https://github.com/helm/charts/tree/master/stable/stolon
10. https://github.com/zalando/patroni
11. https://medium.com/talking-tech-all-around/postgresql-high-availability-on-k8s-the-good-hard-way-554072a2ba8e
12. https://info.enterprisedb.com/rs/069-ALB-339/images/HA%20Options%20for%20Postgres%20in%20Containers.pdf
13. https://news.ycombinator.com/item?id=14113560
14. https://news.ycombinator.com/item?id=15868495
15. https://news.ycombinator.com/item?id=14112838
16. https://news.ycombinator.com/item?id=15861952

[the-redback]

1. How zalando's patroni works: https://jobs.zalando.com/tech/blog/zalandos-patroni-a-template-for-high-availability-postgresql/?gh_src=4n3gxh1
2. Kubecon talk on patroni https://www.youtube.com/watch?v=CftcVhFMGSY
3. patroni + pg = spilo docker image
4. Helm chart: https://github.com/helm/charts/tree/master/incubator/patroni

Install : helm install --name my-release incubator/patroni --namespace demo

Few essential commands to test failover [wip]:

$ PGPASSWORD_SUPERUSER=$(kubectl get secret --namespace demo my-release-patroni -o jsonpath="{.data.password-superuser}" | base64 --decode)
$ PGPASSWORD_ADMIN=$(kubectl get secret --namespace demo my-release-patroni -o jsonpath="{.data.password-admin}" | base64 --decode)
$ kubectl run -i --tty --rm psql --image=postgres \                                      
      --env "PGPASSWORD=$PGPASSWORD_SUPERUSER" \
      --command -- psql -U postgres \
      -h my-release-patroni.demo.svc.cluster.local postgres

select pg_is_in_recovery(), txid_current(); \watch

kubectl exec -it -n demo my-release-patroni-3 -- su postgres --command 'patronictl list my-release-patroni'
watch -n 2 "kubectl exec -it -n demo my-release-patroni-3 -- su postgres --command 'patronictl list my-release-patroni'"

controlled failover- Manually change leader [maybe, for maintanance]:

patronictl list my-release-patroni
patronictl failover my-release-patroni

[Excds]

3. patroni + pg = spilo docker image

I spent the last two days testing out Zalando's postgres operator (which uses the spilo image (also theirs)) and I can't say that I'm super happy. Sure, the failover setup seems to work nicely without nuking my data (which kubedb does), but trying to use cloning from wal-g and things does not work great. The python code for managing the configuration of spilo is a bit convoluted.

I don't think using the spilo image as a base for anything would be a good idea (well, except that it does contain the timescaledb extensions by default...).

[Excds]

@tamalsaha @the-redback

Is there any chance that looking into the data loss will have priority soon?

[schmitch]

btw. @the-redback basically pg_rewind is the solution. the first versions of the postgres operator from zalando had a similar problem (i used both projects and even zalando patroni without k8s at all). the also used different solutions for leader election. the newest one is totally different than kubedb uses. (they only change endpoints) and as already said the problem can't happen if wal-g is used.

[Excds]

@schmitch I had the problem happening with wal-g.

[the-redback]

@schmitch, I tried using pg_rewind back in 9.6 version may be. Is it more mature now? coz, It didn't work at that time. :(

[schmitch]

well it works obviously better than loosing data.

[the-redback]

Patroni:

Pros:

• active development.
• cloud-native development focus.
• Officially provided bundled PostgreSQL and Patroni: spilo. https://github.com/zalando/spilo
• Spilo has a broad range of configurations through ENVs. https://github.com/zalando/spilo/blob/master/ENVIRONMENT.rst
• Uses Replication slot over wal streaming. So, the master does not remove WAL segments until they have been received by all standbys. ref: https://www.postgresql.org/docs/12/warm-standby.html#STREAMING-REPLICATION-SLOTS
• Uses Pg-rewind: In case of failover, pg-rewind helps to recover from wal files without wiping out and full basebackup of postgres.
• Supports both synchronous and asynchronous
• How patroni handles split brain: https://dzone.com/articles/managing-high-availability-in-postgresql-part-iii

Cons:

• Written in Python.
• Not enough documentation to know the steps done by spilo.

[Excds]

This is where spilo starts configuring if to use wal-e or wal-g, I'm not confident in this way of configuring something without a single test that it is actually accurate: https://github.com/zalando/spilo/blob/09b4cc3c18c0b1d8e666157171794a2a181cdc14/postgres-appliance/scripts/configure_spilo.py#L718

[Adriien-M]

What is the status of the PR https://github.com/kubedb/postgres/pull/248?
Is it preferable to use patroni rather that kubeDB for postgres database currently?

[thevkevindk]

Guys, My vrp_vehicle gets deleted. Might it have something to do with mysql-async? I read a post that mysql-async is not supported anymore.

Plus I see that I need to alter table cuz the maximum of lenght in the database is to long. I think it has do to with mysql or lscustoms....

[ghost]

Guys, My vrp_vehicle gets deleted. Might it have something to do with mysql-async? I read a post that mysql-async is not supported anymore.

Plus I see that I need to alter table cuz the maximum of lenght in the database is to long. I think it has do to with mysql or lscustoms....

Please delete this and your account, thanks.

[tamalsaha]

Fixed by https://github.com/kubedb/postgres/pull/474

[tamalsaha]

This issue has been resolved in KubeDB release v2021.03.17 . Please open a new bug if you still see this issue.