rigger a manual vacuum against the tables. Be sure that the
vacuum_delay_cost features are off (note, there are autovacuum_delay and
vacuum_delay - the plain vacuum_delay_* are for manual vacuums).
Be sure you have the IO to spare though, as it can be IO intensive.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
ivity, but are
there any modules/scripts that are available to help capture (on a
scheduled basis)
and retain necessary server stats in tables?
Thanks in advance.
http://pgfoundry.org/projects/pgstatspack/
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
should not affect those values.
Has your Postgres instance crashed or been restarted with immediate mode
since the last vacuum? This will cause the autovacuum information to be
null.
Also, are you sure that autovacuum has processed these tables at least once?
--
Brad Nicholson 416-673-4106
D
monitoring scripts and cron
jobs to each...
Take a look at Hyperic.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
d the number of live updates (n_tup_upd - n_tup_hot_upd).
Those are the two operations that will require vacuuming. If those
numbers are low, the table will get vacuumed less frequently. If they
are 0, the table will never get vacuumed.
On 07/30/2010 10:36 AM, Brad Nicholson wrote:
On 10-07-30
ive autovacuum?
For starters, we need to know what version of PostgreSQL this is:
select version();
It wouldn't hurt to see the contents of your postgresql.conf (with
all comments removed) and to know something about the table, and how
it's used. One way to get that information would be:
pulate something as critical as
wal files. The folks that wrote it (NTT) are solid though.
> If the archive is stored on a differend harddisk (or storgae system) as the
> data directory, I'd reckon it wouldn't have much impact on the primary
> server. Or am I
ds to be faster on fail over as long as you are
applying the wal files at a reasonable rate.
One further thing to mention - all of these solutions are based on
making the physical blocks available (actually, I'm not sure about
Streaming replication in 9.0). As such, it is possible for co
> [mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Brad Nicholson
> Sent: Friday, July 09, 2010 10:19 AM
> To: Thomas Kellerer
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] High Availability: Hot Standby vs. Warm Standby
>
> On Fri, 2010-07-09 at 18:31 +0200, Thomas Kell
t transactions between streaming replication and 8.4 PITR, I doubt
that letting the standby lag for the duration of the pg_dump is going to
be something that interests you.
Full details are here:
http://www.postgresql.org/docs/9.0/static/hot-standby.html#HOT-STANDBY-CONFLICT
--
Brad Nichols
= 8.3, but I'm
> sure slony 2.x is not backwards compatible.
Slony 1.2.17 and higher is compatible with PG 8.4. I'd go with the
latest stable release though.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-admin mailing list (pgs
cking it and it has been
> growing steadily.
> We must migrate to newer and better optimized hardware.
That will be your sticking point with Slony. If your IO system is
already taxed, Slony will just add to the burden.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Cor
."
Again, this will depend on business drivers.
> After all the switch won't be without interruption - you need to switch
> to the new server anyway.
There is a very big difference between saying the system will be down
for a short duration during a Slony switchover and the system
tems down long
enough to do a dump and restore upgrade.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
in
> viruses which could damage your own computer system. Whilst Grant
> Instruments (Cambridge) Ltd has taken every reasonable precaution to
> minimise this risk, we cannot accept liability for any damage which
> you sustain as a result of software viruses. You should therefore
> c
nauthorised use of their e-mail facilities or e-mails sent other than
> strictly for business purposes.
>
>
>
>
> __
> The contents of and attachments to this e-mail are inte
__
> The contents of and attachments to this e-mail are intended for the
> addressee only, and may contain the confidential information of UCS
> Group and/or its subsidiaries. Any review, use or dissemination
> thereof by anyone other than the intended addressee is prohibited. If
> you are not the intended addressee please notify the writer
> immediately and destroy the e-mail. UCS Group Limited and its
> subsidiaries distance themselves from and accept no liability for
> unauthorised use of their e-mail facilities or e-mails sent other than
> strictly for business purposes.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
>
> Thanks
> Frant101
I didn't have much luck getting pgbouncer to build on AIX, but I didn't
put too much effort into it.
Probably better to ask on the pgbouncer list.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-admi
on in PGCluster.
>
>
> Please let me know if I am unclear in any point. Any input will be a
> great help.
What do you need replication for? DR? Query offloading? Custom schema
or data transformations on a replica?
Can you justify the cost warm standby hardware?
--
Brad Nichols
but I'm not sure exactly which components you will need to
touch.
We always build our binaries passing with this set higher, so I can't
comment on the effects of changing it on existing ones.
> Thanks again for all your help and patience !!
>
> Fran
>
> -Original Messag
available per process.
If you watch your server process while this is happening, it will be
hitting 256MB in size.
Upping this limit is probably the way to go. You can use the ldedit
command to up this limit for your binaries, or specify it when you build
Postgres. See the file docs/
s
> correctly for a 64-bit install onto an AIX system. The only pieces of
> information I found where from this forum and off the back of that I
> configured postgres with the following settings.
Can you post the output from the following command please?
dump -X64 -ovH //bin/postgres
--
uest size. The issue was due to the use of temp
tables in a single session. PG does not release the memory for the temp
tables until the session ends. The postgres process grows up the the
2GB mark, then the final trivial request pushes it over the tipping
point, and you get the out of memory error.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
command to move
from the 8.2.4 node to the 8.3.9.
5: If you still need a Slony replica - drop the 8.2.4 node from
replication and rebuild it as an 8.3.9 replica.
OP if you need any more information about the Slony portions of this -
please ask them on the Slony list.
--
Brad Nichols
s - that
will be very beneficial in for your usage pattern.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
> create a large indexes i get the error below
> >
> > Out of Memory
> > Failed on request of size 167873 (this value will change)
> >
> > Any thoughts or ideas would be greatly appreciated
>
> Don't set work_mem so high? shared_buffers at 256M is no b
directly. Have a look at dblink
http://www.postgresql.org/docs/current/static/dblink.html
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
re the information pg_stat_activity is
incorrectly report a transaction as "" when it is in fact blocking
the removal of dead tuples.
I always chalked it up to inaccuracy of the stats collector in 8.1.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
lier versions which caused lazy vacuum to do
> cost-based delays during this phase, which caused the lock to be held
> for ridiculous lengths of time. This was fixed in 8.2.something; it
> shouldn't sleep anymore, but it does need to scan those pages in order
> to truncate, so
On Tue, 2008-10-21 at 10:45 -0700, Isabella Ghiurea wrote:
> where can I download a copy of oid2name source code?
> Isabella
Grab the Postgres source for your version of choice. It's under the
contrib/ directory.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias
am another postgres that
> runs is Slony-I
This sounds like checkpoint issues to me. Try adjusting the bgwriter
settings as suggested.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
for that? The solution may be to push dirty
buffers to disk more aggressively via the bgwriter so you have less work
to do at checkpoint time.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org
replicating a HUGE amount of data daily with zero
> failures. Of course, the servers were fast and reliable, so that might
> have helped cover a lot of issues other people had for us.
And when we ran it we had replica's getting corrupted due to bugs almost
weekly (based on a particular
s work with 7.3.x, x>2. I don't recommend
> sticking with 7.3, however.
If you ignore this very wise advise, and stick with 7.3 and decide to
give Slony 1.0.x a shot, be prepared for lots of late nights rebuilding
replicas and dealing if a whole multitude of other bugs it had then
or should it
be transparent to the application.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
B and start C from
there?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
; Is there an possibility for having such an system-action table??
You can't put triggers on the system catalog tables (as your error
points out). The only way that I know of to track this directly through
the DB is to log the DDL changes in the Postgres logs, and then parse
that o
ting whether it is doing what it needs to for your application is a
whole other ball game. You need to do some serious benchmarking of your
application to figure that out.
>
>
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
On Tue, 2007-06-05 at 16:59 -0700, Charles.Hou wrote:
> On 6 5 , 9 12 , [EMAIL PROTECTED] (Brad Nicholson) wrote:
> > On Tue, 2007-06-05 at 03:33 -0700, Charles.Hou wrote:
> > > how can i know that it's the time to vacuumdb? i set the crontab to
> > > vacuumd
.
If not, you need to examine the output of VACUUM VERBOSE or pg_stattuple
(in contrib) and set a policy based on the output.
It's entirely possible that some tables will need to get vacuumed
frequently, and others will not.
--
Brad Nicholson 416-673-4106
Database Administrator,
On Tue, 2007-04-24 at 09:02 +0530, Mageshwaran wrote:
> Hi ,
>
> I want to do replication using WAL , please tell the methods by which
> log shipping is done ie moving the wal files to slaves and executing it.
http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.ht
mber cluster.
> Every member cluster Execute Log that receive.
> Send message that to Sender.
> Delete log.
>
> That's model that I want to make code .
How are you planning on handling collision detection and resolution?
--
Brad Nicholson 416-673-4106
Database Administrator, A
01411mrt/direct/01/
> >
> >
> > ---(end of broadcast)---
> > TIP 6: explain analyze is your friend
> >
> > !DSPAM:37,45f1a140103006986215648!
> >
> >
>
>
> ---(end of b
choose an index scan if your joining column's datatypes do not
> >match
> >
> >
> How many tables have you got in your database ?
>
> If you have only a few tables you can dump them one at a time
This approach can get you into serious t
created on MASTER. I
> > checked other tools available and thougth this would be best approach if it
> > works.
> >
> > Awaiting reply soon.
> >
> > Regards,
> > Moiz Kothari
>
> [ Attachment, skipping... ]
>
> >
> > ---
os
> >
> > ---(end of broadcast)---
> > TIP 7: You can help support the PostgreSQL project by donating at
> >
> > http://www.postgresql.org/about/donate
> >
>
> ---(end of br
On Wed, 2006-10-11 at 16:12 -0500, Jim C. Nasby wrote:
> On Wed, Oct 11, 2006 at 10:28:44AM -0400, Andrew Sullivan wrote:
> > On Thu, Oct 05, 2006 at 08:43:21PM -0500, Jim Nasby wrote:
> > > Isn't it entirely possible that if the master gets trashed it would
> > > start sending garbage to the Slo
On Thu, 2006-10-05 at 04:24 +, Sebastian Reitenbach wrote:
> Hi,
>
> Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> > On Wed, Oct 04, 2006 at 11:23:45 -,
> > Sebastian Reitenbach <[EMAIL PROTECTED]> wrote:
> > >
> > > something I thought that might work:
> > > is there sth. that will rep
On Wed, 2006-10-04 at 10:43 -0500, Bruno Wolff III wrote:
> On Wed, Oct 04, 2006 at 11:23:45 -,
> Sebastian Reitenbach <[EMAIL PROTECTED]> wrote:
> If you have multiple data centers to protect against disaster, then you might
> look at SLONY which you can use to replicate to a slave system.
On Thu, 2006-09-28 at 12:32 -0400, Bruce Momjian wrote:
> Lane Van Ingen wrote:
> > Forgot about 'pg_ctl status'. That will work fine for my needs.
> >
> > Ray Stell mentioned it would be helpful to see a description of all the
> > things that go on from start to finish, in general or course. I a
On Tue, 2006-08-01 at 09:22 +0200, Thomas Günther wrote:
> Hello,
>
>
>
> my configuration exists of 2 db-nodes, 2 replication nodes and 1
> loadbalancer.
>
>
>
> Is it able to replicate only some tables (not all) to a third db
> node.
>
> Has someone experiences with cascaded reolication
ed behavior? I would assume that the updated/deleted
> stats should also get cleared.
>
>
>
> I am running PG 8.1.4 on Windows.
Stats get updated by the ANALYZE command. Run it on the table and check
again.
--
Brad Nicholson 416-673-4106
Databas
27;ll notice it unless
you're really close to the edge. You do have to restart the database to
enable the stats collector if it's off. The pg_stat_activity stuff is
pretty essential for debugging problems in the database.
--
Brad Nicholson 416-673-4106
Database Administr
//www.postgresql.org/docs/8.1/interactive/runtime-config-logging.html
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 6: explain analyze is your friend
base often enough, ect, ect.
A good place to start looking would be to use the explain command to
look at the query plans for some of your queries. Post some to the list
if you need a hand.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
-
dule the script to run at the approriate time using cron.
--
Brad Nicholson 416-673-4106[EMAIL PROTECTED]
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire
ny-I users standpoint - the idle transactions cause
pg_listener bloat, which can impact replication performance on busy sites.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
nvironement where the end user can change the schema), it's certainly
out of the picture. In that scenario, I think you're going to have a
hard time finding a replication engine that will let you have a hot
standyby.
--
Brad Nicholson 416-673-4106
Dat
/slony1/genpage.php?slonik_commands#stmt_ddl_script
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
or
later for some functionality though.
Is there another way to do it in 7.* ?
http://www.postgresql.org/docs/faqs.FAQ.html#4.3
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
--
Brad
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP
I suspect that (gently) killing the IDLE transactions that are sitting
around doing nothing will prevent you from having to restart the postmaster.
--
Brad Nicholson
Database Administrator, Afilias Canada Corp.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Bra
f the following:
VACUUM ANALYZE VERBOSE pg_listener;
--
Brad Nicholson
Database Administrator, Afilias Canada Corp.
Thomas F.O'Connell wrote:
It doesn't make all that much more sense. I'd keep posting to the
lists to let other people continue to take a crack at it. Is the
If there are any foreign key constraints, you'll have to truncate the
tables in the appropriate order. You would also have to reset the
sequence values as well.
Naomi Walker wrote:
How about just truncating all the tables?
Dick Davies wrote:
* Tom Lane <[EMAIL PROTECTED]> [0232 16:32]:
64 matches
Mail list logo
