Hello, I have a master database up in AWS that is using wal-e to archive backups and WAL files. Periodically (say once a week - not "real-time" but regular synching), I would like to have an up-to-date copy/snapshot of this master DB on to a local local virtual machine. We want to experiment with and run reports on the data, at a very safe distance away from the production master!
So far, I've been able to successfully use wal-e and backup-fetch and the recovery.conf file to get a new base backup up and running in Postgres on the local VM. This Rackspace post<http://developer.rackspace.com/blog/postgresql-plus-wal-e-plus-cloudfiles-equals-awesome.html>was quite helpful (though I'm using AWS and not OpenStack) and let me know about needing to change the hot_standby = on setting in postgresql.conf - before I did that I was having trouble getting my VM's Postgres to start up after doing backup-fetch. Now it starts, and I have data from the latest base backup I got with wal-e. I've looked at the wal-e Github documentation and posts on this list, but I can't figure out what I think should be the next step: Now that I have a working snapshot from a while ago, I want to update it and re-play the new WAL files to get the most recent data. I *think* this should be simple and achievable by stopping postgres, re-naming recovery.done to recovery.conf, and then restarting Postgres. However, I can't restart postgres after doing that. The log file, which I've pasted below, complains at the end about hot_standby and wal_level. If possible, I'd rather not change settings on the remote master. *Big question: How do I use recovery.conf and/or wal-fetch to get updates since a base backup was taken from the remote master into my local replica?* For the record, my recovery.conf is short, 1 line: ===== restore_command = ‘envdir /etc/wal-e.d/env /var/lib/postgresql/9.1/env/bin/wal-e wal-fetch “%f” “%p”’ ===== Below is the postgres log, and the part I'm curious about bolded. Thanks for your help and making what seems to be a very capable tool! =========================== Start of Postgres log snippet ================ 2014-05-09 14:55:13 EDT LOG: database system was shut down at 2014-05-09 14:53:42 EDT wal_e.operator.backup INFO MSG: begin wal restore STRUCTURED: time=2014-05-09T18:55:13.811184-00 pid=9921 action=wal-fetch key=s3://database-snapshot-backup/archive/wal_005/00000002.history.lzo prefix=archive/ seg=00000002.history state=begin 2014-05-09 14:55:13 EDT LOG: incomplete startup packet lzop: <stdin>: not a lzop file wal_e.blobstore.s3.s3_util WARNING MSG: could no longer locate object while performing wal restore DETAIL: The absolute URI that could not be located is s3://database-snapshot-backup/archive/wal_005/00000002.history.lzo. HINT: This can be normal when Postgres is trying to detect what timelines are available during restoration. STRUCTURED: time=2014-05-09T18:55:14.214868-00 pid=9921 wal_e.operator.backup INFO MSG: complete wal restore STRUCTURED: time=2014-05-09T18:55:14.216322-00 pid=9921 action=wal-fetch key=s3://database-snapshot-backup/archive/wal_005/00000002.history.lzo prefix=archive/ seg=00000002.history state=complete 2014-05-09 14:55:14 EDT LOG: starting archive recovery wal_e.operator.backup INFO MSG: begin wal restore STRUCTURED: time=2014-05-09T18:55:14.350063-00 pid=9929 action=wal-fetch key=s3://database-snapshot-backup/archive/wal_005/000000020000010700000088.lzo prefix=archive/ seg=000000020000010700000088 state=begin 2014-05-09 14:55:14 EDT FATAL: the database system is starting up lzop: <stdin>: not a lzop file wal_e.blobstore.s3.s3_util WARNING MSG: could no longer locate object while performing wal restore DETAIL: The absolute URI that could not be located is s3://database-snapshot-backup/archive/wal_005/000000020000010700000088.lzo. HINT: This can be normal when Postgres is trying to detect what timelines are available during restoration. STRUCTURED: time=2014-05-09T18:55:14.680576-00 pid=9929 wal_e.operator.backup INFO MSG: complete wal restore STRUCTURED: time=2014-05-09T18:55:14.683109-00 pid=9929 action=wal-fetch key=s3://database-snapshot-backup/archive/wal_005/000000020000010700000088.lzo prefix=archive/ seg=000000020000010700000088 state=complete *2014-05-09 14:55:14 EDT WARNING: WAL was generated with wal_level=minimal, data may be missing* *2014-05-09 14:55:14 EDT HINT: This happens if you temporarily set wal_level=minimal without taking a new base backup.* *2014-05-09 14:55:14 EDT FATAL: hot standby is not possible because wal_level was not set to "hot_standby" on the master server* *2014-05-09 14:55:14 EDT HINT: Either set wal_level to "hot_standby" on the master, or turn off hot_standby here.* *2014-05-09 14:55:14 EDT LOG: startup process (PID 9919) exited with exit code 1* *2014-05-09 14:55:14 EDT LOG: aborting startup due to startup process failure* -- You received this message because you are subscribed to the Google Groups "wal-e" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
