[GENERAL] LDAP authentication not working
Hi, I'm running postgresql 9.1 on Debian and am trying to set up LDAP authentication using the following configuration in pg_hba.conf: hostssl testdb all 143.50.203.0/24 ldap ldapserver=wegc24.uni-graz.at ldapport=636 ldapbinddn=cn=nss,dc=uni-graz,dc=at ldapbindpasswd=thepasswd ldapbasedn=dc=uni-graz,dc=at Trying to access testdb via psql fails with the following error in the log: '''could not perform initial LDAP bind for ldapbinddn cn=nss,dc=uni-graz,dc=at on server wegc24.uni-graz.at: error code -1''' Unfortunately I did not find what error code -1 means. Ldapsearch works fine: ldapsearch -W -H ldaps://wegc24.uni-graz.at:636/ -D CN=nss,DC=uni-graz,DC=at Interesting is also, that postgres seems to not even reach the ldap server: If I change parameter ldapserver to a non-existing url it gives the same error code -1. Any help much appreciated! Best, Juergen signature.asc Description: OpenPGP digital signature
Re: [GENERAL] LDAP authentication not working
I don't think SSL support for LDAP is supported. Have you tried TLS on port 389? On May 13, 2014 8:20 PM, Jürgen Fuchsberger juergen.fuchsber...@uni-graz.at wrote: Hi, I'm running postgresql 9.1 on Debian and am trying to set up LDAP authentication using the following configuration in pg_hba.conf: hostssl testdb all 143.50.203.0/24 ldap ldapserver= wegc24.uni-graz.at ldapport=636 ldapbinddn=cn=nss,dc=uni-graz,dc=at ldapbindpasswd=thepasswd ldapbasedn=dc=uni-graz,dc=at Trying to access testdb via psql fails with the following error in the log: '''could not perform initial LDAP bind for ldapbinddn cn=nss,dc=uni-graz,dc=at on server wegc24.uni-graz.at: error code -1''' Unfortunately I did not find what error code -1 means. Ldapsearch works fine: ldapsearch -W -H ldaps://wegc24.uni-graz.at:636/ -D CN=nss,DC=uni-graz,DC=at Interesting is also, that postgres seems to not even reach the ldap server: If I change parameter ldapserver to a non-existing url it gives the same error code -1. Any help much appreciated! Best, Juergen
[GENERAL] what should be the best autovacuum configuration for daily partition table
Hi, I have a table with daily partition setup where old partitions are static tables that is after each day we don't get any new data in old partitions. The database size is 2 TB and I am running with autovacuum on for Postgresql 8.4. Now, I am facing a problem where old tables are not being vacuumed by autovacuum deamon and every now and then we are seeing autovacuum to prevent wrap around in the database and age(datfrozenzid) gets high for the database. Any idea what should be best configuration for this type of database environment. Thanks.
Re: [GENERAL] LDAP authentication not working
On Wed, May 14, 2014 at 8:35 AM, Stephan Fabel sfa...@hawaii.edu wrote: I don't think SSL support for LDAP is supported. Have you tried TLS on port 389? Correct, and you need to set ldaptls=1 to use that as well. (And yes, unfortunately the LDAP error messages from openldap are notoriously bad) //Magnus On May 13, 2014 8:20 PM, Jürgen Fuchsberger juergen.fuchsber...@uni-graz.at wrote: Hi, I'm running postgresql 9.1 on Debian and am trying to set up LDAP authentication using the following configuration in pg_hba.conf: hostssl testdb all 143.50.203.0/24 ldap ldapserver= wegc24.uni-graz.at ldapport=636 ldapbinddn=cn=nss,dc=uni-graz,dc=at ldapbindpasswd=thepasswd ldapbasedn=dc=uni-graz,dc=at Trying to access testdb via psql fails with the following error in the log: '''could not perform initial LDAP bind for ldapbinddn cn=nss,dc=uni-graz,dc=at on server wegc24.uni-graz.at: error code -1''' Unfortunately I did not find what error code -1 means. Ldapsearch works fine: ldapsearch -W -H ldaps://wegc24.uni-graz.at:636/ -D CN=nss,DC=uni-graz,DC=at Interesting is also, that postgres seems to not even reach the ldap server: If I change parameter ldapserver to a non-existing url it gives the same error code -1. Any help much appreciated! Best, Juergen -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [GENERAL] better performance on poorer machine?
Hi, all. I found the problem - it was merely a poorly written query, which for some reason was less terrible on my laptop. Looking at http://explain.depesz.com/ helped me solve the issue. Performance is now back to normal. It seems this was a classic case of pebcak. Thank you for your help and time! VG - Original Message - Fra: Alban Hertroys haram...@gmail.com Til: Vegard Bønes vegard.bo...@met.no Kopi: pgsql-general@postgresql.org Sendt: 13. mai 2014 17:49:28 Emne: Re: [GENERAL] better performance on poorer machine? On 13 May 2014 16:48, Vegard Bønes vegard.bo...@met.no wrote: I have a database on a test server with queries that perform terribly. Trying to fix this problem, I copied the database (using pg_dump) to my laptop, and reran tests there. The same queries perform perfectly on my laptop. We can but guess, but... Quite possibly your laptop is not actually a poorer machine for single uncomplicated queries. If it's cores are faster than the test servers, than your laptop would out-perform the server for such queries. Once you get other users starting to run queries as well, turning the load into a parallel load, the server will probably turn out faster again. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] Full-Text Search question
Search for fulltext tutorial + json functions http://www.postgresql.org/docs/9.3/static/functions-json.html On Wed, May 14, 2014 at 1:00 AM, Jesus Rafael Sanchez Medrano jesusraf...@gmail.com wrote: thanks... could you please be so kind to post some snippet/code for this? Att. == Jesus Rafael Sanchez Medrano Life is a dream, of which all must wake up On Tue, May 13, 2014 at 5:33 PM, Oleg Bartunov obartu...@gmail.comwrote: Easy, you need to extract text fields from json and construct tsvector from them (use concatenation, for example). On Tue, May 13, 2014 at 7:38 PM, Jesus Rafael Sanchez Medrano jesusraf...@gmail.com wrote: can postgres do FTS (full text search) on a json column? if possible, please be so kindd to give some snippet/example. Att. == Jesus Rafael Sanchez Medrano Life is a dream, of which all must wake up
Re: [GENERAL] Log Data Analytics : Confused about the choice of Database
On Wed, May 14, 2014 at 6:48 AM, Peeyush Agarwal
peeyushagarwal1...@gmail.com wrote:
Hi,
Thanks for the reply :)
Yes, Storing timestamp as timestamp [ (p) ] would be better. I simplified
the session in question. It may contain alphabets as well. So, I will
probably need to store it as a string only.
The problem with types of events is that it is not fixed and will keep
increasing over time (as more event types are added depending on the need).
Would you still recommend saving a map in another table? Will it have a
significant storage benefit and are there any other benefits? Storing it in
the app will require some addition in code each time a new event type is
added which is not difficult but time consuming and tedious.
Depending on how many types of events (store it as an smallint) . Integer
indexes can be searched faster + require less size than text ones.
If some parameters inside the json field show up all the time maybe put
them on their own fields, so you don't store the key each time + indexes
build faster.
I am not very familiar with json data type. Can I query on it as
effectively as hstore? Also, is it possible to index it partially so as to
increase query speeds for certain types of queries? For eg. I would require
parameter username in a lot of queries so I would like to partially index
over it.
http://www.postgresql.org/docs/9.3/static/functions-json.html for
expression indexes (also make partial indexes, when fields don't exist).
(more functions will come with jsonb in 9.4).
Peeyush Agarwal
On Tue, May 13, 2014 at 3:13 PM, Dorian Hoxha dorian.ho...@gmail.comwrote:
Why not store session as integer?
And timestamp as timesamp(z?) ?
If you know the types of events, also store them as integer , and save a
map of them in the app or on another table ?
And save the parameters as a json column, so you have more data-types?
Hstore only has strings.
Be carefull with the mongodb hipster on the stackoverflow post.
Elasticsearch is often used for log collection.
So, what really is the problem ?
On Tue, May 13, 2014 at 4:11 AM, Peeyush Agarwal
peeyushagarwal1...@gmail.com wrote:
Hi,
I have log data of the following format:
SessionTimestampEventParameters1 1
Started Session 1 2Logged In
Username:user12 3Started Session1 3
Started Challengetitle:Challenge 1, level:22 4
Logged InUsername:user2
Now, a person wants to carry out analytics on this log data (And would
like to receive it as a JSON blob after appropriate transformations). For
example, he may want to receive a JSON blob where the Log Data is grouped
by Session and TimeFromSessionStart and CountOfEvents are added before
the data is sent so that he can carry out meaningful analysis. Here I
should return:
[
{
session:1,CountOfEvents:3,Actions:[{TimeFromSessionStart:0,Event:Session
Started}, {TimeFromSessionStart:1, Event:Logged In,
Username:user1}, {TimeFromSessionStart:2, Event:Startd Challenge,
title:Challenge 1, level:2 }]
},
{
session:2,
CountOfEvents:2,Actions:[{TimeFromSessionStart:0,Event:Session
Started}, {TimeFromSessionStart:2, Event:Logged In,
Username:user2}]
}]
Here, TimeFromSessionStart, CountOfEvents etc. [Let's call it synthetic
additional data] will not be hard coded and I will make a web interface to
allow the person to decide what kind of synthetic data he requires in the
JSON blob. I would like to provide a good amount of flexibility to the
person to decide what kind of synthetic data he wants in the JSON blob.
If I use PostgreSQL, I can store the data in the following manner:
Session and Event can be string, Timestamp can be date and Parameters can
be hstore(key value pairs available in PostgreSQL). After that, I can
use SQL queries to compute the synthetic (or additional) data, store it
temporarily in variables in a Rails Application (which will interact with
PostgreSQL database and act as interface for the person who wants the JSON
blob) and create JSON blob from it.
However I am not sure if PostgreSQL is the best choice for this use
case. I have put the detailed question on SO at
http://stackoverflow.com/questions/23544604/log-data-analytics
Looking for some help from the community.
Peeyush Agarwal
--
Peeyush Agarwal
IIT Kanpur
+91 8953453689
Re: [GENERAL] LDAP authentication not working
On Wed, May 14, 2014 at 11:48 AM, Jürgen Fuchsberger juergen.fuchsber...@uni-graz.at wrote: On 05/14/2014 09:10 AM, Magnus Hagander wrote: On Wed, May 14, 2014 at 8:35 AM, Stephan Fabel sfa...@hawaii.edu mailto:sfa...@hawaii.edu wrote: I don't think SSL support for LDAP is supported. Have you tried TLS on port 389? Thanks for the hint, no wonder it does not work. Unfortunately this info is not in the postgres documentation. It is - indirectly, in the ldapurl documentation. To use encrypted LDAP connections, the ldaptls option has to be used in addition to ldapurl. The ldaps URL scheme (direct SSL connection) is not supported. But maybe it could be made more clear... Correct, and you need to set ldaptls=1 to use that as well. This does not work with our LDAP server (seems it is not configured to support TLS) That's strangely configured. The LDAP TLS support (in the protocol) is the standardized one, and the SSL wrapper mode is not in the standard. I *think* the SSL wrapper really is just that - wrap it in a standard SSL connection. In which case it might work if you set up stunnel or something like that to proxy the connection for you. Any idea whether LDAP over SSL will be supported in future postgres releases? I am not aware of any such plans, but if you (or somebody else) is willing to write a patch, I don't see a reason it would be rejected. Even though it's non-standard, it's fairly widespread. I recall there being a reason it wasn't added in the first place, but I don't recall what it was. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [GENERAL] Receiving many more rows than expected
On Friday 09 May 2014 08:36:04 David G Johnston wrote: This seems to likely be the same, still open, bug reported previously: No Number Assigned: http://www.postgresql.org/message-id/CANCipfpfzoYnOz5jj=UZ70_R=CwDHv36dqWSpw si27vpm1z...@mail.gmail.com #8464 http://www.postgresql.org/message-id/E1VN53g-0002Iy-Il@wrigleys.postgresql.o rg #8470 is referenced in the first thread as well...though that is specifically a performance issue and not a query bug. The recommended work-around is to move the sub-query using the FOR UPDATE into a CTE. Thanks for those pointers, it certainly looks like the same issue (the only difference being the size of the limit) and there has been a good amount of discussion there. I'll try the CTE workaround, although that leaves a bad taste in my mouth. From the discussions in the 1st thread, I wonder wether raising the isolation level to repeatable read would also fix the issue ? In any case, testing will take time because the bug triggers less than once a day and I haven't yet managed to reproduce it locally. ps: sorry I'm only seeing your answer now, it helps if you cc me when answering the list. -- Vincent de Phily -- 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] LDAP authentication not working
On May 14, 2014 12:56 AM, Magnus Hagander mag...@hagander.net wrote: On Wed, May 14, 2014 at 11:48 AM, Jürgen Fuchsberger juergen.fuchsber...@uni-graz.at wrote: On 05/14/2014 09:10 AM, Magnus Hagander wrote: On Wed, May 14, 2014 at 8:35 AM, Stephan Fabel sfa...@hawaii.edu mailto:sfa...@hawaii.edu wrote: I don't think SSL support for LDAP is supported. Have you tried TLS on port 389? Thanks for the hint, no wonder it does not work. Unfortunately this info is not in the postgres documentation. It is - indirectly, in the ldapurl documentation. To use encrypted LDAP connections, the ldaptls option has to be used in addition to ldapurl. The ldaps URL scheme (direct SSL connection) is not supported. In the documentation for 9.1 ldapurl is not mentioned. That's what the OP is using. This does not work with our LDAP server (seems it is not configured to support TLS) That's strangely configured. The LDAP TLS support (in the protocol) is the standardized one, and the SSL wrapper mode is not in the standard. Enabling TLS on OpenLDAP is trivial, especially if you have SSL enabled already. Ask your SysAdmin. I *think* the SSL wrapper really is just that - wrap it in a standard SSL connection. In which case it might work if you set up stunnel or something like that to proxy the connection for you. That would work, but it shouldn't be necessary. Just enable TLS in OpenLDAP. Any idea whether LDAP over SSL will be supported in future postgres releases? I am not aware of any such plans, but if you (or somebody else) is willing to write a patch, I don't see a reason it would be rejected. Even though it's non-standard, it's fairly widespread. I recall there being a reason it wasn't added in the first place, but I don't recall what it was. I agree that it would be nice to support the LDAPS scheme in PostgreSQL as well. -Stephan
Re: [GENERAL] what should be the best autovacuum configuration for daily partition table
Hi Rumman, Please publish the below information. 1. vacuum and auto-vacuum parametters current settings on cluster. select name,setting from pg_settings where name ilike '%vacuum%'; 2. show maintenance_work_mem ; show autovacuum_max_workers ; 3. Physical Ram size on server. --Chiru On Wed, May 14, 2014 at 12:36 PM, AI Rumman rumman...@gmail.com wrote: Hi, I have a table with daily partition setup where old partitions are static tables that is after each day we don't get any new data in old partitions. The database size is 2 TB and I am running with autovacuum on for Postgresql 8.4. Now, I am facing a problem where old tables are not being vacuumed by autovacuum deamon and every now and then we are seeing autovacuum to prevent wrap around in the database and age(datfrozenzid) gets high for the database. Any idea what should be best configuration for this type of database environment. Thanks.
Re: [GENERAL] what should be the best autovacuum configuration for daily partition table
On Wed, May 14, 2014 at 12:06 AM, AI Rumman rumman...@gmail.com wrote: Hi, I have a table with daily partition setup where old partitions are static tables that is after each day we don't get any new data in old partitions. The database size is 2 TB and I am running with autovacuum on for Postgresql 8.4. Now, I am facing a problem where old tables are not being vacuumed by autovacuum deamon and every now and then we are seeing autovacuum to prevent wrap around in the database and age(datfrozenzid) gets high for the database. Any idea what should be best configuration for this type of database environment. How high is age(datfrozenxid) getting? What is the problem you are experiencing? Cheers, Jeff
Re: [GENERAL] what should be the best autovacuum configuration for daily partition table
On Wed, May 14, 2014 at 3:45 PM, Jeff Janes jeff.ja...@gmail.com wrote:
On Wed, May 14, 2014 at 12:06 AM, AI Rumman rumman...@gmail.com wrote:
Hi,
I have a table with daily partition setup where old partitions are static
tables that is after each day we don't get any new data in old partitions.
The database size is 2 TB and I am running with autovacuum on for
Postgresql 8.4.
Now, I am facing a problem where old tables are not being vacuumed by
autovacuum deamon and every now and then we are seeing autovacuum to
prevent wrap around in the database and age(datfrozenzid) gets high for the
database.
Any idea what should be best configuration for this type of database
environment.
How high is age(datfrozenxid) getting? What is the problem you are
experiencing?
Cheers,
Jeff
It'd be good to know what you have autovacuum_freeze_max_age set to. You
may have it set a bit too low and causing that automatic vacuuming to kick
in too soon.
Even with autovacuum_freeze_max_age set to a reasonable value, we still see
this issue often with data warehousing systems with a lot of static data.
As you are seeing, autovacuum will never kick in for these tables until you
hit autovacuum_freeze_max_age. The best solution we've found for this is to
run a cronjob to routinely vacuum a controlled batch of the tables with the
oldest vacuum freeze age. This controls how many tables are being vacuumed
instead of running into the situation where many of them all hit
autovacuum_freeze_max_age at the same time and cause extensively long
vacuuming sessions.
Below is a script we run for one of our clients twice a day. You can adjust
the limit on the first query to set how many you want to run per batch.
This has to be high enough (or run the script often enough) to keep the
count of old tables below hitting autovacuum_freeze_max_age and having
autovacuum kick in on them. Just pass the name of the database as a
parameter to the script.
manual_vacuum.sh:
# Manually vacuum tables with the oldest xid (25)
psql -d $1 -t -o /tmp/manual_vacuum_$1.sql -c select 'vacuum analyze
verbose ' || oid::regclass || ';' from pg_class where relkind in ('r', 't')
and age(relfrozenxid) 1 order by age(relfrozenxid) desc limit 25
psql -d $1 -t -a -f /tmp/manual_vacuum_$1.sql $HOME/manual_vacuum_$1.log
21
Keith
http://www.keithf4.com
[GENERAL] are analyze statistics synced with replication?
We have a master/slave setup with replication. Today we failed over to the slave and saw disk I/O go through the roof. Are the pg_statistic statistics synced along with streaming replication? Are you expected to have to do a vacuum analyze after failing over? That's what we're trying now to see if it makes a difference. Our next step will be to fall back to the first host and see where this one went wrong (society? lax discipline at home? the wrong sort of friends?)
