restoring from WAL-E backup

[yayitswei]

Hi, thanks for making Patroni, I think it's a good fit for my use case and I'm currently putting together a proof of concept for my team.

Assuming I have a WAL-E backup on S3 (both an initial basebackup and WAL segments), how would I configure patroni to restore the backup? I've looked through the documentation, particularly the custom bootstrap section but I believe I'm not understanding a few things about how the configuration works. Here's my patroni.yml config:

bootstrap:
  method: restore_from_backup
  restore_from_backup:
    command: patroni_wale_restore --envdir /etc/wal-e.d/env --scope pg-test-cluster --connstring postgresql://postgres@0.0.0.0 --datadir /val/lib/postgresql/data
    recovery_conf: 
       recovery_target_action: promote
       recovery_target_timeline: latest
       restore_command: patroni_wale_restore --envdir /etc/wal-e.d/env --scope pg-test-cluster --connstring postgresql://postgres@0.0.0.0 --datadir /val/lib/postgresql/data
  
  dcs:
    postgresql:
      use_pg_rewind: true
      create_replica_method:
        - wal_e
        - basebackup
      wal_e:
        command: patroni_wale_restore --envdir /etc/wal-e.d/env --scope pg-test-cluster --connstring postgresql://postgres@0.0.0.0 --datadir /val/lib/postgresql/data
        no_master: 1
  pg_hba:
    - host all all 0.0.0.0/0 md5
    - host replication replicator 10.0.0.0/8 md5

As you can see, I've duplicated the patroni_wale_restore command a few times since I'm not sure where it goes. Getting a few warnings and an error:

/usr/local/lib/python3.5/dist-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
...
psycopg2.OperationalError: could not connect to server: Connection refused
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
	Is the server running on host "0.0.0.0" and accepting TCP/IP connections on port 5432?
...
2018-03-06 20:47:39,724 INFO: Failed to import patroni.dcs.consul
2018-03-06 21:08:09,316 ERROR: could not determine difference with the master location

Any pointers would be appreciated. Thanks!

[CyberDem0n]

Hi,

Let's assume that you have a backup of your cluster and archive logs on S3.

What Patroni can do with it:

1. It can create a new replica in your exiting cluster with the help of patroni_wale_restore (it will make some decisions whether to get backup from s3 or handover to basebackup). And here you can take an example of configuration for patroni_wale_restore.
2. With the help of custom bootstrap you can create a new Patroni cluster, restored either up to the last available wal segment, to specific transaction or to a specific time (PITR) and patroni_wale_restore can' do it. You have to write your own script for that. Here is an example how we did it: https://github.com/zalando/spilo/blob/master/postgres-appliance/bootstrap/clone_with_s3.py

And here you can have a look how it could be configured.

[yayitswei]

Yes, I'm looking for a solution to 2., creating a new cluster restored to the latest point from a previous backup. Thanks a lot for the examples, I'll try to adapt it for our case. Just to confirm, does clone_with_s3.py start with the latest WAL-E base backup on S3 (pushed with WAL-E's backup-push), or does it work only with WAL segments?

Unrelated question- any chance Spilo works with Docker Swarm instead of Kubernetes?

[CyberDem0n]

clone_with_s3.py gets --recovery-target-time argument and tries to find the most suitable backup for that. It's not necessary would be a latest one.

We never tried it on Docker Swarm. But I think it more or less boils down to providing correct configuration via environment variables.