Hello,
Is anyone aware of tools which can be used to enforce schema standards on
PostgreSQL 9.0? For examples: 1) Fail/rollback DDL attempting to create a
table in the public schema 2) Create a scheduled process which raises
alerts for columns of datatype XML
Thanks,
Robert
Is anyone aware of tools which can be used to enforce or test schema
standards on PostgreSQL 9.0? Examples being something like: 1)
Fail/rollback DDL attempting to create a table in the public schema 2)
Create a scheduled process which raises alerts for columns of datatype XML
Thanks, Robert
Hi All,
I believe I may have experienced a Postgres bug and am eager for bit
of feedback. It seems we may have had some type of catalog corruption
as overview in the events pasted below. I am including our
observations of the problem, but am asking the list to see if I can
perform any further
And the \d+ on users? Also would you mind pasting the output exactly as it
is appears on screen, assuming it is in English?
I'm trying to build up a picture here of how many problems you have and
whether they can be fixed conventionally with the database interface or
whether you need to start
You can describe an object with the psql command \d. \d+ gives you extended
information. So at the psql command line if you type the following -
\d+ users
You should see a description of the layout of the table along with
associated indexes.
On Mon, Nov 22, 2010 at 8:56 AM, Lukasz Brodziak
I might be being optimistic but I should be able to make sense of the \d+
output if you can post that.
On Mon, Nov 22, 2010 at 10:04 AM, Lukasz Brodziak lukasz.brodz...@gmail.com
wrote:
The output is in Polish :) is there anything particular to look for?
Depends on your OS. Are you using Linux or Windows?
If you're using a linux terminal window you can usually highlight it and
ctrl-shift-c. Or use the menus.
On Mon, Nov 22, 2010 at 10:15 AM, Lukasz Brodziak lukasz.brodz...@gmail.com
wrote:
Lame question - how to copy output from psql
A few more information requests if you don't mind.
Can you analyze the table ie ANALYZE USERS without error?
Could you please paste the output from \d+ users.
And try SELECT COUNT(*) FROM users WHERE id_usr IS NULL;
Is your database large? Can you shut down the instance cleanly and take a
Can you post the select and the exact error?
On Sun, Nov 21, 2010 at 6:43 PM, Lukasz Brodziak
lukasz.brodz...@gmail.comwrote:
Hello,
I have a problem with one of the tables in my database. The thing is
that one of the rows is present in corresponding data file yet I'm
unable to perform any
Oh and could we please have the output from SELECT version(); too please.
On Sun, Nov 21, 2010 at 7:56 PM, Matthew Walden matthew.wal...@bcs.orgwrote:
A few more information requests if you don't mind.
Can you analyze the table ie ANALYZE USERS without error?
Could you please paste
index on that table either.
Thoughts? How do I fix this so I can run DDL?
Please let me know what further information I can provide.
Thanks!
Matt
Matthew Excell
That did it. Thanks!
Matthew Excell
On Mon, Jun 7, 2010 at 1:46 PM, Alvaro Herrera
alvhe...@commandprompt.comwrote:
Excerpts from Matthew Excell's message of lun jun 07 15:33:27 -0400 2010:
I'm on PostgreSQL 8.3.11 on Debian.
I have a small piece of DDL (alter table title drop column
Michael Monnerie wrote:
Dear list, this is postgres 8.1, and I run a dbmail mailserver where we deleted
most e-mails.
I then did a VACUUM (see below), and just before the dbmail_messages
truncated rows
there was a very long time where obviously nothing happened. No CPU usage (8%)
and
no disk
Rafael Domiciano wrote:
I'm not using autovacuum. Regular vacuum goes ok.
To see the last 10 lines of verbose i will need to run vacuum tonight
If a run a reindex before the vacuum full, increase the speed of
doing vacuum? I found something about it googling.
It might help a bit, but by the
is such
command.
Thanks and regards
Matthew
--
Matthew Pulis
URL : http://www.solutions-lab.net
MSN : [EMAIL PROTECTED]
ICQ : 145951110
Skype : solutions-lab.net
Alvaro Herrera wrote:
Matthew T. O'Connor escribió:
As Alvaro already said this is a case where autovacuum still isn't
great.
While I have your attention ;-) do you have any ideas on how to improve
this? I don't see anything that looks like a solution for this case.
No good ideas
Chris Hoover wrote:
Another question.
When autovacuum sleeps, does it release the lock it has on the table?
What we would like to have happen is for vacuum to work for a while,
sleep, and while it is sleeping run an analyze on the table. We need
this due to how quickly our data is
Hello, I've been looking into having apache log to postgresql, and it
isn't clear to me what the best way to set this up is. The options
that I see so far are:
1) Apache Pipe Logging: This seems simple enough, but I'm not sure
what tool I would as the logging process?
2) pgLOGd: This
Hello, I've been looking into having apache log to postgresql, and it
isn't clear to me what the best way to set this up is. The options that
I see so far are:
1) Apache Pipe Logging: This seems simple enough, but I'm not sure what
tool I would as the logging process?
2) pgLOGd: This
Kieran Cooper, Lyris UK wrote:
What is it about autovacuum that isn't working for you?
Hi Matthew. I have to admit that I haven't tested full yet, but here's
the scenario:
When Lyris sends a mailing there is an intense amount of database
activity - particularly on two tables. When I had auto
Kieran Cooper, Lyris UK wrote:
We're managing a database on version 8.2.3 (running Lyris ListManager
email software). I ran a 'vacuum analyze full verbose' (we haven't been
able to get autovacuum to work properly on Lyris installs so we need to
run a full vacuum every week or so, and we'd been
Yes, because all these messages really mean is that autovacuum woke up,
and looked at database xx, it may or may not have decided to do anything
(analyze or vacuum) and judging by the fact that these messages are
almost exactly 1 minute apart, I would say that autovacuum never felt
the need to
Tobias Brox wrote:
[Matthew T. O'Connor - Wed at 02:33:10PM -0400]
In addition autovacuum respects the work of manual or cron based
vacuums, so if you issue a vacuum right after a daily batch insert /
update, autovacuum won't repeat the work of that manual vacuum.
I was experimenting
Sriram Dandapani wrote:
The only issue I have with autovacuum is the fact that I have to briefly
stop/restart postgres every couple of days, which kills autovacuum and
it has no memory of previous work done. I work with several databases
with partitioned tables having high daily volume. Dropping
Sriram Dandapani wrote:
If I were to specify in the pg_autovacuum catalog that certain high
volume partitioned tables(that get dropped daily) be ignored, then
when autovacuum finishes, will it update the transaction id wraparound
counter (this way, I can get autovacuum to finish quickly )
Csaba Nagy wrote:
On Wed, 2006-09-27 at 18:08, Edoardo Ceccarelli wrote:
How can I configure the vacuum to run after the daily batch insert/update?
Check out this:
http://www.postgresql.org/docs/8.1/static/catalog-pg-autovacuum.html
By inserting the right row you can disable
Arnau wrote:
I'm migrating our DDBB from postgresql 7.4 to postgresql 8.1. In
postgreql 7.4 we don't have autovaccum running. So, every night before
doing the daily backup we do a vaccuumdb -f -z for each DB. My doubt
is, the autovaccuum with the version 8.1 is started by default and I
don't
Sriram Dandapani wrote:
Hi
Is it possible to tell autovacuum to ignore certain tables (These
tables are high traffic daily partition tables that get dropped every
other day).
I am trying to get autovacuum to complete quicker and prevent Xactn
wraparound (currently, it takes very
Eamonn Kent wrote:
I am using PostgreSQL 8.1.4 for an embedded application. For some
reason, vacuum is not able to identify rows that are candidates for
removal (i.e., mark space as available).
[snip]
If I shutdown our application and run a vacuum full, the space is
recovered and the
. That's probably
insufficiently aggressive :-(
Alvaro, Matthew, any thoughts about improving that?
Not really. Alvara mentioned reducing the constant that we test against
for DB wide vacuum. I'm a little concerned that might result is
database-wide vacuums more often than desired, but I don't
I think the closest approximation of disabling autovacuum on a per
database basis is to connect to the database in question and perform:
update pg_autovacuum set enabled = 'false';
This will prevent autovacuum from vacuuming or analyzing any of the
tables in the database, but will still
Alvaro Herrera wrote:
Chris Browne wrote:
It strikes me as a slick idea for autovacuum to take on that
behaviour. If the daily backup runs for 2h, then it is quite futile
to bother vacuuming a table multiple times during that 2h period when
none of the tuples obsoleted during the 2h period
Jim C. Nasby wrote:
Small tables are most likely to have either very few updates (ie: a
'lookup table') or very frequent updates (ie: a table implementing a
queue). In the former, even with vacuum_threshold = 0 vacuum will be a
very rare occurance. In the later case, a high threshold is likely
Legit concern. However one of the things that autovacuum is supposed to
do is not vacuum tables that don't need it. This can result in an overal
reduction in vacuum overhead. In addition, if you see that autovacuum is
firing off vacuum commands during the day and they are impacting your
As Alvaro already mentioned this is fixed in 8.1 because it was
integrated into the backend and benifits from the logging features that
backend already has developed.
As for the 7.4.x 8.0.x versions of contrib autovacuum, I don't think
there are any plans for improving it. Also, added it to
Unfortunately, you are correct, there is no way to tell
contrib/autovacuum to rotate it's logs.
Ameet Kini wrote:
Hello,
I'm using postgresql 8.0 and am using contrib/pg_autovacuum. Is there a
way to tell pg_autovacuum to rotate the log that it generates after
it reaches a particular size?
I don't have any RHES 4.0 boxen, but I do have a CentOS 4.1 box. You
can get RPMS for the new version of PostgreSQL, you will probably want
to install a compat rpm that has the libraries that the other software
on the system requires. I forget where I downloaded mine from, but
someone on one
hashes, that
might be safer long-term than with just 1 strong hash. I s'pose it
would add a little cost to the connection-establishing process...
--
Matthew Fuller (MF4839) | [EMAIL PROTECTED]
Systems/Network Administrator | http://www.over-yonder.net/~fullermd/
On the Internet
Scott Marlowe wrote:
I would appreciate an easy way to keep tabs on autovacuum's activity.
A stat table seems like a practical way to have this info readily
available.
No need to keep values across dump/reloads, right?
This sounds more and more like a good idea. I don't think there's
Scott Marlowe wrote:
On Thu, 2005-11-10 at 09:56, Matthew T. O'Connor wrote:
Scott Marlowe wrote:
I would appreciate an easy way to keep tabs on autovacuum's activity.
A stat table seems like a practical way to have this info readily
available.
No need to keep values across dump
Jeff Bohmer wrote:
I assumed (perhaps incorrectly) that you were talking about
maintaining the data in the theoretical / not yet in existence
autovacuum stats table through database restart, the stats system
already has a GUC var that dictates whether or not it dumps it's data
upon DB
Joe Maldonado wrote:
I have not seen any information so far on this but is there a way to
get the autovacuum daemon in PostgreSQL 8.1 to log the tables which it
is perfoming vacuum or analyze operation on?
I think the autovacuum daemon does log it's activity however all it's
output is set at
Tom Lane wrote:
This would be a pretty bad idea IMHO, since it would lead to bloating
the logs with autovacuum progress messages by default --- and whatever
you may think about it, I really doubt that the average DBA will want
those messages there all the time.
I wonder whether it would be
Enzo D'addario wrote:
Hi All,
I currently have postgres 7.4.2 running on a dedicated server which has
1GB RAM, a Pentium 4 2600 Mhz processor, uses ReiserFS filesystem and is
running Debian stable.
After approximately 1 month I have to dump restore the database
because vacuum time spirals
deleted.
If nothing has them open (fstat or lsof or something) they should be
safe to delete (but don't blame me if it blows up!).
--
Matthew Fuller (MF4839) | [EMAIL PROTECTED]
Systems/Network Administrator | http://www.over-yonder.net/~fullermd/
On the Internet, nobody can
On Fri, Jun 03, 2005 at 12:45:08AM -0300 I heard the voice of
Marc G. Fournier, and lo! it spake thus:
On Thu, 2 Jun 2005, Matthew D. Fuller wrote:
If nothing has them open (fstat or lsof or something) they should
be safe to delete (but don't blame me if it blows up!).
This is what I
If you could connect to the database server from the web server and get
data with PGAdmin then it doesn't' sound like a database or database
server problem. How is your web app connecting to the database? ODBC?
That might be a bit of an issue. PgAdmin doesn't use ODBC, it uses
libpq
I
don't really understand all the implications of locale settings.
Thanks,
Matthew O'Connor
Details below:
On the old server
-bash-2.05b$ cat /etc/redhat-release
Fedora Core release 1 (Yarrow)
-bash-2.05b$ locale
LANG=en_US
LC_CTYPE=en_US
LC_NUMERIC=en_US
LC_TIME=en_US
LC_COLLATE=en_US
LC_MONETARY
Steve Crawford wrote:
On Monday 21 March 2005 11:40 am, Tom Lane wrote:
Matthew T. O'Connor matthew@zeut.net writes:
I believe this discrepancy has to do with the fact that ANALYZE
can return some very bogus values for reltuples, where as vacuum
always returns an accurate count. I'm
support
Recompile without this flag.
--
Matthew Wronkowski, CCNP
Bioinformatics
Rochester Institute of Technology
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes
Steve Crawford wrote:
On Thursday 17 March 2005 3:15 pm, Steve Crawford wrote:
I'm having trouble with physical growth of postgresql system
tables
Additional info. The most recent autovacuum entries for the
pg_attribute table are:
[2005...] Performing: VACUUM ANALYZE
Tom Lane wrote:
Steve Crawford [EMAIL PROTECTED] writes:
pg_autovacuum is from 7.4.6 release and is showing periodic vacuums of
the problem tables. I thought that bug was in some release prior to
7.4.6. Does the bug allow it to show a vacuum taking place but not do
it?
I don't recall
pg_autovacuum should work against Postgresql 7.3.x. I don't know if
compiling it from source, or using a 7.4 rpm will be easier
Theo Galanakis wrote:
Hi,
We are currently running an older version of postgres on linux
redhat, the auto_vacuum is not part of the contrib package for
Sudhakar Kurumella wrote:
We have to move from Linux based PostgreSQL to Windows based
PostgreSQL. Highly appreciated for Any ideas of smooth export/import
options.
You do know that the native windows version of PostgreSQL is still only
in Beta. The cygwin port has never really been considered
I am using postgresql 7.4.0 on Debian 3.0r2 (Woody) on an x86 box. I'm
using the binary package from http://people.debian.org/~elphick/debian/
If I run the initscript from the command line:
invoke-rc.d postgresql start
it starts up just fine. But when I reboot the system, it fails to
start
On Jan 13, 2004, at 2:28 PM, Manuel Sugawara wrote:
Matthew M.Burke [EMAIL PROTECTED] writes:
FATAL: XX000: failed to initialize lc_messages to
LOCATION: InitializeGUCOptions, guc.c:1866
Edit your $PGDATA/postgresql.conf file and look for that variable
(lc_messages) and change the empty value
I am using postgresql 7.4.0 on Debian 3.0r2 (Woody) on an x86 box. I'm
using the binary package from http://people.debian.org/~elphick/debian/
If I run the initscript from the command line:
invoke-rc.d postgresql start
it starts up just fine. But when I reboot the system, it fails to
start
should
have been provided.
.
.
.
Am I missing something?
I believe I have all the pre-reqs fulfilled per the docs.
Any help is appreciated
Thank You,
Matthew Montgomery
---(end of broadcast)---
TIP 8: explain analyze is your friend
.
Thanks
Matthew
--
Matthew Pinhorn E-Mail: mailto:[EMAIL PROTECTED]
IONA WWW:http://www.orbacus.com
Making Software Work Together (tm) Phone: (709) 738-3725 x 10
---(end of broadcast
;).
Recommendations?
Thanks.
-Matthew
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
recall offhand.
--
Matthew Fuller (MF4839) |[EMAIL PROTECTED]
Unix Systems Administrator |[EMAIL PROTECTED]
Specializing in FreeBSD |http://www.over-yonder.net/
The only reason I'm burning my candle at both ends, is because I
haven't figured out how to light
stuff.
Thanks,
Matthew M. Copeland
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Well, I reindexed on 7.0.3, and that worked. I also upgraded, and that
worked with my code also, so ignore the not working comment.
Thanks for the help,
Matthew M. Copeland
On Tue, 14 Aug 2001 [EMAIL PROTECTED] wrote:
Yeah, VACUUM doesn't shrink indexes presently (it's on the TODO
to get smaller. They just keep growing. What do I have to do to make the
pkey files flush there extra blocks?
PostgreSQL version 7.0.3 running under Linux.
Matthew M. Copeland
---(end of broadcast)---
TIP 6: Have you searched our list archives
For large tables that require a 24/7 uptime and see around 6-10 million
changes a day, how do I run VACUM without taking the database/table down.
Considering moving from MySQL, but can't quite understand how to run
PostgreSQL in a production enviroment properly.
a) Using replication, you
called product.
Matthew M. Copeland
[EMAIL PROTECTED]
as of the time of the crash.
Again, any word from the developers on when we might be able to expect this?
Matthew H. North
Software Engineer
CTSnet Internet Services
t (858) 637-3600
f (858) 637-3630
mailto:[EMAIL PROTECTED]
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED
I think this ability has been put in place for 7.1. (Anyone confirm?)
-Original Message-
From: Peter Galbavy [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, October 19, 2000 10:42 AM
To: [EMAIL PROTECTED]
Subject: [ADMIN] binding to a specific IP address
This may be an FAQ but
I see that postgre supports win32, but all I see mentioned is NT. Does
postgre run on Win 9x? I don't want this as a production environment,
but need it for development.
Matt
seemed to work and I have not had any problems stem from
this.
-Original Message-
From: Vladimir V. Zolotych [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, May 04, 2000 1:44 AM
To: Matthew; [EMAIL PROTECTED]
Subject: Re: [ADMIN] Removing NOT NULL Contraint
When I need to change
I may be missing something simple here, but I have an attribute in a
table that was created as NOT NULL. I now need to remove this
constraint. I don't see a proper way to do this. I think I can update
the pg_attribute table and change attnotnull from true to false. Is
this an acceptable
I'm not sure if this will help but I just dealt with a similar problem.
When I tried to do a pg_dump it would fail. It turned out the culpret
was one single bad value in one row of one table. Any query that
returned that value would fail also, so I had to go through an
interitive process of
, and that I may have to switch
to another database engine. I would of course prefer to avoid this,
and any suggestions that might enable me to do so would be much
appreciated.
Thanking you in advance,
Matthew Fleming
--
Matthew G
I'm running postgresql 6.5.1 on Redhat 6.0. I am trying to move a
database to a new box running 6.5.3. So I tried a 'pg_dump db_name
db_name.out' so that I could restore the database to the new server.
The pg_dump command failed with the following error:
pqWait() -- connection not open
74 matches
Mail list logo
