From: Chris Hoover <revoohc@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Why so long between archive calls?
Date: Wed, 30 Aug 2006 15:12:52 -0400
Message-ID: <1d219a6f0608301212k699061d0ge52c953ecc5bddfa@mail.gmail.com> (raw)
In-Reply-To: <1d219a6f0608290934g6d3ed785oa2814dbf3619306b@mail.gmail.com>
References: <1d219a6f0608290835u6647a8e1g437dfd4cedb81658@mail.gmail.com>
<44F4690D.2010503@mailnetwork.co.uk>
<1d219a6f0608290934g6d3ed785oa2814dbf3619306b@mail.gmail.com>
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@gmail.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.shellam-lists@mailnetwork.co.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
> >
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: revoohc@gmail.com
Subject: Re: Why so long between archive calls?
In-Reply-To: <1d219a6f0608301212k699061d0ge52c953ecc5bddfa@mail.gmail.com>
References: <1d219a6f0608290835u6647a8e1g437dfd4cedb81658@mail.gmail.com> <44F4690D.2010503@mailnetwork.co.uk> <1d219a6f0608290934g6d3ed785oa2814dbf3619306b@mail.gmail.com> <1d219a6f0608301212k699061d0ge52c953ecc5bddfa@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox