Restore from remote server possible?

[bdurrow]

I have three machines, PROD, STAGE, BACKUP. BACKUP is docker container that runs pghoard. BACKUP uses a replica slot to backup PROD. Before a deployment I use pghoard_restore to setup STAGE with the most recent data from PROD (without any additional load on PROD).

I upgraded BACKUP last night now STAGE can't perform a restore. It seems like without a shared filesystem there isn't a way to get the xlogs that STAGE needs to make the base backup consistent from the the object store. The pghoard_postgres_command that is set in recovery.conf restores the necessary files to BACKUP (not STAGE where they are needed).

Could you provide some information about the vision of how recovery is supposed to work? Is my use case wildly different than what you imagined? Am I supposed modify the config so that I can safely run it locally on STAGE then interact with that instance of pghoard?

[Ormod]

I'm not sure I understand your setup fully, are you using "local" as object storage or an actual object storage? Also what version of PostgreSQL are you running? Pre 9.3 versions need to take a new basebackup every time you promote a node to be the new master since it doesn't transfer the required .history files over the replication protocol.

Currently if you use a remote object storage you need a pghoard daemon running on the machine to be restored, since the daemon is the thing that actually both downloads the files and writes them to the xlog directory. So if you were running a remote object store you'd just have to also run a pghoard daemon in your STAGE during restore. (and point the pghoard_postgres_command at the local http server).

We'd also like to support the other way around where you'd need only a HTTP accessible PGHoard daemon available on some other machine and the machine which was being restored only had the command line tools installed. In order to support this the pghoard_postgres_command would need a new commandline option to download the required files over HTTP from the webserver and the Webserver would need to support letting the client download both basebackups and WALs.

We originally had some support for this but a cleanup commit fb6ca61335c33396fb461523b4e6b3efecc062cb removed the support. We'd love to get the ability back in a cleanly implemented way. (attached with unittests proving that it works)

PRs implementing this would be welcome.

[bdurrow]

We use S3 as object storage and Postgres 9.4. Even with 9.4 we don't get enough data from the basebackup to cleanly start; we receive an error message suggesting we remove backup_label.

I'll work to change our scripts to run pghoard as a daemon while restoring to STAGE. Is there a simple way to reuse the config .json file from BACKUP and neuter it so there is no corruption (or contention) of the object store by STAGE and BACKUP both writing?

[bdurrow]

What do you think about reusing the existing logic for the webserver that retrieves the file to the local filesystem then if a header is set on the request offers a 302 (temporary redirect) instead of a 200 to the file for fetching. After pghoard_postgres_command successfully retrieved the file it could make an additional call to remove the unneeded file. I would need to be careful not to allow an attacker to arbitrarily return and/or delete files outside of pg_xlog_directory.

I propose that if the request header x-pghoard-target-http is set (to anything) that x-pghoard-target-path is ignored and triggers the logic described above.

I think I could use similar logic to serve up basebackups. I'll look through the cleanup commit to understand what method used to work to try to make this backward compatible.

[bdurrow]

How do you imagine the unit tests working? There are two "remote" relationships, in our use case they are represented by STAGE -> BACKUP and BACKUP -> S3. I haven't written any python before and writing a complex unit test like this is daunting. I'm guessing I would need to extend test_webserver.py to confirm that data is returned when appropriate. I don't see where pghoard_postgres_command is exercised but I would need to add something there and finally test the config is generated properly in test_restore.py.

[Ormod]

To mark a site inactive on your STAGE (meaning don't backup anything from it) you should set the configuration key active to false in the site's config. ---clip--- (active (default true)

Can be set on a per backup_site level to false to disable the taking of new backups and to stop the deletion of old ones. ---clip---

The header thing you're proposing for selecting which way to save the file sounds ok to me though I'm not sure what you meant by: "After pghoard_postgres_command successfully retrieved the file it could make an additional call to remove the unneeded file. "

The thing is even after a restore you would not want to remove the xlog file from your archive since you'd probably want to keep it for as long as a basebackup that might potentially need it exists. On the fetcher side PostgreSQL itself will take care of deleting the file from pg_xlog eventually.

As for the tests test_webserver actually excercises both the webserver and the pghoard_postgres_command code. (see the use of restore_command which calls the code from the command line tool directly)

You should be able to reuse the test code for restore_command and webserver in test_webserver.py pretty much as is and just add a new test for the new command line flags.

Anyway if you have any questions please don't hesitate to ask for more info.

[bdurrow]

The header thing you're proposing for selecting which way to save the file sounds ok to me though I'm not sure what you meant by: "After pghoard_postgres_command successfully retrieved the file it could make an additional call to remove the unneeded file. "

The thing is even after a restore you would not want to remove the xlog file from your archive since you'd probably want to keep it for as long as a basebackup that might potentially need it exists. On the fetcher side PostgreSQL itself will take care of deleting the file from pg_xlog eventually.

I was thinking that I wouldn't want to keep the transient xlog around after it was retrieved. I'm not suggesting I remove it from the object store. Perhaps for now I should just have the GET that is a result of the 302 handle the cleanup if it completes successfully (server side logic) and later work to return the file without the use of a temporary file. Thoughts?

[Ormod]

No need to use temp files for the first version of the xlog http retrieval. The xlogs are 16 megs in size and since the caller is the PG restore_command there would only be one in transit at a time so you'd just need to call. mybyteobject = object_storage.get_contents_to_string(key)

In general I'd say it'd be acceptable for v1 of this http download thing to not use prefetching since that's just an optimization. I'd leave adding prefetch support for this for later.

[bdurrow]

I believe I need to create test_storage_configs.py to allow for object store testing. Can you provide an example for how to set this up?