thout entering 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 cho
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
;s doing this to
re-generate statistics for the table for the query planner to use.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
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 t
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 t
pesky behind the scenes 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, Afil
ating. They will
happen 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
ce requirements 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
e, I'd be surprise if they aren't a lot cheaper. Especially
when figuring in all the other costs that go along with disk arrays -
power, cooling, rack space costs.
Depends on the your vantange point I guess. I'm looking at these as
potential alternatives to some high end, expensiv
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
--
Sent via pgsql-general mailing list
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
m pretty impressed with it so far.
--
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
he new location or
> is there a config option somewhere that says where the pg_xlog resides?
There is an option to do this during 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
ase it by a factor of 5 when doing so.
It does look like you need to increase it though.
> Can the checkpoint operation actually cause the DB to stop responding
> for a few seconds at a time? That seems to be what I observe.
> Sometimes for 5 or more seconds one transaction will just stall.
A
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 writes:
> > > autoanalyze will automatically analyze new tables when they don't have
> > > stats. It seems logical that it should
On Mon, 2009-10-19 at 15:01 -0400, Tom Lane wrote:
> Brad Nicholson 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.
>
not a vacuum you want, it's an analyze. Once the stats are back,
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 N
On Mon, 2009-10-19 at 11:16 -0600, Scott Marlowe wrote:
> On Mon, Oct 19, 2009 at 11:06 AM, Brad Nicholson
> wrote:
> > On Mon, 2009-10-19 at 10:53 -0600, Scott Marlowe wrote:
> >> On Mon, Oct 19, 2009 at 10:44 AM, Tom Lane wrote:
> >> > Brad Nicholson writes:
On Mon, 2009-10-19 at 10:53 -0600, Scott Marlowe wrote:
> On Mon, Oct 19, 2009 at 10:44 AM, Tom Lane wrote:
> > Brad Nicholson writes:
> >> On Mon, 2009-10-19 at 12:07 -0400, Tom Lane wrote:
> >>> That seems like a fundamentally stupid idea, unless you are uncon
On Mon, 2009-10-19 at 12:07 -0400, Tom Lane wrote:
> Brad Nicholson 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 o
tabases.
4: after restart, why does pgstattuple shoe dead_tuple_percent = 8.54,
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 Adminis
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 wid
t for
> > something like MRTG to graph this data.
> >
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.7 (GNU/Linux)
> Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org
>
> iD8DBQFKL8ND2FH5GXCfxAsRAu/XAJ43UGqlzv5gfzg1YgECbhvL2MaPzwCdEnt3
> GfewITsorV/t7cfpq3WxVqM=
>
lane
Is the referenced query reliable for even 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-g
olks though when things are
performing just fine.
> Sorry this is so vague, I'm 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
help
> here!
>
> I was hoping 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 li
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.
--
Sen
sed to be (will gather more records
> per disk block read if record density 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.
ocumentation 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
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 (p
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
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 tuple
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: rela
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 | psql -d
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias
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 master...).
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 twi
ned the iostat and vmstat) (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.
--
Bra
h (and I emphasise the word they, as I had 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)---
>
> First of all, this should not happen on a machine with proper
> fsyncing. The possible causes are generally either fsync is off in
> postgresql.conf or the drive array <--> OS layer is lying about fsync
> operations.
What filesystem are you using? I've seen similar p
e if it helps. You can change
these with a reload. If you are doing this on a production system as
opposed to a test system, keep a close eye 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
1 255.255.255.255
> trust
> # IPv6-style local connections:
> hostall all ::1
> ::::::: trust
>
>
> can you please guide me on what the problem might be.
>
> Regards
>
> Rajaram
>
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
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? E
define as high 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/
5a0 in PostmasterMain (argc=1, argv=0x300853c8) at
postmaster.c:897
#20 0x153c 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?
http://www.postgresql.org/docs/faq
Period. If you can accept
the potential for data loss, and you've proven 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
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
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
he US with good success.
Successfully using slony over a wide area is going to depend on how much
data you are replicating, how fast the connection between the two sites
is, and how stable 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
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 C
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 Canad
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"
&
PostgreSQL 8.1.5 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 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
mance for heavy-update
> scenarios, but its latency is variable (low update 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, Af
bug in the current
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.
---
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
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
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-67
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() ca
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?
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 st
appropriate permissions to?
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
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 :-)
> >
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;
> > >
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 establi
On Thu, 2006-08-17 at 12:12 -0400, Merlin Moncure wrote:
> On 8/17/06, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:
> > On 8/17/06, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> > > On 8/16/06, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:
> > > > -- then create a function to retrieve the values:
> > > > CR
d gets x as a value for max id
Transaction 2 (t2) does a select max(id) for update, has to wait for t1
to release its lock.
t1 inserts (x+1) as the new max id of the table. t1 releases its lock
t2 is granted the lock on the tuple it has been waiting for, which
cont
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
> g
RANSACTION ISOLATION LEVEL 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 Nicholso
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
ny statement that is in a transaction 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 Admi
guess (based on the time 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
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 an
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 will
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
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])
f all 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'
nges are going to
work on all nodes before 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.
-
at the error says. You 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 Admini
e the copy is completed, the events 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
P 2: you can get off all 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)---
87 matches
Mail list logo
