Обсуждение: WAL archive cleanup om master
Hoi, I've setup a up WAL shipping configuration as described in the wiki. On the master I have: archive_mode = on archive_command = 'cp %p /path_to/archive/%f' and on the slave in the recovery.conf I have: archive_cleanup_command = 'pg_archivecleanup /path/to/archive/ %r' restore_command = 'cp /path/to/archive/%f %p' In between the two machines I have an rsync process copying the files from the master to the slave. My problem is, that the archive_cleanup_command on the slave is clearing old WAL files, which the rsync on the master just copies again because they are still on the master. I can make a script on the master that deletes files older than an hour, but that will break horribly if the copying breaks for an hour. Is there a smarter way to do this, like having rsync not copy stuff already copied once? Thanks in advance, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
Вложения
On 01/06/2014 03:18 AM, Martijn van Oosterhout wrote: > Hoi, > > I've setup a up WAL shipping configuration as described in the wiki. On > the master I have: > > archive_mode = on > archive_command = 'cp %p /path_to/archive/%f' > > and on the slave in the recovery.conf I have: > > archive_cleanup_command = 'pg_archivecleanup /path/to/archive/ %r' > restore_command = 'cp /path/to/archive/%f %p' > > In between the two machines I have an rsync process copying the files > from the master to the slave. > > My problem is, that the archive_cleanup_command on the slave is > clearing old WAL files, which the rsync on the master just copies again > because they are still on the master. > > I can make a script on the master that deletes files older than an > hour, but that will break horribly if the copying breaks for an hour. > > Is there a smarter way to do this, like having rsync not copy stuff > already copied once? So the rsync and archive_command combination is a belt and suspenders thing? As to rsync, if I understand what you want you might take a look at --existing: http://linux.die.net/man/1/rsync --existing, --ignore-non-existing This tells rsync to skip creating files (including directories) that do not exist yet on the destination. If this option is combined with the --ignore-existing option, no files will be updated (which can be useful if all you want to do is delete extraneous files). > > Thanks in advance, > -- Adrian Klaver adrian.klaver@gmail.com
On Mon, Jan 06, 2014 at 07:16:25AM -0800, Adrian Klaver wrote: > On 01/06/2014 03:18 AM, Martijn van Oosterhout wrote: > >Hoi, > > > >I've setup a up WAL shipping configuration as described in the wiki. On > >the master I have: > > > >archive_mode = on > >archive_command = 'cp %p /path_to/archive/%f' > > > >and on the slave in the recovery.conf I have: > > > >archive_cleanup_command = 'pg_archivecleanup /path/to/archive/ %r' > >restore_command = 'cp /path/to/archive/%f %p' > > > >In between the two machines I have an rsync process copying the files > >from the master to the slave. > > > >My problem is, that the archive_cleanup_command on the slave is > >clearing old WAL files, which the rsync on the master just copies again > >because they are still on the master. > > > >I can make a script on the master that deletes files older than an > >hour, but that will break horribly if the copying breaks for an hour. > > > >Is there a smarter way to do this, like having rsync not copy stuff > >already copied once? > > So the rsync and archive_command combination is a belt and suspenders thing? I'm not sure what you mean, isn't this the recommended way of doing things? The configuration comes from here: http://wiki.postgresql.org/wiki/Hot_Standby The master saves the archives to a directory, rsync copies them to the slave, where there restore_command can find them. I suppose you could get the archive_command to rsync directly, but will that work in the case of network failure? Note that from a network topology view, the slave cannot connect to the master, so streaming replication is out. > As to rsync, if I understand what you want you might take a look at > --existing: > > http://linux.die.net/man/1/rsync I don't think that will help me, since the rsync is supposed to copy the new WAL files. The problem is that it needs to *not* copy the old ones. However, right under that switch I see --remove-source-files which looks closer, as long as I only have one slave that is. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
Вложения
On 01/06/2014 03:18 AM, Martijn van Oosterhout wrote: > Hoi, > > I've setup a up WAL shipping configuration as described in the wiki. On > the master I have: > > archive_mode = on > archive_command = 'cp %p /path_to/archive/%f' > > and on the slave in the recovery.conf I have: > > archive_cleanup_command = 'pg_archivecleanup /path/to/archive/ %r' > restore_command = 'cp /path/to/archive/%f %p' > > In between the two machines I have an rsync process copying the files > from the master to the slave. > > My problem is, that the archive_cleanup_command on the slave is > clearing old WAL files, which the rsync on the master just copies again > because they are still on the master. > > I can make a script on the master that deletes files older than an > hour, but that will break horribly if the copying breaks for an hour. > > Is there a smarter way to do this, like having rsync not copy stuff > already copied once? From the Department of Second Thoughts and with aid of additional caffeine, I am fairly certain that the rsync switch I sent in previous post will not help. If the archive_command cp is working then rsync will have nothing to do. If the archive_command cp is not working then rsync will not create any new files in its absence. > > Thanks in advance, > -- Adrian Klaver adrian.klaver@gmail.com
On 01/06/2014 07:35 AM, Martijn van Oosterhout wrote: > On Mon, Jan 06, 2014 at 07:16:25AM -0800, Adrian Klaver wrote: >> On 01/06/2014 03:18 AM, Martijn van Oosterhout wrote: >>> Hoi, >>> >>> > > I'm not sure what you mean, isn't this the recommended way of doing > things? The configuration comes from here: > > http://wiki.postgresql.org/wiki/Hot_Standby > > The master saves the archives to a directory, rsync copies them to > the slave, where there restore_command can find them. Well this is the above is where is could be redundant. In your original post it would seem the archive_command and restore_command are pointing at the same directory. I realize they are just placeholder names, so is that the case? If so I am not sure what the rsync accomplishes. If not why not just make it so? The minimal cover your bases setup is usually given as: Primary(Machine1) --> Archive --> Archive Directory(Machine2) | | | Streaming Restore | -> Standby(Machine3) < - Excuse the ASCII art. The Standby will stream WAL unless the connection to the primary goes down then it will pull from the Archive directory. Not sure what your setup is and what you want. That will determine the archiving procedure. > > Have a nice day, > -- Adrian Klaver adrian.klaver@gmail.com
On Mon, Jan 06, 2014 at 08:17:37AM -0800, Adrian Klaver wrote: > On 01/06/2014 07:35 AM, Martijn van Oosterhout wrote: > >On Mon, Jan 06, 2014 at 07:16:25AM -0800, Adrian Klaver wrote: > >>On 01/06/2014 03:18 AM, Martijn van Oosterhout wrote: > >>>Hoi, > >>> > >>> > > > >I'm not sure what you mean, isn't this the recommended way of doing > >things? The configuration comes from here: > > > >http://wiki.postgresql.org/wiki/Hot_Standby > > > >The master saves the archives to a directory, rsync copies them to > >the slave, where there restore_command can find them. > > Well this is the above is where is could be redundant. In your > original post it would seem the archive_command and restore_command > are pointing at the same directory. I realize they are just > placeholder names, so is that the case? If so I am not sure what > the rsync accomplishes. If not why not just make it so? Well, they're the same directory on different machines. The directory is actually /var/lib/postgresql/9.2/xlogs on both, but that's not really relevent. There's a cronjob on the master that says: rsync -avz /var/lib/postgresql/9.2/xlogs/* slave:/var/lib/postgresql/9.2/xlogs/ The question is: what is it that prevents the WAL files in /var/lib/postgresql/9.2/xlogs from filling the disk on the master? > The minimal cover your bases setup is usually given as: > > > Primary(Machine1) --> Archive --> Archive Directory(Machine2) > | | > | Streaming Restore | > -> Standby(Machine3) < - > > Excuse the ASCII art. The ascii art is fine, but you have the same problem: on the Machine1 the WAL files are stored prior to copying to Machine2. How do you know when you can delete files safely on Machine1? Anyway, I found this page on the wiki: http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial which says that: archive_command = 'rsync -a %p 192.168.0.2:/var/lib/pgsql/data/archive/%f' is acceptable and also avoids the problem. I'll just test what happens with network failure (hopefully it doesn't kill the master). Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
Вложения
On 01/06/2014 08:45 AM, Martijn van Oosterhout wrote: > On Mon, Jan 06, 2014 at 08:17:37AM -0800, Adrian Klaver wrote: >> On 01/06/2014 07:35 AM, Martijn van Oosterhout wrote: >>> On Mon, Jan 06, 2014 at 07:16:25AM -0800, Adrian Klaver wrote: >>>> On 01/06/2014 03:18 AM, Martijn van Oosterhout wrote: >>>>> Hoi, >>>>> >>>>> >>> >>> I'm not sure what you mean, isn't this the recommended way of doing >>> things? The configuration comes from here: >>> >>> http://wiki.postgresql.org/wiki/Hot_Standby >>> >>> The master saves the archives to a directory, rsync copies them to >>> the slave, where there restore_command can find them. >> >> Well this is the above is where is could be redundant. In your >> original post it would seem the archive_command and restore_command >> are pointing at the same directory. I realize they are just >> placeholder names, so is that the case? If so I am not sure what >> the rsync accomplishes. If not why not just make it so? > > Well, they're the same directory on different machines. The directory > is actually /var/lib/postgresql/9.2/xlogs on both, but that's not > really relevent. > > There's a cronjob on the master that says: > > rsync -avz /var/lib/postgresql/9.2/xlogs/* slave:/var/lib/postgresql/9.2/xlogs/ > > The question is: what is it that prevents the WAL files in > /var/lib/postgresql/9.2/xlogs from filling the disk on the master? > >> The minimal cover your bases setup is usually given as: >> >> >> Primary(Machine1) --> Archive --> Archive Directory(Machine2) >> | | >> | Streaming Restore | >> -> Standby(Machine3) < - >> >> Excuse the ASCII art. > > The ascii art is fine, but you have the same problem: on the Machine1 > the WAL files are stored prior to copying to Machine2. How do you know > when you can delete files safely on Machine1? WAL files are recycled by the server. http://www.postgresql.org/docs/9.3/interactive/continuous-archiving.html 24.3.1. Setting Up WAL Archiving " When not using WAL archiving, the system normally creates just a few segment files and then "recycles" them by renaming no-longer-needed segment files to higher segment numbers. It's assumed that segment files whose contents precede the checkpoint-before-last are no longer of interest and can be recycled. When archiving WAL data, we need to capture the contents of each segment file once it is filled, and save that data somewhere before the segment file is recycled for reuse. " For a more detailed explanation of those settings that affect this see: http://www.postgresql.org/docs/9.3/interactive/wal-configuration.html in particular http://www.postgresql.org/docs/9.3/interactive/runtime-config-replication.html#GUC-WAL-KEEP-SEGMENTS > > Anyway, I found this page on the wiki: > > http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial > > which says that: > > archive_command = 'rsync -a %p 192.168.0.2:/var/lib/pgsql/data/archive/%f' > > is acceptable and also avoids the problem. I'll just test what happens > with network failure (hopefully it doesn't kill the master). > > Have a nice day, > -- Adrian Klaver adrian.klaver@gmail.com