Re: Why so long between archive calls?

Any other ideas? I am still 508 archive log files behind.

Here is my postgresql.conf

listen_addresses = '*'
port = 50001
max_connections = 256
shared_buffers = 32768
max_prepared_transactions = 256
work_mem = 4096
maintenance_work_mem = 1048576
max_fsm_pages = 8000000
max_fsm_relations = 10000
vacuum_cost_delay = 1000
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 8000
wal_buffers = 64
checkpoint_segments = 256
checkpoint_timeout = 1800
archive_command = '/home/postgres/bin/archive_pg_xlog.sh %p %f 50001'
effective_cache_size = 383490
random_page_cost = 2
default_statistics_target = 100
constraint_exclusion = on
redirect_stderr = on
log_directory = 'pg_log'
log_truncate_on_rotation = on
log_rotation_age = 1440
log_rotation_size = 1048576
log_min_messages = debug2
log_min_duration_statement = 0
log_connections = on
log_disconnections = on
log_duration = on
log_line_prefix = '%d,%p,%u,%m,%c,%l,%s,%x,%i,'
log_statement = 'all'
stats_start_collector = on
stats_command_string = on
stats_block_level = on
stats_row_level = on
stats_reset_on_server_start = on
autovacuum = on
autovacuum_naptime = 60
autovacuum_vacuum_threshold = 1000
autovacuum_analyze_threshold = 500
autovacuum_vacuum_scale_factor = 0.001
autovacuum_analyze_scale_factor = 0.0005
autovacuum_vacuum_cost_delay = -1
autovacuum_vacuum_cost_limit = -1
statement_timeout = 0
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
add_missing_from = on

On 8/29/06, Chris Hoover <revoohc(at)gmail(dot)com> wrote:
>
> The oldest log file that is not archived is:
> -rw------- 1 postgres postgres 16777216 Aug 28 15:24
> 00000001000001930000005A
>
> The command I am using is running successfully and I am getting good
> entries in my log files.
> ,15282,,2006-08-29 11:07:04.443 EDT,,13552,,,,LOG: archived transaction
> log file "00000001000001930000004E"
> ,15282,,2006-08-29 11:22:54.982 EDT,,13553,,,,LOG: archived transaction
> log file "00000001000001930000004F"
> ,15282,,2006-08-29 11:24:40.126 EDT,,13554,,,,LOG: archived transaction
> log file "000000010000019300000050"
> ,15282,,2006-08-29 11:32:04.650 EDT,,13555,,,,LOG: archived transaction
> log file "000000010000019300000051"
> ,15282,,2006-08-29 11:33:30.900 EDT,,13556,,,,LOG: archived transaction
> log file "000000010000019300000052"
> ,15282,,2006-08-29 11:37:21.333 EDT,,13557,,,,LOG: archived transaction
> log file "000000010000019300000053"
> ,15282,,2006-08-29 11:44:50.792 EDT,,13558,,,,LOG: archived transaction
> log file "000000010000019300000054"
> ,15282,,2006-08-29 12:24:54.566 EDT,,13559,,,,LOG: archived transaction
> log file "000000010000019300000055"
> ,15282,,2006-08-29 12:25:08.530 EDT,,13560,,,,LOG: archived transaction
> log file "000000010000019300000056"
> ,15282,,2006-08-29 12:25:10.754 EDT,,13561,,,,LOG: archived transaction
> log file "000000010000019300000057"
> ,15282,,2006-08-29 12:25:14.619 EDT,,13562,,,,LOG: archived transaction
> log file "000000010000019300000058"
>
> So, it looks like everything is good, just the archive command is not
> getting called enough.
>
> Thanks,
>
> Chris
>
>
> On 8/29/06, Andy Shellam <andy(dot)shellam-lists(at)mailnetwork(dot)co(dot)uk > wrote:
> >
> > Chris,
> >
> > I take it you've checked that the archive_command is actually succeeding
> >
> > (check the postgresql log file.)
> > Perhaps try running it manually with an early log file as the PostgreSQL
> > user account?
> >
> > When you say you're 500 files behind, how old is the earliest log?
> >
> > Andy.
> >
> > Chris Hoover wrote:
> > > I am working on getting my development system working with archive
> > > logging so I can use the pitr features of Postgres. However, I am
> > > finding that the actual archiving is not keeping up with the
> > > generation of the logs. I am now over 500 wal archive files behind
> > > (8+ GB).
> > >
> > > What controls how often the archive_command is called?
> > >
> > > The actual running of my archive script is taking 1-2 seconds, so I'm
> > > not sure why we are so far behind.
> > >
> > > Thanks for any advise,
> > >
> > > Chris
> > >
> > > PG 8.1.3
> > > RH AS 4
> > > !DSPAM:37,44f45f03143293039237985!
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> >
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
>