Have you tried using a TRUNCATE instead of a DELETE ? I've found it to be *way*
faster and has some better aspects with vacuuming the table afterwards.
I am not sure why you would see such a change from 8.0.9 though (we mostly
skipped to 8.1 and now 8.2).
Do you know if the aggregation part
Realistically you should include some time for testing ... a few things may
fail to work (not many -- the core is quite good about legacy issues, me
thinks), but you may well run into some things that take longer to run that
they used to. Explain Analyze is your faithful companion, as is the
There is no internal method in PostgreSQL for cron-like jobs. (There has been
discussion on this in past mails -- see the archives.)
I use an external scheduler (cron since we're a *Nix shop); there are Windows'
equivalents but I'm not sure what they are).
HTH,
Greg Williamson
DBA
PROTECTED] on behalf of Gregory S. Williamson
Sent: Fri 10/20/2006 5:07 PM
To: vipin SS; pgsql-admin@postgresql.org
Cc:
Subject:Re: [ADMIN] Postgre SQL Urgent Help
There is no internal method in PostgreSQL for cron-like jobs. (There has been
discussion on this in past mails -- see
Paul,
Others may have better advice, but my $0.02 worth ...
It is certainly possible to run several instances of postgres on one server (at
least on *NIX, don't know about Windoze). This is about postgres 7.4 and 8.1.
We have some servers that have a several different postmasters running; the
This morning one of our production servers (8.1.1, Linux) threw a number of
errors like these (different final number; first two [1663/43801] are all the
same]:
2006-09-26 10:02:15.124 PDT 1387333718 ERROR: could not open segment 1 of
relation 1663/43801/7274801 (target block 478216192): No
-only for 99.999 of
the time).
Greg
-Original Message-
From: Talha Khan [mailto:[EMAIL PROTECTED]
Sent: Tue 9/26/2006 3:21 PM
To: Gregory S. Williamson
Cc: pgsql-admin@postgresql.org
Subject:Re: [ADMIN] Runtime error: could not open segment 1 of relation
...
Hi Greg
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Tue 9/26/2006 3:36 PM
To: Gregory S. Williamson
Cc: Talha Khan; pgsql-admin@postgresql.org
Subject:Re: [ADMIN] Runtime error: could not open segment 1 of relation
...
Gregory S. Williamson [EMAIL PROTECTED
Talha,
Seems like a plan ... once we have it out of runtime I'll be able to redo
indexes, whatever.
Thanks,
Greg
-Original Message-
From: Talha Khan [mailto:[EMAIL PROTECTED]
Sent: Tue 9/26/2006 3:33 PM
To: Gregory S. Williamson
Cc: pgsql-admin@postgresql.org
Subject
it is/
G
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Thu 2/16/2006 10:19 PM
To: Gregory S. Williamson
Cc: pgsql-admin@postgresql.org
Subject:Re: [ADMIN] Something like pg_dump for 7.4
Gregory S. Williamson [EMAIL PROTECTED] writes:
I am trying
: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Thu 2/16/2006 10:19 PM
To: Gregory S. Williamson
Cc: pgsql-admin@postgresql.org
Subject:Re: [ADMIN] Something like pg_dump for 7.4
Gregory S. Williamson [EMAIL PROTECTED] writes:
I am trying to migrate a modest sized database (the unload
I am trying to migrate a modest sized database (the unload file is just under a
gigabyte) which is moderately complex, using schemas and the like from 7.4 to
8.1
Alas, pg_dump appears to incompatable with my needs as it keeps introducing
invalid UTF-8 characters.
I have gone back and changed
Back in the way-back Tom Lane wrote:
Actually, as a developer I would've first wanted to look into the core
files and try to see why they showed up in the first place. A gdb stack
trace would often tell something useful (... if not to you, then to
someone on the -hackers list ...).
FWIW,
I can at least report the resolution of the original problem.
I went sleuthing and found some core files in the ./base/13860299 directory.
Deleteing those freed up some gigabytes of space (each core was 1-2 gigs).
The server that I had tried to stop with -m immediate command did in fact
Tom Lane conjured forth the following characters:
Might want to turn off dumping of core files; I believe man ulimit is
the place to look.
Actually, as a developer I would've first wanted to look into the core
files and try to see why they showed up in the first place. A gdb stack
: 650-649-1954
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Gregory S. Williamson
Sent: Sunday, January 01, 2006 11:58 PM
To: Jeff Frost; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] full data disk -- any chance of recovery
Jeff --
Thanks
think this beast may be of that flavor.
'
Thanks,
Greg
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Mon 1/2/2006 9:45 AM
To: Gregory S. Williamson
Cc: Jeff Frost; pgsql-admin@postgresql.org
Subject:Re: [ADMIN] full data disk -- any chance of recovery
Ah well, figures. If only ops had listened to me, we'd be on 8.1 right now.
Thanks anyway, as always, for the sage advice.
G
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Mon 1/2/2006 2:11 PM
To: Gregory S. Williamson
Cc: Jeff Frost; pgsql-admin
An enthusiastic person in out content department went and did a silly thing ...
Well, he went and fired off an update that consumed all of the remaining disk
space on two runtime servers.
We've fallen back to a hot spare and I am faced with trying to retrieve these
machines by Tuesday morning
of the two hot spares (one of which is now
in play), juts in case.
Have a good {day|afternoon|evening|night) !
Greg
-Original Message-
From: [EMAIL PROTECTED] on behalf of Jeff Frost
Sent: Sun 1/1/2006 11:49 PM
To: Gregory S. Williamson; pgsql-admin@postgresql.org
Cc:
Subject
I'd advise staying far away from RAID5 -- the link below is one frequently
pointed to in Informix discussions, but I think the points apply to any RDBMS.
If you value your data (and sanity) stay with a more reliable setup --
performance is not the only problem with RAID5.
Using PostSQL 7.4 on a linux server, we have one postgres instance with two
databases, gex_runtime and mq_geoloc. We had a mishap in which we ran out
of disk space. The failure occured in writing to the gex_runtime database.
Then an unfortunate slip of an admin's keyboard led to the deletion of
Tom --
Thanks for suggestion. We'll give it a try.
Greg W.
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Thu 8/11/2005 7:07 AM
To: Gregory S. Williamson
Cc: pgsql-admin@postgresql.org
Subject:Re: [ADMIN] restoring an old database to a new instance
Did you vacuum the template1 database as well as whatever databases you have
created ?
HTH,
Greg Williamson
DBA
GlobeXplorer LLC
-Original Message-
From: [EMAIL PROTECTED] on behalf of FM
Sent: Mon 8/1/2005 12:35 PM
To: Mailing List Postgres-Admin
Cc:
Subject:
Edward,
We have used it for a simple mirroring of transaction data. It has worked like
a champ so far, but the number of tables is low (less than 10) and the
transactions tend to be inserts (common), updates (less so) and deletes rarely,
none of them very frequent. The two servers are quite
Sidnei,
In principle, yes, postgres can do quite well (we use 2 CPU boxes with 2 gigs
of RAM for most of our production servers), but a lot would depened on what
sort of use your database gets -- all read and bulk updates ? Or lots of
updates ? Are the queries complex ? etc., etc. How fast
As root you will need to edit the /etc/system file to add some information for the OS
about how much RAM to use for shared memory.
These are settings for Informix (don't have a postgres SUN setup at hand and don't
have doucumentation either):
set shmsys:shminfo_shmmax=1205306368
set
Can't resist 2 minor points --
a) a code package should never be judged on the amount of money made of it; some great
software has been a source of real loses (too late to market, bad market positioning,
unfair competitive practices, etc., etc). e.g. How much money has postgres earned for
its
Pyatalo,
As a start, perhaps you could provide some more information about what release of
postgres, what OS platform and perhaps some more about the connection type (JDBC,
perl, PHP, PgAdmin, etc) and some information about the application itself ? I am not
knowledgable enough about postgres
]
Sent: Mon 5/17/2004 8:03 AM
To: Gregory S. Williamson
Cc: [EMAIL PROTECTED]
Subject:Re: [ADMIN] Keep a user from creating tables ?
On Sun, 16 May 2004, Gregory S. Williamson wrote:
In postgres 7.4, is there any way to stop a user from creating tables in a given
database ?
Make
Of course ... implicit in the docs if I reread them.
Thanks very much for the tip ...
G
-Original Message-
From: Stephan Szabo [mailto:[EMAIL PROTECTED]
Sent: Monday, May 17, 2004 1:45 PM
To: Gregory S. Williamson
Cc: [EMAIL PROTECTED]
Subject: Re: [ADMIN] Keep a user from creating
In postgres 7.4, is there any way to stop a user from creating tables in a given
database ?
I tried (as postgres user):
REVOKE ALL ON DATABASE test FROM testuser;
testuser can still connect and can still select from the tables I want them to see,
but as user testuser:
test= CREATE TABLE
I just tried the sql below on a 7.4 instance and it works fine.
# insert into vm_mailbox
values('PERSONAL000',0,null,1,60,true,0,30,true,
10,0,1081462504500);
INSERT 13985274 1
Are you sure this is the offending data ?
Greg Williamson
DBA
GlobeXplorer LLC
-Original
, April 08, 2004 5:29 PM
To: Gregory S. Williamson
Subject: Re: [ADMIN] Problem inserting data into 7.4.2 table
I am harrassed DAILY by about 20 of the guys in your ADMIN group, a group
which I NEVER signed up for or asked to be signed up for! And then you make
it sound so easy just unsubscribe from
] (Gregory S. Williamson) writes:
No point to beating a dead horse (other than the sheer joy of the
thing) since postgres does not have raw device support, but ... raw
devices, at least on solaris, are about 10 times as fast as cooked
file systems for Informix. This might still be a gain for postgres
No point to beating a dead horse (other than the sheer joy of the thing) since
postgres does not have raw device support, but ...
raw devices, at least on solaris, are about 10 times as fast as cooked file systems
for Informix. This might still be a gain for postgres' performance, but the
Informix fees vary but figure about $33,000 per CPU for a web environment (other
licenses are cheaper, for instance, a server with only a handful of connections). On
the plus side for Informix, the Oracle stuff we had consists of dozens of tapes and
CDs ... Informix was rarely more a CD and
Check
the log file for the postgres instance you are connecting to -- it may be that
pg_hba.conf file does not have your client server listed; the log file would
show that error.
Greg
Williamson
DBA
GlobeXplorer LLC
-Original Message-From: Eduardo Sachwek Fontanetti
pg_ctl stop[-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
e.g.
pg_ctl stop -D /data/postgres/gex_runtime -m fast
will shut down all connections and stop the postgres instance:
Shutdown modes are:
smart quit after all clients have disconnected
fastquit directly, with proper
I had a similar problem recently and in the end it seems to have been me not
restarting the postmaster; changes to the pg_hba.conf file weren't seen until then (I
am sure this is documented but I managed to overlook it; Informix' sqlhosts file is
read at connection time so changes are immediate
Dear peoples,
I'm setting up a database in postgres which will have light traffic writing to a few
tables (and somewhat heavier traffic reading them). We'd like to mirror that databse
using dbmirror to two others.
Let's assume that we're humming along and the master goes down. We switch
I don't have a clue about which is which, but I bet providing some dates (ls -l) would
help figuring out which is the most recent vs. oldest) ... might provide helpful
information to people who know more about compiling this beast.
Greg Williamson
-Original Message-
From: shreedhar
42 matches
Mail list logo