Is there any way to set an access log for a specific table?
What I'd like to do is have a record of all access for one table. It
would be nice to do this by raising a notice and having it show up in
syslog. I could use triggers, but I would need to log for SELECT
queries as well. Rules look like l
On Mon, Aug 16, 2010 at 3:01 PM, Tom Lane wrote:
> Greg Smith writes:
>> Tom Lane wrote:
>>> On versions where autovacuum is on by default, I would certainly
>>> recommend trying to use only autovacuum. cron-driven vacuum still
>>> has some uses but they are corner cases.
>
>> Corner cases impli
On Mon, Aug 16, 2010 at 1:34 PM, Scott Marlowe wrote:
> If autovac is properly configured, very few, if any, PostgreSQL
> databases need routine vacuuming jobs. However, other than sleep
> states making it run slower, autovacuum is no different than a regular
> old vacuum. Are you sure this wasn
Hi all,
I have an database server that is experiencing some lock contention
and deadlock. It's infrequent, maybe once every two months, but
time-consuming to deal with.
The issue was that a routine VACUUM process (vacuumdb -az, called
nightly via cron) was locking a table and wasn't completing. T
On Wed, Dec 9, 2009 at 3:29 PM, Adam Tauno Williams
wrote:
> On Wed, 2009-12-09 at 15:18 -0600, Peter Koczan wrote:
>> I found the packages. I'm still confused why local ident
>> authentication works even without a running ident server, though. Does
>> anyone know wh
On Wed, Dec 9, 2009 at 1:25 PM, wrote:
> This looks like you have no ident server running...
>
> I guess you must install it first in recent distros, as there is not much
> use of ident anymore. So you have to search the packages for your OS.
I found the packages. I'm still confused why local id
Hi all,
I'm having some trouble configuring ident authentication for TCP
connections. I can get it to work fine for local connections, but I
can't
Local connection:
$ psql postgres
...
postgres=>
Server Log:
Dec 9 11:06:59 mitchell postgres[27482]: [4-1] LOG: connection
received: host=[local]
On Fri, Apr 10, 2009 at 4:34 AM, Kevin Bailey
wrote:
> We're trying to get a script to backup and restore a whole database server.
>
> So far we're looking at something like
> ...
> which is obviously a rough-cut - and the main problem we have is that a
> client has set up a DB where the owner nam
On Wed, Oct 29, 2008 at 10:53 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Peter Koczan" <[EMAIL PROTECTED]> writes:
>> On Wed, Oct 29, 2008 at 9:11 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
>>> Right. So in the meantime, what Peter should do is try t
On Wed, Oct 29, 2008 at 1:49 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> According to this, you're trying to connect with SSL off, right? I
> don't think you've got any matching lines for that in your
> pg_hba.conf. But I could be reading that wrong.
Well, psql is trying to connect with SSL o
On Fri, Oct 24, 2008 at 8:43 AM, Carol Walter <[EMAIL PROTECTED]> wrote:
> I'm running on Solaris 10 and postgreSQL 8.2.10.
>
> Thanks,
> Carol
>
> On Oct 24, 2008, at 7:47 AM, Helio Campos Mello de Andrade wrote:
>
>> On Thu, Oct 23, 2008 at 4:12 PM, Carol Walter <[EMAIL PROTECTED]> wrote:
>>>
>>>
Hi all,
I'm looking to add an md5-based user to a postgres server, and I can't
seem to get psql to connect using SSL. Normally I use Kerberos to
connect, and that works flawlessly with SSL. I'd much prefer to use
SSL connections, so I'm wondering if there's something I'm doing
wrong.
Here's what
On Wed, Sep 10, 2008 at 6:50 PM, Stephen Frost <[EMAIL PROTECTED]> wrote:
> Peter,
>
> * Peter Koczan ([EMAIL PROTECTED]) wrote:
>> Gritty details:
>> PostgreSQL 8.3.3
>> Windows XP SP2 (32-bit)
>> Kerberos for Windows 3.2.2
>> Server is running on
Hello,
I'm trying to offer a Windows XP client for Postgres 8.3.3 to my
users. I have it built and installed and everything is working except
for GSSAPI. I'm getting the following error:
C:\temp>S:\postgresql-8.3.3\bin\psql -h mitchell -p 5432 postgres
psql: SSPI continuation error: No credential
On Wed, Jun 18, 2008 at 2:00 PM, Roberto Garcia
<[EMAIL PROTECTED]> wrote:
> Just to mention one issue we had here:
>
> In 8.1 we did this to retrieve all data from a specific date:
> SELECT * FROM xxx
> WHERE LIKE '2008-05-20%'
>
> In 8.3 we had to change to:
> SELECT * FROM xxx
> WHERE >= CAST(
On Thu, Jun 19, 2008 at 7:18 PM, Joshua D. Drake <[EMAIL PROTECTED]> wrote:
> Kunal Gupta wrote:
>>
>> Hi ,
>>
>> I have a question, I am suppose to create database with limited size. Is
>> there any way I can set limit to my database created in postgresql.
>
> Not reasonably no.
>
>> - Since in po
On Sun, Jun 15, 2008 at 12:11 PM, Andreas Philipp
<[EMAIL PROTECTED]> wrote:
> Hi all,
>
> We are implementing a hospital information system and a human
> resources/payroll processing system on two identical dedicated servers with
> two Xeon Quad Core processors and 32 GB RAM each, both servers bei
Hi all,
I'm looking to upgrade a server to postgres 8.3, but there's one
connection that's being problematic. It continually says it's
UPDATE'ing, but it's been at it for weeks now, and while the database
is big, it shouldn't take weeks.
Even weirder is that it looks like the client-side connecti
ted"
> into your PG init script and restarting.
> On Tue, Apr 29, 2008 at 10:35 AM, Peter Koczan <[EMAIL PROTECTED]> wrote:
> Yeah, a stack trace and if possible, a self contained test case to
> reproduce the bug would help. If you are using a custom build, then
> usi
Hi all,
I'm looking to push 8.3 out this week, but I'm running into a
particularly nasty bus error. I'm not sure what's causing it as it
appears to be transient (or at least somewhat random), but I do know
that it bites on connection time and takes down the entire server with
it.
I'm going to try
On Fri, Apr 18, 2008 at 12:43 PM, Peter Koczan <[EMAIL PROTECTED]> wrote:
> On Thu, Apr 17, 2008 at 11:40 AM, Peter Koczan <[EMAIL PROTECTED]> wrote:
> > Hi all,
> >
> > I just upgraded one of my servers and I'm having a bit of trouble
> > gett
On Thu, Apr 17, 2008 at 11:40 AM, Peter Koczan <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I just upgraded one of my servers and I'm having a bit of trouble
> getting some of the kerberos authentication bits working.
> Specifically, any Kerberos instance run out of a v
Hi all,
I just upgraded one of my servers and I'm having a bit of trouble
getting some of the kerberos authentication bits working.
Specifically, any Kerberos instance run out of a v5srvtab doesn't work
so well. Using stashed tickets or normal principals worked fine.
Gritty details follow.
Peter
On Wed, Apr 2, 2008 at 2:05 PM, Hyatt, Gordon
<[EMAIL PROTECTED]> wrote:
> I want to move an existing tablespace on an FC8 box. I read (at
> http://www.postgresql.org/docs/8.2/static/manage-ag-tablespaces.html)
> that one can simply stop the server, update the symbolic link in
> $PGDATA/pg_tbls
> > I had to deal with this recently where the
> > status was "notify interrupt"
>
> Is this a response from "pg_ctl status" command?
I'm referring to the last field of "ps ax". For instance, this line...
>13841 ?Rs 234:32 postgres: gadb canon 127.0.0.1(44837) SELECT
would have
> > did you try pg_ctl -m immediate stop ???
>
> I just did, and it worked.
>
>$ pg_ctl stop -m immediate
>waiting for server to shut down done
>server stopped
I'd be careful about shutting down using "immediate" mode. It forces
the database into recovery mode.
Your problem co
On Mon, Mar 10, 2008 at 9:41 AM, Mark Steben <[EMAIL PROTECTED]> wrote:
> We have an application that cuts and pastes from Word and is getting this
> error now that we have converted to 8.2.5.
>
> The characters that are erroring are imbedded in the document. We would
> prefer not having to rem
On Fri, Feb 22, 2008 at 8:54 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Dawid Kuroczko" <[EMAIL PROTECTED]> writes:
> > I have recently found a nice utility called memstat (which shows how
> > much of private
> > (not shared)) memory each process uses. What kind of surprised me was
> > the amo
On Feb 19, 2008 3:04 PM, Carol Walter <[EMAIL PROTECTED]> wrote:
> I went to the page. It said the OS is Linux. Is there anything for
> Solaris. I've got Solaris 10.
It looks like it's just some C code, a shell file, and some sql
scripts. It should compile and run fine on any postgresql supporte
> > Is there some magic rpm source I am missing? Or should I just install
> > from source?
>
> Honestly, on a db only server it's not an entirely crazy idea. About
> 2/3 of all pgsql servers I've set up have been source builds for
> various reasons. Just create a conf.local file that's a sh scrip
On Feb 8, 2008 6:01 PM, Scott Cotton <[EMAIL PROTECTED]> wrote:
> I have a database that I dropped some users on. However, when doing dumps
> and restores, it became clear that these users were still being referenced
> by sysid. So I created new users and assigned them the sysids of the users I
> h
On Feb 7, 2008 11:27 PM, Vishal Arora <[EMAIL PROTECTED]> wrote:
> You can have more than one instance of PostgreSQL server running on the same
> Windows machine as long as you have different DataDir for each of them. you
> can have initdb process indicating different datadir.
And you need each in
On Feb 1, 2008 9:08 AM, Bronson, Allan B. (Mission Systems)
<[EMAIL PROTECTED]> wrote:
> I am trying to create a SSl connection to PostGres using JDBC and am not
> sure if it is working.
>
> I read that you must have openssl on the client machine for the connection
> to take place. I have turned s
> Yeah, it's expecting to see a set of three related triggers. For
> instance, if I do this in a 7.0 database:
> ...
> If your original database doesn't have all three triggers, then you had
> a problem already --- the FK constraint wasn't being enforced properly.
Ah, these were from before I bec
> I see what you're saying, but the weird thing is that this happened
> even when I did a full dump/restore of that database. I'll try out a
> few different things (like restoring the tables, then restoring the
> triggers). Hopefully I'll be able to suss out the issue or at least
> find a workaroun
> > I finally found more time to test this more extensively. i'm still
> > running into an issue with this, although it's different this time.
> > There are no errors printed to the terminal, but neither the foreign
> > key nor the trigger get made.
>
> It looks like you are trying to restore from
> > [ squint... ] Which 8.3beta are you testing, exactly? This was dealt
> > with in beta3.
> >
> > If it is beta3 or later, please send the output of "pg_dump -s -t wspolicy"
> > from the 8.2 database.
>
> *lightbulb* beta2. I haven't had much time to dedicate to testing new
> betas yet (I was j
Hi all,
I'm looking into adding things to the way I monitor pgsql
installations. One of them was free space map stuff. Is there a way to
get free space information (especially used free space) without having
to screen-scrape the output of VACUUM VERBOSE? I know you can get the
allocated fsm pages
> After looking for a way to transfer PostgreSQL/PostGIS data from windowsXP
> to linux (Ubuntu 7.10), I did not find it.
> Please, does anyone know an easy way or free tool for it.
Are pg_dump/pg_restore somehow insufficient for your needs?
(http://www.postgresql.org/docs/current/interactive/ba
Hi all,
I'm planning a lot of changes for migrating to PostgreSQL 8.3, among
them being a better way of separating data and logs (transaction logs,
that is).
Currently, the OS and log data are on one disk system, and the data
(including configs) are on the other disk system. After creating the
da
On Dec 11, 2007 9:06 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Peter Koczan" <[EMAIL PROTECTED]> writes:
> >> No, pg_dump isn't involved --- the new smarts are inside the server,
> >> in CREATE CONSTRAINT TRIGGER. It sounds like Peter has a case
> No, pg_dump isn't involved --- the new smarts are inside the server,
> in CREATE CONSTRAINT TRIGGER. It sounds like Peter has a case that is
> confusing that new code. Could we see a complete example?
Sure, here's the command:
$ /s/postgresql-8.2.5/bin/pg_dump -h sensei -p 5432 -C -Fc sushi |
On Dec 10, 2007 1:50 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> Peter Koczan escribió:
> > Hi all,
> >
> > I am having a problem with what appear to be legacy foreign keys in
> > some of my databases (probably pre-7.2 days). They won't restore from
>
Hi all,
I am having a problem with what appear to be legacy foreign keys in
some of my databases (probably pre-7.2 days). They won't restore from
8.2 to 8.3, because the '' field in the called function isn't
unique when I try to restore more than one of these legacy keys
Here's an example
=> \d
Hi all,
I'd like to move my database encoding from SQL_ASCII to UTF8, mostly
because "No encoding conversion will be done when the setting is
SQL_ASCII. Thus, this setting is not so much a declaration that a
specific encoding is in use, as a declaration of ignorance about the
encoding." (from
http
> Minimum shared mem: 140731400 bytes = 135.0 MB
> Maximum shared mem: 268435456 bytes = 256.0 MB
>
> kern.ipc.shmmin="140731400"
> kern.ipc.shmmax="268435456"
>
> Nov 13 20:46:19 neptunus postgres[669]: [1-2] DETAIL: Failed system
> call was shmget(key=5432001, size=21725184, 03600).
The share
>
> *CC mailing* : I got the bounced mail as in attachment from you when I
> CCed to "pgsql-admin@postgresql.org" So, I am not doing so.
>
Like the message says, you should contact the powers that be to figure out
what happened. Please always email the mailing list, as I am *not* your
personal help
>
> I had a file with name "dump_live" which contains all the dump using
> "pg_dumpall > dump_live". Now, I need to restore it to another schema
> "schema2". How can I restore the data from that file to specified empty
> schema2. (This schema is created with out any objects). Pls advice me how to
>
Hi Suresh, you should Cc: pgsql-admin@postgresql.org in case I don't know or
someone else can help you sooner.
I am using on prod server with psql version : psql 7.4.2
>
> In another test server the version of psql is : Portions Copyright (c)
> 1996-2003, (could not get version)
>
You should figur
> Here I need to know from you
>
> a) Is there any better way to do this other than the above?
>
That's more complicated than you need to make it. What I do is something
like this (in a shell):
pg_dumpall -h host1 -p 5432 | psql -h host2 -p 5432 template1
and then delete the data as necessar
> The docs state that if there are no %-escapes in log_filename, then it will
> add the epoch onto the end of the log filename. Is there any way to turn off
> this behavior and just use the filename specified in log_filename? I'd like
> have all the log data written to a file like postgresql.log an
On 10/23/07, Suresh Gupta VG <[EMAIL PROTECTED]> wrote:
>
> Hi Peter,
>
>
>
> 1) We are using "psql 7.4.2" version of Postgresql, need to create a
> new schema similar to the current schema with all the objects as in the
> current schema. Do we have any command to support this operation?
>
On 10/18/07, Peter Koczan <[EMAIL PROTECTED]> wrote:
> > Is there any other way to clear trailing spaces when I restore the table?
>
> If you're running 8.x, you can do this in place:
>
> ALTER TABLE c1 ALTER COLUMN name varchar(20) USING rtrim(name), ALTER
> COL
> Is there any other way to clear trailing spaces when I restore the table?
If you're running 8.x, you can do this in place:
ALTER TABLE c1 ALTER COLUMN name varchar(20) USING rtrim(name), ALTER
COLUMN date varchar(20) USING rtrim(date);
This could take a long time if the table is large or has a
Hi all,
I'm making a lot of changes for my eventual migration to postgres 8.3,
and I was considering changing how data and logs are separated for a
few of my servers.
Currently, the OS and log data are on one disk system, and the actual
data cluster (including configs) are on the other disk syste
On 10/7/07, Suresh Gupta VG <[EMAIL PROTECTED]> wrote:
>
> Hi Peter,
>
>
>
> Thanks for your reply and to your colleague Scott. Can you pls explain
> below sentence marked in red.
>
>
>
> - - -- -
>
> As an alternative to Scott's suggestion (upg
>
> We are using "psql 7.4.2" version of Postgresql, these days all the
> transactions on the database are taking long time to execute. We are
> planning to do "ANALYZE" command on the database. Could you please advice
> us, how much time it takes and what are the conditions we need to keep on an
>
Hi all,
I have a question regarding functions. I'm in the process of porting
old but necessary applications to postgres, and was wondering about a
couple things regarding portability of functions.
- Below I have an error message from pg_restore on an old database
dump. Apparently this function wo
> The results are ok, but the query is too slow. I tried to applay a GIST and
> gtree index but performance did not improve.
> By explaining the query I see that the where condition gets the query slow.
> Is there a more simple way to check if two poligons have a not null
> intersection (they
> Is it possible to generate the output of SQL statements in PostgreSQL to XML
> output?
>
> If anybody has came across with this or worked on it? Or anybody has any
> Documentation for that?
Yes, you can either install the xml2 contrib module for current
versions, or if you can hold out until 8.3
A few notes. postgres looks through pg_hba.conf until it finds the
*first* entry that matches and tries to authenticate and connect using
that method. There's no fallback, so order of entries is important.
If you just want to have any IP connect using one particular
authentication method, put some
Thank you Alvaro, it worked.
I got confused when I did it before since gdb couldn't figure out half
of the elements in the backtrace, and strace kept saying "I can't
attach", which was only because gdb was already attached to that same
process.
Peter
Alvaro Herrera
Hi all,
I'm running into a few problems with postgres connections, specifically
notify/listening connections. What program(s) should I attach to the
connection using gdb or strace in order to actually get some useful data?
i.e. I'll be using something like "strace -p [pid] [command]", what should
Most database systems can figure out the size (in bytes) of a column
rather quickly from offsets within a record. I doubt that postgres is
any different. The only difference I can think of is that it might be
slower to filter out trailing spaces of a char column before comparing,
though the act
text alway better than varchar ?--- when it comes
to a string column...
thanks
*/Peter Koczan <[EMAIL PROTECTED]>/* wrote:
Hi, Jessica,
> 1. How do I get rid of the nonstandard warning, but still using the
> index search?
You have two options.
- Turn off the warn
Hi, Jessica,
1. How do I get rid of the nonstandard warning, but still using the
index search?
You have two options.
- Turn off the warnings in the postgresql.conf file. Use this with
caution (or don't use it at all) as it does pose a potential threat for
SQL injections if other options aren'
Hi all,
I would like to, but can't seem to get Kerberos working for local
connections.
Here are the last few lines of my pg_hba.conf
# TYPE DATABASEUSERIP-ADDRESSIP_MASK METHOD
local all all krb5
hostssl all
Tommy Gildseth wrote:
Campbell, Lance wrote:
I will be converting from PostgreSQL 8.1.4 to 8.2.4. Is it OK to do
the following command in order to load the new database after install?
pg_dump -h /host1/ –p 1234 /dbname/ | psql -h /host1 –p 56789/
/dbname///
Remember that port 1234 is Post
Hello,
I checked in the docs and forums and couldn't find exactly what I wanted, so
I figured I'd ask around here. Are there any good programs (specifically
portable ones) for doing fairly intense benchmarking of postgres?
Mostly, I'm interested in getting performance numbers from different
hard
Check the pg_locks system view in the pg_catalog schema. It will tell
you a wealth of information.
Peter
Dan Harris wrote:
Greetings..
I'm running 8.0.12 and the system has been very stable for years now
with no significant application changes. I am using
Apache::Session::Postgres in a web
Chris Hoover wrote:
I am doing some research into partitioning my postgres database.
While doing this, I am trying to take the opportunity to improve the
over all database.design. One of the things I am thinking of
implementing would be the use of nullif/coalesce. Our application
tends to s
Let me know if you need more info.
Peter
On 5/22/07, Peter Koczan <[EMAIL PROTECTED]> wrote:
The release is 8.2.4. I haven't been able to reproduce the condition yet,
but I will send along stack traces as soon as I can. I have this strange
feeling that it's only going to happen wh
AIL PROTECTED]> wrote:
"Peter Koczan" <[EMAIL PROTECTED]> writes:
> [ lots of processes stuck in "notify interrupt" code ]
That's weird. If it's still in that state, or if you can reproduce it,
could you attach to a few of those processes with gdb and get
Hi all, I'm having some trouble restarting a long-running server. Basically,
pg_ctl can't kill any processes waiting on a notify interrupt. Here's what
happened after trying a "stop -m fast"
[EMAIL PROTECTED] ~]# ps axvw | grep post
3606 ?S 3:30 23 3260 41575 6644 0.1 /usr/bin/
roup management scripts to properly reflect this.
Peter
Peter Koczan wrote:
A few months ago, I upgraded postgres from 7.4 to 8.2. There were a
few gotchas, but we don't keep a whole lot of data so even the biggest
problems were, on the whole, minor.
- The cidr data type became more strict,
A few months ago, I upgraded postgres from 7.4 to 8.2. There were a few
gotchas, but we don't keep a whole lot of data so even the biggest problems
were, on the whole, minor.
- The cidr data type became more strict, and a few tables in our network
database would not restore until this was fixed.
I've noticed in my own experiments and experiences with VACUUM FULL that
it tries to reindex all the indexes to compress them. While a good idea,
this unfortunately takes a *long* time.
You should check two things. First, the server CPU usage should be high
(~100% if on a single core). Second,
Just for funsies, today I decided to try and serve postgres data out of AFS
(just a tablespace, not an entire database cluster). For those of you not in
the know, AFS is a distributed, networked file system. It's like NFS, but
with differences in structure, permissions, quotas, administration, and
I saw something along these lines recently when I was running stuff from
postgres 7.4.7 (we recently upgraded to 8.2.3). Basically, a unique index
somehow fails and allows duplicate rows...but I don't know why.
Deleting doesn't work because postgres only looks for the record shown by
the index (t
Hello,
I understand that this is probably outside the realm of Postgres itself, so
if no one has a really good definite answer, that's fine. Still, I thought
I'd fish for information and see if anyone else has run into this problem.
I've already made a bug report to the DBD::Pg folks.
The proble
Hi everyone,
I'm looking to convert a lot of data from Sybase to Postgres. As this will
be a painful process, I was wondering if there are any utilities and
programs to ease the conversion process. Specifically, I'm looking for help
in 3 areas.
1. Recreation of the schema, especially converting
Michael Monnerie wrote:
On Freitag, 9. Februar 2007 04:08 Peter Koczan wrote:
Case in point, I use xfs as the filesystem running under postgres,
and after a few days the "major" database clusters showed ~90%
fragmentation on their respective partitions (which is about a 10 to
1 rat
Moiz Kothari wrote:
Hi All,
What are the reasons of data getting fragmented in postgres? Do we
have any page which explains different scenarios of data getting
fragmented?
Regards,
Moiz Kothari
I guess there are two types of fragmentation in play with postgres,
internal database fragmentati
On 1/30/07, Tom Lane <[EMAIL PROTECTED]> wrote:
"Peter Koczan" <[EMAIL PROTECTED]> writes:
> So, is there any remedy to my problem (see below) short of granting
> superuser access? Is this a bug (which I would then report on the
> appropriate channels)?
?
As for Tom's suggestion, there's no way to specify the database in
pg_dumpall, only the server, and the same bug occurs if I run as the user on
the same server and cluster with the same major version.
Peter
On 1/29/07, Tom Lane <[EMAIL PROTECTED]> wrote:
"Peter Koczan&q
Hi all,
Thanks for your help thus far. I almost have 8.2 ready (hooray no more
ancient 7.4). I do have one more problem which is likely the last thing
before pushing out live support. pg_dumpall isn't working.
Right now, we run pg_dumpall as a non-superuser, let's call it backup, whom
we give re
and other staff.
On 1/21/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
On Fri, Jan 19, 2007 at 03:45:18PM -0600, Peter Koczan wrote:
> I'm upgrading our Postgres installation from 7.4 to 8.2 (I know, I know,
but
> we've been busy). The biggest thing to change will be our use
I'm upgrading our Postgres installation from 7.4 to 8.2 (I know, I know, but
we've been busy). The biggest thing to change will be our user/group
management scripts, as they directly modify pg_shadow and pg_group. Beyond
being potentially stupid (but hey, I didn't write it), this just won't work
a
88 matches
Mail list logo
