a degraded state or worrying
about STONITH. If you switch roles in a controlled manner, both nodes
remain in the cluster. Slony prevents writes against the replica.
I do agree that for most, Slony is overkill and streaming replication
and hot standby will be the better choice.
--
Brad
tuples -- a number we know in fact to be correct? How
could both statements be correct?
It found 45878 dead tuples in 396 pages for the index authors_archive_pkey.
It found 16558 dead tuples in 492 pages for the table authors_archive.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias
On 10-07-29 08:54 PM, Greg Smith wrote:
Brad Nicholson wrote:
Postgres also had a reputation of being slow compared to MySQL.
This was due to a lot of really poor MySQL vs Postgres benchmarks
floating around in the early 2000's.
I think more of those were fair than you're giving them credit
On 10-07-29 08:54 PM, Greg Smith wrote:
Brad Nicholson wrote:
Postgres also had a reputation of being slow compared to MySQL.
This was due to a lot of really poor MySQL vs Postgres benchmarks
floating around in the early 2000's.
I think more of those were fair than you're giving them credit
-generate statistics for the table for the query planner to use.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
protection
for your data that MySQL didn't worry about.
No one really tested it in a way that mattered, which was how the two
databases performed under concurrent load, where Postgres won hands down.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via
if their is no index on the updated column and there is enough
space in the physical page to keep the tuple on the same page. You can
adjust the fillfactor to try and favour this.
You can check if you are doing hot updates by looking at
pg_stat_user_tables for the number of hot updates.
--
Brad
are tied to that app.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I want to put the functions from pgcrypto into a separate schema, but
pgcrypto.sql is explicitly setting the search path to public. Is there
a reason it does this that I should be aware of? Is it fine to change
that and install the functions in a separate schema?
--
Brad Nicholson 416-673-4106
Hi,
Is anyone using Lifekeeper for Linux availability with Postgres?
If so, what are your thoughts on it? Work as advertised? Any dangerous
gotchas?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-general mailing list (pgsql-general
space costs.
Depends on the your vantange point I guess. I'm looking at these as
potential alternatives to some high end, expensive storage products, not
a cheap way to get really fast disk.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql
Could someone please point me towards the changes for 8.3.10 that was
mentioned on -announce this morning?
Also, any idea when this is going to be released?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
pgsql-general pgsql-general@postgresql.org
--
Sent via
allocate all 30MB, or just the 10MB I need?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
,
Check_posgres:database_size will alert me if I go over X size, which is
useful but with Cacti, I get a graph of waht the size is over a 5 min
period of time, so I can watch growth ...
Check out Hyperic. We're currently evaluating it for profiling out
systems, and I'm pretty impressed with it so far.
--
Brad
initdb. If you want to do it after
the DB is created, move the contents of pg_xlog/ (when the DB is shut
down) and make a symlink to the new directory.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
.
Absolutely. The smoothing is supposed to alleviate that, but I'm not
sure how well it works with aggressive activity driven checkpoints like
you have. Keep up posted, I'm curious.
Thanks for any ideas.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent
,
but after deleting one row, it shows dead_tuple_percent = 0.09?
5: on the missing stats - does this mean my query plans are potentially
bad until the stats are regenerated?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-general mailing list
On Mon, 2009-10-19 at 12:07 -0400, Tom Lane wrote:
Brad Nicholson bnich...@ca.afilias.info writes:
If you issue an immediate shutdown to the database, autovacumm will not
process tables that should be vacuumed until manually re-analyzed.
AFAICS this is an unsurprising consequence
On Mon, 2009-10-19 at 10:53 -0600, Scott Marlowe wrote:
On Mon, Oct 19, 2009 at 10:44 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Brad Nicholson bnich...@ca.afilias.info writes:
On Mon, 2009-10-19 at 12:07 -0400, Tom Lane wrote:
That seems like a fundamentally stupid idea, unless you
On Mon, 2009-10-19 at 11:16 -0600, Scott Marlowe wrote:
On Mon, Oct 19, 2009 at 11:06 AM, Brad Nicholson
bnich...@ca.afilias.info wrote:
On Mon, 2009-10-19 at 10:53 -0600, Scott Marlowe wrote:
On Mon, Oct 19, 2009 at 10:44 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Brad Nicholson bnich
,
autovacuum will vacuum accordingly.
autoanalyze will automatically analyze new tables when they don't have
stats. It seems logical that it should handle this case where the table
also does not have stats.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via
On Mon, 2009-10-19 at 15:01 -0400, Tom Lane wrote:
Brad Nicholson bnich...@ca.afilias.info writes:
autoanalyze will automatically analyze new tables when they don't have
stats. It seems logical that it should handle this case where the table
also does not have stats.
It will autoanalyze
On Mon, 2009-10-19 at 15:09 -0400, Brad Nicholson wrote:
On Mon, 2009-10-19 at 15:01 -0400, Tom Lane wrote:
Brad Nicholson bnich...@ca.afilias.info writes:
autoanalyze will automatically analyze new tables when they don't have
stats. It seems logical that it should handle this case where
On Wed, 2009-07-15 at 14:13 +0200, Rafael Martinez wrote:
Hello
Should not the execution of pg_stat_reset() reset *all* statistics
counters everywhere in the database?
It only resets the stats for the current database, not the cluster wide
stats - pg_database is cluster wide.
--
Brad
/XAJ43UGqlzv5gfzg1YgECbhvL2MaPzwCdEnt3
GfewITsorV/t7cfpq3WxVqM=
=84cI
-END PGP SIGNATURE-
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org
estimating, or is it flat our
wrong?
Co-workers that were PGCon are saying that this is becoming a
popular/accepted way to check for bloated tables.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-general mailing list (pgsql-general
frustrated with this request as I figured
just the amount of bug-fixes alone would be adequate reasoning.
Unfortunately, what seems adequate to us technical folks is seldom is to
the business folks.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via
for a function I could call, or maybe some variable I write
to, that would cause the contents to be invalidated.
Restart the database.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
Is there a reason that pg_stat_reset doesn't reset the stats in
pg_stat_bgwriter and pg_stat_database? PG 8.3 (obviously).
The call to pg_stat_reset works, as my other stats tables are clear.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via
on disk is greater). Is there a
way to do this?
Regular VACUUM is the correct operation to get rid of the dead tuples.
If you want to compact the the table, you either need to use CLUSTER or
VACUUM FULL + REINDEX.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp
on how to use it?
There are a series of functions in the database core that will tell you
this now.
http://www.postgresql.org/docs/8.3/interactive/functions-admin.html
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
--
Sent via pgsql-general mailing list (pgsql
On Wed, 2008-03-26 at 15:31 -0400, Tom Lane wrote:
Brad Nicholson [EMAIL PROTECTED] writes:
We just took a test database down (PG 8.1.11) fairly hard (pulled a SAN
It could be that but not necessarily. These could be pages that were
allocated to put new tuples into, but the crash happened
On Thu, 2008-03-27 at 10:29 -0300, Alvaro Herrera wrote:
Brad Nicholson wrote:
On Wed, 2008-03-26 at 15:31 -0400, Tom Lane wrote:
Brad Nicholson [EMAIL PROTECTED] writes:
We just took a test database down (PG 8.1.11) fairly hard (pulled a SAN
It would be easier to believe
On Thu, 2008-03-27 at 10:37 -0400, Tom Lane wrote:
What do you mean by two separate SAN switches pulled out --- is the
DB spread across multiple SAN controllers?
It's using IO mutilpath through 2 HBAs. Both of those were taken down.
Brad.
--
Sent via pgsql-general mailing list
We just took a test database down (PG 8.1.11) fairly hard (pulled a SAN
switch out while it was under load), which caused the DB to crash. It
started up fine, when I vacuumed the DB, I saw the following messages.
WARNING: relation my_table page 652139 is uninitialized --- fixing
WARNING:
On Tue, 2008-02-26 at 15:19 -0500, Kynn Jones wrote:
Is there a simple way to copy a table from one database to another
without generating an intermediate dump file?
pg_dump -t table name source DB | psql -d target DB
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada
Dawid Kuroczko wrote:
Slony is good as long as there are no DDLs issued. And its easy to
shoot oneself in the foot if one is not careful (some time ago I have
lost all the triggers while upgrading from 8.1 to 8.2; it was my fault
since I did pg_dump -s on a slave database, not on the
) (we had set postgres' db block size as
8 and oracle's is 16kb...)
Do you have any comments on this?
8k is the defualt. You can change the block size if you need to. You
need to modify src/include/pg_config_manual.h recompile and re-initdb.
--
Brad Nicholson 416-673-4106
Database
On Tue, 2007-11-20 at 13:04 -0500, Josh Harrison wrote:
On Nov 20, 2007 11:13 AM, Brad Nicholson [EMAIL PROTECTED] wrote:
On Tue, 2007-11-20 at 07:22 -0500, Josh Harrison wrote:
There were a couple of things we noted.
1. Tablesize twice as much than oracle-- Im not sure if postgres null
no part in this :-))
was a cron job was that restarted the MySQL server every night.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http
is lying about fsync
operations.
What filesystem are you using? I've seen similar problems on JFS2, it
was something with logredo or fsck being busted on a Big Blue OS.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast
on what is going on, as it is
possible that you can make things worse.
I would start with something like 2% for bgwriter_all_maxpages and see
if that helps things out.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast
:::::::trust
can you please guide me on what the problem might be.
Regards
Rajaram
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 1: if posting/reading
I just want to confirm that the cluster/MVCC issues are due to
transaction visibility. Assuming that no concurrent access is happening
to a given table when the cluster command is issued (when takes it
visibility snapshot), it is safe to cluster that table. Correct?
--
Brad Nicholson 416-673
I have a couple of database clusters that need a vacuum full, and I
would like to estimate how long it will take, as it will need to be in a
maintenance window. I have the times that it takes to to do a regular
vacuum on the clusters, will vacuum full take longer?
--
Brad Nicholson 416-673-4106
in main (argc=1, argv=0x2ff22c40) at main.c:222
#21 0x1204 in __start ()
Ideas?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ
for 7.4? I bumped it up to ~ 245mbs
--
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/
On Tue, 2007-07-10 at 11:31 -0400, Tom Lane wrote:
Brad Nicholson [EMAIL PROTECTED] writes:
On Tue, 2007-07-10 at 11:19 -0400, Tom Lane wrote:
Oh, I forgot to mention --- you did check that vacuum_mem is set to
a pretty high value, no? Else you might be doing a lot more
btbulkdelete
that there is a
worthwhile performance benefit from turning it off (which there may not
be), and you gotten your boss/clients/stakeholders to sign off
(preferably in writing) that data loss is acceptable if the db crashes,
then go ahead and turn it off.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias
Running PG8.1 - will it recognize CPU and memory that are added
dynamically to the server when the postmaster is running?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 1: if posting
the connection between the two sites is.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
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.html
--
Brad
Question about pg_dump and Postgres 8.1.
Assuming you've let you buffers settle, and then you dump your
database. Will this clobber your shared buffers like a seq scan against
a large table will?
--
Brad Nicholson 416-673-4106[EMAIL PROTECTED]
Database Administrator, Afilias Canada
On Wed, 2007-01-10 at 16:51 +0100, Andy Dale wrote:
If anyone can help or offer advice on how to achieve my objective it
would be greatly appreciated.
Slony log shipping will do this
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp
on powerpc-ibm-aix5.3.0.0, compiled by GCC gcc (GCC)
3.3.2
(1 row)
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
On Tue, 2006-12-12 at 11:13 -0500, Tom Lane wrote:
Brad Nicholson [EMAIL PROTECTED] writes:
This seems odd. Any idea what's going on here?
template1=# SET TimeZone TO 'GMT';
ERROR: unrecognized time zone name: GMT
Worksforme. Perhaps you are missing the /usr/share/pgsql/timezone
version that causes log shipping to fall over if you have more than 2
nodes in your config (not just log shipped nodes).
If you have more questions, please sign up for the Slony list.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end
rate = higher
latency), and not easy to put a bound on pre-8.2.
I'm not entirely sure how battle tested the Slony log shipping stuff
actually is.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast
On Fri, 2006-11-10 at 15:16 -0500, Tom Lane wrote:
Brad Nicholson [EMAIL PROTECTED] writes:
On Fri, 2006-11-10 at 15:07 -0500, Tom Lane wrote:
Those are two different methods: you'd use one or the other, not both.
Slony has its own log shipping, I think that was what he was referring
I'm wondering what that status of the fix for this is. Looking at the
archives, it looks like Bruce had a patch
http://beta.linuxports.com/pgsql-jdbc/2006-08/msg00036.php
I don't see anything in the release notes though. What's the status on
this?
--
Brad Nicholson 416-673-4106
Database
On Mon, 2006-10-30 at 10:14 -0500, Tom Lane wrote:
Brad Nicholson [EMAIL PROTECTED] writes:
I'm wondering what that status of the fix for this is.
AFAIK it all works ... grab beta2 and try it.
Will do. Is this strictly an 8.2 patch, or will it be back-ported to
8.1 and 7.4?
--
Brad
On Mon, 2006-10-30 at 10:27 -0500, Tom Lane wrote:
Brad Nicholson [EMAIL PROTECTED] writes:
Will do. Is this strictly an 8.2 patch, or will it be back-ported to
8.1 and 7.4?
We aren't going to change the behavior of logging that much in existing
releases --- or were you just thinking
appropriate permissions to?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
On Wed, 2006-10-18 at 13:00 -0400, Tom Lane wrote:
Brad Nicholson [EMAIL PROTECTED] writes:
Can someone please provide a bit of information where the following
error is coming from? This is PG 8.1.3 on AIX 5.3
LOG: could not bind socket for statistics collector: Permission denied
On Wed, 2006-10-18 at 14:31 -0400, Tom Lane wrote:
Brad Nicholson [EMAIL PROTECTED] writes:
On Wed, 2006-10-18 at 13:00 -0400, Tom Lane wrote:
That's bizarre. What error conditions does your man page for bind(2)
document as yielding EACCES? The only one mentioned on my systems
On Wed, 2006-10-18 at 15:59 -0400, Tom Lane wrote:
Brad Nicholson [EMAIL PROTECTED] writes:
On Wed, 2006-10-18 at 14:31 -0400, Tom Lane wrote:
Would you try strace'ing postmaster start to see what gets passed to the
socket() and bind() calls just before this message comes out?
Here
Is it by file name or by inode?
Brad.
---(end of broadcast)---
TIP 6: explain analyze is your friend
On Wed, 2006-09-20 at 16:38 -0500, Philip Hallstrom wrote:
On Wed, Sep 20, 2006 at 10:10:56AM -0500, Tony Caduto wrote:
For a high level corp manager all they ever hear about is MS SQL Server,
Oracle and DB2, and the more it costs the more they think it is what
they need :-)
I think
On Thu, 2006-08-17 at 16:07 -0400, Merlin Moncure wrote:
On 8/17/06, Brad Nicholson [EMAIL PROTECTED] wrote:
Hmm, I think you are wrong. There is a SELECT ... FOR UPDATE;
The first-to-obtain the gapless sequence transaction will establish
a lock onthe tax_id row. The other
On Thu, 2006-08-17 at 15:13 -0500, Scott Marlowe wrote:
On Thu, 2006-08-17 at 15:07, Merlin Moncure wrote:
On 8/17/06, Brad Nicholson [EMAIL PROTECTED] wrote:
Hmm, I think you are wrong. There is a SELECT ... FOR UPDATE;
The first-to-obtain the gapless sequence transaction
the max id of x
t2 tries to insert a value of x+1, insert fails (if it doesn't, you
really want to have a close look at your constraints :-)
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 1
On Tue, 2006-07-25 at 10:45 -0700, Redefined Horizons wrote:
I'm having trouble figuring out how to use the currval() function for
sequences in an INSERT statement. I did some searching online, but
couldn't find what I was looking for.
I have two Schemas in my Database:
metadata
geometry
SERIALIZABLE;
-- SELECT * from foo;
INSERT INTO foo (id) --
VALUES (1); --
-- SELECT * from foo;
The select in t2 (the last one, obviously) does not see the insert from t1.
What's up?
--
Brad Nicholson 416-673-4106[EMAIL
In version 7.4, could someone please tell me the start and end points
are for measuring the execution time of a query when log_duration is
enabled? I need to know exactly what gets measured in this time.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp
will automatically go to
the master DB. If some queries are sensitive to this issue, and some
aren't, then you might be able to make pgpool work for you by wrapping
the sensitive ones in a transaction.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp
the slony sync was generated) is the
closest that you will be able to come.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
, at the end of the updates I
would like to automatically copy all of the data to a second server
as a live backup in case the main system ever goes down.
You could use batch replication via Mammoth Replicator or PITR.
Slony's log shipping is another option.
--
Brad Nicholson 416-673-4106
Does anybody have regular expression handy to verfiy email addresses?
--
Brad Nicholson
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose
on the slaves.
As long as you don't have any functions that write to the db. pgpool
could (and likely would) redirect some of these to the subscriber.
Slony would prevent the data from being written (which would prevent
the subscriber from being corrupted).
--
Brad Nicholson 416-673-4106
is poor, then it is impossible to have good performance
doing anything across that connection.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner
is in the works.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 7: don't forget to increase your
are applied, in
the proper order, to bring the set up to date.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 8: explain analyze is your friend
are trying to run vacum
inside a transaction, which can't be done. JDBC is likely opening a
transaction for you automatically. Stop it from doing this, and you
should be able to vacuum.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end
firing them through this, because you only get
one shot. The accepted best practice for doing this is to put the
schema changes in a transacation block that rolls back, and run them via
psql against all the nodes, and make sure nothing breaks.
--
Brad Nicholson 416-673-4106
Database
lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
--
Brad Nicholson 416-673-4106[EMAIL PROTECTED]
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 8: explain
86 matches
Mail list logo