Sleep often seems a better problem solver than thinking. Create
databases ahead of time and assign at the appropriate time.
Begin forwarded message:
From: Ben Eliott ben.apperr...@googlemail.com
Date: 2 March 2010 18:22:17 GMT
To: pgsql-general@postgresql.org
Subject: createdb but revoke
On 02/03/10 18:22, Ben Eliott wrote:
I have two roles, 'adminuser' with createdb permission, and 'dbuser' a
user with CRUD privileges.
adminuser is a member of the dbuser role, this seems to allow adminuser
to createdb databases for dbuser with:
createdb -U adminuser -O dbuser new_database_name
On 02/03/10 21:47, akp geek wrote:
I am doing a replication of one of the database and the size of the slave
database is growing exponentially . Right now the size of master db is 849M
and the slave is 7GB.
my master is 8.3 and slave is 8.4
I'm guessing your slave isn't being vacuumed. Or,
Hi,
Thank-you for coming back and your advice. I understand what you mean.
However, in order to run the script without additional user
input, .pgpass is always needed. One way or another, which ever way i
try and twist this, something has to give on security. Perhaps it
would be just
On Tue, Mar 2, 2010 at 8:26 PM, Thom Brown thombr...@gmail.com wrote:
On 2 March 2010 14:49, Antonio Goméz Soto antonio.gomez.s...@gmail.com
wrote:
Op 02-03-10 13:00, Thom Brown schreef:
On 2 March 2010 11:46, Nilesh Govindarajanli...@itech7.com wrote:
On Tue, Mar 2, 2010 at 4:57 PM,
Thank you all for the suggestions. I did a vacuum and the size has gone down
drastically. But still it is not the same size as my master. I am looking
into it
Appreicate your help
On Wed, Mar 3, 2010 at 4:41 AM, Richard Huxton d...@archonet.com wrote:
On 02/03/10 21:47, akp geek wrote:
I am
On 03/03/10 13:32, akp geek wrote:
Thank you all for the suggestions. I did a vacuum and the size has gone down
drastically. But still it is not the same size as my master. I am looking
into it
Check your autovacuum settings and you should be able to keep things
stable at least.
You might
Hi.
I'm continuing on with the problems I have in our reports/data warehouse
system. Basically, the system brings in tables from our various production
systems (sybase, postgresql, mssql, different servers) every night. Some tables
are brought in whole, and some are brought in based on a date
On Wed, 2010-03-03 at 08:32 -0500, akp geek wrote:
Thank you all for the suggestions. I did a vacuum and the size has
gone down drastically. But still it is not the same size as my master.
I am looking into it
Plain vacuum does not file system size of relations. It just marks dead
spaces are
On Tue, Mar 2, 2010 at 9:56 AM, Thom Brown thombr...@gmail.com wrote:
As far as I'm aware. It's only in the upcoming version 9.0 that you
can do things like:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
Other folk on here may have some alternative suggestions though.
9.0 will
I just made changes to postgresql.conf to make sure the autovacuum is turned
on and I will update you.. Again thanks for your time
Regards
2010/3/3 Devrim GÜNDÜZ dev...@gunduz.org
On Wed, 2010-03-03 at 08:32 -0500, akp geek wrote:
Thank you all for the suggestions. I did a vacuum and the
Hello all,
I am trying to do the following query but I would like to know how
many rows were deduplicated in the process:
psql -d foo -c 'SELECT DISTINCT ON (error) error,ev_text FROM
clients_event_log' -o fullfoo
For example, I would want the output to be the following where count
is the
On Wednesday 03 March 2010 07:29:21 am Merlin Moncure wrote:
On Tue, Mar 2, 2010 at 9:56 AM, Thom Brown thombr...@gmail.com wrote:
As far as I'm aware. It's only in the upcoming version 9.0 that you
can do things like:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
Other
select count(*) AS count, error, ev_text FROM clients_event_log GROUP BY
error, ev_text;
you can add 'HAVING count(*) X'; , if you want to see only those with
count above X, etc.
--
GJ
2010/3/3 Grzegorz Jaśkiewicz gryz...@gmail.com:
select count(*) AS count, error, ev_text FROM clients_event_log GROUP BY
error, ev_text;
you can add 'HAVING count(*) X'; , if you want to see only those with
count above X, etc.
--
GJ
I was just about to reply to the group. Would this
just try if it does what you want it to do ;)
I've a function defined as:
create or replace function catalog_relateditems(__itemid bigint,
families int[]...
I call it with
select * from catalog_relateditems(6538::bigint, ARRAY[1,2,3,4,5]);
and I get:
HINT: No function matches the given name and argument types. You
might need to add
Hi,
I am having a problem with authentication using LDAP on PostgreSQL 8.4.2.
The problem seems to be limited to which attribute is specified in the
ldapprefix. If I specify uid= and then try login using the username
trobst (which is the value in the ldap db) I get an error:
hostall
On 3 March 2010 14:51, Kevin Kempter kev...@consistentstate.com wrote:
On Wednesday 03 March 2010 07:29:21 am Merlin Moncure wrote:
On Tue, Mar 2, 2010 at 9:56 AM, Thom Brown thombr...@gmail.com wrote:
As far as I'm aware. It's only in the upcoming version 9.0 that you
can do things like:
2010/3/3 Tom Robst tomro...@thermocable.com:
Hi,
I am having a problem with authentication using LDAP on PostgreSQL 8.4.2.
The problem seems to be limited to which attribute is specified in the
ldapprefix. If I specify uid= and then try login using the username
trobst (which is the value
Kevin Kempter kev...@consistentstate.com writes:
I believe all you have to do is this to create a read only user:
create user ro_user with password 'passwd';
alter user ro_user set default_transaction_read_only = true;
You do realize the user can just unset that again?
Ivan Sergio Borgonovo m...@webthatworks.it writes:
I've a function defined as:
create or replace function catalog_relateditems(__itemid bigint,
families int[]...
If you want any useful comments, you're going to have to show a complete
example, rather than selectively editing out what you
Thanks Magnus. I should have mentioned I'm using OpenLDAP 2.2. I guess
I'll just have to wait for Postgres 9 and workaround it in the meantime.
It's not an insurmountable issue...
Regards,
Tom Robst
--
On 03/03/10 15:18, Magnus Hagander wrote:
2010/3/3 Tom Robsttomro...@thermocable.com:
Hi,
On Wed, 3 Mar 2010 16:05:29 +0100
Ivan Sergio Borgonovo m...@webthatworks.it wrote:
I've a function defined as:
create or replace function catalog_relateditems(__itemid bigint,
families int[]...
Forget about it... there was a typo (missed out) that mixed in/out
parameters.
Sorry for the
On Wed, 03 Mar 2010 10:22:31 -0500
Tom Lane t...@sss.pgh.pa.us wrote:
Ivan Sergio Borgonovo m...@webthatworks.it writes:
I've a function defined as:
create or replace function catalog_relateditems(__itemid bigint,
families int[]...
If you want any useful comments, you're going to have
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
My question is this: will ALTER TABLE ONLY $subtable DISABLE TRIGGER
ALL within a transaction only affect my transaction, or will it affect
anyone inserting into this subtable. If it blocks external inserts
that's ok since my transactions
On 03/03/10 15:46, Greg Sabino Mullane wrote:
ALTER TABLE will lock and block, but I'd be remiss if I didn't point
out the use of session_replication_role as a much better solution to
this particular class of problem. (Even if your version does not
support it, Vick, it should be noted here for
Hi!
I've set up some system to track slow page executions in one of our (as
yet not live) web apps. The tracking itself is handled completely within
the database using a function. Within a very short time (approx. 1 week)
and although we haven't got that much traffic on our testpages, the
table
On 3 March 2010 15:33, Markus Wollny markus.wol...@computec.de wrote:
Hi!
I've set up some system to track slow page executions in one of our (as
yet not live) web apps. The tracking itself is handled completely within
the database using a function. Within a very short time (approx. 1 week)
do a vacuum analyze verbose on it, and see if it complains about FSM (free
space map) setting. Which it probably will be.
On Wed, Mar 3, 2010 at 8:31 AM, Herouth Maoz hero...@unicell.co.il wrote:
First, the easy part - regarding allowing/disallowing queries. Is it
possible to GRANT or REVOKE access to tables based on the originating IP?
I'd suggest separating out access to your tables by roles, and then
On Mar 3, 2010, at 18:01 , Josh Kupershmidt wrote:
On Wed, Mar 3, 2010 at 8:31 AM, Herouth Maoz hero...@unicell.co.il wrote:
First, the easy part - regarding allowing/disallowing queries. Is it possible
to GRANT or REVOKE access to tables based on the originating IP?
I'd suggest
I have just configured our disks to raid10 from raid5.
The raid 10 is now 6 drives giving us 750G use by OS and postgres.
What is the preferred setup for postgres concerning cache settings on the
controller and disks and the preferred block size allocated for the disks when
postgres uses
1: Is the default work_mem pre-allocated to the Postgres processes - or
does it get allocated when needed? Say I have work_mem at 30MB - will
Postgres allocate that 30MB on connection, or only when it needed by a
query?
2: If work_mem is, say, 30MB, and my query needs 10MB - will Postgres
2010/3/3 Terry td3...@gmail.com:
2010/3/3 Grzegorz Jaśkiewicz gryz...@gmail.com:
select count(*) AS count, error, ev_text FROM clients_event_log GROUP BY
error, ev_text;
you can add 'HAVING count(*) X'; , if you want to see only those with
count above X, etc.
--
GJ
I was just about
In response to Brad Nicholson bnich...@ca.afilias.info:
1: Is the default work_mem pre-allocated to the Postgres processes - or
does it get allocated when needed? Say I have work_mem at 30MB - will
Postgres allocate that 30MB on connection, or only when it needed by a
query?
It's allocated
On Wednesday 03 March 2010 08:19:46 am Tom Lane wrote:
Kevin Kempter kev...@consistentstate.com writes:
I believe all you have to do is this to create a read only user:
create user ro_user with password 'passwd';
alter user ro_user set default_transaction_read_only = true;
You do realize
Hi All -
I need some help from you. this question is in follow up with my
earlier questions. I turned the autovacuum and restarted the db and the
settings I have as follows. It seems the autovacuum process has not been
turned on. It's almost more than 3 hours I have restarted my DB with
-Ursprüngliche Nachricht-
Von: Thom Brown [mailto:thombr...@gmail.com]
Gesendet: Mittwoch, 3. März 2010 16:56
An: Markus Wollny
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Massive table bloat
If you update rows, it actually creates a new version of it.
The old one
On Wed, 2010-03-03 at 12:46 -0500, akp geek wrote:
Hi All -
I need some help from you. this question is in follow up
with my earlier questions. I turned the autovacuum and restarted the
db and the settings I have as follows. It seems the autovacuum process
has not been turned on.
thank you . I changed the value to 1M and I started seeing the autovacuum
being triggered. But I am getting the following message
ERROR: canceling autovacuum task, is it because the table are getting
updated and the vacuum process in place and vacuum happens at a later point
of time
Regards
Hello.
I've been looking at the PQntuples function, in section 29.3.2.
'Retrieving query result information' of
http://www.postgresql.org/docs/8.2/interactive/libpq-exec.html
The declaration is:
int PQntuples(const PGresult *res);
I'm wondering: why the 'int'? why not a 'long
Hello,
What is PostgreSQL's likely behaviour when it encounters a large
volume of data that is chronologically ordered (there's a btree index
on a date column)? Is postgreSQL intelligent enough to discern that
since the most frequently accessed data is invariably recent data,
that it should store
Second, and the more complicated one - what do I do about rogue queries
that are running when my process starts? Today we had a query that ran since
yesterday. I called pg_cancel_backend() on it several times and waited for
almost two hours - to no avail. Eventually I had to ask our sysadmin
John Moran johnfrederickmo...@gmail.com writes:
What is PostgreSQL's likely behaviour when it encounters a large
volume of data that is chronologically ordered (there's a btree index
on a date column)? Is postgreSQL intelligent enough to discern that
since the most frequently accessed data is
On Mar 3, 2010, at 10:05 AM, Guillaume Yziquel wrote:
Hello.
I've been looking at the PQntuples function, in section 29.3.2. 'Retrieving
query result information' of
http://www.postgresql.org/docs/8.2/interactive/libpq-exec.html
The declaration is:
int PQntuples(const
I have several tables in a SQL Anywhere 10 database that I'm converting to
PostgreSQL 8.4.2. In ASA we did not use any GUID columns but we are in
Postgres.
I have one table that is citystateinfo and the primary key is a UUID and it
is automatically generated using the contrib module oosp-uuid.
On 3/3/2010 3:40 PM, Michael Gould wrote:
On my machine the UUID that is returned is 16 bytes and I cannot make
out any relevant numbers from the UUID key in the citystateinfo
table. I've tried this in a Windows XP machine and a Windows 7 64 bit.
Now here is the weird thing. I did a
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
I'm asking this because I'm wondering whether you could have more tuples
in the result of a query than could be indexed by the 'int' type. May
this happen?
Yes, but it's a low concern as many other things would break before
you got to
Chris Barnes wrote:
What is the preferred setup for postgres concerning cache settings on
the controller and disks and the preferred block size allocated for
the disks when postgres uses block size 8192?
Controller settings
Read-cache mode - Enabled
Write-cache mode - Enabled (write-back)
John Moran wrote:
Is postgreSQL intelligent enough to discern that
since the most frequently accessed data is invariably recent data,
that it should store only that in memory, and efficiently store less
relevant, older data on disk
When you ask for a database block from disk, it increments a
What's the complete error message?
Vacuum is using maintenance_work_mem. What is your setting
maintenance_work_mem compared to your RAM size.
Igor Neyman
-Original Message-
From: akp geek [mailto:akpg...@gmail.com]
Sent: Wednesday, March 03, 2010 1:10 PM
To: Joao Ferreira gmail
Greg (or anyone out there) ... have you had any experience with using
check_postgres.pl --action=dbstats with Cacti? I'm getting, in my
cacti.log, a result of:
SPINE: Poller[0] Host[124] DS[2619] SCRIPT:
/usr/local/bin/check_postgres.pl --action=dbstats --host=192.0.2.23
--dbuser=monitor
Justin Graf jus...@magwerks.com wrote:
On 3/3/2010 3:40 PM, Michael Gould wrote:
On my machine the UUID that is returned is 16 bytes and I cannot make
out any relevant numbers from the UUID key in the citystateinfo
table. I've tried this in a Windows XP machine and a Windows 7 64 bit.
Now
Hello everyone,
I have inadvertently set off a furball on an unrelated ng on what is the
actual cost of SQL joins. But there's no reliable conclusion. I would
like to actually know that, that is, are JOINs truly expensive?
As they say, one measurement is worth a thousand opinions, so I've
One thing I've noticed is that on my machines, when I install the odbc
driver I get no error messages but when I look in the ODBC administrator I
do not see any entry for PostGres in the drivers list.
I do know that it somehow is working because the DMC conversion tool only
uses ODBC and builds
Found it ... spine (the new poller for cacti) doesnt' seem to report
errors very well ... switched to cmd.php and it turns out that, low and
behold, it can't find psql since /usr/local/bin isn't part of the path ...
fixed that in check_postgres.pl and all works well now ...
On Wed, 3 Mar
Greg Sabino Mullane a écrit :
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
I'm asking this because I'm wondering whether you could have more tuples
in the result of a query than could be indexed by the 'int' type. May
this happen?
Yes, but it's a low concern as many other things
On Wed, Mar 3, 2010 at 3:59 PM, Marcin Krol mrk...@gmail.com wrote:
Hello everyone,
I have inadvertently set off a furball on an unrelated ng on what is the
actual cost of SQL joins. But there's no reliable conclusion. I would like to
actually know that, that is, are JOINs truly expensive?
On 3/3/2010 5:16 PM, Michael Gould wrote:
One thing I've noticed is that on my machines, when I install the odbc
driver I get no error messages but when I look in the ODBC administrator I
do not see any entry for PostGres in the drivers list.
I do know that it somehow is working because the
Is there an SQL code generator for ArgoUML for Postgres? I have not been
able to find it, which does not mean it is not there.
Thanking you,
John
Is there anyone out there using Windows 7 64 bit with Postgres 8.4.2 and
the psqlodbc driver? I've tried to install using the one that is available
with the standard windows installer. I've also tried to install with the
one from EDB. It appears that the installer finishes the install but when
Hi
I am trying to install Postgres 8.2 on a virtual machine that has
Windown 2003 SP2. The installation fails with the error Failed to run
initdb: 128. I searched the archives and looks like it is a catch-all
error. But is there anything specific that we can do to resolve this
issue?
I am
Hi
I am trying to install Postgres 8.2 on a virtual machine that has
Windown 2003 SP2. The installation fails with the error Failed to run
initdb: 128. I searched the archives and looks like it is a catch-all
error. But is there anything specific that we can do to resolve this
issue?
I am
Michael Gould wrote:
Does anyone have any idea on what is going on here and how to fix the
problem? I've logged in as the domain adminstrator and the system
administrator for our domain and I've turned UAC off also, but as of yet
no luck.
I'm wondering if you should post to the pgsql-odbc
Argh - a follow-up re ODBC 32 and 64 bit-ness:
http://blog.danovich.com.au/2010/02/02/odbc-settings-on-64-bit-servers/
It's way crazier than you'd ever expect. Both the 32-bit and 64-bit
versions are called odbcad32.exe but are in different (but not
distinctively named) locations.
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
It's way crazier than you'd ever expect. Both the 32-bit and 64-bit
versions are called odbcad32.exe but are in different (but not
distinctively named) locations.
http://support.microsoft.com/kb/942976/en-us
Wow. If I even had the
Greg Sabino Mullane wrote:
Wow. If I even had the slightest regret about my move to abandon
Windows years ago, this would have pretty much squashed it.
Magnus et. al., I don't know how you do it. :)
yeah, (l)unix systems/users/apps never get ./lib and ./lib64 mixed up,
oh no.
--
On Wed, Mar 3, 2010 at 6:45 PM, Greg Sabino Mullane g...@turnstep.com wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
It's way crazier than you'd ever expect. Both the 32-bit and 64-bit
versions are called odbcad32.exe but are in different (but not
distinctively named) locations.
On Wed, Mar 3, 2010 at 6:54 PM, John R Pierce pie...@hogranch.com wrote:
Greg Sabino Mullane wrote:
Wow. If I even had the slightest regret about my move to abandon Windows
years ago, this would have pretty much squashed it. Magnus et. al., I don't
know how you do it. :)
yeah, (l)unix
Hi All,
I'm in the processing of migrating to Postgres 8.3.8.
I did :
(1) initdb
(2) psql -e template1 -f 21.bak /tmp/out21.bak
Now, I've decided that I want to reload the same file again, do I just delete
the PGDATA directory and then
perform the same steps again?
(1) initdb
(2) psql -e
Wang, Mary Y wrote:
Hi All,
I'm in the processing of migrating to Postgres 8.3.8.
I did :
(1) initdb
(2) psql -e template1 -f 21.bak /tmp/out21.bak
Now, I've decided that I want to reload the same file again, do I just delete
the PGDATA directory and then
perform the same steps again?
(1)
72 matches
Mail list logo
