Postgres Pro
Downloads
Home   >   mailing lists

Archiving while idle every archive_timeout with wal_level hot_standby - Mailing list pgsql-general

From Laurence Rowe
Subject Archiving while idle every archive_timeout with wal_level hot_standby
Date
Msg-id CAOycyLTc0+jtJhsTOs5i7gJLEuKW_Y8yxBQms0yXY12HxemfBA@mail.gmail.com
Whole thread Raw
List pgsql-general
Tree view
I'm seeing Postgres 9.4.4 archive while idle every archive_timeout when I set ``wal_level hot_standby``:

$ initdb testpg
$ cat << 'EOF' >> testpg/postgresql.conf
> wal_level = hot_standby
> archive_mode = on
> archive_timeout = 10
> checkpoint_timeout = 1h
> archive_command = 'echo $(date) archive %p'
> log_checkpoints = on
> EOF
$ postgres -D testpg
LOG:  database system was shut down at 2015-10-13 11:58:45 PDT
LOG:  MultiXact member wraparound protections are now enabled
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
Tue Oct 13 12:00:47 PDT 2015 archive pg_xlog/000000010000000000000001
Tue Oct 13 12:00:57 PDT 2015 archive pg_xlog/000000010000000000000002
LOG:  checkpoint starting: xlog
Tue Oct 13 12:01:07 PDT 2015 archive pg_xlog/000000010000000000000003
LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.026 s; sync files=0, longest=0.000 s, average=0.000 s
Tue Oct 13 12:01:17 PDT 2015 archive pg_xlog/000000010000000000000004
Tue Oct 13 12:01:27 PDT 2015 archive pg_xlog/000000010000000000000005
LOG:  checkpoint starting: xlog
Tue Oct 13 12:01:38 PDT 2015 archive pg_xlog/000000010000000000000006
LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 3 recycled; write=0.000 s, sync=0.000 s, total=0.027 s; sync files=0, longest=0.000 s, average=0.000 s
Tue Oct 13 12:01:48 PDT 2015 archive pg_xlog/000000010000000000000007
Tue Oct 13 12:01:58 PDT 2015 archive pg_xlog/000000010000000000000008
LOG:  checkpoint starting: xlog
LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 3 recycled; write=0.000 s, sync=0.000 s, total=0.001 s; sync files=0, longest=0.000 s, average=0.000 s


At ``wal_level archive`` I only see archiving every checkpoint_timeout (see http://www.postgresql.org/message-id/1407389876762-5813999.post@n5.nabble.com):

$ initdb testpg
$ cat << 'EOF' >> testpg/postgresql.conf
> wal_level = archive
> archive_mode = on
> archive_timeout = 10
> checkpoint_timeout = 60
> archive_command = 'echo $(date) archive %p'
> log_checkpoints = on
> EOF
# lrowe@Laurences-iMac ~/scratch 
$ postgres -D testpg
LOG:  database system was shut down at 2015-10-13 12:25:38 PDT
LOG:  MultiXact member wraparound protections are now enabled
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
Tue Oct 13 12:25:49 PDT 2015 archive pg_xlog/000000010000000000000001
LOG:  checkpoint starting: time
LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.029 s; sync files=0, longest=0.000 s, average=0.000 s
Tue Oct 13 12:26:39 PDT 2015 archive pg_xlog/000000010000000000000002
LOG:  checkpoint starting: time
LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 1 recycled; write=0.000 s, sync=0.000 s, total=0.030 s; sync files=0, longest=0.000 s, average=0.000 s
Tue Oct 13 12:27:39 PDT 2015 archive pg_xlog/000000010000000000000003


Is there some way to avoid the extra archiving while idle at ``wal_level hot_standby``?


Laurence

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Drop or alter column under load give ERROR #42804 structure of query does not match function result type:
Next
From: droberts
Date:
Subject: Re: ID column naming convention