Re: [GENERAL] Determining that a schema has changed for purpose of monitoring
On 04/10/12 01:48, Thor Michael Støre wrote: Hello. Is there a way in PostgreSQL for a client to determine that a database schema may have changed since it checked it earlier, for example to get the transaction ID or timestamp of the last successfully committed transaction that included DDL commands and compare them over time? For a while I've also seen plans and bits of work related to DDL triggers that might help me but don't have track of where this stands today. The background is that I've created a runtime application testing tool and that information would be useful to me, as it's currently manually triggered and I'm looking to explore the possibility of making applications capable of performing non-invasive self-testing with minimal configuration or work needed, at first to the extent of verifying that SQL statements parse and that the types and constraints of their fields don't change in a way that could cause problems for the application. check_postgres might come in handy for this. See http://bucardo.org/check_postgres/check_postgres.pl.html#same_schema -- Postgresql & php tutorials http://www.designmagick.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] user defined XML aggregate not working as (i think) it should
Thanks Tom, That was indeed the issue. Regards, Rhys On Wed, Oct 3, 2012 at 8:58 PM, Tom Lane wrote: > "Rhys A.D. Stewart" writes: >> I have an xml aggregate function that always returns 'ERROR: invalid >> XML content'. However an identical function that uses text produces >> valid xml with the same inputs. > > I believe the reason this doesn't work is that the aggregate's initial > value is faulty: it's supposed to be an XML value and it isn't. > > regression=# select ''::xml; > ERROR: invalid XML content > LINE 1: select ''::xml; >^ > > It's unfortunate that the CREATE AGGREGATE command doesn't complain > about that --- maybe we could improve it. > > As far as making a working aggregate goes, I'd try leaving off the > initcond clause (so that the initial value is NULL) and then marking > the transition function STRICT. (plays around with xmlconcat on > nulls ...) hm, maybe you'd not even need the STRICT marking, though > personally I'd suggest it. > > 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] COPY FROM with BYTEA fields - escaping doesn't work
Replying to my own question, but.. the solution seems to be in two stages. 1) Escape your binary data according to the BYTEA escape method. Eg. "Test\ff\0\9Again" becomes \x54657374ff0009416761696e 2) Escape that string as per COPY escape method. Eg. \x540065 becomes \\x540065 On 04/10/12 12:58, Toby Corkindale wrote: Hi, I'm trying to use the COPY .. FROM system with some data which includes binary values. They aren't large, but they include invalid UTF8 bytes, so I'm storing them into a BYTEA field. However I get errors when I try to do this.. CREATE TABLE foo (id SERIAL PRIMARY KEY, bar BYTEA); COPY foo (bar) FROM STDIN; Test\xff\x0\x9Again \. The error given is: ERROR: invalid byte sequence for encoding "UTF8": 0xff CONTEXT: COPY foo, line 1: "Test\xff\x0\x9Again" As far as I can tell, I'm obeying the instructions about escaping here: http://www.postgresql.org/docs/current/static/sql-copy.html I'm on version 9.1.5 of PostgreSQL. Thanks, Toby -- .signature -- 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] pros and cons of two security models
On 10/03/2012 07:06 PM, Chris Travers wrote: Hi all; I wanted to get opinions of folks who do a lot of this as well. LedgerSMB uses database user accounts and role grants to restrict access of the front-end application. Also posted to http://dba.stackexchange.com/questions/25357/choice-of-authentication-approach-for-financial-app-on-postgresql When you cross-post, please link. -- Craig Ringer -- 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] pg_typeof equivalent for numeric scale, numeric/timestamp precision?
On 10/03/2012 05:50 AM, Martijn van Oosterhout wrote: On Tue, Oct 02, 2012 at 10:19:18AM +0800, Craig Ringer wrote: Hi all While examining a reported issue with the JDBC driver I'm finding myself wanting SQL-level functions to get the scale and precision of a numeric result from an operation like: select NUMERIC(8,4) '1.' union select INTEGER 4; The typmod in postgres is not maintained very well. In the wire-protocol the typmod is provided if known (the C interface calls the function PQfmod) and if it's not there (which is very often, postgres doesn't try very hard) there's no way to "encourge" postgres to work it out for you. OK, thanks for confirming that. It seems the JDBC spec requires preservation of type qualifiers in ways that PgJDBC just won't be able to do without that info. If the server doesn't keep track of it I don't think there's anything PgJDBC can do to be compliant on that topic :-( -- Craig Ringer -- 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] [PL/PGSQL] column name substitution in PG8.4
On Wed, Oct 3, 2012 at 8:15 AM, Léon Melis wrote: > For some of my customers I wrote a PL/PGSQL function that stores the > difference between an OLD en NEW record when updating a record. This system > can be applied as a trigger on the table the customer likes to audit. > Because the function can be applied as a trigger on different tables, the > function needs to work with dynamic field names. > > For PG 9.x I wrote the function like this: > > [...] > new_rec = hstore(NEW); > old_rec = hstore(OLD); > FOR col IN SELECT attname FROM pg_attribute WHERE attrelid = TG_RELID AND > attstattarget != 0 LOOP > IF new_rec->col IS DISTINCT FROM old_rec->col THEN > INSERT INTO audit (...); > END IF; > END LOOP; > [...] > > I use the hstore extension to load the OLD en NEW recordset into an array > and then fetch the column names from pg_attribute to iterate through the > arrays. This worked just fine for me. > > However, I now have a customer using PG8.4 and I they need a similar > auditing functionality. The problem is that hstore in PG8.4 does not seem to > support creating an array from a record. So I'm searching for a solution to > either load an record into an array in PG8.4 or any other method to iterate > through a recordset without knowing the layout of the record. > > Any suggestion would be highly appreciated! back in the old days, that is, pre-9.0 hstore (the release of which was an absolute game changer for trigger functions), I used to manage the the record textually: the trigger function would cast new/old etc to text and stick it in the audit table that way. If I needed to access specific fields I would cast it back to the record if necessary. If the table structure changed, I'd be rewriting the audit table if there weren't too many records and keeping the old structure around as a composite type if there were. If I was lazy (I am), I might just leave it as text and deal with it that way -- textually parsing (a robust parser for that duplicates record_in() is alot of work but a simple parser covering common cases is pretty doable) the record or just plain eyeballing it if I had to go back and look at something. Really though, upgrading the database makes the most sense IMO. 8.4 indicates that your customer is probably sticking with the rhel stock packaging -- typically the workaround there is to just forcefully argue with the IT dept until they give up and let you run with the pgdg packaging which is quite excellent. Rumor has it that rhel 7 will standardize on 9.2 which is nice. Anyways, good luck! merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] COPY FROM with BYTEA fields - escaping doesn't work
Hi, I'm trying to use the COPY .. FROM system with some data which includes binary values. They aren't large, but they include invalid UTF8 bytes, so I'm storing them into a BYTEA field. However I get errors when I try to do this.. CREATE TABLE foo (id SERIAL PRIMARY KEY, bar BYTEA); COPY foo (bar) FROM STDIN; Test\xff\x0\x9Again \. The error given is: ERROR: invalid byte sequence for encoding "UTF8": 0xff CONTEXT: COPY foo, line 1: "Test\xff\x0\x9Again" As far as I can tell, I'm obeying the instructions about escaping here: http://www.postgresql.org/docs/current/static/sql-copy.html I'm on version 9.1.5 of PostgreSQL. Thanks, Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] pros and cons of two security models
On Wed, Oct 3, 2012 at 6:25 PM, Stephen Frost wrote: > Chris, > > * Chris Travers (chris.trav...@gmail.com) wrote: > > Well, that's the tradeoff I see. It can be handled using a bunch of > > different means. One that I have suggested is two-factor auth, where you > > require a client-side SSL cert with a specific issuing authority and a cn > > of the username that comes in under basic auth. We don't support that > as a > > matter of course yet, but, the other option is to use kerberos. > > Is it possible to proxy those client-side SSL credentials..? I've not > seen that, personally, but I've heard of people doing it.. Would be > very interested in that possibility. > > I wonder how you'd validate the certificate without being able to ask for something to be encrypted via a private key. > > I guess the what I am wondering is whether some of the pushback we get > from > > some developers is really a different aspect of the "databases should be > > dumb information stores" mentality, which isn't really the way we are > going > > or if there really are issues here we haven't considered. > > If they're asking about specific authentication mechanisms, I don't > think they're feeling like the database is a dumb data store... I'll > admit that I could be wrong there though. > Well, that hasn't been the request. The request has been for one role for the application to use in the db. I was going through the tradeoffs that I had seen. The arguments I have heard are: 1) If you are giving access to your db, then people can do things that aren't possible in the application itself (however here a lot of the business logic is contained in the database, and so I don't think this applies), and 2) A simple assertion that that doing things this way is "a security flaw" and that the abstraction of user accounts in the web application is a "necessary abstraction." The concern appears to be some sort of architectural concern and I don't really understand it, hence one reason for asking around a bit. My thinking is that security needs to follow business logic and it is a mistake to have them on separate layers. > > > My general feeling is that centralizing the security in the database > means > > a narrower security perimeter in the areas that matter, and that this > > mostly comes at the expense of easy multi-tenant hosting. > > I wouldn't compromise on having one central authority for security and > access control, particularly for a product such as LedgerSMB. Just my > 2c though, I'm sure others have differing opinions. > > > Out of curiosity, since you are using krb5/gssapi, why do you go through > > the set role? Why not just pass krb5 tickets around, since this > represents > > a re-usable auth method itself? > > When we're using krb5/gssapi, we *don't* use the set role approach, we > just proxy the credentials, as you'd expect. We use the set role > approach when we *can't* use krb5/gssapi (we're supporting users outside > of our AD infrastructure- clients, subcontractors, etc). In those > cases, we still want a strong auth system, so we go to a solution such > as client-side certificates or hardware tokens, but those aren't > proxyable credentials (that I've seen anyway.. maybe there's a way to > do it now), and so, in those cases, we use the SET ROLE approach. > They had better not be proxyiable... However one thing I have considered is this: 1) Password auth using HTTP basic and an SSL connection, and 2) The web server checks the SSL client cert to ensure that the cn on the certificate matches the supplied username. If they don't match you just ask for new credentials. In this regard, basically a username/password combo is unlocked by a certificate and you don't get to use a password unless you have a certificate for the same username. The password itself is proxyable, and so you get essentially two perimeters: a) The web app then can enforce two factor auth, and then b) The web app can then use the username/password as proxyable credentials. > > > Yeah. Of course AD integration with anything on Linux is not as simple > as > > it looks, but it still isn't that painful once you get used to it. > > Yeah, it really isn't that hard once you know the correct > 'incarnations', which are actually in a few briefings/trainings I've > given and are online in various places... > I wrote a paper on doing AD integration for OpenSSL on Linux. I was rather... amused by the differences in in how service accounts are handled in AD (at least in 2007) vs MIT Kerberos. Best Wishes, Chris Travers
Re: [GENERAL] user defined XML aggregate not working as (i think) it should
"Rhys A.D. Stewart" writes: > I have an xml aggregate function that always returns 'ERROR: invalid > XML content'. However an identical function that uses text produces > valid xml with the same inputs. I believe the reason this doesn't work is that the aggregate's initial value is faulty: it's supposed to be an XML value and it isn't. regression=# select ''::xml; ERROR: invalid XML content LINE 1: select ''::xml; ^ It's unfortunate that the CREATE AGGREGATE command doesn't complain about that --- maybe we could improve it. As far as making a working aggregate goes, I'd try leaving off the initcond clause (so that the initial value is NULL) and then marking the transition function STRICT. (plays around with xmlconcat on nulls ...) hm, maybe you'd not even need the STRICT marking, though personally I'd suggest it. 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: Fwd: [GENERAL] pros and cons of two security models
Chris, * Chris Travers (chris.trav...@gmail.com) wrote: > Well, that's the tradeoff I see. It can be handled using a bunch of > different means. One that I have suggested is two-factor auth, where you > require a client-side SSL cert with a specific issuing authority and a cn > of the username that comes in under basic auth. We don't support that as a > matter of course yet, but, the other option is to use kerberos. Is it possible to proxy those client-side SSL credentials..? I've not seen that, personally, but I've heard of people doing it.. Would be very interested in that possibility. > I guess the what I am wondering is whether some of the pushback we get from > some developers is really a different aspect of the "databases should be > dumb information stores" mentality, which isn't really the way we are going > or if there really are issues here we haven't considered. If they're asking about specific authentication mechanisms, I don't think they're feeling like the database is a dumb data store... I'll admit that I could be wrong there though. > My general feeling is that centralizing the security in the database means > a narrower security perimeter in the areas that matter, and that this > mostly comes at the expense of easy multi-tenant hosting. I wouldn't compromise on having one central authority for security and access control, particularly for a product such as LedgerSMB. Just my 2c though, I'm sure others have differing opinions. > Out of curiosity, since you are using krb5/gssapi, why do you go through > the set role? Why not just pass krb5 tickets around, since this represents > a re-usable auth method itself? When we're using krb5/gssapi, we *don't* use the set role approach, we just proxy the credentials, as you'd expect. We use the set role approach when we *can't* use krb5/gssapi (we're supporting users outside of our AD infrastructure- clients, subcontractors, etc). In those cases, we still want a strong auth system, so we go to a solution such as client-side certificates or hardware tokens, but those aren't proxyable credentials (that I've seen anyway.. maybe there's a way to do it now), and so, in those cases, we use the SET ROLE approach. > Yeah. Of course AD integration with anything on Linux is not as simple as > it looks, but it still isn't that painful once you get used to it. Yeah, it really isn't that hard once you know the correct 'incarnations', which are actually in a few briefings/trainings I've given and are online in various places... Thanks! Stephen signature.asc Description: Digital signature
Re: [GENERAL] PostgreSQL force create table / ignore constraints?
On Wed, 3 Oct 2012 08:12:25 -0700 (PDT) hartrc wrote: > Version Postgresql 9.1.6 > OS: SLES 11 64 bit > > Background: > Our developers create database schema in development environment using > PGAdmin (often using the GUI to generate the DDL). > We always deploy to production using a script, a single .sql file > which we execute via psql command line. This allows us to generate an > output with any errors and have good view of deployment history over > time. > > Issue > The issue we have is that developers generate the .sql script mainly > by copying and pasting from PGAdmin's SQL pane. The issue we have is > then the order of the object creation is important otherwise creation > of tables and fail when there is a foreign key constraint on another > table that does not exist (but is created later in the script). This > is not a big deal in a schema with 3 or 4 tables but when there are 20 > + it is time consuming task to reorder all the create statements. > > Can anyone recommend a way of dealing with this? My only other > thought has been pg_dump although i would prefer if the developers > could generate the scripts themselves. What about using pg_dump --schema-only when creating the files? (Or are you talking about icremental changes?) Cheers, Frank -- Frank Lanitz pgpJnRr67CUNQ.pgp Description: PGP signature
[GENERAL] syntax error collate
Hello If I do this from pgAdmin on windows to my localhost database 9.2 it works fine CREATE COLLATION Test ( LOCALE = 'en_US.UTF-8' ) If I connect to a linux server and do the sameI get ERROR: syntax error at or near "COLLATION" LINE 1: CREATE COLLATION Test ( On linux we have version string PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu On my windows computer I have PostgreSQL 9.2.1 Does anyone have some ideas what the problem might be. We get the same error if we run psql (with the same create collation query) under linux too. Thanks Niklas
[GENERAL] [PL/PGSQL] column name substitution in PG8.4
For some of my customers I wrote a PL/PGSQL function that stores the difference between an OLD en NEW record when updating a record. This system can be applied as a trigger on the table the customer likes to audit. Because the function can be applied as a trigger on different tables, the function needs to work with dynamic field names. For PG 9.x I wrote the function like this: [...] new_rec = hstore(NEW); old_rec = hstore(OLD); FOR col IN SELECT attname FROM pg_attribute WHERE attrelid = TG_RELID AND attstattarget != 0 LOOP IF new_rec->col IS DISTINCT FROM old_rec->col THEN INSERT INTO audit (...); END IF; END LOOP; [...] I use the hstore extension to load the OLD en NEW recordset into an array and then fetch the column names from pg_attribute to iterate through the arrays. This worked just fine for me. However, I now have a customer using PG8.4 and I they need a similar auditing functionality. The problem is that hstore in PG8.4 does not seem to support creating an array from a record. So I'm searching for a solution to either load an record into an array in PG8.4 or any other method to iterate through a recordset without knowing the layout of the record. Any suggestion would be highly appreciated! Regards, Léon Melis
Re: [GENERAL] Postgres will not start due to corrupt index
> -Original Message- > From: Tom Lane [mailto:t...@sss.pgh.pa.us] > Sent: Wednesday, October 03, 2012 2:47 PM > To: Igor Neyman > Cc: Robert Sosinski; Merlin Moncure; pgsql-general@postgresql.org; > Spike Grobstein > Subject: Re: [GENERAL] Postgres will not start due to corrupt index > > Igor Neyman writes: > > I wonder if there is a column in pg catalog, that indicates the type > of the index. I couldn't find one. > > join relam to pg_am.oid > > > So, I ran the following sql trying to find system indexes of gin or > gist type: > > There aren't any. > > regards, tom lane Tom, thank you. In this case: select i.indexname, a.amname, i.tablename from pg_indexes i JOIN (pg_class c join pg_am a ON (c.relam = a.oid) ) ON (i.indexname = c.relname) WHERE i.schemaname = 'pg_catalog'; Regards, Igor Neyman -- 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] syntax error collate
On 03/10/2012 15:31, Niklas Langvig wrote: > Hello > If I do this from pgAdmin on windows to my localhost database 9.2 it > works fine > CREATE COLLATION Test ( > > LOCALE = 'en_US.UTF-8' > > ) > > > If I connect to a linux server and do the sameI get > > ERROR: syntax error at or near "COLLATION" > > LINE 1: CREATE COLLATION Test ( > > On linux we have version string > > PostgreSQL 9.0.3 on x86_64-unknown-linux-gnu > > On my windows computer I have PostgreSQL 9.2.1 > > Does anyone have some ideas what the problem might be. A quick look at the docs indicates that CREATE COLLATION doesn't exist in 9.0; it was introduced in 9.1. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer
I use pgpool but some of the problem you listed are same as I had with pgpool I would not run pgbouner in /var/run/pbbouner. Every time you reboot the directory will get deleted. I set my parameter to another directory the would not get deleted after a reboot. /var/log/pgbouncer.log: what is the permission on /var/log? If you don't have write permission on the directory then you cannot write to the file. Psql: ERROR: No such user: You have to create the user in postgres, check you users postgres=# /du Role name -- testuser Wolfgang Schwurack DBA/SA UEN -Original Message- From: pgbouncer-general-boun...@pgfoundry.org [mailto:pgbouncer-general-boun...@pgfoundry.org] On Behalf Of Phoenix Kiula Sent: Wednesday, October 03, 2012 12:02 PM To: raghu ram Cc: pgbouncer-gene...@pgfoundry.org; PG-General Mailing List Subject: Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer On Tue, Oct 2, 2012 at 5:27 PM, Phoenix Kiula wrote: > On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula > wrote: >> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula >> wrote: Could you please check permission of /var/run/pgbouncer/ directory. If pgbouncer directory does not have "postgres" user permissions,please assign it and then start the pgbouncer. >>> >>> >>> The /var/run/pgbouncer/ directory has >>> >>>chown -R postgres:postgres .. >>> >>> The port number everywhere is already 6789. >>> >>> What else? >> >> >> >> And just to be safe, I also added pgbouncer user to postgres group: >> >> >> usermod -a -G postgres pgbouncer >> >> >> Now when I restart the pgbouncess service, it fails. The log has this >> message: >> >> >> 2012-10-01 23:25:24.004 21037 FATAL >> Cannot open logfile: '/var/log/pgbouncer.log': >> Permission denied >> >> >> That file is owned by "postgres:postgres" as indicated in a gazillion >> threads and documentation online (none of which is comprehensive) but >> just to be sure I also did this: >> >> >> chown :postgres /var/log/pgbouncer.log >> >> >> Still the same permission error. Seriously, why can't the log message >> be a little more useful? Why can't it say clearly WHICH USER is >> looking for permission to the log file? Both "pgbouncer" and >> "postgres" have permissions (through the group "postgres") on that >> file. So which is it? > > > > I made the port number 6389 everywhere. I changed the permissions of > the pgbouncer.log to: > >chown pgbouncer:postgres /var/log/pgbouncer.log > > Now at least the service starts. But when I try and connect via the > pgbouncer ID: > >psql -p 6389 -U snipurl_snipurl snipurl > > I get this error: > >psql: ERROR: No such user: MYSITE_MYSITE > > And yet, the authfile has this: > > "MYSITE_MYSITE" "" > "MYSITE_MYSITE" "" > "postgres" "" > "MYSITE_pgbouncer" "" > > > The authfile permissions are: > >283377983 -rw-r--r-- 1 pgbouncer postgres 262 Apr 14 11:15 > /var/lib/pgsql/pgbouncer.txt > > > What else? No response. Is there anyone who can help me with pgbouncer? What are the permissions for the authfile, etc? ___ Pgbouncer-general mailing list pgbouncer-gene...@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgbouncer-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] What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?
On Tue, Oct 2, 2012 at 9:18 AM, Merlin Moncure wrote: > > Yes but that is irrelevant to the discussion. I am comparing the speed of > > repeated table existence checks with the speed of repeated exception > blocks > > that access said table. > > Both approaches have to do a catalog scan (even if you've established > an exception block the server still internally has to do a catalog > scan in order to raise an appropriate error). The exception block has > the additional overhead of a subtransaction. > OK this makes sense. Thanks :-) I will go for the CREATE TABLE approach. -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com
Re: [GENERAL] surprising behavior or nothing to see here?
On Oct 3, 2012, at 11:50 AM, Tom Lane wrote: > Ben Chobot writes: >> 4. What might cause autovacuum analyze to make an index perform worse >> immediately, when a manual vacuum analyze does not have the same affect? And >> I'm not talking about changing things so the planner doesn't use the index, >> but rather, having the index actually take longer. > > Dunno about the replication angle, but would this have been a GIN index? > I'm wondering about possible interference with flushing of its > pending-insert queue (the FASTUPDATE stuff). Nope, btree: create index get_delayed_jobs_index on delayed_jobs (priority, run_at) tablespace data1 where locked_at is null and queue='queue' and next_in_strand=true; There are half a dozen other indices on this table too (that weren't applicable to the long query) but they're all btrees. -- 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] surprising behavior or nothing to see here?
Ben Chobot writes: > 4. What might cause autovacuum analyze to make an index perform worse > immediately, when a manual vacuum analyze does not have the same affect? And > I'm not talking about changing things so the planner doesn't use the index, > but rather, having the index actually take longer. Dunno about the replication angle, but would this have been a GIN index? I'm wondering about possible interference with flushing of its pending-insert queue (the FASTUPDATE stuff). 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] Postgres will not start due to corrupt index
Igor Neyman writes: > I wonder if there is a column in pg catalog, that indicates the type of the > index. I couldn't find one. join relam to pg_am.oid > So, I ran the following sql trying to find system indexes of gin or gist type: There aren't any. 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] Sequence and Schema permissions information schema
hartrc writes: > PostgreSQL v9.1.6 > Are sequence and schema permissions documented anywhere in the information > schema. In 9.1, I don't believe so. According to the GRANT reference page, which I think is accurate, privileges on schemas are a PG extension not found in the SQL standard; so of course the standard's views wouldn't have anyplace to expose that information. As for the sequence situation, you might find the commentary and documentation changes in this 9.2 commit of interest: http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=82e83f46a2ed311c6e7536f607f73a6f2a1d7dea 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] Postgres will not start due to corrupt index
From: Robert Sosinski [mailto:rsosin...@ticketevolution.com] Sent: Wednesday, October 03, 2012 10:54 AM To: Merlin Moncure Cc: pgsql-general@postgresql.org; Spike Grobstein Subject: Re: Postgres will not start due to corrupt index Hey Merlin, Thanks. Starting postgres with -P was something that I did not try. Does postgres have any GIN or GIST system indexes though? I would love to try it out, but the database has already been restored. Will definitely keep this in mind for the future though. Thanks again for the help, -- Robert Sosinski I wonder if there is a column in pg catalog, that indicates the type of the index. I couldn't find one. So, I ran the following sql trying to find system indexes of gin or gist type: select * from pg_indexes where schemaname = 'pg_catalog' and (indexdef like '%USING gist%' OR indexdef like '%USING gin%'); and it returned zero rows. Regards, Igor Neyman -- 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] Again, problem with pgbouncer
Hi, Do you have "MYSITE_MYSITE" user at your database. Please login to the database directly (I mean, without any pgbouncer and check once. select* from pg_user where usename ~~* 'MYSITE_MYSITE'; And also please check your's pgbouncer.ini admin users list also. Best Regards, Dinesh manojadinesh.blogspot.com On Wed, Oct 3, 2012 at 11:31 PM, Phoenix Kiula wrote: > On Tue, Oct 2, 2012 at 5:27 PM, Phoenix Kiula > wrote: > > On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula > wrote: > >> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula > wrote: > Could you please check permission of /var/run/pgbouncer/ directory. If > pgbouncer directory does not have "postgres" user permissions,please > assign > it and then start the pgbouncer. > >>> > >>> > >>> The /var/run/pgbouncer/ directory has > >>> > >>>chown -R postgres:postgres .. > >>> > >>> The port number everywhere is already 6789. > >>> > >>> What else? > >> > >> > >> > >> And just to be safe, I also added pgbouncer user to postgres group: > >> > >> > >> usermod -a -G postgres pgbouncer > >> > >> > >> Now when I restart the pgbouncess service, it fails. The log has this > message: > >> > >> > >> 2012-10-01 23:25:24.004 21037 FATAL > >> Cannot open logfile: '/var/log/pgbouncer.log': > >> Permission denied > >> > >> > >> That file is owned by "postgres:postgres" as indicated in a gazillion > >> threads and documentation online (none of which is comprehensive) but > >> just to be sure I also did this: > >> > >> > >> chown :postgres /var/log/pgbouncer.log > >> > >> > >> Still the same permission error. Seriously, why can't the log message > >> be a little more useful? Why can't it say clearly WHICH USER is > >> looking for permission to the log file? Both "pgbouncer" and > >> "postgres" have permissions (through the group "postgres") on that > >> file. So which is it? > > > > > > > > I made the port number 6389 everywhere. I changed the permissions of > > the pgbouncer.log to: > > > >chown pgbouncer:postgres /var/log/pgbouncer.log > > > > Now at least the service starts. But when I try and connect via the > > pgbouncer ID: > > > >psql -p 6389 -U snipurl_snipurl snipurl > > > > I get this error: > > > >psql: ERROR: No such user: MYSITE_MYSITE > > > > And yet, the authfile has this: > > > > "MYSITE_MYSITE" "" > > "MYSITE_MYSITE" "" > > "postgres" "" > > "MYSITE_pgbouncer" "" > > > > > > The authfile permissions are: > > > >283377983 -rw-r--r-- 1 pgbouncer postgres 262 Apr 14 11:15 > > /var/lib/pgsql/pgbouncer.txt > > > > > > What else? > > > > > No response. > > Is there anyone who can help me with pgbouncer? > > What are the permissions for the authfile, etc? > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] surprising behavior or nothing to see here?
Today we saw a couple behaviors in postgres we weren't expecting, and I'm not sure if there's something odd going on, or this is all business as usual and we never noticed before. In steady-state, we have a 32-core box with a fair amount of ram acting as a job queue. It's constantly busy inserting, updating, and deleting a table that rarely has more than 100k rows. All access is done via indices, and query times are usually just a few ms. So that's all good. This server version is: PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit This morning, one of the two servers that this streams wal files to for hot standby got locked up xlog replay pause mode. Nobody noticed this for several hours. The other server kept replicating just fine. Within an hour, we had a query idle in transaction that was blocking other queries. This happens with our application sometimes, so we killed it and all the locks cleared out immediately. We didn't think much of it at the time. However, soon after that, all other queries started taking longer. Even though there wasn't apparently any locking going on, queries were now taking almost 2 seconds each, and the CPUs were pegged. Eventually, we noticed that if we rebuilt the index that was being used by that query, things would speed right back up again until autovacuum analyze kicked off, at which point things would immediately start taking 2 seconds again. Manually analyzing would not immediately cause things to get bad, but they would eventually revert back to being too slow. To be clear, even with the 2s query times, the proper index was still being used - it was just taking hundreds of ms, instead of a few ms. We tried increasing column stats to try to tease out any change in value distribution but this had no effect. So this was our first mystery. Then we noticed that autovacuum wasn't clearing out tuples. Even odder, it appeared that when autovacuum started running, all the dead tuples would be listed as live tuples in pg_stat_user_tables, and after the vacuum completed, would go back to being dead tuples. Vacuum verbose showed that these tuples were not yet removable, even though there was no long transaction running. That was the second and third mysteries. Finally, we found the wal replication problem and resume wal replay. All the problems listed above disappeared. So that's mystery #4. My questions are: 1. Could wal replay on a hot streaming asynchronous slave really cause this? It certainly seems to have, and we do have hot_standby_feedback turned on, but we wouldn't have expected any symptoms from that which would have reduced the effectiveness of an index on the master. 2. Is it expected that during a vacuum, dead tuples show up as live tuples in pg_stat_user_tables? 3. Does a paused wal replay on an asynchronous slave count as a long running transaction, at least in terms of vacuuming out dead tuples on the master? If not, what else might keep vacuum from removing dead rows? All I could find on teh intarwebz was mention of long-running transactions, which pg_stat_activity and pg_locks didn't indicate. 4. What might cause autovacuum analyze to make an index perform worse immediately, when a manual vacuum analyze does not have the same affect? And I'm not talking about changing things so the planner doesn't use the index, but rather, having the index actually take longer. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Sequence and Schema permissions information schema
PostgreSQL v9.1.6 Are sequence and schema permissions documented anywhere in the information schema. I've looked through documentation and the information_schema itself but have had no luck. I noticed in the 9.2 documentation there is a reference to 'sequences' in information_schema.usage_privileges. I also noticed there is a has_schema_privilege and has_sequence_privilege function so I'm assuming this data is somewhere? Thanks Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/Sequence-and-Schema-permissions-information-schema-tp5726514.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] Again, problem with pgbouncer
On Tue, Oct 2, 2012 at 5:27 PM, Phoenix Kiula wrote: > On Tue, Oct 2, 2012 at 11:29 AM, Phoenix Kiula > wrote: >> On Tue, Oct 2, 2012 at 12:59 AM, Phoenix Kiula >> wrote: Could you please check permission of /var/run/pgbouncer/ directory. If pgbouncer directory does not have "postgres" user permissions,please assign it and then start the pgbouncer. >>> >>> >>> The /var/run/pgbouncer/ directory has >>> >>>chown -R postgres:postgres .. >>> >>> The port number everywhere is already 6789. >>> >>> What else? >> >> >> >> And just to be safe, I also added pgbouncer user to postgres group: >> >> >> usermod -a -G postgres pgbouncer >> >> >> Now when I restart the pgbouncess service, it fails. The log has this >> message: >> >> >> 2012-10-01 23:25:24.004 21037 FATAL >> Cannot open logfile: '/var/log/pgbouncer.log': >> Permission denied >> >> >> That file is owned by "postgres:postgres" as indicated in a gazillion >> threads and documentation online (none of which is comprehensive) but >> just to be sure I also did this: >> >> >> chown :postgres /var/log/pgbouncer.log >> >> >> Still the same permission error. Seriously, why can't the log message >> be a little more useful? Why can't it say clearly WHICH USER is >> looking for permission to the log file? Both "pgbouncer" and >> "postgres" have permissions (through the group "postgres") on that >> file. So which is it? > > > > I made the port number 6389 everywhere. I changed the permissions of > the pgbouncer.log to: > >chown pgbouncer:postgres /var/log/pgbouncer.log > > Now at least the service starts. But when I try and connect via the > pgbouncer ID: > >psql -p 6389 -U snipurl_snipurl snipurl > > I get this error: > >psql: ERROR: No such user: MYSITE_MYSITE > > And yet, the authfile has this: > > "MYSITE_MYSITE" "" > "MYSITE_MYSITE" "" > "postgres" "" > "MYSITE_pgbouncer" "" > > > The authfile permissions are: > >283377983 -rw-r--r-- 1 pgbouncer postgres 262 Apr 14 11:15 > /var/lib/pgsql/pgbouncer.txt > > > What else? No response. Is there anyone who can help me with pgbouncer? What are the permissions for the authfile, etc? -- 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] Unable to uninstall completely Postgre SQL
On 10/02/12 10:19 PM, Alex Putra wrote: I have problem with uninstalling postgre sql from my windows and as well to reset the service account password. Would you mind assist me in this case? you'll need to be a little more specific with why you're having a problem uninstalling postgresql if you want useful help with that. re: the service password, you go into Computer Management, Local Users and Groups, find the postgres account, and set the password there. details vary with flavor of windows.right click on Computer and choosing Manage from the menu generally gets you to Computer Management. -- 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] Upgrading 8.3-rc1 to 8.3.20
On 2012-10-03 16:51:59 +0200, Andreas Kretschmer wrote: > Raymond O'Donnell wrote: > > > On 03/10/2012 15:21, Leonardo M. Ramé wrote: > > > I want to upgrade an 8.3-rc1 to 8.3.20 Win32 server (I cannot upgrade to > > > 8.4 nor 9.1 because the client app doesn't support them). The question > > > is, is there a tutorial, or a step-by-step guide to to this?. > > > > The usual way is to pg_dump the old one first, then restore it > > afterwards into the new installation. I don't know, however, if you need > > to do this in your case - generally you don't when moving to a minor > > release of the same major version... the release notes for 8.3.0 should > > say, I'd imagine. > > He is using an RC-Release, so i'm not sure, but i think, he have to go > the long way (Backup & restore) and he can't go the short way (Minor > release update) > > > 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 Thanks, I'll go the long way... -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 351 6629292 -- 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 will not start due to corrupt index
Robert Sosinski writes: > We are running Postgres 9.1.3, and after stopping it by physically shutting > off the machine, we rebooted and now get this error whenever we try to start > it. > 2012-10-02 13:54:30 PDT PANIC: GIN metapage disappeared This looks like an issue that was fixed in 9.1.4. Update and it should start successfully (assuming your machine does fsyncs correctly; otherwise you might have some actual data corruption there ...) 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] Determining that a schema has changed for purpose of monitoring
Hello. Is there a way in PostgreSQL for a client to determine that a database schema may have changed since it checked it earlier, for example to get the transaction ID or timestamp of the last successfully committed transaction that included DDL commands and compare them over time? For a while I've also seen plans and bits of work related to DDL triggers that might help me but don't have track of where this stands today. The background is that I've created a runtime application testing tool and that information would be useful to me, as it's currently manually triggered and I'm looking to explore the possibility of making applications capable of performing non-invasive self-testing with minimal configuration or work needed, at first to the extent of verifying that SQL statements parse and that the types and constraints of their fields don't change in a way that could cause problems for the application. Thanks, Thor Michael Støre -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] pros and cons of two security models
On Wed, Oct 3, 2012 at 6:17 AM, Stephen Frost wrote: > Chris, > > * Chris Travers (chris.trav...@gmail.com) wrote: > > This has a few significant drawbacks. As far as the web application is > > concerned, the types of supported authentication are limited to those > > which are re-usable, which basically means BASIC and KRB5. This maps to > a > > much larger number on the web server to db server tier, but often the web > > server hop is the most exposed one. This is a significant problem. On > the > > other hand it buys us: > > If I'm following along correctly, the issue is that the web server tier > can support many more authentication methods, but LedgerSMB doesn't > support them because it needs an authentication method which can pass > credentials through to the database..? > Well, that's the tradeoff I see. It can be handled using a bunch of different means. One that I have suggested is two-factor auth, where you require a client-side SSL cert with a specific issuing authority and a cn of the username that comes in under basic auth. We don't support that as a matter of course yet, but, the other option is to use kerberos. I guess the what I am wondering is whether some of the pushback we get from some developers is really a different aspect of the "databases should be dumb information stores" mentality, which isn't really the way we are going or if there really are issues here we haven't considered. My general feeling is that centralizing the security in the database means a narrower security perimeter in the areas that matter, and that this mostly comes at the expense of easy multi-tenant hosting. > > The way that I've addressed this issue in the past (which has allowed us > to support client-side SSL-based authentication, RADIUS-based auth > before it was in PG, etc), is to have the web server connect to the > database with a set of fixed credentials, preferably using encryption > and a strong auth method (eg: client-side cert), and then allow the role > which the web server logs in as to 'SET ROLE' to all the user accounts > in the system. By setting up the web server role as 'noinherit' (or > having a role between the web server role and the users which is > 'noinherit), this means that the initial web server connection has no > rights to anything in the database until that 'SET ROLE' is done, and > after that, it only has access to what that role can see. > Ok, that makes sense. I don't think it would be right for us, but it certainly makes sense in the right time and place. > > There are down sides to this, of course. It allows an individual who > can gain sufficient access on the web server to be able to log in as the > web server role and then 'SET ROLE' to any user. When the credentials > have to be passed through the web server, an attacker would have to be > on the system and monitoring when a user logs in, to be able to steal > their credentials, and even they they'd only get the credentials of the > user who logged in during that time. It also increases the risk that an > SQL injection or similar would allow a 'RESET ROLE; SET ROLE' to be > issued by the web server which could change the rights that the web > server is logged in as. I've wondered about a way to address that (eg: > a GUC that essentially disallows a RESET ROLE), but that make it > impossible to use pg_bouncer or similar connection pool in front of PG. > > > 3) Centralized security logic which means consistent access enforced > > through a variety of clients. > > This is certainly the big win for us, but we've settled on > GSSAPI/KRB5 based authentication for our internal systems, which allows > for SSO and one set of user credentials across the environment. For > external systems, we typically require non-admins to go through the web > layer and use the SET ROLE method that I mentioned above and then use > client-side SSL certificates or RADIUS-based hardware-tokens. > Out of curiosity, since you are using krb5/gssapi, why do you go through the set role? Why not just pass krb5 tickets around, since this represents a re-usable auth method itself? > > > Which does everyone else prefer? Why? I am asking because this choice > has > > spawned some controversy around LedgerSMB from time to time, though not > as > > much as being Pg-only at least looking at public and private discussions > of > > the software I have seen. > > It seems like the SET ROLE option would be reasonably straight-forward > to implement and would give people the flexibility they're asking for.. > I would still try to encourage them to use Kerberos-based authentication > which is integrated in with their enterprise systems (eg: Active > Directory), which would provide SSO. It's amazing how often I run into > environments where people are running AD but they have a completely > independent set of user credentials for accessing PG.. > > Yeah. Of course AD integration with anything on Linux is not as simple as it looks, but it sti
[GENERAL] PostgreSQL force create table / ignore constraints?
Version Postgresql 9.1.6 OS: SLES 11 64 bit Background: Our developers create database schema in development environment using PGAdmin (often using the GUI to generate the DDL). We always deploy to production using a script, a single .sql file which we execute via psql command line. This allows us to generate an output with any errors and have good view of deployment history over time. Issue The issue we have is that developers generate the .sql script mainly by copying and pasting from PGAdmin's SQL pane. The issue we have is then the order of the object creation is important otherwise creation of tables and fail when there is a foreign key constraint on another table that does not exist (but is created later in the script). This is not a big deal in a schema with 3 or 4 tables but when there are 20+ it is time consuming task to reorder all the create statements. Can anyone recommend a way of dealing with this? My only other thought has been pg_dump although i would prefer if the developers could generate the scripts themselves. Thanks, Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-force-create-table-ignore-constraints-tp5726469.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] Postgres will not start due to corrupt index
Hey Merlin, Thanks. Starting postgres with -P was something that I did not try. Does postgres have any GIN or GIST system indexes though? I would love to try it out, but the database has already been restored. Will definitely keep this in mind for the future though. Thanks again for the help, -- Robert Sosinski On Wednesday, October 3, 2012 at 10:44 AM, Merlin Moncure wrote: > On Wed, Oct 3, 2012 at 9:33 AM, Robert Sosinski > mailto:rsosin...@ticketevolution.com)> wrote: > > We are running Postgres 9.1.3, and after stopping it by physically shutting > > off the machine, we rebooted and now get this error whenever we try to start > > it. > > > > 2012-10-02 13:54:30 PDT LOG: database system was interrupted; last known up > > at 2012-10-02 13:46:20 PDT > > 2012-10-02 13:54:30 PDT LOG: database system was not properly shut down; > > automatic recovery in progress > > 2012-10-02 13:54:30 PDT LOG: redo starts at A/764C4C0 > > 2012-10-02 13:54:30 PDT PANIC: GIN metapage disappeared > > 2012-10-02 13:54:30 PDT CONTEXT: xlog redo Update metapage, node: > > 1663/899422/21182896 blkno: 2 > > 2012-10-02 13:54:30 PDT LOG: startup process (PID 940) was terminated by > > signal 6: Aborted > > 2012-10-02 13:54:30 PDT LOG: aborting startup due to startup process > > failure > > 2012-10-02 14:42:49 PDT LOG: database system was interrupted while in > > recovery at 2012-10-02 13:54:30 PDT > > 2012-10-02 14:42:49 PDT HINT: This probably means that some data is > > corrupted and you will have to use the last backup for recovery. > > 2012-10-02 14:42:49 PDT LOG: database system was not properly shut down; > > automatic recovery in progress > > 2012-10-02 14:42:49 PDT LOG: redo starts at A/764C4C0 > > 2012-10-02 14:42:49 PDT PANIC: GIN metapage disappeared > > 2012-10-02 14:42:49 PDT CONTEXT: xlog redo Update metapage, node: > > 1663/899422/21182896 blkno: 2 > > 2012-10-02 14:42:49 PDT LOG: startup process (PID 954) was terminated by > > signal 6: Aborted > > 2012-10-02 14:42:49 PDT LOG: aborting startup due to startup process > > failure > > > > I guess it is a problem with an index, because it is saying that there in a > > GIN metapage missing. Any idea how to get postgres to boot up after it gets > > into this condition without having to recover from a backup? Would > > upgrading to 9.2 prevent this issue from happening again? > > > > > You an boot it up in single user mode and force a reindex: > "One way to do this is to shut down the server and start a single-user > PostgreSQL server with the -P option included on its command line. > Then, REINDEX DATABASE, REINDEX SYSTEM, REINDEX TABLE, or REINDEX > INDEX can be issued, depending on how much you want to reconstruct. If > in doubt, use REINDEX SYSTEM to select reconstruction of all system > indexes in the database. Then quit the single-user server session and > restart the regular server. See the postgres reference page for more > information about how to interact with the single-user server > interface." > > (via http://www.postgresql.org/docs/9.2/static/sql-reindex.html) > > Not sure if things have been improved in 9.2 -- historically gist/gin > haven't been as robust in terms of WAL/crash recovery IIRC. > > merlin
Re: [GENERAL] Upgrading 8.3-rc1 to 8.3.20
Raymond O'Donnell wrote: > On 03/10/2012 15:21, Leonardo M. Ramé wrote: > > I want to upgrade an 8.3-rc1 to 8.3.20 Win32 server (I cannot upgrade to > > 8.4 nor 9.1 because the client app doesn't support them). The question > > is, is there a tutorial, or a step-by-step guide to to this?. > > The usual way is to pg_dump the old one first, then restore it > afterwards into the new installation. I don't know, however, if you need > to do this in your case - generally you don't when moving to a minor > release of the same major version... the release notes for 8.3.0 should > say, I'd imagine. He is using an RC-Release, so i'm not sure, but i think, he have to go the long way (Backup & restore) and he can't go the short way (Minor release update) 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] Postgres will not start due to corrupt index
On Wed, Oct 3, 2012 at 9:33 AM, Robert Sosinski wrote: > We are running Postgres 9.1.3, and after stopping it by physically shutting > off the machine, we rebooted and now get this error whenever we try to start > it. > > 2012-10-02 13:54:30 PDT LOG: database system was interrupted; last known up > at 2012-10-02 13:46:20 PDT > 2012-10-02 13:54:30 PDT LOG: database system was not properly shut down; > automatic recovery in progress > 2012-10-02 13:54:30 PDT LOG: redo starts at A/764C4C0 > 2012-10-02 13:54:30 PDT PANIC: GIN metapage disappeared > 2012-10-02 13:54:30 PDT CONTEXT: xlog redo Update metapage, node: > 1663/899422/21182896 blkno: 2 > 2012-10-02 13:54:30 PDT LOG: startup process (PID 940) was terminated by > signal 6: Aborted > 2012-10-02 13:54:30 PDT LOG: aborting startup due to startup process > failure > 2012-10-02 14:42:49 PDT LOG: database system was interrupted while in > recovery at 2012-10-02 13:54:30 PDT > 2012-10-02 14:42:49 PDT HINT: This probably means that some data is > corrupted and you will have to use the last backup for recovery. > 2012-10-02 14:42:49 PDT LOG: database system was not properly shut down; > automatic recovery in progress > 2012-10-02 14:42:49 PDT LOG: redo starts at A/764C4C0 > 2012-10-02 14:42:49 PDT PANIC: GIN metapage disappeared > 2012-10-02 14:42:49 PDT CONTEXT: xlog redo Update metapage, node: > 1663/899422/21182896 blkno: 2 > 2012-10-02 14:42:49 PDT LOG: startup process (PID 954) was terminated by > signal 6: Aborted > 2012-10-02 14:42:49 PDT LOG: aborting startup due to startup process > failure > > I guess it is a problem with an index, because it is saying that there in a > GIN metapage missing. Any idea how to get postgres to boot up after it gets > into this condition without having to recover from a backup? Would > upgrading to 9.2 prevent this issue from happening again? You an boot it up in single user mode and force a reindex: "One way to do this is to shut down the server and start a single-user PostgreSQL server with the -P option included on its command line. Then, REINDEX DATABASE, REINDEX SYSTEM, REINDEX TABLE, or REINDEX INDEX can be issued, depending on how much you want to reconstruct. If in doubt, use REINDEX SYSTEM to select reconstruction of all system indexes in the database. Then quit the single-user server session and restart the regular server. See the postgres reference page for more information about how to interact with the single-user server interface." (via http://www.postgresql.org/docs/9.2/static/sql-reindex.html) Not sure if things have been improved in 9.2 -- historically gist/gin haven't been as robust in terms of WAL/crash recovery IIRC. merlin -- 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] Upgrading 8.3-rc1 to 8.3.20
On 03/10/2012 15:21, Leonardo M. Ramé wrote: > I want to upgrade an 8.3-rc1 to 8.3.20 Win32 server (I cannot upgrade to > 8.4 nor 9.1 because the client app doesn't support them). The question > is, is there a tutorial, or a step-by-step guide to to this?. The usual way is to pg_dump the old one first, then restore it afterwards into the new installation. I don't know, however, if you need to do this in your case - generally you don't when moving to a minor release of the same major version... the release notes for 8.3.0 should say, I'd imagine. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres will not start due to corrupt index
We are running Postgres 9.1.3, and after stopping it by physically shutting off the machine, we rebooted and now get this error whenever we try to start it. 2012-10-02 13:54:30 PDT LOG: database system was interrupted; last known up at 2012-10-02 13:46:20 PDT 2012-10-02 13:54:30 PDT LOG: database system was not properly shut down; automatic recovery in progress 2012-10-02 13:54:30 PDT LOG: redo starts at A/764C4C0 2012-10-02 13:54:30 PDT PANIC: GIN metapage disappeared 2012-10-02 13:54:30 PDT CONTEXT: xlog redo Update metapage, node: 1663/899422/21182896 blkno: 2 2012-10-02 13:54:30 PDT LOG: startup process (PID 940) was terminated by signal 6: Aborted 2012-10-02 13:54:30 PDT LOG: aborting startup due to startup process failure 2012-10-02 14:42:49 PDT LOG: database system was interrupted while in recovery at 2012-10-02 13:54:30 PDT 2012-10-02 14:42:49 PDT HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. 2012-10-02 14:42:49 PDT LOG: database system was not properly shut down; automatic recovery in progress 2012-10-02 14:42:49 PDT LOG: redo starts at A/764C4C0 2012-10-02 14:42:49 PDT PANIC: GIN metapage disappeared 2012-10-02 14:42:49 PDT CONTEXT: xlog redo Update metapage, node: 1663/899422/21182896 blkno: 2 2012-10-02 14:42:49 PDT LOG: startup process (PID 954) was terminated by signal 6: Aborted 2012-10-02 14:42:49 PDT LOG: aborting startup due to startup process failure I guess it is a problem with an index, because it is saying that there in a GIN metapage missing. Any idea how to get postgres to boot up after it gets into this condition without having to recover from a backup? Would upgrading to 9.2 prevent this issue from happening again? Thanks, -- Robert Sosinski
[GENERAL] Upgrading 8.3-rc1 to 8.3.20
I want to upgrade an 8.3-rc1 to 8.3.20 Win32 server (I cannot upgrade to 8.4 nor 9.1 because the client app doesn't support them). The question is, is there a tutorial, or a step-by-step guide to to this?. Can I just decompress the postgresql-8.3.20-1-binaries-no-installer.zip file and just overwrite the 8.3-rc1 directory?. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 351 6629292 -- 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] Securing .pgpass File?
On 10/02/2012 04:19 PM, Martijn van Oosterhout wrote: - Punt. Check in the password but set the access controls so it only work for very few IPs, then you only need to worry about people who can log into *those* machines. Which is controlled by public SSH keys which you can check-in safely. Not super safe, but for read-only accounts for e.g. nagios might be ok. I think this is the right answer for us. :) I'll just go through each specialized user we have now (replication, performance metrics, slony) and make IP->IP locked-down cases for all of them. It just feels wrong, somehow. :) Thanks again, everyone! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Replication err
On 10/03/2012 12:35 AM, Khizer wrote: I am doing streaming replication master-slave with postgresql-9.0.4 , i am getting following err how do i correct this In order for replication to work, the WAL segments necessary for a slave to "catch up" must be available long enough for them to do so. What this error means, is that the slave requested the next WAL segment it needs, but the master server had already recycled it. Either by sending it elsewhere via archive_command, or by deleting it because it was no longer needed. You're going to have to rebuild your slave, unless you can supply it with those missing WAL files. One way to avoid this is to increase wal_keep_segments to a higher number so slaves can lag behind for longer periods of time, without needing to be rebuilt. You should also make sure you have archive_command set, and always back up your old WAL transaction logs, so you can reuse them in cases like this, or for emergency recovery from the backups you're making on a regular basis. So: 1. Rebuild your slave. 2. Increase wal_keep_segments on the master. Start at 500, but try to set it up so there's at least enough for the slave to fall behind by an hour and still able to catch up. 3. Make sure archive_command is set, and regularly back-up / cycle these files for later use in recovery / standby. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- 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] Replication err
Khizer wrote: > Hi, > > I am doing streaming replication master-slave with > postgresql-9.0.4 , i am getting following err > how do i correct this > > > streaming replication successfully connected to primary > 2012-10-03 00:00:06 IST FATAL: could not receive data from WAL stream: > FATAL: requested WAL segment 00010011 has already been > removed Increase wal_keep_segments. Default is IIRC 0, set it, for instance, to 20 or higher. 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] pros and cons of two security models
Chris, * Chris Travers (chris.trav...@gmail.com) wrote: > This has a few significant drawbacks. As far as the web application is > concerned, the types of supported authentication are limited to those > which are re-usable, which basically means BASIC and KRB5. This maps to a > much larger number on the web server to db server tier, but often the web > server hop is the most exposed one. This is a significant problem. On the > other hand it buys us: If I'm following along correctly, the issue is that the web server tier can support many more authentication methods, but LedgerSMB doesn't support them because it needs an authentication method which can pass credentials through to the database..? The way that I've addressed this issue in the past (which has allowed us to support client-side SSL-based authentication, RADIUS-based auth before it was in PG, etc), is to have the web server connect to the database with a set of fixed credentials, preferably using encryption and a strong auth method (eg: client-side cert), and then allow the role which the web server logs in as to 'SET ROLE' to all the user accounts in the system. By setting up the web server role as 'noinherit' (or having a role between the web server role and the users which is 'noinherit), this means that the initial web server connection has no rights to anything in the database until that 'SET ROLE' is done, and after that, it only has access to what that role can see. There are down sides to this, of course. It allows an individual who can gain sufficient access on the web server to be able to log in as the web server role and then 'SET ROLE' to any user. When the credentials have to be passed through the web server, an attacker would have to be on the system and monitoring when a user logs in, to be able to steal their credentials, and even they they'd only get the credentials of the user who logged in during that time. It also increases the risk that an SQL injection or similar would allow a 'RESET ROLE; SET ROLE' to be issued by the web server which could change the rights that the web server is logged in as. I've wondered about a way to address that (eg: a GUC that essentially disallows a RESET ROLE), but that make it impossible to use pg_bouncer or similar connection pool in front of PG. > 3) Centralized security logic which means consistent access enforced > through a variety of clients. This is certainly the big win for us, but we've settled on GSSAPI/KRB5 based authentication for our internal systems, which allows for SSO and one set of user credentials across the environment. For external systems, we typically require non-admins to go through the web layer and use the SET ROLE method that I mentioned above and then use client-side SSL certificates or RADIUS-based hardware-tokens. > Which does everyone else prefer? Why? I am asking because this choice has > spawned some controversy around LedgerSMB from time to time, though not as > much as being Pg-only at least looking at public and private discussions of > the software I have seen. It seems like the SET ROLE option would be reasonably straight-forward to implement and would give people the flexibility they're asking for.. I would still try to encourage them to use Kerberos-based authentication which is integrated in with their enterprise systems (eg: Active Directory), which would provide SSO. It's amazing how often I run into environments where people are running AD but they have a completely independent set of user credentials for accessing PG.. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] Indexing JSON type
Ankur Soni wrote: > Hi, > > I am using PostgreSQL 9.2. I was wondering if anyone has been successful in > indexing JSON data? Is it possible to index all JSON data (keys and values)? http://people.planetpostgresql.org/andrew/index.php?/archives/249-Using-PLV8-to-index-JSON.html 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
[GENERAL] Indexing JSON type
Hi, I am using PostgreSQL 9.2. I was wondering if anyone has been successful in indexing JSON data? Is it possible to index all JSON data (keys and values)? Thanks! Regards, Ankur Soni
[GENERAL] Unable to uninstall completely Postgre SQL
Dear Sir/Mdm, I have problem with uninstalling postgre sql from my windows and as well to reset the service account password. Would you mind assist me in this case? Thank You Regards, Alex Putra
[GENERAL] Replication err
Hi, I am doing streaming replication master-slave with postgresql-9.0.4 , i am getting following err how do i correct this streaming replication successfully connected to primary 2012-10-03 00:00:06 IST FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 00010011 has already been removed Regards Mehdi -- 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] stored procedure multiple call call question
Thanks very much for that David - really appreciate your response - it works like a dream c On Tuesday, 2 October 2012 19:42:59 UTC+1, Chris McDonald wrote: > Hi, > > > > > > If I had a single table targ to insert into I would do an > > > > INSERT INTO targ SELECT thiscol, thatcol, theothercol FROM FOO. > > > > The problem is that I have tables targ1, targ2, targn to insert things into > and a nice stored procedure myproc which does the insertion into all 3 tables > - problem is that I dont see how I can effectively do > > > >INSERT INTO myproc SELECT thiscol, thatcol, theothercol FROM FOO. > > > > The only way I can work out how to do this is with another stored procedure > which allows me to do: > > > >FOR rec IN SELECT thiscol, thatcol, theothercol > >FROM FOO > >LOOP > > PERFORM myproc(rec.thiscol, rec.thatcol, rec.theothercol); > >END LOOP; > > > > But is there a way to do this just in SQL only without resorting to plpgsql > or a language like C/Java? > > > > thanks > > > > c -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pros and cons of two security models
Hi all; I wanted to get opinions of folks who do a lot of this as well. LedgerSMB uses database user accounts and role grants to restrict access of the front-end application. This means: 1) The database does not trust the application. The application has no access to the db in the absence of user-supplied credentials. 2) Permissions are granted on functions and tables, and the application is aware of granted roles, and so can make informed decisions about what options to give the user. This has a few significant drawbacks. As far as the web application is concerned, the types of supported authentication are limited to those which are re-usable, which basically means BASIC and KRB5. This maps to a much larger number on the web server to db server tier, but often the web server hop is the most exposed one. This is a significant problem. On the other hand it buys us: 1) access to every method of authentication Pg supports for non-web apps, and 2) access to every method of password auth that Pg supports for web apps 3) Centralized security logic which means consistent access enforced through a variety of clients. Which does everyone else prefer? Why? I am asking because this choice has spawned some controversy around LedgerSMB from time to time, though not as much as being Pg-only at least looking at public and private discussions of the software I have seen. Best Wishes, Chris Travers
Re: [GENERAL] strange permission error
2. If somebody manages to hijack your connection, you have much worse problems than whether they can read your system catalogs. They can at least copy, and probably modify, your user data. If I have restricted those permissions (i.e. access to specific schemas only, allowing specific operations - like INSERT only on just the tables needed for that particular db user) how would a user, who hijacked the connection, be able to "at least copy, and probably modify user data" then? The catalogs are unlikely to contain anything that's very interesting to an attacker who knows enough about your operations to hijack a connection in the first place. They give a comprehensive information about the entire structure of the database - that, at least to me, is good-enough reason to restrict such an access. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
