Greetings, * Rui DeSousa (rui.desousa@xxxxxxxxxx) wrote: > > On Mar 5, 2018, at 6:15 AM, Stephen Frost <sfrost@xxxxxxxxxxx> wrote: > > Using replication slots and pg_receivewal can be a good solution if > > you're on a version which supports the combination of both. That > > doesn't mean the proposed archive commands are any good though, just to > > be clear. An issue with pg_receivewal though is that it's > > single-threaded. > > I must say this is a bit absurd; I didn’t realize that telling someone not to delete Postgres WAL files from underneath Postgres would require me to provide a complete redundant backup solution. It doesn't- but please don't encourage partial solutions which have very clear issues. > Pg_receivxlog or the archive_command being single threaded is not an issue; at least not for me and I’m generating 1/2TB of WALs a day. The real problem is that applying the WALs is single threaded — trying to apply a single days worth of WALs takes too long and is one of the reasons I take multiple backups a day to reduce the number of WALs required during a PITR. I agree that the single-threaded nature of pg_basebackup and pg_receivewal isn't an issue for all environments, and that it would be good to have a way to replay WAL faster, and that performing frequent incrementals is a way to reduce the amount of WAL to be replayed. > The solution you proposed; would not be able to keep up with the rate of backups I issue daily nor is it capable of taking a backups on the replica at least not yet from the presentation I reviewed. I do my backups on replicas at multiple sites with WAL files also being stored at multiple sites. I also do daily restores in a lower environment which take less than 5 minutes to do — obviously I’m making extensive use of snapshots and snapshot replication. The only solution which I mentioned on this thread was pgBackRest, which keeps up very well with very frequent backups, including those which utilize a replica, so I'm not sure what you're referring to here. pgBackRest is also able to perform fast restores when there have been few changes. Note that you had suggested pg_receivewal- I was just commenting on it. Snapshots and snapshot replication can be useful, but it's vital to ensure that the entire snapshot is taken atomically across all tablespaces (unless you're doing both snapshots *and* using pg_start/stop_backup, collecting necessary WAL, and being sure to put the backup_label file into place during restore). > If your archive server is crashing then you have other issues and one should work to remove single points of failure. I’m not sure what filesystem you’re using but the one I use sync to disk every 30 seconds. This thread began with a concern about an unreliable network, I don't think it's unreasonable to consider other failure scenarios. PostgreSQL performs a great deal of work to ensure that data is written out in a manner which can be recovered in the event of a server crash- utilities which are also working with that data should be similairly resiliant. > The original poster stated that rsync wasn’t even an option and it not even using it. I’m not here trying to push a backup solution or anything else; I was just trying give some simple advice to the given problem. I agree that rsync is a bad approach. Similairly, proposing archive commands which don't do the basics, such as ensure the WAL file has been sync'd to disk before returning success, isn't good. Thanks! Stephen
Attachment:
signature.asc
Description: PGP signature
