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 di
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 wrote:
> Lame question - how to copy output from psql console?
>
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 wrote:
> The output is in Polish :) is there anything particular to look for?
>
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
wro
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 mes
Oh and could we please have the output from SELECT version(); too please.
On Sun, Nov 21, 2010 at 7:56 PM, Matthew Walden wrote:
> A few more information requests if you don't mind.
>
> Can you analyze the table ie "ANALYZE USERS" without error?
>
> Could you pl
Can you post the select and the exact error?
On Sun, Nov 21, 2010 at 6:43 PM, Lukasz Brodziak
wrote:
> 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 actions on it includi
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
That did it. Thanks!
Matthew Excell
On Mon, Jun 7, 2010 at 1:46 PM, Alvaro Herrera
wrote:
> 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 titl
s that I can't execute a "drop 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
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 dis
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.
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 changing.
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 isn
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 isn't
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.
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 exp
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 autovacuu
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 long
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'
A PostgreSQL superuser, so yes user postgres will work just fine.
Sriram Dandapani wrote:
Do you mean that I login as say root and issue a vacuumdb (or do I login
as a postgres user with special privileges)
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Wednesday, S
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 databa
uld happen. 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
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 chec
Alvaro Herrera wrote:
Jim C. Nasby wrote:
On Wed, Mar 29, 2006 at 03:36:36PM +0530, Gourish Singbal wrote:
we are using postgresql 8.1.3 and wanted to enable autovacuuming for only
one of the many databases on the same postgresql cluster.
pg_autovacuum seems to allow to ignore only a particular
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 to
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 wil
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
response
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? Fro
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
or 3 weak 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/
O
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 restart
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 a
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 a
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 pract
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
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 ou
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 i
member
which case it is. But they're renames of files that were "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 Adminis
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 directl
ems since 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&
Steve Crawford wrote:
On Monday 21 March 2005 11:40 am, Tom Lane wrote:
"Matthew T. O'Connor" 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&#x
ompiled with
--enable-integer-datetimes enable 64-bit integer date/time support
Recompile without this flag.
--
Matthew Wronkowski, CCNP
Bioinformatics
Rochester Institute of Technology
---(end of broadcast)---
TIP 9: the planner will ignore yo
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 t
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 "pg_catalog"."pg
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 7.3
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 a
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_mess
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 a
_v1.2/java/build.xml:146: Compile failed, messages 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
ript using ODBC ;).
Recommendations?
Thanks.
-Matthew
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
to connect as the DB superuser assuming no password is a rather
inappropriate way of going about it, but that's another can of worms.
--
Matthew Fuller (MF4839) |[EMAIL PROTECTED]
Unix Systems Administrator |[EMAIL PROTECTED]
Specializing in FreeBSD |
which one it is you have to take
out, I can't 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 b
little feature? I really
really need the async notification 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&#x
it is working again in the current 7.1.3? I will take a look
at it and see if I can get it working though.
Thanks for all the help,
Matthew M. Copeland
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
su
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 replic
Postgre is not threaded, but every connection gets it's own process. The OS
will distribute the processes across the processors. Not sure I said that
very will.
Basically a single connection will not be any faster with SMP, but multiple
connections will be.
> -Original Message-
> From:
bases I created called product.
Matthew M. Copeland
[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 FA
s pretty
much up-to-date 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
Any word on when (if?) live/hot backup will be available?
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]]On
Behalf Of Michel Decima
Sent
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
Everything 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 Cont
Everything I have read indicates that there is no need to take down the
database server before doing a backup. This is true regardless of the
number of connected users and what they are working on at the time. In
fact pg_dump fails unless postmaster is running. The only reason you
would want to
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 soluti
Not sure about this, but I beleive that pgAdmin has some tools built in
that will port your database. At least they were planning on that a
while ago. Worth a look I think.
-Original Message-
From: miguel valero
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: 3/7/00 8:11 PM
Subject: Re:
This is an easy one.
issue the command 'createuser' (without quotes). It will prompt you for
the username to add. Enter user nobody, you can accecpt his Unix id. I
would say no to the ability to create a database and is superuser
questions.
Then you have to grant permissions to user nobody on
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 sele
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
--
Matt
I had a similar problem that I was able to solve by specifying the -B
option and having postgre use more RAM. By default postgre uses very
little memory, something like 100-200k, I set it to use 8M and my
queries were faster and I have yet to run into a out of memory problem.
I'm running RedHat
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
PQendc
as commandline arguments, so it might
not be grabbing the ouput from postmaster at all.
Here's my startup script:
echo "postmaster -B 256 >/home/postgres/pm.log 2>&1 &" | su - postgres
This is on my Slackware system.
-M@
--
Matthew Hixson - CIO Linux, n;
FroZenWave Communications the nouveau postmodern operating
http://www.frozenwave.com system for the masses
is before? any help would be grateful.
On this line:
-S -o -F > /usr/local/pgsql/errlog' &
Modify it to look like this:
-S -o -F 2>&1 > /usr/local/pgsql/errlog' &
Assuming this script runs through bash it will direct standard error
(stderr) to standard output (stdo
I've been looking on the mailing lists for this problem, but haven't found
anything yet. My apologies if I'm not looking in the right place.
System:
Linux Kernel 2.1.108 (but occurs under 2.0.33 as well)
Glibc 2.0.7pre3
Bash 2.02
Problems:
(1) Compiles. Installs. But when running regression t
87 matches
Mail list logo
