Re: [GENERAL] High checkpoint_segments

[Albe Laurenz]

Jay Levitt wrote:
> We need to do a few bulk updates as Rails migrations.  We're a typical
> read-mostly web site, so at the moment, our checkpoint settings and
WAL are
> all default (3 segments, 5 min, 16MB), and updating a million rows
takes 10
> minutes due to all the checkpointing.
> 
> We have no replication or hot standbys.  As a consumer-web startup,
with no
> SLA, and not a huge database, and if we ever do have to recover from
> downtime it's ok if it takes longer.. is there a reason NOT to always
run
> with something like checkpoint_segments = 1000, as long as I leave the
> timeout at 5m?

There's nothing wrong with the idea except for the amount of WAL and a
huge checkpoint that can stall your system for a while in a worst-case
scenario.  You can't get rid of checkpoint I/O completely.

I'd tune to a more conservative value, maybe 30 or at most 100 and see
if that solves your problem.  Check statistics to see if checkpoints
are time-driven or not.  As soon as almost all checkpoints are time-
driven, further raising of checkpoint_segments won't do anything for
you.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backup database remotely

[Andre Lopes]

Hi all,

To do this backup remotely we need to open the 5434 port in the Firewall?

Best Regards,


On Mon, Feb 6, 2012 at 5:28 PM, Andreas Kretschmer
 wrote:
> Fanbin Meng  wrote:
>
>> I installed the PostgreSql9.0 in windows 7 with "one click installer".
>> How can i backup another PostgreSql server database remotely  via an internet
>> connection .
>> I trid "add a connection to a server", but it did not work.
>
> Don't know waht you mean with 'add a connection to a server'. You should
> use something like:
>
> pg_dump -h  (or pg_dumpall)
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect.                              (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."   (unknown)
> Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High checkpoint_segments

[Venkat Balaji]

On Wed, Feb 15, 2012 at 12:21 PM, Scott Marlowe wrote:

> On Tue, Feb 14, 2012 at 10:57 PM, Venkat Balaji 
> wrote:
> >
> > On Wed, Feb 15, 2012 at 1:35 AM, Jay Levitt 
> wrote:
> >>
> >> We need to do a few bulk updates as Rails migrations.  We're a typical
> >> read-mostly web site, so at the moment, our checkpoint settings and WAL
> are
> >> all default (3 segments, 5 min, 16MB), and updating a million rows
> takes 10
> >> minutes due to all the checkpointing.
> >>
> >> We have no replication or hot standbys.  As a consumer-web startup, with
> >> no SLA, and not a huge database, and if we ever do have to recover from
> >> downtime it's ok if it takes longer.. is there a reason NOT to always
> run
> >> with something like checkpoint_segments = 1000, as long as I leave the
> >> timeout at 5m?
> >
> >
> > Still checkpoints keep occurring every 5 mins. Anyways
> > checkpoint_segments=1000 is huge, this implies you are talking about
> > 16MB * 1000 = 16000MB worth pg_xlog data, which is not advisable from I/O
> > perspective and data loss perspective. Even in the most unimaginable
> case if
> > all of these 1000 files get filled up in less than 5 mins, there are
> chances
> > that system will slow down due to high IO and CPU.
>
>

> As far as I know there is no data loss issue with a lot of checkpoint
> segments.
>

Data loss would be an issue when there is a server crash or pg_xlog crash
etc. That many number of pg_xlog files (1000) would contribute to huge data
loss (data changes not synced to the base are not guaranteed). Of-course,
this is not related to the current situation.  Normally we calculate the
checkpoint completion time, IO pressure, CPU load and the threat to the
data loss when we configure checkpoint_segments.

Re: [GENERAL] Postgresql 9.0.6 backends pruning process environment?

[Magnus Hagander]

On Wed, Feb 15, 2012 at 06:40, dennis jenkins
 wrote:
> djenkins@ostara ~/code/capybara $ psql -U$someuser -dpostgres -c
> "select version();"
>                                                             version
> --
>  PostgreSQL 9.0.6 on x86_64-pc-linux-gnu, compiled by GCC
> x86_64-pc-linux-gnu-gcc (Gentoo 4.5.3-r1 p1.0, pie-0.4.5) 4.5.3,
> 64-bit
> (1 row)
>
> djenkins@ostara ~/code/capybara $ uname -a
> Linux ostara 3.1.6-gentoo #1 SMP PREEMPT Mon Jan 9 22:43:24 CST 2012
> x86_64 Intel(R) Core(TM) i5 CPU 760 @ 2.80GHz GenuineIntel GNU/Linux
>
>
>
> I recently updated my Gentoo Linux development system from postgresql
> 9.0.4 to 9.0.6-r1 (9.0.6 plus some Gentoo specific patches).  One of
> my 'C' language functions (been using it for years) stopped working
> because the backend no longer had access to the PGDATA environment
> variable.  A snippet of code is included below.
>
> My routine gathers some data about the file system that the "base"
> data directory resides on (ignoring table-spaces that could move data
> to other file systems).  The existing postgresql server admin
> functions are not sufficient to accomplish my goal:
>
> 1) "pg_database_size" does not give me all of the info that I'm after.
> 2) "pg_relation_filepath" only returns the path relative to PGDATA
> (eg, "base/n/m", not what I'm after
> ("/var/lib/postgresql/9.0", but may vary from system to system).
>
> Development on 8.4.4 through 9.0.4 worked fine.  getenv("PGDATA")
> returned a valid pathname in a shared object C function when ran by
> the back end.
>
> 9.0.6 (and 9.0.6-r1) backends appear to have no environment variables
> set in their backends.
>
> Gentoo's portage no longer has an ebuild for 9.0.4, so I reverted to
> 9.0.5.  My function resumed working again.
>
> I then tried Gentoo's portage for postgresql-9.0.6 (no -r1) and it
> failed the same (the env var is not available to the forked backend)
>
> For each postgresql version test, I recompiled and re-installed my
> function (a '.so' file).
>
> I skimmed the errata for Postgresql-9.0.6 and could not find anything
> relevant.  (http://www.postgresql.org/docs/9.0/static/release-9-0-6.html)
>
> I tried digging around in a mirrored source repository
> (https://github.com/postgres/postgres/tree/master/src), but didn't
> make much headway.
>
> Thank you for your time and thoughts.
>
>
> Questions:
>
> 1) Is the envvar present, and somehow my code or development system is faulty?
>
> 2) Were the envvars of the backends purposefully removed in version 9.0.6?

I don' trecall any such changes in PostgreSQL between those dates, and
if there were it should've been in the release notes. You can also
check the git source history for tha tbranch of course, but I doubt
you'll find anything.

I suggest you look at the version history of the gentoo packaging and
scripts instead. My guess is that something was changed there.


> 3) Is there a formal way to get the location of the "pg data dir" from
> a C language routine?

You can look at the configuration variable data_directory, or use the
C symbol DataDir which is exported from the backend.


> 4) It seems that the "cwd" (/prod/self/cwd sym link) would give me
> what I need, but can this behavior be relied on for future versions of
> Postgresql on Linux?

I have heard of no plans to change it, but I wouldn't rely on it myself.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Backup database remotely

[Andreas Kretschmer]

Andre Lopes  wrote:

> Hi all,
> 
> To do this backup remotely we need to open the 5434 port in the Firewall?

If the database running on this port and if there are a firewall, so
yes.



Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Easy form of "insert if it isn't already there"?

[Philip Couling]

This must be a function or trigger to break one statement into two.  You
could of course simply use two separate statements in PHP as long as
they are in the same transaction.  If you're going to perform this
action in two steps then putting both in a function or trigger is often
preferable.

Looking back at your original question.  Although your option 2 "feels
chunky", it feels to me a generally better option.

INSERT INTO table_name (col1,col2,col3) SELECT val1,val2,val3 WHERE NOT
EXISTS (SELECT * FROM table WHERE col1=val1 AND col2=val2)

or

INSERT INTO table (col1,col2,col3) SELECT val1,val2,val3 WHERE
(val1,val2,val3) NOT IN (SELECT col1,col2,col3 FROM table)

"It does what it says on the tin" and someone else maintaining your code
will understand what it does at a glance.  The same can not be said for
triggers and perhaps functions.  My gut feeling is that the performance
of this will be better too.

Regards,
Phil



On 15/02/2012 07:14, Bartosz Dmytrak wrote:
> Yes it is.
> You can implement trigger on table to check if inserted record is new.
> Still it is on DB side.
> I don't know PHP well enough but I think You can call function e.g.
> SELECT myschema."InsertWhenNew" ("val1", "val2", "val3"); in the same
> way as You call INSERTS
> 
> Regards,
> Bartek
> 
> 
> 2012/2/15 Chris Angelico mailto:ros...@gmail.com>>
> 
> On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrak  > wrote:
> > Hi,
> > similar topic is in NOVICE mailing
> > list: http://archives.postgresql.org/pgsql-novice/2012-02/msg00034.php
> >
> > e.g. You can use BEGIN... EXCEPTION END, good example of
> > such approach is
> >
> there: 
> http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE;
> 
> Ah, thanks for that!
> 
> Currently the query is a single PHP pg_query_params() call, and it's
> inside a larger transaction. By the look of it, this requires writing
> a function to do the job, rather than embedding the logic straight
> into the query - is this correct?
> 
> ChrisA
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 
> 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [GENERA]: Postgresql-9.1.1 synchronous replication issue

[Venkat Balaji]

In-short, I would like to understand if i am achieving the same
asynchronous streaming replication by putting synchronous_commit='local' -

I understand that streaming replication is record based log-shipping.

Below is what shows up on our primary test server where we are testing
synchronous replication -

*1. Synchronous setup enabled with synchronous_commit='local'*

postgres=# select * from pg_stat_replication ;

procpid | usesysid | usename  | application_name | client_addr |
client_hostname | client_port |backend_start |   state   |
sent_location | write_locat
ion | flush_location | replay_location | sync_priority | sync_state
-+--+--+--+-+-+-+--+---+---+
++-+---+
   24099 |   10 | postgres | walreceiver  ||
  |   56432 | 2012-02-15 12:55:39.65663+03 | streaming |
0/E78 | 0/E78
| 0/E78  | 0/E78   | 1 | *sync*
(1 row)

postgres=# show synchronous_commit ;

synchronous_commit

* local*
(1 row)

postgres=# show synchronous_standby_names ;

synchronous_standby_names
-
 *
(1 row)

Does this mean that the system is still replicating synchronously ? If yes,
by what means ?

*Below is our actual production setup in 9.1.1 with asynchronous
replication setup -*

*2. Asynchronous enabled with synchronous_commit='on'*

psql (9.1.1)
Type "help" for help.

postgres=# select * from pg_stat_replication;

procpid | usesysid | usename  | application_name | client_addr |
client_hostname| client_port | backend_start |   state
  | sent_location | write
_location | flush_location | replay_location | sync_priority | sync_state
-+--+--+--+-+--+-+---+---+---+--
--++-+---+
3159 |   10 | postgres | walreceiver  |  |
 |   40165 | 2012-02-08 12:41:51.858897+03 | streaming |
1/86F83B50| 1/86F
83B50 | 1/86F83B50 | 1/86F83B50  | 0 | *async*

(1 row)

postgres=# show synchronous_commit ;
 synchronous_commit

 on
(1 row)

postgres=# show synchronous_standby_names ;

synchronous_standby_names
---

(1 row)

Operation wise, I am not seeing much difference by inserting few 1000 rows.
Its almost the same behavior both in asynch and sync rep.

Thanks,
VB


On Wed, Feb 15, 2012 at 11:11 AM, Venkat Balaji wrote:


> On Wed, Feb 15, 2012 at 11:01 AM, Venkat Balaji wrote:
>
>>
>> On Tue, Feb 14, 2012 at 8:09 PM, Adrian Klaver 
>> wrote:
>>
>>> On Tuesday, February 14, 2012 4:21:22 am Venkat Balaji wrote:
>>> > Hello,
>>> >
>>> > Disaster Recovery testing for Synchronous replication setup -
>>> >
>>> > When the standby site is down, transactions at the production site
>>> started
>>> > hanging (this is after the successful setup of synchronous
>>> replication).
>>> >
>>> > We changed synchronous_commit to 'local' to over-come this situation.
>>> >
>>> >  - No transactions are hanging at the production site even when the
>>> standby
>>> > is down
>>> >  - Standby is automatically getting synced when it is back up again.
>>> >
>>> > Can someone let us know if there are any "-ve" effects of putting
>>> > synchronous_commit='local' ??
>>> >
>>> > I am assuming that this as good as putting "synchronous_commit=on" on
>>> an
>>> > stand-alone system.
>>>
>>> It would seem you are really after streaming replication(which is
>>> asynchronous)
>>> more than synchronous replication. I have not used synchronous
>>> replication
>>> enough to be  sure, but I think by setting synchronous_commit='local'
>>> you are
>>> basically turning the system into a straight streaming(asynchronous)
>>> system
>>> anyway.
>>>
>>
>> Sorry. Ignore my earlier message -
>
> Yeah. Its a kind of asynchronous ( at the transaction level, NOT WAL based
> ). All i wanted to achieve is as follows -
>
> 1. Synchronous replication - which would perform transactions
> simultaneously on production and standby.
> 2. Ideally, if the commit does not occur at the standby site, then it
> would not commit at the production as well, which will cause production
> site
> to hang. I do not want production site to hang if the standby site is
> down or not accessible.
> 3. I would need the commit to occur on production and the production apps
> should not be disturbed if the standby fails to respond. To achieve this,
> I have set synchronous_commit='local' to ensure that transactions are
> committed at production site first.
>
> We do have streaming replication (of PG-9.0) setup on our other production
> boxes, which is asynchronous and is WAL base

Re: [GENERAL] High checkpoint_segments

[Tomas Vondra]

On 15 Únor 2012, 10:38, Venkat Balaji wrote:
>
> Data loss would be an issue when there is a server crash or pg_xlog crash
> etc. That many number of pg_xlog files (1000) would contribute to huge
> data
> loss (data changes not synced to the base are not guaranteed). Of-course,
> this is not related to the current situation.  Normally we calculate the
> checkpoint completion time, IO pressure, CPU load and the threat to the
> data loss when we configure checkpoint_segments.

So you're saying that by using small number of checkpoint segments you
limit the data loss when the WAL gets corrupted/lost? That's a bit like
buying a Maseratti and then not going faster than 10mph because you might
crash at higher speeds ...

The problem here is that the WAL is usually placed on more reliable drives
(compared to the data files) or a RAID1 array and as it's just writing
data sequentially, so the usage pattern is much less likely to cause
data/drive corruption (compared to data files that need to handle a lot of
random I/O, etc.).

So while it possible the WAL might get corrupted, the probability of data
file corruption is much higher. And the corruption might easily happen
silently during a checkpoint, so there won't be any WAL segments no matter
how many of them you keep ...

And by using low number of checkpoint segments it actually gets worse,
because it means more frequent checkpoints -> more I/O on the drives ->
more wearout of the drives etc.

If you need to protect yourself against this, you need to keep a WAL
archive (prefferably on a separate machine) and/or a hot standby for
failover.

kind regards
Tomas


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High checkpoint_segments

[Andres Freund]

On Wednesday, February 15, 2012 10:38:23 AM Venkat Balaji wrote:
> On Wed, Feb 15, 2012 at 12:21 PM, Scott Marlowe 
wrote:
> > On Tue, Feb 14, 2012 at 10:57 PM, Venkat Balaji 
> > > all of these 1000 files get filled up in less than 5 mins, there are
> > > chances that system will slow down due to high IO and CPU.
> > As far as I know there is no data loss issue with a lot of checkpoint
> > segments.
> Data loss would be an issue when there is a server crash or pg_xlog crash
> etc. That many number of pg_xlog files (1000) would contribute to huge data
> loss (data changes not synced to the base are not guaranteed). Of-course,
> this is not related to the current situation.  Normally we calculate the
> checkpoint completion time, IO pressure, CPU load and the threat to the
> data loss when we configure checkpoint_segments.
I think you might be misunderstanding something. A high number of 
checkpoint_segments can lead to slower recovery - all those changes need to be 
reapplied - but it won't lead to lost data. The data inside the wal will be 
fsynced at appropriate times (commit; background writer; too much written).


Andres

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] High checkpoint_segments

[Venkat Balaji]

> > Data loss would be an issue when there is a server crash or pg_xlog crash
> > etc. That many number of pg_xlog files (1000) would contribute to huge
> > data
> > loss (data changes not synced to the base are not guaranteed). Of-course,
> > this is not related to the current situation.  Normally we calculate the
> > checkpoint completion time, IO pressure, CPU load and the threat to the
> > data loss when we configure checkpoint_segments.
>
> So you're saying that by using small number of checkpoint segments you
> limit the data loss when the WAL gets corrupted/lost? That's a bit like
> buying a Maseratti and then not going faster than 10mph because you might
> crash at higher speeds ...
>

No. I am not saying that checkpoint_segments must be lower. I was just
trying to explain the IO over-head on putting high (as high as 1000)
checkpoint segments.  Lower number of checkpoint segments will lead to more
frequent IOs which is not good. Agreed.

>
> The problem here is that the WAL is usually placed on more reliable drives
> (compared to the data files) or a RAID1 array and as it's just writing
> data sequentially, so the usage pattern is much less likely to cause
> data/drive corruption (compared to data files that need to handle a lot of
> random I/O, etc.).
>

Agreed.


> So while it possible the WAL might get corrupted, the probability of data
> file corruption is much higher. And the corruption might easily happen
> silently during a checkpoint, so there won't be any WAL segments no matter
> how many of them you keep ...
>

Agreed. When corruption occurs, it really does not matter how many WAL
segments are kept in pg_xlog.
But, at any point of time if PG needs


> And by using low number of checkpoint segments it actually gets worse,
> because it means more frequent checkpoints -> more I/O on the drives ->
> more wearout of the drives etc.
>

Completely agreed. As mentioned above. I choose checkpoint_segments and
checkpoint_timeout once i observe the checkpoint behavior.

If you need to protect yourself against this, you need to keep a WAL
> archive (prefferably on a separate machine) and/or a hot standby for
> failover.
>

WAL archiving is a different situation where-in you need to backup the
pg_xlog files by enabling archiving.
I was referring to an exclusive situation, where-in pg_xlogs are not
archived and data is not yet been synced to base files (by bgwriter) and
the system crashed, then PG would depend on pg_xlog to recover and reach
the consistent state, if the pg_xlog is also not available, then there
would be a data loss and this depends on how much data is present in
pg_xlog files.

Thanks,
VB

Re: [GENERAL] High checkpoint_segments

[Venkat Balaji]

On Wed, Feb 15, 2012 at 4:12 PM, Andres Freund  wrote:

> On Wednesday, February 15, 2012 10:38:23 AM Venkat Balaji wrote:
> > On Wed, Feb 15, 2012 at 12:21 PM, Scott Marlowe
> wrote:
> > > On Tue, Feb 14, 2012 at 10:57 PM, Venkat Balaji <
> venkat.bal...@verse.in>
> > > > all of these 1000 files get filled up in less than 5 mins, there are
> > > > chances that system will slow down due to high IO and CPU.
> > > As far as I know there is no data loss issue with a lot of checkpoint
> > > segments.
> > Data loss would be an issue when there is a server crash or pg_xlog crash
> > etc. That many number of pg_xlog files (1000) would contribute to huge
> data
> > loss (data changes not synced to the base are not guaranteed). Of-course,
> > this is not related to the current situation.  Normally we calculate the
> > checkpoint completion time, IO pressure, CPU load and the threat to the
> > data loss when we configure checkpoint_segments.
> I think you might be misunderstanding something. A high number of
> checkpoint_segments can lead to slower recovery - all those changes need
> to be
> reapplied - but it won't lead to lost data. The data inside the wal will be
> fsynced at appropriate times (commit; background writer; too much written).
>

Recovery would take time because all the changes in WAL files of pg_xlog
(which is high) must be replayed to reach consistent state. When disaster
strikes and if pg_xlogs are not available and data in WAL is not fsynced
yet, then recovery is not possible and data loss will be huge. It also
depends on how much data is not fsynced.

Thanks,
VB

Re: [GENERAL] Large object rights management

[Giuseppe Sacco]

Is there anyone interested on this subject?

Il giorno dom, 05/02/2012 alle 23.30 +0100, Giuseppe Sacco ha scritto:
> Hi all,
> I wrote an application that store a large quantity of files in the
> database as large binary objects. There are around 50 tables (all in one
> schema) and only one table host all these large objects. Every user
> connect to database using his own user, so all users are parts of the
> same group role for granting permissione to every application user.
> 
> My problem is about large object permissions introduced in postgresql
> 9.0, since my application permits to everyone to insert/update/delete
> any large object. Now, since 9.0, deleting a large object is only
> possible for the user that owns it.
> 
> I know that 9.0 also introduced an option for reverting this behaviour
> as it was in 8.4, but I wonder if there is any other way of sharing and
> deleting large objects in 9.0.
> 
> Thanks,
> Giuseppe



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Rules of Thumb for Autovaccum

[Robert James]

What rules of thumb exist for:
* How often a table needs to be vacuumed?
* How often a table needs to be analyzed?
* How to tune Autovacuum?

I have a large DB server, and I'm concerned that it's not being
autovaccumed and autoanalyzed frequently enough.  But I have no idea
what proper values should be?

A related question: If I INSERT a large number of records per day,
similar in nature to the existing records, does that require new
vacuum? new analyze? Or do I only need those for DELETEs or changes to
the nature of records?

Finally: What type of performance impact can I expect from vacuum and
analyze, in general?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Index for low selectivity field

[Robert James]

A table has a column "obj_type" which has very low selectivity (let's
say 5 choices, with the top choice making up 50% of records).  Is
there any sense in indexing that column? B-trees won't be that useful,
and the docs discourage other index types/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Memory tuning Postgres

[Robert James]

I have a 4 core, 4 GB server dedicated to running Postgres (only other
thing on it are monitoring, backup, and maintenance programs).  It
runs about 5 databases, backing up an app, mainly ORM queries, but
some reporting and more complicated SQL JOINs as well.

I'm currently using the out-of-the box postgresql.conf, but I get the
sense that isn't using all the resources it should.

What parameters should I change to use the server best? What are good
starting points or them? What type of performance increase should I
see?

>From basic research, it seems shared_buffers should be 1G, and
effective_cache_size 3G.  Is that correct?  Can you suggest what other
params I should change, and to what?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Rules of Thumb for Autovaccum

[Bruce Momjian]

On Wed, Feb 15, 2012 at 09:14:34AM -0500, Robert James wrote:
> What rules of thumb exist for:
> * How often a table needs to be vacuumed?
> * How often a table needs to be analyzed?
> * How to tune Autovacuum?
> 
> I have a large DB server, and I'm concerned that it's not being
> autovaccumed and autoanalyzed frequently enough.  But I have no idea
> what proper values should be?
> 
> A related question: If I INSERT a large number of records per day,
> similar in nature to the existing records, does that require new
> vacuum? new analyze? Or do I only need those for DELETEs or changes to
> the nature of records?
> 
> Finally: What type of performance impact can I expect from vacuum and
> analyze, in general?

Unless you are very high-volume, the auto-vacuum default settings are
fine.  The default do allow up to 20% of unused space in tables, but
making that lower is expensive to performance.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Memory tuning Postgres

[Tomas Vondra]

On 15 Únor 2012, 15:20, Robert James wrote:
> I have a 4 core, 4 GB server dedicated to running Postgres (only other
> thing on it are monitoring, backup, and maintenance programs).  It
> runs about 5 databases, backing up an app, mainly ORM queries, but
> some reporting and more complicated SQL JOINs as well.
>
> I'm currently using the out-of-the box postgresql.conf, but I get the
> sense that isn't using all the resources it should.
>
> What parameters should I change to use the server best? What are good
> starting points or them? What type of performance increase should I
> see?
>
> From basic research, it seems shared_buffers should be 1G, and
> effective_cache_size 3G.  Is that correct?  Can you suggest what other
> params I should change, and to what?

Actually there's a nice wiki page about tuning:

  http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Yes, 1GB (25%) of RAM is generally the recommended value. But you haven't
mentioned which version of PostgreSQL is used and what is the size of the
database. Older version / small database would work fine even with smaller
shared buffers.

The other parameters that may be interesting are probably
checkpoint_segments, checkpoint_completion_target, work_mem,
maintenance_work_mem (read the wiki, it's described there).

You should also tweak the log_line_prefix (use something that works with
pgfounie), log_checkpoints/log_temp_files and maybe set reasonable value
to log_statement_min_duration.

T.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgresql 9.0.6 backends pruning process environment?

[Tom Lane]

Magnus Hagander  writes:
> On Wed, Feb 15, 2012 at 06:40, dennis jenkins
>  wrote:
>> I recently updated my Gentoo Linux development system from postgresql
>> 9.0.4 to 9.0.6-r1 (9.0.6 plus some Gentoo specific patches).  One of
>> my 'C' language functions (been using it for years) stopped working
>> because the backend no longer had access to the PGDATA environment
>> variable.  A snippet of code is included below.

> I suggest you look at the version history of the gentoo packaging and
> scripts instead. My guess is that something was changed there.

Yes.  A PG backend will not remove a "PGDATA" envar, but *it does not
set it either*.  This sounds to me like a change in the startup script.

> You can look at the configuration variable data_directory, or use the
> C symbol DataDir which is exported from the backend.

Quite --- at the C level, looking at DataDir is the right thing, and
looking at PGDATA could be misleading even if it exists --- consider
the possibility that we took the data_directory setting from the
command line or postgresql.conf.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [GENERA]: Postgresql-9.1.1 synchronous replication issue

[Adrian Klaver]

On Wednesday, February 15, 2012 2:15:34 am Venkat Balaji wrote:
> In-short, I would like to understand if i am achieving the same
> asynchronous streaming replication by putting synchronous_commit='local' -
> 
> I understand that streaming replication is record based log-shipping.
> 
> Below is what shows up on our primary test server where we are testing
> synchronous replication -
> 
> *1. Synchronous setup enabled with synchronous_commit='local'*
> 
> postgres=# select * from pg_stat_replication ;
> 
> procpid | usesysid | usename  | application_name | client_addr |
> client_hostname | client_port |backend_start |   state   |
> sent_location | write_locat
> ion | flush_location | replay_location | sync_priority | sync_state
> -+--+--+--+-+--
> ---+-+--+---+--
> -+
> ++-+---+ 24099
> |   10 | postgres | walreceiver  ||
> 
>   |   56432 | 2012-02-15 12:55:39.65663+03 | streaming |
> 
> 0/E78 | 0/E78
> 
> | 0/E78  | 0/E78   | 1 | *sync*
> 
> (1 row)
> 
> postgres=# show synchronous_commit ;
> 
> synchronous_commit
> 
> * local*
> (1 row)
> 
> postgres=# show synchronous_standby_names ;
> 
> synchronous_standby_names
> -
>  *
> (1 row)
> 
> Does this mean that the system is still replicating synchronously ? If yes,
> by what means ?
> 
> *Below is our actual production setup in 9.1.1 with asynchronous
> replication setup -*
> 
> *2. Asynchronous enabled with synchronous_commit='on'*
> 
> psql (9.1.1)
> Type "help" for help.
> 
> postgres=# select * from pg_stat_replication;
> 
> procpid | usesysid | usename  | application_name | client_addr |
> client_hostname| client_port | backend_start |   state
> 
>   | sent_location | write
> 
> _location | flush_location | replay_location | sync_priority | sync_state
> -+--+--+--+-+--
> +-+---+---+
> ---+--
> --++-+---+
> 3159 |   10 | postgres | walreceiver  |  |
>  |   40165 | 2012-02-08 12:41:51.858897+03 | streaming |
> 1/86F83B50| 1/86F
> 83B50 | 1/86F83B50 | 1/86F83B50  | 0 | *async*
> 
> (1 row)
> 
> postgres=# show synchronous_commit ;
>  synchronous_commit
> 
>  on
> (1 row)
> 
> postgres=# show synchronous_standby_names ;
> 
> synchronous_standby_names
> ---
> 
> (1 row)
> 
> Operation wise, I am not seeing much difference by inserting few 1000 rows.
> Its almost the same behavior both in asynch and sync rep.

First sync replication is just an advanced form of streaming replication.

http://www.postgresql.org/docs/9.1/interactive/warm-standby.html#STREAMING-
REPLICATION
"
Streaming replication allows a standby server to stay more up-to-date than is 
possible with file-based log shipping. The standby connects to the primary, 
which 
streams WAL records to the standby as they're generated, without waiting for 
the 
WAL file to be filled.
"

In both cases WAL information is being used. Though just one record at a time, 
so the entire WAL file does not have to be shipped over. In the case of sync 
replication a commit on the master is not complete until it also completes on 
the standby. This is for the default case where synchronous_commit=on and 
synchronous_standby_names has valid names. In your case you are using 
synchronous_commit=local  and per:

http://www.postgresql.org/docs/9.1/interactive/runtime-config-wal.html#RUNTIME-
CONFIG-WAL-SETTINGS

synchronous_commit 


"However, the special value local is available for transactions that wish to 
wait for local flush to disk, but not synchronous replication."


So in this case you are not waiting for confirmation of the commit being 
flushed 
to disk on the standby.  It that case you are bypassing the primary reason for 
sync replication. The plus is transactions on the master will complete faster 
and do so in the absence of the standby. The minus is that you are in sort of 
an 
in between state. 

Personally, I take sync replication to be basically an all or nothing 
proposition. By setting it up you are saying you want, at minimum, two database 
clusters to be in sync at any point in time all the time (except for start up). 
If that is not possible then you are really looking for async replication. 

> 
> Thanks,
> VB


-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Index for low selectivity field

[Andy Colson]

On 2/15/2012 8:16 AM, Robert James wrote:

A table has a column "obj_type" which has very low selectivity (let's
say 5 choices, with the top choice making up 50% of records).  Is
there any sense in indexing that column? B-trees won't be that useful,
and the docs discourage other index types/



It, of course, depends on your usage.

Lets say you have lots and lots of records.  And lets say you don't have 
another field that can be used as a better index.  And, lets say you are 
interested in obj_type's not in the 50%.  Then an index on obj_type 
would be useful.


select * from table where obj_type = '10%_type'

would use the index to cut the table down to 10% and then do a table 
scan on just that.



On the other hand, lets say you have a field that has better 
selectivity.  PG will ignore an index on obj_type because it can scan a 
much smaller set by using the more selective index.


On the other hand, lets say you have one or two obj_type's you are 
interested in, with a low % of records.  Its possible to create a 
functional index where obj_type in('a', 'b').  Then when you:


select ... where obj_type = 'a'

the index can be used, and it'll be more selective, and it'll be smaller.


On the other hand, lets say you dont have very many records.. and most 
of them fit into ram.  In that case an index wont really be useful 
because PG can table scan very very fast.


-Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Rules of Thumb for Autovaccum

[Robert James]

Thanks.  What about auto-analyze? When will they be analyzed by default?
And what actions generally require new analyze?

On 2/15/12, Bruce Momjian  wrote:
> On Wed, Feb 15, 2012 at 09:14:34AM -0500, Robert James wrote:
>> What rules of thumb exist for:
>> * How often a table needs to be vacuumed?
>> * How often a table needs to be analyzed?
>> * How to tune Autovacuum?
>>
>> I have a large DB server, and I'm concerned that it's not being
>> autovaccumed and autoanalyzed frequently enough.  But I have no idea
>> what proper values should be?
>>
>> A related question: If I INSERT a large number of records per day,
>> similar in nature to the existing records, does that require new
>> vacuum? new analyze? Or do I only need those for DELETEs or changes to
>> the nature of records?
>>
>> Finally: What type of performance impact can I expect from vacuum and
>> analyze, in general?
>
> Unless you are very high-volume, the auto-vacuum default settings are
> fine.  The default do allow up to 20% of unused space in tables, but
> making that lower is expensive to performance.
>
> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
>   + It's impossible for everything to be true. +
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] postgresql-9.0

[khizer]

Hi,
  In postgresql 9.0.4  i connected to a database and trying to make 
queries but
  i am facing memory issue, getting err as *glibc* detected 
*realloc* invalid next size

  so kindly requesting u to provide your valuable feed backs

Regards
Mehdi

[GENERAL] Dump functions alone

[Rajan, Pavithra]

Hello all-  Is there a way to just dump functions in a schema in to a
txt file/ sql file ?  Thank you.

Re: [GENERAL] Easy form of "insert if it isn't already there"?

[Berend Tober]

Chris Angelico wrote:

On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrak  wrote:


e.g. You can use BEGIN... EXCEPTION END, good example of
such approach is
there: 
http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE;




I wonder why, in that example, you would not try the INSERT 
first, and if that fails, then do the update?




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Memory tuning Postgres

[Robert James]

On 2/15/12, Tomas Vondra  wrote:
> On 15 Únor 2012, 15:20, Robert James wrote:
>> What parameters should I change to use the server best? What are good
>> starting points or them? What type of performance increase should I
>> see?

...
> But you haven't
> mentioned which version of PostgreSQL is used and what is the size of the
> database.


Pg 8.3, Linux.  Each individual database when dumped to a .sql file
takes between 1 - 3 GB.

> The other parameters that may be interesting are probably
> checkpoint_segments, checkpoint_completion_target, work_mem,
> maintenance_work_mem (read the wiki, it's described there).
>
> You should also tweak the log_line_prefix (use something that works with
> pgfounie), log_checkpoints/log_temp_files and maybe set reasonable value
> to log_statement_min_duration.


Thanks.  Can you give me starting points for those for a 4 core 4 GB
Ram server with about 10 GB of data?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dump functions alone

[Raghavendra]

You have two options.

   - Use contrib module pg_extractor
   https://github.com/omniti-labs/pg_extractor
   - Use pg_proc catalog to get function definition


---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/



On Wed, Feb 15, 2012 at 6:59 PM, Rajan, Pavithra  wrote:

> Hello all-  Is there a way to just dump functions in a schema in to a txt
> file/ sql file ?  Thank you.
>

[GENERAL] Reassigned Owned Error- unexpected classid 2328

[pcasper]

Hi,

During execute follow statement to reassign owner of objects in the database
I have an error: 
unexpected classid 2328

Statement:
REASSIGN OWNED BY olduser TO postgres;

Regards,

Pawel

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Reassigned-Owned-Error-unexpected-classid-2328-tp5486492p5486492.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dump functions alone

[Raghavendra]

One more thing you can also get it from pg_get_functiondef() system
function.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/



On Wed, Feb 15, 2012 at 9:32 PM, Raghavendra <
raghavendra@enterprisedb.com> wrote:

> You have two options.
>
>- Use contrib module pg_extractor
>https://github.com/omniti-labs/pg_extractor
>- Use pg_proc catalog to get function definition
>
>
> ---
> Regards,
> Raghavendra
> EnterpriseDB Corporation
> Blog: http://raghavt.blogspot.com/
>
>
>
> On Wed, Feb 15, 2012 at 6:59 PM, Rajan, Pavithra  wrote:
>
>> Hello all-  Is there a way to just dump functions in a schema in to a txt
>> file/ sql file ?  Thank you.
>>
>
>

[GENERAL] Drop big index

[Vojtěch Rylko]

Hi,

I need to drop some b-tree indexes because they are not used anymore. 
Size of indexes vary between 700 MB and 7 GB. I tried common DROP 
INDEX... but this query performed so long and blocked table so I had to 
interrupt it. Is there any way how to drop large indexes in non-blocking 
or /faster/ way?


Regards,
Vojta R.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] DDL Triggers

[Bob Pawley]

Could someone point me to documentation regarding DDL triggers in Postgresql?

Bob

Re: [GENERAL] Dump functions alone

[Rajan, Pavithra]

Thank you. The PGExtractor is interesting!  I was trying to get all the
function declaration and definition ( about 400+) by this method 

 

pg_dump -Fc -v -s schemaname -f temp.dump yourdatabase

pg_restore -l temp.dump | grep FUNCTION >functionlist

pg_restore -L functionlist temp.dump >yourfunctions.sql

 

mentioned in
http://archives.postgresql.org/pgsql-general/2005-10/msg01633.php

 

I'll try this and your solutions as well

 

 

 

 

From: Raghavendra [mailto:raghavendra@enterprisedb.com] 
Sent: Wednesday, February 15, 2012 11:05 AM
To: Rajan, Pavithra
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Dump functions alone

 

One more thing you can also get it from pg_get_functiondef() system
function.


 

---

Regards,

Raghavendra

EnterpriseDB Corporation

Blog: http://raghavt.blogspot.com/





On Wed, Feb 15, 2012 at 9:32 PM, Raghavendra
 wrote:

You have two options.

*   Use contrib module pg_extractor
https://github.com/omniti-labs/pg_extractor 
*   Use pg_proc catalog to get function definition

 

---

Regards,

Raghavendra

EnterpriseDB Corporation

Blog: http://raghavt.blogspot.com/





On Wed, Feb 15, 2012 at 6:59 PM, Rajan, Pavithra  wrote:

Hello all-  Is there a way to just dump functions in a schema in to a
txt file/ sql file ?  Thank you.

 

 

Re: [GENERAL] DDL Triggers

[Steve Crawford]

On 02/15/2012 08:25 AM, Bob Pawley wrote:
Could someone point me to documentation regarding DDL triggers in 
Postgresql?

Bob


No, because PostgreSQL does not have them (basically triggers on system 
tables). There is a sparse wiki page to discuss the issue at 
http://wiki.postgresql.org/wiki/DDL_Triggers and you can find plenty of 
discussions of the issue in the mailing list archives.


Cheers,
Steve


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] DDL Triggers

[Adrian Klaver]

On 02/15/2012 08:25 AM, Bob Pawley wrote:

Could someone point me to documentation regarding DDL triggers in
Postgresql?


You are going to need to be more specific. Are you talking triggers that 
are activated by a DDL statement or a trigger that creates a DDL 
statement? Also what pl language(s) are you using?



Bob



--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Table growing faster than autovacuum can vacuum

[Asher Hoskins]

Hello.

I've got a database with a very large table (currently holding 23.5 
billion rows, the output of various data loggers over the course of my 
PhD so far). The table itself has a trivial structure (see below) and is 
partitioned by data time/date and has quite acceptable INSERT/SELECT 
performance.


  CREATE TABLE rawdata (
value REAL NOT NULL,
sample_time TIMESTAMP WITH TIME ZONE NOT NULL,
-- Dataset reference.
dataset INTEGER NOT NULL
  );

The data loggers are collected every month or so and uploaded into the 
database, resulting in another 1-2 billion rows in the table each time. 
Data is never deleted from the table and so it's essentially read-only.


My problem is that the autovacuum system isn't keeping up with INSERTs 
and I keep running out of transaction IDs. SELECT performance also drops 
off the more I insert, which from looking at the output of iostat seems 
to be because the autovacuum tasks are taking up a lot of the disk 
bandwidth - the disks are commodity items in a software RAID and not 
terribly fast.


My current workaround is to stop the server after a batch of inserts and 
then manually VACUUM FULL all the databases, letting this run over a 
weekend.


I'm a complete newby when it comes to PostgreSQL system settings and it 
isn't obvious to me what I'd need to change to improve the autovacuum. 
Is there any way to manually freeze the rows of the table after they've 
been inserted so that the autovacuum doesn't need to touch the table? 
The rest of the database contains metadata about the main data, about 
250 million rows at the moment, which I guess is a small enough amount 
that I can let PostgreSQL handle it automatically.


The server is running PostgreSQL 8.4.2 (under FreeBSD) at the moment but 
it wouldn't be a problem to upgrade to 9.1 if that was helpful.



Asher.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to make DBT-3 workload work?

[Yangyang]

Hi,all
Is there someone using DBT-3 workload? I can make each of the 22
queries work by input independently. When I run run_workload.sh, the
workload is consuming all the CPU, but eventually it doesn't give any
output files. As I see from the online tutorial, I need the output
files to generate the throughput and power analysis.
I have modified environment config files and other workload related
files. But the output is still missing.
Anyone can give me some help on resolving this issue?
Thanks very much.
-Yangyang

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table growing faster than autovacuum can vacuum

[Marti Raudsepp]

On Wed, Feb 15, 2012 at 18:46, Asher Hoskins  wrote:
> My problem is that the autovacuum system isn't keeping up with INSERTs and I
> keep running out of transaction IDs.

This is usually not a problem with vacuum, but a problem with
consuming too many transaction IDs. I suspect you're loading that data
with individual INSERT statements with no explicit transaction
management -- which means every data-modifying query gets its own
transaction ID.

In most applications, the simplest solution is batching up lots of
INSERTs (say, 10k) and run them all in a single transaction between
BEGIN and COMMIT -- thus consuming just 1 transaction ID for 10k rows.
You could also look at multi-row inserts or the COPY command to get
better performance. Here's an overview of different data loading
approaches: 
http://www.depesz.com/2007/07/05/how-to-insert-data-to-database-as-fast-as-possible/

But it's also possible that transaction IDs are consumed by other
queries. Any PL/pgSQL procedure that makes use of exception handling
consumes a transaction ID. So does every SAVEPOINT command.

> I'm a complete newby when it comes to PostgreSQL system settings and it
> isn't obvious to me what I'd need to change to improve the autovacuum. Is
> there any way to manually freeze the rows of the table after they've been
> inserted so that the autovacuum doesn't need to touch the table?

VACUUM FREEZE is the command. :)

You may need to tune the vacuum settings to be more aggressive if you
want it to run faster (reducing cost_delay, increasing cost_limit).
But I don't have much experience with these myself.
http://www.postgresql.org/docs/8.4/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST

Note that vacuum freeze is only necessary when you have a shortage of
transaction IDs. Normally Postgres doesn't vacuum insert-only tables
at all (it just analyzes them).

VACUUM FULL is extremely inefficient in PostgreSQL 8.4 and older.

> The server is running PostgreSQL 8.4.2 (under FreeBSD) at the moment but it
> wouldn't be a problem to upgrade to 9.1 if that was helpful.

Upgrading to 9.1 certainly lets you take advantage of several
performance improvements, but I don't know how useful those are in
your workload.



Additionally, it's often a good idea to partition large tables into
smaller partitions (e.g. separate partition for each day/week worth of
data). This way maintenance commands (VACUUM, ANALYZE) don't have to
scan the whole huge table, but work with smaller individual tables,
and you can drop chunks using a simple DROP TABLE.

BUT it also has its problems: some types of query optimizations are
impossible with partitioned tables (version 9.1 relaxed those
limitations somewhat). So if you find your current performance to be
sufficient, then this might not be worth doing.

Regards,
Marti

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table growing faster than autovacuum can vacuum

[Marti Raudsepp]

On Wed, Feb 15, 2012 at 19:25, Marti Raudsepp  wrote:
> VACUUM FULL is extremely inefficient in PostgreSQL 8.4 and older.

Oh, a word of warning, PostgreSQL 9.0+ has a faster VACUUM FULL
implementation, but it now requires twice the disk space of your table
size, during the vacuum process.

Regards,
Marti

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] DDL Triggers

[Bob Pawley]

I have a table that is generated through ogr2ogr.

To get ogr2ogr working the way I want, I need to use the -overwrite 
function. If I use the append function information is lost. Something to do 
with the way the switches work.


Overwrite drops the existing table and also the attached trigger .

I am attempting to have the dropped table trigger a function as it is being 
populated by insert commands.


DDL trigger was mentioned as a possibility on the GDAL list.

Any suggestions will be welcome.

Bob


-Original Message- 
From: Adrian Klaver

Sent: Wednesday, February 15, 2012 8:36 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] DDL Triggers

On 02/15/2012 08:25 AM, Bob Pawley wrote:

Could someone point me to documentation regarding DDL triggers in
Postgresql?


You are going to need to be more specific. Are you talking triggers that
are activated by a DDL statement or a trigger that creates a DDL
statement? Also what pl language(s) are you using?


Bob



--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general 



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] DDL Triggers

[Adrian Klaver]

On 02/15/2012 09:42 AM, Bob Pawley wrote:

I have a table that is generated through ogr2ogr.

To get ogr2ogr working the way I want, I need to use the -overwrite
function. If I use the append function information is lost. Something to
do with the way the switches work.

Overwrite drops the existing table and also the attached trigger .

I am attempting to have the dropped table trigger a function as it is
being populated by insert commands.


How could you even hope to accomplish the above? If the table is dropped 
there would be nothing to insert into.




DDL trigger was mentioned as a possibility on the GDAL list.

Any suggestions will be welcome.

Bob




--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Reassigned Owned Error- unexpected classid 2328

[Tom Lane]

pcasper  writes:
> During execute follow statement to reassign owner of objects in the database
> I have an error: 
> unexpected classid 2328

Hm, that would be a foreign data wrapper ... looks like REASSIGN OWNED
is missing support for that type of object.  For the moment, you'll need
to alter the owner manually.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] query problems

[Andreas Berglund]

Hi!
I'm trying to query the database of a fictional bookstore to find out 
which publisher has sold the most to the bookstore.

This is the database structure

books((book_id), title, author_id, subject_id)
publishers((publisher_id), name, address)
authors((author_id), last_name, first_name)
stock((isbn), cost, retail_price, stock)
shipments((shipment_id), customer_id, isbn, ship_date)
customers((customer_id), last_name, first_name)
editions((isbn), book_id, edition, publisher_id, publication_date)
subjects((subject_id), subject, location)


This is my query

select publisher_id, sum(sum) from ((select publisher_id, 
sum(cost*stock) from stock natural join editions group by publisher_id) 
UNION (select publisher_id, sum(cost * count) from stock natural join 
(select isbn, count(isbn) from shipments group by isbn)a natural join 
editions group by publisher_id))a group by publisher_id;


That gets me a table with publisher_id and the total amount of sales for 
every publisher. From that I would like to extract the tuple with the 
biggest sum. But I can only seem to get the sum itself not the whole 
tuple. How do I go about this?


If there's a smarter way to approach the problem then I'm open to 
suggestions.


regards
Andreas Berglund



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgresql-9.0

[John R Pierce]

On 02/14/12 10:29 PM, khizer wrote:
  In postgresql 9.0.4  i connected to a database and trying to 
make queries but
  i am facing memory issue, getting err as *glibc* detected 
*realloc* invalid next size

  so kindly requesting u to provide your valuable feed backs


insufficient information.

 * what OS?
 * what distribution of postgresql 9.0.4? (often there's multiple
   choices for a given OS),
 * history of this system? (did this used to work?  if so, what changed?),
 * what mechanism are you using to make these queries? (psql shell,
   pgadmin3, application written in  using language binding ,
   etc etc).
 * etc etc.






--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table growing faster than autovacuum can vacuum

[John R Pierce]

On 02/15/12 8:46 AM, Asher Hoskins wrote:
I've got a database with a very large table (currently holding 23.5 
billion rows,


a table that large should probably be partitioned, likely by time.   
maybe a partition for each month.  as each partition is filled, it can 
be VACUUM FREEZE'd since it will never be modified again.   if you're 
doing your batch inserts of 1-2 billion rows once a month or so, perhaps 
that should be the partition right there.


since you're inserting the new data sequentially, and not doing UPDATEs, 
there's no point in doing VACUUM FULL, just a plain vacuum.


so, your ~ monthly batch run could be something like...

create new partition table
copy/insert your 1-2 billion rows
vacuum analyze (NOT full) new table
vacuum freeze new table
update master partition table rules

this way, autovacuum won't even bother with these tables.



--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table growing faster than autovacuum can vacuum

[Scott Marlowe]

On Wed, Feb 15, 2012 at 12:38 PM, John R Pierce  wrote:
> so, your ~ monthly batch run could be something like...
>
>    create new partition table
>    copy/insert your 1-2 billion rows
>    vacuum analyze (NOT full) new table
>    vacuum freeze new table
>    update master partition table rules

Just FYI, you can "vacuum freeze analyze;" all at once.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgresql-9.0

[Scott Marlowe]

On Tue, Feb 14, 2012 at 11:29 PM, khizer  wrote:
> Hi,
>   In postgresql 9.0.4  i connected to a database and trying to make
> queries but
>       i am facing memory issue, getting err as glibc detected   realloc
> invalid next size
>       so kindly requesting u to provide your valuable feed backs

The first thing to figure out is if this is a client or server side
issue.  It's not uncommon to see folks do something like:

psql mybigdb
select * from somebigtable;

and run out of memory on the client.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] query problems

[David Johnston]

-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andreas Berglund
Sent: Wednesday, February 15, 2012 2:31 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] query problems


Hi!
I'm trying to query the database of a fictional bookstore to find out which
publisher has sold the most to the bookstore.

---

Need clarification:

Is this:

1) a bookstore that doesn't exist
2) a bookstore that only sells fiction
3) All the above

Also, what happens if two publishers sold the same maximum amount?

You should really get into the habit of giving your calculated fields names
so that you can avoid stuff like " SUM(SUM) ".

It may be your e-mail client but you should also try to present you
information with generous use of whitespace and, if possible, indenting; I
am not inclined to copy-paste and perform the indenting in order to figure
out what seems to be a basic textbook/homework problem.

Once you know what the maximum amount is (from a sub-query) you then link
that back onto the original table if the form of a "WHERE" clause or,
possibly, a JOIN.

SELECT * FROM publishers WHERE publisher_id IN ( "your big long query goes
here; with an ORDER BY DESC and LIMIT 1; without the SUM(SUM) in the select
list" );

There likely is a more succinct way to write this but I'd need to spend more
time than I have right now.

David J.





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] SPI functions to work with the Execution Plan

[Osmel Barreras Piñera]

Thanks for answering:

I am a member of the PostgreSQL development community here in Cuba. My proposal 
is to develop an extension to PostgreSQL that allows query execution using 
multiple threads. The central idea is to get the execution plan ready to run, 
but send him to run with the same postgres executor concurrently with the help 
of a C library that handles the job with the user-level threads and kernel 
level.
I would appreciate any solution or suggestion that you could help me to fulfill 
this goal.
Is there any query to return the plan or any similar data structure?




Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE 
ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU!
http://www.antiterroristas.cu
http://justiciaparaloscinco.wordpress.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] DDL Triggers

[Lee Hachadoorian]

On Wed, Feb 15, 2012 at 12:42 PM, Bob Pawley  wrote:

> I have a table that is generated through ogr2ogr.
>
> To get ogr2ogr working the way I want, I need to use the -overwrite
> function. If I use the append function information is lost. Something to do
> with the way the switches work.
>
> Overwrite drops the existing table and also the attached trigger .
>
> I am attempting to have the dropped table trigger a function as it is
> being populated by insert commands.
>
> DDL trigger was mentioned as a possibility on the GDAL list.
>
> Any suggestions will be welcome.
>
> Bob
>

Are you sure ogr2ogr is the right tool for what you want to accomplish?
Reading between the lines, it *seems* like you intend to do an append (SQL
INSERT), but ogr2ogr isn't working the way you want, forcing you to use
overwrite (DROP/CREATE TABLE). Trying to use a DDL trigger seems like a
roundabout way to get what you want when the problem is ogr2ogr.

So, I would back up and ask, what are you trying to do, and what
information is being lost using -append?

Also, you mentioned asking this on the GDAL list, did you try the PostGIS
list?

--Lee

-- 
Lee Hachadoorian
PhD, Earth & Environmental Sciences (Geography)
Research Associate, CUNY Center for Urban Research
http://freecity.commons.gc.cuny.edu/

Re: [GENERAL] [postgis-users] ST_AsJpeg

[Stefan Keller]

Hi Regina

2012/2/14 Paragon Corporation  wrote:
> Here it is in the docs now:
>
> http://postgis.refractions.net/documentation/manual-svn/using_raster.xml.html#RasterOutput_PSQL

Citation from there: "Sadly PSQL doesn't have easy to use built-in
functionality for outputting binaries..."

Did anyone show and discuss this issue with postgres specialists?

--Stefan

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Rules of Thumb for Autovaccum

[Ondrej Ivanič]

Hi,

On 16 February 2012 01:14, Robert James  wrote:
> What rules of thumb exist for:
> * How often a table needs to be vacuumed?
> * How often a table needs to be analyzed?
> * How to tune Autovacuum?

I prefer to use autovacuum daemon and sets thresholds on per table
basis i.e. sets reasonable defaults and then add few exceptions.
I keep *_threshold as is and change *_scale_factor and turn off cost
based vacuum/analyse (see other note about this). My lowest
scale_factor is 0.002 ie. 0.2% of table has to change (which
corresponds to ~8mil rows) to trigger analyse/vacuum.

autovacuum/analyse can produce significant I/O so you have two options:
- tune cost based settings in order to limit I/O used by this porocess
- turn off autovacuum daemon and schedule manual acuum/analyse in quiet period

-- 
Ondrej Ivanic
(ondrej.iva...@gmail.com)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] order of evaluation of search arguments

[Kiriakos Georgiou]

As I understand it the order the of evaluation of search arguments is up to the 
optimizer.  I've tested the following query, that is supposed to take advantage 
of advisory locks to skip over rows that are locked by other consumers running 
the exact same query and it seems to work fine.  It seems to me the optimizer 
should always prefer to scan by status.  What say you?

begin;

select *
from queue q
where
   q.status = 'unprocessed'
   and pg_try_advisory_xact_lock(q.qid, q.tableoid::int) = true
limit 2 -- specify batch size here
for update;

-- now process the queue items

Kiriakos

Re: [GENERAL] Dump functions alone

[Rajan, Pavithra]

Any help in getting function argument names is appreciated.  Thank you

 

To dump the functions and their definitions , I first created a
pga_functions view as mentioned in one of the archives. 

 

 

First Step:  Create a pga_functions view

 

create or replace view pga_functions as

select

l.lanname as language,

 
n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtypes)
||')' as name,

t.typname as returntype,

E'\n\n'||'CREATE OR REPLACE FUNCTION
'||n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtyp
es)||E')\n'||'  RETURNS '||t.typname||' AS'||E'\n$$' ||prosrc

/* (select case when lanname <> 'c'

then  replace(prosrc,''',''') else replace(prosrc,''',''')||'.so'

end)*/||E'\n$$'||' LANGUAGE ''' || l.lanname || E''' VOLATILE;\n' as
source

from pg_proc p, pg_type t, pg_namespace n, pg_language l

where p.prorettype = t.oid and p.pronamespace = n.oid

and p.prolang = l.oid;

 

 

Second Step:  Did a select from this view and dumped to a sql file

 

psql -d DATABASENAME -c "select source from pga_functions where name
like 'om%'"> omsfunctions.sql

 

 

Now in the file , not sure how to get the functions with argname or (not
sure if it's called the proargname)

 

 

Eg The original  function is defined as 

 

 

 

-- Function: oms.om_change_i_division(integer, text, text)

 

-- DROP FUNCTION oms.om_change_i_division(integer, text, text);

 

CREATE OR REPLACE FUNCTION oms.om_change_i_division(v_incidentid
integer, v_division text, v_olddivision text)

  RETURNS void AS

$BODY$

   DECLARE

   v_Value  TEXT;

   v_OldValue  TEXT;

   v_Message  TEXT;

BEGIN

   v_Value := SUBSTR(v_Division,1,3);

   v_OldValue := SUBSTR(v_OldDivision,1,3);

   v_Message := 'Changed Incident Division to ' || coalesce(v_Value,'')
|| ' from ' || coalesce(v_OldValue,'');

 

   update OM_Incidents

   set

   Division = v_Division

   where

   IncidentId = v_IncidentId;

 

   PERFORM om_ins_audit(v_IncidentId,'Modified',v_Message);

   RETURN;

END; $BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION oms.om_change_i_division(integer, text, text) OWNER TO
oru;

 

 

 

 

The  output of a function with the above pg_functions view comes out as
below with no argument names.

 

 

-- Function: oms.om_change_i_division(integer, text, text)

 

-- DROP FUNCTION oms.om_change_i_division(integer, text, text);

 

CREATE OR REPLACE FUNCTION oms.om_change_i_division( integer, text,
text) (With no argument names)

  RETURNS void AS

$BODY$

   DECLARE

   v_Value  TEXT;

   v_OldValue  TEXT;

   v_Message  TEXT;

BEGIN

   v_Value := SUBSTR(v_Division,1,3);

   v_OldValue := SUBSTR(v_OldDivision,1,3);

   v_Message := 'Changed Incident Division to ' || coalesce(v_Value,'')
|| ' from ' || coalesce(v_OldValue,'');

 

   update OM_Incidents

   set

   Division = v_Division

   where

   IncidentId = v_IncidentId;

 

   PERFORM om_ins_audit(v_IncidentId,'Modified',v_Message);

   RETURN;

END; $BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION oms.om_change_i_division(integer, text, text) OWNER TO
oru;

 

 

 

From: Raghavendra [mailto:raghavendra@enterprisedb.com] 
Sent: Wednesday, February 15, 2012 11:05 AM
To: Rajan, Pavithra
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Dump functions alone

 

One more thing you can also get it from pg_get_functiondef() system
function.


 

---

Regards,

Raghavendra

EnterpriseDB Corporation

Blog: http://raghavt.blogspot.com/





On Wed, Feb 15, 2012 at 9:32 PM, Raghavendra
 wrote:

You have two options.

*   Use contrib module pg_extractor
https://github.com/omniti-labs/pg_extractor 
*   Use pg_proc catalog to get function definition

 

---

Regards,

Raghavendra

EnterpriseDB Corporation

Blog: http://raghavt.blogspot.com/





On Wed, Feb 15, 2012 at 6:59 PM, Rajan, Pavithra  wrote:

Hello all-  Is there a way to just dump functions in a schema in to a
txt file/ sql file ?  Thank you.

 

 

[GENERAL] conexão no windows 7

[vossistemas]

Instalei o Postgresql 8.3 no windows 7 como servidor. Em uma estação com xp
estou tentando me conectar e ocorre a seguinte mensagem: FATAL: no
pg_hba.conf entry for host "192.168.1.51", user "Vilson", database
"postgres", SSL off .

No servidor com windows 7 está configurado:
postgresql.conf: listen_addresses = '*'

pg.hba.conf: host all all 192.168.1.0/24

o ip do servidor : 192.168.1.48
o ip da estação: 192.168.1.51

da estação chamo o ip do servidor 192.168.1.48

Já configurei em várias máquinas e não deu problema. Esta é a primeira vez
que estou configurando no WINDOWS 7 ULTIMATE

Vilson Zin
VOS Software Ltda


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/conexao-no-windows-7-tp5487197p5487197.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] postgresql90-server package for RHEL5

[Tomonari Katsumata]

Hi

I'm looking for postgresql90-server-9.0.6 RPM package for RHEL5.
I can't find it bellow site.
"http://yum.postgresql.org/9.0/redhat/rhel-5-x86_64/repoview/";
"http://yum.postgresql.org/9.0/redhat/rhel-5-i386/repoview/";.


I can find it for RHEL6.
"http://yum.postgresql.org/9.0/redhat/rhel-6-x86_64/repoview/";
http://yum.postgresql.org/9.0/redhat/rhel-6-i386/repoview/


Why the package for RHEL5 is not there ?


and I have a small comment.

"OS/Arch" column in RPM Chart(*) says "RHEL/CentOS 5/Scientific Linux -
x86_64 ".
(*) http://yum.postgresql.org/rpmchart.php

I think it's better to say "RHEL/CentOS/Scientific Linux 5 - x86_64
".


regards,
--
Tomonari Katsumata



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgresql90-server package for RHEL5

[Devrim GÜNDÜZ]

Hi,

On Thu, 2012-02-16 at 11:21 +0900, Tomonari Katsumata wrote:
> I'm looking for postgresql90-server-9.0.6 RPM package for RHEL5.
> I can't find it bellow site.
> "http://yum.postgresql.org/9.0/redhat/rhel-5-x86_64/repoview/";
> "http://yum.postgresql.org/9.0/redhat/rhel-5-i386/repoview/";.
> 

It is there?

http://yum.postgresql.org/9.0/redhat/rhel-5-x86_64/repoview/postgresql90-server.html

Click "P" on the top right for the packages beginning with P.


> and I have a small comment.
> 
> "OS/Arch" column in RPM Chart(*) says "RHEL/CentOS 5/Scientific Linux
> -
> x86_64 ".
> (*) http://yum.postgresql.org/rpmchart.php
> 
> I think it's better to say "RHEL/CentOS/Scientific Linux 5 - x86_64
> http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] postgresql90-server package for RHEL5

[John R Pierce]

On 02/15/12 6:21 PM, Tomonari Katsumata wrote:

Why the package for RHEL5 is not there ?


the repoview on that site seems somewhat broken.
just install the repository yum.conf.d files via the proper RPM, and you 
can install postgresql via yum install...


rpm -Uvh 
http://yum.postgresql.org/9.0/redhat/rhel-5-i386/pgdg-centos90-9.0-5.noarch.rpm


or

rpm -Uvh 
http://yum.postgresql.org/9.0/redhat/rhel-5-x86_64/pgdg-centos90-9.0-5.noarch.rpm


then...

yum install postgresql90-server postgresql90-compat postgresql90



the actual RPMs are here...
http://yum.postgresql.org/9.0/redhat/rhel-5-x86_64/
and
http://yum.postgresql.org/9.0/redhat/rhel-5-i386/



--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] MD5 salt in pg_authid password hashes

[Stefan Weiss]

>From the manual:

| Because MD5-encrypted passwords use the role name as cryptographic
| salt, renaming a role clears its password if the password is
| MD5-encrypted.

In backend/commands/user.c

if (!pg_md5_encrypt(password, stmt->role, strlen(stmt->role),
encrypted_password))
elog(ERROR, "password encryption failed");
new_record[Anum_pg_authid_rolpassword - 1] =
CStringGetTextDatum(encrypted_password);

I don't understand this. Why was the role name chosen as a salt? Apart
from the problem that the hash becomes unusable when a role is renamed,
roles names are very poor salts. Given how relatively predictable they
are, the hash could just as well be left unsalted.

There is a comment in libpq/md5.c which more or less acknowleges this:
"Place salt at the end because it may be known by users trying to crack
the MD5 output." Ignoring for the moment that cracking PG passwords is
probably not very common, the position of the salt does little to
prevent attacks.

A random salt would eliminate both weaknesses. The only explanation I
can come up with is that the current method of hashing has been kept for
historic reasons, as changing to a random salt would break existing hashes.

Or is there something else I've overlooked?


regards,
stefan


PS: Strictly speaking, the expression "MD5-encrypted" in the manual is
incorrect - MD5 is a hashing algorithm, not an encryption algorithm.



-- 
LOAD"Ph'nglui mglw'nafh Cthulhu R'lyeh wgah'nagl fhtagn!",8,1
RUN!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgresql90-server package for RHEL5

[Devrim GÜNDÜZ]

On Wed, 2012-02-15 at 18:28 -0800, John R Pierce wrote:
> the repoview on that site seems somewhat broken.

It is not *broken*. It just lists the final 20 packages. As written
there, "Please go to the navigation menu on the top right for full set
of packages."

Still, this is not the first complaint that I got about this. Magnus was
pestering me well enough for changing it. Let me try to do something
before the next minor update releases. It is just a matter of changing a
python kid template.

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] postgresql90-server package for RHEL5

[Tomonari Katsumata]

Hi, Devrim

thank you for response.

(2012/02/16 11:28), Devrim GU"NDU"Z wrote:
> Hi,
>
> On Thu, 2012-02-16 at 11:21 +0900, Tomonari Katsumata wrote:
>> I'm looking for postgresql90-server-9.0.6 RPM package for RHEL5.
>> I can't find it bellow site.
>> "http://yum.postgresql.org/9.0/redhat/rhel-5-x86_64/repoview/";
>> "http://yum.postgresql.org/9.0/redhat/rhel-5-i386/repoview/";.
>>
> It is there?
>
> 
http://yum.postgresql.org/9.0/redhat/rhel-5-x86_64/repoview/postgresql90-server.html

>
> Click "P" on the top right for the packages beginning with P.

I could access above step.

but, I need to list the server-package in
"http://yum.postgresql.org/9.0/redhat/rhel-5-x86_64/repoview/";.


>> and I have a small comment.
>>
>> "OS/Arch" column in RPM Chart(*) says "RHEL/CentOS 5/Scientific Linux
>> -
>> x86_64".
>> (*) http://yum.postgresql.org/rpmchart.php
>>
>> I think it's better to say "RHEL/CentOS/Scientific Linux 5 - x86_64
>> 
> Good catch. Thanks, fixed.
thank you for fixing it.


regards,


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] postgresql90-server package for RHEL5

[Devrim GÜNDÜZ]

On Thu, 2012-02-16 at 11:44 +0900, Tomonari Katsumata wrote:
> but, I need to list the server-package in
> "http://yum.postgresql.org/9.0/redhat/rhel-5-x86_64/repoview/";.
> 
> 
Why? We cannot guarantee any specific package to be listed on the
repoview page, since as I just wrote, that list contains only the last
20 packages that I added to the repo. That's all. Correct list is 

http://yum.postgresql.org/9.0/redhat/rhel-5-x86_64/repoview/postgresql90-server.html
.

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

[GENERAL] Postgres 9.1 package for i.MX51X processor from Freescale

[Jayashankar K B]

Hi,

Is there a ready postgres 9.1 Package for the i.MX51X processor (ARM Cortex 
architecture) available or do I need to compile the Postgres source myself ?
I need it for a board having the i.MX51 processor and Linux (one of the latest 
versions of kernel - yet to decide the exact version).
If I need to compile postgres source, then what parameters should I use for 
this particular processor architecture ?
Also, I am yet to find a cross compilation tool chain for this processor.
If any ideas, please share.

Thanks and Regards
Jay



Larsen & Toubro Limited

www.larsentoubro.com

This Email may contain confidential or privileged information for the intended 
recipient (s) If you are not the intended recipient, please do not use or 
disseminate the information, notify the sender and delete it from your system.

Re: [GENERAL] postgresql90-server package for RHEL5

[Tomonari Katsumata]

Hi,

OK, I understand it.

I've thought all PostgreSQL packages are included
in repoview page...

Sorry.

regards,

(2012/02/16 11:48), Devrim GÜNDÜZ wrote:

On Thu, 2012-02-16 at 11:44 +0900, Tomonari Katsumata wrote:

>  but, I need to list the server-package in
>  "http://yum.postgresql.org/9.0/redhat/rhel-5-x86_64/repoview/";.
>  
>  

Why? We cannot guarantee any specific package to be listed on the
repoview page, since as I just wrote, that list contains only the last
20 packages that I added to the repo. That's all. Correct list is

http://yum.postgresql.org/9.0/redhat/rhel-5-x86_64/repoview/postgresql90-server.html
.



--

NTTソフトウェア株式会社
SI＆NI・ソリューション事業グループ
OSSプラットフォーム事業ユニット（SOPF）
勝俣　智成 (Tomonari Katsumata)
〒221-0835
神奈川県横浜市神奈川区鶴屋町3-32-13 第二安田ビル5F
TEL：045-317-7018

E-Mail: katsumata.tomon...@po.ntts.co.jp




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgresql 9.0.6 backends pruning process environment?

[dennis jenkins]

On Wed, Feb 15, 2012 at 9:18 AM, Tom Lane  wrote:
> Magnus Hagander  writes:
>> On Wed, Feb 15, 2012 at 06:40, dennis jenkins
>>  wrote:
>>> I recently updated my Gentoo Linux development system from postgresql
>>> 9.0.4 to 9.0.6-r1 (9.0.6 plus some Gentoo specific patches).  One of
>>> my 'C' language functions (been using it for years) stopped working
>>> because the backend no longer had access to the PGDATA environment
>>> variable.  A snippet of code is included below.
>
>> I suggest you look at the version history of the gentoo packaging and
>> scripts instead. My guess is that something was changed there.
>
> Yes.  A PG backend will not remove a "PGDATA" envar, but *it does not
> set it either*.  This sounds to me like a change in the startup script.
>
>> You can look at the configuration variable data_directory, or use the
>> C symbol DataDir which is exported from the backend.
>
> Quite --- at the C level, looking at DataDir is the right thing, and
> looking at PGDATA could be misleading even if it exists --- consider
> the possibility that we took the data_directory setting from the
> command line or postgresql.conf.
>
>                        regards, tom lane

Magnus, Tom,

Thank you very much.  My code now uses 'DataDir' (export in
server/miscadmin.h) and it works fine.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] MD5 salt in pg_authid password hashes

[Adrian Klaver]

On Wednesday, February 15, 2012 6:34:21 pm Stefan Weiss wrote:
> From the manual:
> | Because MD5-encrypted passwords use the role name as cryptographic
> | salt, renaming a role clears its password if the password is
> | MD5-encrypted.
> 
> In backend/commands/user.c
> 
> if (!pg_md5_encrypt(password, stmt->role, strlen(stmt->role),
> encrypted_password))
> elog(ERROR, "password encryption failed");
> new_record[Anum_pg_authid_rolpassword - 1] =
> CStringGetTextDatum(encrypted_password);
> 
> I don't understand this. Why was the role name chosen as a salt? Apart
> from the problem that the hash becomes unusable when a role is renamed,
> roles names are very poor salts. Given how relatively predictable they
> are, the hash could just as well be left unsalted.

When you alter the role name you are told the password has been cleared. It 
would be fairly easy to wrap the rename and the setting of the password in a 
transaction.

> 
> There is a comment in libpq/md5.c which more or less acknowleges this:
> "Place salt at the end because it may be known by users trying to crack
> the MD5 output." Ignoring for the moment that cracking PG passwords is
> probably not very common, the position of the salt does little to
> prevent attacks.
> 
> A random salt would eliminate both weaknesses. The only explanation I
> can come up with is that the current method of hashing has been kept for
> historic reasons, as changing to a random salt would break existing hashes.
> 
> Or is there something else I've overlooked?

Yes:
http://www.postgresql.org/docs/9.0/static/encryption-options.html
"
Encrypting Passwords Across A Network
The MD5 authentication method double-encrypts the password on the client before 
sending it to the server. It first MD5-encrypts it based on the user name, and 
then encrypts it based on a random salt sent by the server when the database 
connection was made. It is this double-encrypted value that is sent over the 
network to the server. Double-encryption not only prevents the password from 
being discovered, it also prevents another connection from using the same 
encrypted password to connect to the database server at a later time.


Encrypting Data Across A Network
SSL connections encrypt all data sent across the network: the password, the 
queries, and the data returned. The pg_hba.conf file allows administrators to 
specify which hosts can use non-encrypted connections (host) and which require 
SSL-encrypted connections (hostssl). Also, clients can specify that they 
connect 
to servers only via SSL. Stunnel or SSH can also be used to encrypt 
transmissions.
"
> 
> 
> regards,
> stefan
> 
> 
> PS: Strictly speaking, the expression "MD5-encrypted" in the manual is
> incorrect - MD5 is a hashing algorithm, not an encryption algorithm.
> 

-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres 9.1 package for i.MX51X processor from Freescale

[John R Pierce]

On 02/15/12 7:00 PM, Jayashankar K B wrote:


Is there a ready postgres 9.1 Package for the i.MX51X processor (ARM 
Cortex architecture) available or do I need to compile the Postgres 
source myself ?


I need it for a board having the i.MX51 processor and Linux (one of 
the latest versions of kernel – yet to decide the exact version).


If I need to compile postgres source, then what parameters should I 
use for this particular processor architecture ?


Also, I am yet to find a cross compilation tool chain for this processor.

If any ideas, please share.



from the perspective of compiling a package like Postgres, the libc and 
other libraries version is more important than the kernel


IMHO, the important things to decide at compile time are...

 * prefix directory
 * ssl support?
 * plpython?
 * plperl?
 * uuid support?


ssl, perl, python, and uuid will all require the appropriate runtime 
libraries.





--
john r pierceN 37, W 122
santa cruz ca mid-left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] order of evaluation of search arguments

[Tom Lane]

Kiriakos Georgiou  writes:
> As I understand it the order the of evaluation of search arguments is up to 
> the optimizer.  I've tested the following query, that is supposed to take 
> advantage of advisory locks to skip over rows that are locked by other 
> consumers running the exact same query and it seems to work fine.  It seems 
> to me the optimizer should always prefer to scan by status.  What say you?

"When it breaks, you get to keep both pieces."  Was your testing even
capable of noticing the problem if the query locked more rows than you
wanted?

Less dangerous coding practices might involve putting the lock function
in an outer query, while using an OFFSET 0 in the sub-query as an
optimization fence.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [GENERA]: Postgresql-9.1.1 synchronous replication issue

[Venkat Balaji]

Andrian,

Thanks a lot !

So in this case you are not waiting for confirmation of the commit being
> flushed
> to disk on the standby.  It that case you are bypassing the primary reason
> for
> sync replication. The plus is transactions on the master will complete
> faster
> and do so in the absence of the standby. The minus is that you are in sort
> of an
> in between state.
>

I understand. My worry and requirement is to ensure master is not disturbed
for any reason.
In sync rep, the biggest worry is if standby server is unavailable and is
down for longer time, master hangs and will be in the same state until
standby comes back up or replication must be broken temporarily (until
standby comes back up) so that master runs without interruption. This is a
costly exercise on production from downtime perspective.

Personally, I take sync replication to be basically an all or nothing
> proposition. By setting it up you are saying you want, at minimum, two
> database
> clusters to be in sync at any point in time all the time (except for start
> up).
> If that is not possible then you are really looking for async replication.
>

Yeah. We will need to make a decision accordingly.

Thanks again,
VB

Re: [GENERAL] Easy form of "insert if it isn't already there"?

[Bartosz Dmytrak]

Maybe to show how "found" works and how to ignore errors - that is my
assumption only.

Regards,
Bartek


2012/2/15 Berend Tober 

> Chris Angelico wrote:
>
>> On Wed, Feb 15, 2012 at 5:26 PM, Bartosz Dmytrak
>>  wrote:
>>
>>>
>>> e.g. You can use BEGIN... EXCEPTION END, good example of
>>> such approach is
>>> there: http://www.postgresql.org/**docs/9.1/static/plpgsql-**
>>> control-structures.html#**PLPGSQL-UPSERT-EXAMPLE
>>> ;
>>>
>>
>>
> I wonder why, in that example, you would not try the INSERT first, and if
> that fails, then do the update?
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general
>

Re: [GENERAL] [postgis-users] ST_AsJpeg

[Sandro Santilli]

On Wed, Feb 15, 2012 at 10:37:08PM +0100, Stefan Keller wrote:
> Hi Regina
> 
> 2012/2/14 Paragon Corporation  wrote:
> > Here it is in the docs now:
> >
> > http://postgis.refractions.net/documentation/manual-svn/using_raster.xml.html#RasterOutput_PSQL
> 
> Citation from there: "Sadly PSQL doesn't have easy to use built-in
> functionality for outputting binaries..."
> 
> Did anyone show and discuss this issue with postgres specialists?

I don't think there's much to discuss.
I'm sure a patch to psql would be welcome.

--strk; 

  ,--o-. 
  |   __/  |Delivering high quality PostGIS 2.0 !
  |  / 2.0 |http://strk.keybit.net
  `-o--'


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] order of evaluation of search arguments

[Kiriakos Georgiou]

I tested it by visual inspection of advisory locks in pg_locks; once with a 
small test table, and once on a larger 'operations' table in our test 
environment.  It seemed to work, but I hear you, I don't like to depend on the 
mood of the optimizer.  The drawback of the subquery version is that if the 
queue table gets big (eg: consumers are shut off) it will spend a lot of time 
building a worktable only to get a few items from it later in the outer query.  
Perhaps putting a limit in the inner query as well can alleviate that, like:  
LIMIT (expected number of consumers) * (batch size)

There is a theoretical race condition with this approach (fast vs slow 
consumers) but it's harmless - a consumer that executes the inner query slowly 
may get less or no items to process, although there might be some, but they 
will picked up eventually, so it's no big deal.

thanks,
Kiriakos

On Feb 16, 2012, at 12:44 AM, Tom Lane wrote:

> Kiriakos Georgiou  writes:
>> As I understand it the order the of evaluation of search arguments is up to 
>> the optimizer.  I've tested the following query, that is supposed to take 
>> advantage of advisory locks to skip over rows that are locked by other 
>> consumers running the exact same query and it seems to work fine.  It seems 
>> to me the optimizer should always prefer to scan by status.  What say you?
> 
> "When it breaks, you get to keep both pieces."  Was your testing even
> capable of noticing the problem if the query locked more rows than you
> wanted?
> 
> Less dangerous coding practices might involve putting the lock function
> in an outer query, while using an OFFSET 0 in the sub-query as an
> optimization fence.
> 
>   regards, tom lane
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general