Thread: archive_timeout and WAL size
Dear community, I'm using PostgreSQL 9.3.6 on Linux x64. Would sound a stupid questions, and sorry if it was already asked before: if I set the "archive_timeout", and then I have them sent every minute (for example), are the files still 16MB in size, or are they truncated and sent in smaller sizes as well? I've not found this information in docs. Thanks, -- Atenciosamente, Edson Carlos Ericksson Richter
On 06/29/2015 11:49 AM, Edson Richter wrote: > Dear community, > > I'm using PostgreSQL 9.3.6 on Linux x64. > > Would sound a stupid questions, and sorry if it was already asked > before: if I set the "archive_timeout", and then I have them sent every > minute (for example), are the files still 16MB in size, or are they > truncated and sent in smaller sizes as well? > I've not found this information in docs. See here: http://www.postgresql.org/docs/9.4/interactive/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING "archive_timeout (integer) The archive_command is only invoked for completed WAL segments. Hence, if your server generates little WAL traffic (or has slack periods where it does so), there could be a long delay between the completion of a transaction and its safe recording in archive storage. To limit how old unarchived data can be, you can set archive_timeout to force the server to switch to a new WAL segment file periodically. When this parameter is greater than zero, the server will switch to a new segment file whenever this many seconds have elapsed since the last segment file switch, and there has been any database activity, including a single checkpoint. (Increasing checkpoint_timeout will reduce unnecessary checkpoints on an idle system.) Note that archived ^^^^^^^^^^^^^^^^^^ files that are closed early due to a forced switch are still the same ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ length as completely full files. Therefore, it is unwise to use a very ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ short archive_timeout — it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable. You should consider using streaming replication, instead of archiving, if you want data to be copied off the master server more quickly than that. This parameter can only be set in the postgresql.conf file or on the server command line. " > > Thanks, > -- Adrian Klaver adrian.klaver@aklaver.com
Thanks, Adrian. That's the reference I was looking for. Atenciosamente, Edson Carlos Ericksson Richter Em 29/06/2015 15:55, Adrian Klaver escreveu: > On 06/29/2015 11:49 AM, Edson Richter wrote: >> Dear community, >> >> I'm using PostgreSQL 9.3.6 on Linux x64. >> >> Would sound a stupid questions, and sorry if it was already asked >> before: if I set the "archive_timeout", and then I have them sent every >> minute (for example), are the files still 16MB in size, or are they >> truncated and sent in smaller sizes as well? >> I've not found this information in docs. > > See here: > > http://www.postgresql.org/docs/9.4/interactive/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING > > > "archive_timeout (integer) > > The archive_command is only invoked for completed WAL segments. > Hence, if your server generates little WAL traffic (or has slack > periods where it does so), there could be a long delay between the > completion of a transaction and its safe recording in archive storage. > To limit how old unarchived data can be, you can set archive_timeout > to force the server to switch to a new WAL segment file periodically. > When this parameter is greater than zero, the server will switch to a > new segment file whenever this many seconds have elapsed since the > last segment file switch, and there has been any database activity, > including a single checkpoint. (Increasing checkpoint_timeout will > reduce unnecessary checkpoints on an idle system.) Note that archived > ^^^^^^^^^^^^^^^^^^ > files that are closed early due to a forced switch are still the same > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > length as completely full files. Therefore, it is unwise to use a very > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > short archive_timeout — it will bloat your archive storage. > archive_timeout settings of a minute or so are usually reasonable. You > should consider using streaming replication, instead of archiving, if > you want data to be copied off the master server more quickly than > that. This parameter can only be set in the postgresql.conf file or on > the server command line. > " >> >> Thanks, >> > >