Re: [GENERAL] Moving from Java 1.5 to Java 1.6
Swayam Prakash Vemuri, 04.10.2012 08:52: Hi We have an application which uses postgresql 7.4.5. Now when we moved to Java 1.6, we are seeing lots of jdbc driver related compilation issues like shown at end of this email. Not only are you using an outdated (and unsupported) PostgreSQL version. You are also moving to a Java version that is soon to be de-supported. Why not move to 1.7 directly? -- 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] Moving from Java 1.5 to Java 1.6
On 10/04/12 11:21 PM, Thomas Kellerer wrote: You are also moving to a Java version that is soon to be de-supported. Java is supported? only if you mean the non-stop stream of updates brought on by web exploit exposures. if you're using Java as a server side application development environment, whats 'supported' mean ? frankly, at least from our perspective, Java 7 has been a no-fly so far. it brings nothing to the table we're interested in, just more Oracle induced nonsense. -- 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] Trajectory of a [Pg] DBA
On Fri, Oct 5, 2012 at 6:44 AM, Thalis Kalfigkopoulos tkalf...@gmail.com wrote: Is it an easier and more common entry point to be a part-time DBA e.g. perform DBA duties as part of being a U**X sysadmin? Is it more common to start as a developer and change focus to DBA? In particular how does one go about starting as a Pg DBA? Is the most common case by migrating from another DBMS? I work for a (very) small company, so by nature we're all generalists. I basically got the DBA job charlieocratically[1] - that is, I was pushed into the position, having been the primary apologist/evangelist for Postgres. My main job is software developer, but of the team who write database access code, I'm the one who generally (a) is the go-to guy for schema advice, and (b) gets asked to ensure that the database will perform adequately under crazy load on crazy hardware. (And just FYI, everything said on this list about Amazon EC2/EBS performance is right; a mid-range Dell laptop can outperform an Amazon Micro instance, and the fatter instances still don't do all that well, bang-for-buck.) Be good at databasing, then get any sort of IT job, and you'll likely end up being or helping the DBA. [1] See my blog for a definition of that term: http://rosuav.blogspot.com.au/2011/11/world-is-full-of-charlieocracies.html ChrisA -- 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] Moving from Java 1.5 to Java 1.6
John R Pierce, 05.10.2012 08:34: You are also moving to a Java version that is soon to be de-supported. Java is supported? only if you mean the non-stop stream of updates brought on by web exploit exposures. Yes it is. In a similar way as PostgreSQL is supported. if you're using Java as a server side application development environment, whats 'supported' mean ? There are versions where Oracle will apply bugfixes and there are version where they will not. It's not only about security fixes for the Browser plugin-in (which I frankly think is totally useless), but there are also fixes to the whole JDK and Java API which could well affect server apps. Think IPv6 - AFAIK that will not be available for older Java versions. frankly, at least from our perspective, Java 7 has been a no-fly so far. it brings nothing to the table we're interested in, just more Oracle induced nonsense. I beg to differ. There are some new features in the language that are really nice. And also several new technologies will not be available on the old Java platforms (think Tomcat 7, JavaEE 6 and so on) The same way you should stay up-to-date with a PostgreSQL release you should with your Java development environment (even if you don't really need or use the new features) -- 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] Moving from Java 1.5 to Java 1.6
John R Pierce, 05.10.2012 08:34: On 10/04/12 11:21 PM, Thomas Kellerer wrote: You are also moving to a Java version that is soon to be de-supported. Java is supported? Found the link as well: http://www.oracle.com/technetwork/java/javase/eol-135779.html Quote: After February 2013, Oracle will no longer post updates of Java SE 6 to its public download sites -- 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] Moving from Java 1.5 to Java 1.6
On 10/04/2012 02:52 PM, Swayam Prakash Vemuri wrote: Hi We have an application which uses postgresql 7.4.5. Wow. Upgrade time on two levels: - You're running a fossilized point release. The latest 7.4.x is 7.4.30, 25 patch-levels after yours, and came out in October 2010 vs the August 2004 (!!) release date of your version. You're missing SIX YEARS OF BUG FIXES. - 7.4.x is unsupported. No future releases will be made with bug fixes or compatibility improvements. Upgrade urgently, because it's only going to get more difficult. Read the release notes from every .0 version between 7.4 and 9.1 or 9.2 to find possible compatibility issues you may face. Pay particular attention to the bytea_output change (if you use bytea), to standard_conforming_strings, and to the removal of implicit casts to text. Now when we moved to Java 1.6, we are seeing lots of jdbc driver related compilation issues like shown at end of this email. You shouldn't need to compile PgJDBC; there are pre-built binaries provided for JDBC3 and JDBC4 versions of the platform. You haven't said which version of PgJDBC you are updating *from* or *to*, making it hard to help you. The latest PgJDBC should be fine in Java 1.5, 1.6 or 1.7 and should work against Pg 8.3 or newer, and may also work on older versions of Pg as well. There's code in there for 7.3 and older. You cannot reasonably expect an ancient PgJDBC to compile on a new Java, or a new PgJDBC on a new Java to support a truly ancient Pg like 7.4. -- 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] Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?
On 5 October 2012 04:53, Moshe Jacobson mo...@neadwerx.com wrote: On Thu, Oct 4, 2012 at 6:12 AM, Ivan Voras ivo...@freebsd.org wrote: On 01/10/2012 15:36, Moshe Jacobson wrote: I am working on an audit logging trigger that gets called for every row inserted, updated or deleted on any table. For this, I need to store a couple of temporary session variables such as the ID of the user performing the change, which can be set at the start of the session. Do you know about session variables? The major benefit here is that it doesn't touch the table engines, temporary or not. This sounds incredibly useful. Why have I not heard of this until today?? In your example you still had to use a BEGIN...EXCEPTION block. Is that faster than a create temp table? I think I can make a fairly educated guess that catching exceptions while dealing with session variables should be much, much faster than creating any kind of a table :) Besides, from what you said, you will ensure on the app level that the session variable is set sometime close to when you open a connection to Pg, so the catch part of the exception block will probably not run at all. -- 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] Trajectory of a [Pg] DBA
On Thu, Oct 04, 2012 at 05:44:54PM -0300, Thalis Kalfigkopoulos wrote: I see that most of the DBA job posts ask for Sr or Ssr which is understandable given that databases are among a company’s most valuable assets, but it is also an obvious catch-22. So I'd like to ask the list's part- and full-time DBAs, if it's not too personal, how they landed their jobs. Is it an easier and more common entry point to be a part-time DBA e.g. perform DBA duties as part of being a U**X sysadmin? That's an excellent way to become experienced in any database system, but particularly Postgres. I'm not a DBA these days at all, but I guess I was a fairly senior one when I still had that sort of job. I landed my job (at what became Afilias) by having a clue what a Postgres was. Before that, I'd worked on some projects in other jobs where I'd used Postgres, so I had a little bit of knowledge about how the system worked, and I had a reasonable depth of knoweledge about how its environment worked. It's this combination that is rare. A difficult thing about hiring DBAs to work on Postgres, I found, is the frequency with which people with database backgrounds want the database engine to be in charge of everything. Postgres is really dependent on the services of the underlying operating system in a way that many other comparable RDBMSes are not (or try not to be). It's this sort of understanding of the way multiple parts of the system can work together that I was always looking for in a hire. I usually had to hire people who'd mostly worked with other RDBMSes, but who liked Postgres for some reason and could tell me why. I never used automatic tools to match employees to my job descriptions, however, because I thought that they depended too much on keywords. It's easy to use keyword whittling on Oracle DBA hires: you just look for a magic number of years and the right certification, and you probably have a competent (but likely not stellar) candidate. If you try to find people with 5 years' Postgres experience, well, come to this list :) The shallow pool of qualified Postgres admins remains one of the costs of using Postgres today: you add cost to your administration. I think the cost is worth it, note. Hope this is helpful. Good luck, A -- Andrew Sullivan a...@crankycanuck.ca -- 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] Trajectory of a [Pg] DBA
On 10/04/2012 03:44 PM, Thalis Kalfigkopoulos wrote: Is it more common to start as a developer and change focus to DBA? In particular how does one go about starting as a Pg DBA? Is the most common case by migrating from another DBMS? You've already gotten a bunch of good responses from this thread, but I figured I could add an anecdote or two of my own, since I didn't even really know what a database was when I graduated in 1999. You're probably going to get this story a lot, but quite a few of us started as plain old developers. Not every company has a budget for a database department, so it's not infrequent for a dev who's working on a database-driven app to take over care and feeding of the database itself. In 2001, that's exactly what I did with our PostgreSQL and MySQL databases. And I got myself into a lot of trouble fairly often. Not just because PostgreSQL 6.5 would crash at the drop of a hat and corrupt data on its way down, but because I only knew UNIX from a user's perspective. The other responders are right, that to be a good DBA, you have to be something of a Jack of All Trades. One of those trades in a UNIX environment, is that it doesn't hurt to be at least junior-level as a systems administrator. So like many here, I picked that up out of necessity. And like many here, I didn't stop at just keeping the database alive, but learning more about it. It wasn't long before I noticed there was no reindex script, and it was badly needed in those days. So I wrote one and contributed it. That utility has long since been deprecated, but one of the reasons we turn down so many applicants, is that they're point-and-click DBAs with little to no understanding of what their tools actually do. And this extends into modeling, reporting, query optimization, and any DBA related field you can imagine. The more critical the database, the higher the TPS or larger the size, the more important it is to understand the why as much as the how. This is one good reason a lot of people here are glad to have Greg Smith's book on PostgreSQL performance. For the first time in a long time, there was a good resource to point to, and say, That's a good place to start. And it is. But it's not the end goal. That's what we look for in potential DBAs. We've turned down countless senior-level DBAs because they interviewed as complacent, or button-pushers who couldn't operate outside of a tool. But on the same token, we've hired basic newbies who may have only had exposure in SQL Server and never even held a role as a DBA, because they displayed an ability to understand as opposed to regurgitate. I've found we're not exactly unique in that approach. Having a good resume focused on relevant DBA exposure certainly helps, but it's not critical if a candidate can prove they're adaptable and won't crack under pressure. I actually kinda like how Cisco used to (and may still?) test for their higher level certificates. Put the candidates in a room, and break some random configuration or hardware element of the network gear. Watch their approach to fixing it. I wish there was some kind of industry standard DBA equivalent. :) -- 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] Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?
On Fri, Oct 5, 2012 at 3:09 AM, Ivan Voras ivo...@freebsd.org wrote: I think I can make a fairly educated guess that catching exceptions while dealing with session variables should be much, much faster than creating any kind of a table :) That is true, but it's not clear how using session variables keeps you from having to create the table. If the table is already there, a session variable guarding the table construction shouldn't be any faster/better than a simple 'create if not exists'. A catalog scan is basically boils down to a query. So I'm not sure that actually helps. On the other hand, if the temp table can be completely ditched for a session variable or two, then yeah, that would be much better since you'd avoid the overhead of creating the table completely. 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] Counting all results before LIMIT
We have a PHP web application that pulls results from the database and paginates them. We show e.g. 1-50 of 300 so the user knows how many total results there are, and which ones are currently being displayed. To achieve this, we use a query with LIMIT...OFFSET to get the displayed results, and we do another identical query using count(*) to get the total count. Is there a more efficient way to do this that does not require us to do two queries? I just feel that it's a waste of resources the way we do it. Thanks! -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com
[GENERAL] Add a stemmer for fts
Hello. I was trying to find a stemmer for greek but it isnt supported out of the box. Dictionaries dont help at all because they just limit the words they do nothing useful for my problem. I found several stemmers in php / java etc from lucene but i am not sure how i can use it with postgresql. I guess i can use PL/Java PL/php but seems difficult to compile it under windows and production is linux 8.4 version.. Do you have any solution idea for my problem ?
Re: [GENERAL] Counting all results before LIMIT
You cant. There is an article about count performance. Generally its slow but latest version 9.2 i think supports index for count under some condition.But 300 isnt that much that you should worry.Another modern solution is to not count results just add one more at limit to see if there is next page. On Fri, Oct 5, 2012 at 5:29 PM, Moshe Jacobson mo...@neadwerx.com wrote: We have a PHP web application that pulls results from the database and paginates them. We show e.g. 1-50 of 300 so the user knows how many total results there are, and which ones are currently being displayed. To achieve this, we use a query with LIMIT...OFFSET to get the displayed results, and we do another identical query using count(*) to get the total count. Is there a more efficient way to do this that does not require us to do two queries? I just feel that it's a waste of resources the way we do it. Thanks! -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com
Re: [GENERAL] Counting all results before LIMIT
You could use a windowing function. Something like: SELECT x, y, z, COUNT(*) OVER() FROM Foo LIMIT 50; On Fri, Oct 5, 2012 at 8:02 AM, P Gouv kad...@gmail.com wrote: You cant. There is an article about count performance. Generally its slow but latest version 9.2 i think supports index for count under some condition.But 300 isnt that much that you should worry.Another modern solution is to not count results just add one more at limit to see if there is next page. On Fri, Oct 5, 2012 at 5:29 PM, Moshe Jacobson mo...@neadwerx.com wrote: We have a PHP web application that pulls results from the database and paginates them. We show e.g. 1-50 of 300 so the user knows how many total results there are, and which ones are currently being displayed. To achieve this, we use a query with LIMIT...OFFSET to get the displayed results, and we do another identical query using count(*) to get the total count. Is there a more efficient way to do this that does not require us to do two queries? I just feel that it's a waste of resources the way we do it. Thanks! -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com
Re: [GENERAL] Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?
On 5 October 2012 15:55, Merlin Moncure mmonc...@gmail.com wrote: On Fri, Oct 5, 2012 at 3:09 AM, Ivan Voras ivo...@freebsd.org wrote: I think I can make a fairly educated guess that catching exceptions while dealing with session variables should be much, much faster than creating any kind of a table :) On the other hand, if the temp table can be completely ditched for a session variable or two, then yeah, that would be much better since you'd avoid the overhead of creating the table completely. Yes, this is what I was aiming at, based on the OP mentioning he only has a limited amount of data to manage in this way. -- 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] Re: What's faster? BEGIN ... EXCEPTION or CREATE TEMP TABLE IF NOT EXISTS?
On Fri, Oct 5, 2012 at 11:13 AM, Ivan Voras ivo...@freebsd.org wrote: On the other hand, if the temp table can be completely ditched for a session variable or two, then yeah, that would be much better since you'd avoid the overhead of creating the table completely. Yes, this is what I was aiming at, based on the OP mentioning he only has a limited amount of data to manage in this way. Yup, I'm going to see how this goes using session variables. I think it is exactly what I need. Thanks! -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com
Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer
On Thu, Oct 4, 2012 at 2:50 AM, Wolf Schwurack w...@uen.org wrote:
I use pgpool but some of the problem you listed are same as I had with pgpool
Thanks Wolf, for the thoughts.
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.
OK, but this is not a showstopper here. Right?
/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.
Permissions:
/var/run/pgbouncer --
70058074 drwxr-xr-x 2 pgbouncer postgres 4.0K Oct 2 06:17 pgbouncer/
/var/log --
145686529 drwxr-xr-x 17 root root 4.0K Oct 5 04:29 log/
Please note that whatever the settings, they were working before a
server reboot. What settings do I need to give /var/log (currently
root) so the pgbouncer process can write to it? Why are these special
permissions needed-- I mean Apache, MysQL, Nginx etc...all of them can
write to the logs in this log folder.
Psql: ERROR: No such user:
You have to create the user in postgres, check you users
postgres=# /du
Yes, this user exists in the postgres database.
List of roles
Role name|Attributes | Member of
-+---+---
postgres| Superuser, Create role, Create DB | {}
rvadmin | | {}
MYSITE | | {}
MYSITE_MYSITE | Superuser, Create DB | {}
And the authfile also has permissions for pgbouncer:postgres.
What else?
--
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] Counting all results before LIMIT
Em 05/10/2012 12:17, Mike Christensen escreveu: You could use a windowing function. Something like: SELECT x, y, z, COUNT(*) OVER() FROM Foo LIMIT 50; Good to know! I'll give a try! On Fri, Oct 5, 2012 at 8:02 AM, P Gouv kad...@gmail.com mailto:kad...@gmail.com wrote: You cant. There is an article about count performance. Generally its slow but latest version 9.2 i think supports index for count under some condition.But 300 isnt that much that you should worry.Another modern solution is to not count results just add one more at limit to see if there is next page. I've used two queries for 100 000 (with filters applied - table has 1 800 000 records), and is very acceptable (200ms with 8Gb and Xeon dual core). Edson. On Fri, Oct 5, 2012 at 5:29 PM, Moshe Jacobson mo...@neadwerx.com mailto:mo...@neadwerx.com wrote: We have a PHP web application that pulls results from the database and paginates them. We show e.g. 1-50 of 300 so the user knows how many total results there are, and which ones are currently being displayed. To achieve this, we use a query with LIMIT...OFFSET to get the displayed results, and we do another identical query using count(*) to get the total count. Is there a more efficient way to do this that does not require us to do two queries? I just feel that it's a waste of resources the way we do it. Thanks! -- Moshe Jacobson Nead Werx, Inc. | Senior Systems Engineer 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com mailto:mo...@neadwerx.com | www.neadwerx.com http://www.neadwerx.com/
[GENERAL] pg_dump problem
I'm trying to migrate a PostgreSql 8.3 database from a Windows 2003 server to a PostgreSql 8.4 Linux x86_64 server running Ubuntu Server 12.04. When running pg_dump from the Linux server, I get: postgres@ubuntupostgresql:~$ pg_dump -h 192.168.10.105 -U postgres ris pg_dump: SQL command failed pg_dump: Error message from server: ERROR: unrecognized configuration parameter synchronize_seqscans pg_dump: The command was: SET synchronize_seqscans TO off I've read in older mails that this massage shows when one side is running EnterpriseDB version of PostgreSql and the other is running a PostgreSql version. I must point out this is not my case, both sides are running PostgreSql versions, the Linux side is running the one from the official repository, and the Windows one is running an old 8.3 version downloaded from postgresql.org. Any hint?. 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: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer
-- OK, but this is not a showstopper here. Right?
Your right - just a thought
-- What settings do I need to give /var/log (currently root) so the pgbouncer
process can write to it? Why are these special permissions needed
You need to have a pgbouner directory in /var/log and have the owner pgbouncer.
This is easy to test try creating a file in /var/log as the user pgbouncer. It
should fail because pgbouncer does not have writer permissions to /var/log. As
root create a directory /var/log/pgbouncer, change owner to pgbouncer. Set your
parameter for pgbouncer.log to /var/log/pgbouncer. Then test by creating a file
in /var/log/pgbouncer as user pgbouncer
If the user exists in the postgres then I'm not sure why it fails.
Wolf
-Original Message-
From: Phoenix Kiula [mailto:phoenix.ki...@gmail.com]
Sent: Friday, October 05, 2012 9:37 AM
To: Wolf Schwurack
Cc: raghu ram; pgbouncer-gene...@pgfoundry.org; PG-General Mailing List
Subject: Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer
On Thu, Oct 4, 2012 at 2:50 AM, Wolf Schwurack w...@uen.org wrote:
I use pgpool but some of the problem you listed are same as I had with
pgpool
Thanks Wolf, for the thoughts.
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.
OK, but this is not a showstopper here. Right?
/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.
Permissions:
/var/run/pgbouncer --
70058074 drwxr-xr-x 2 pgbouncer postgres 4.0K Oct 2 06:17 pgbouncer/
/var/log --
145686529 drwxr-xr-x 17 root root 4.0K Oct 5 04:29 log/
Please note that whatever the settings, they were working before a server
reboot. What settings do I need to give /var/log (currently
root) so the pgbouncer process can write to it? Why are these special
permissions needed-- I mean Apache, MysQL, Nginx etc...all of them can write to
the logs in this log folder.
Psql: ERROR: No such user:
You have to create the user in postgres, check you users
postgres=# /du
Yes, this user exists in the postgres database.
List of roles
Role name|Attributes | Member of
-+---+---
postgres| Superuser, Create role, Create DB | {}
rvadmin | | {}
MYSITE | | {}
MYSITE_MYSITE | Superuser, Create DB | {}
And the authfile also has permissions for pgbouncer:postgres.
What else?
--
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_dump problem
Leonardo =?iso-8859-1?Q?M=2E_Ram=E9?= l.r...@griensu.com writes: I'm trying to migrate a PostgreSql 8.3 database from a Windows 2003 server to a PostgreSql 8.4 Linux x86_64 server running Ubuntu Server 12.04. When running pg_dump from the Linux server, I get: postgres@ubuntupostgresql:~$ pg_dump -h 192.168.10.105 -U postgres ris pg_dump: SQL command failed pg_dump: Error message from server: ERROR: unrecognized configuration parameter synchronize_seqscans pg_dump: The command was: SET synchronize_seqscans TO off An 8.3 server certainly ought to accept that command. I've read in older mails that this massage shows when one side is running EnterpriseDB version of PostgreSql and the other is running a PostgreSql version. I must point out this is not my case, both sides are running PostgreSql versions, the Linux side is running the one from the official repository, and the Windows one is running an old 8.3 version downloaded from postgresql.org. What old 8.3 version is that? A pre-release snapshot perhaps? After looking through the commit history, it appears that this setting was exposed post-beta, which means your results would fit with running an 8.3 beta or RC release. Leaving aside the wisdom of still being on a beta release four years later, my advice would be to dump with the pg_dump that came with the beta release. 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] pg_dump problem
- Mensaje original - Fecha: Fri, 05 Oct 2012 17:30:15 -0400 De: Tom Lane t...@sss.pgh.pa.us Para: Leonardo M. Ramé l.r...@griensu.com Asunto: Re: [GENERAL] pg_dump problem Copia: PostgreSql-general pgsql-general@postgresql.org Leonardo =?iso-8859-1?Q?M=2E_Ram=E9?= l.r...@griensu.com writes: I'm trying to migrate a PostgreSql 8.3 database from a Windows 2003 server to a PostgreSql 8.4 Linux x86_64 server running Ubuntu Server 12.04. When running pg_dump from the Linux server, I get: postgres@ubuntupostgresql:~$ pg_dump -h 192.168.10.105 -U postgres ris pg_dump: SQL command failed pg_dump: Error message from server: ERROR: unrecognized configuration parameter synchronize_seqscans pg_dump: The command was: SET synchronize_seqscans TO off An 8.3 server certainly ought to accept that command. I've read in older mails that this massage shows when one side is running EnterpriseDB version of PostgreSql and the other is running a PostgreSql version. I must point out this is not my case, both sides are running PostgreSql versions, the Linux side is running the one from the official repository, and the Windows one is running an old 8.3 version downloaded from postgresql.org. What old 8.3 version is that? A pre-release snapshot perhaps? After looking through the commit history, it appears that this setting was exposed post-beta, which means your results would fit with running an 8.3 beta or RC release. Leaving aside the wisdom of still being on a beta release four years later, my advice would be to dump with the pg_dump that came with the beta release. regards, tom lane Thanks!, that was exactly the problem. The old server is running 8.3-rc 1, so, I'll back it up using pg_dump from the old server, then restore from the new one. Leonardo.
Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer
On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack w...@uen.org wrote: You need to have a pgbouner directory in /var/log and have the owner pgbouncer. This is easy to test try creating a file in /var/log as the user pgbouncer. It should fail because pgbouncer does not have writer permissions to /var/log. As root create a directory /var/log/pgbouncer, change owner to pgbouncer. Set your parameter for pgbouncer.log to /var/log/pgbouncer. Then test by creating a file in /var/log/pgbouncer as user pgbouncer Wolf, I think you missed the earlier posts in this thread. The /var/log/pgbouncer.log already has those permissions. Note this important fact: the same permissions have been working for nearly 2 years. Anyway, I created a directory: /var/log/pgbouncer/, put the pgbouncer.log file in it. chown -R pgbouncer:postgres /var/log/pgbouncer chown pgbouncer:postgres /var/log/pgbouncer/pgbouncer.log chmod 777 /var/log/pgbouncer/pgbouncer.log As was already happening, pgbouncer starts. No problem. It's now that I cannot connect to PSQL via pgbouncer (of course I can connect to psql directly) because it fails with this error: psql: ERROR: No such user: MYSITE_MYSITE Which is weird, because that user does exist. Both inside the postgres database when I do \du as you suggested, and of course in the pgbouncer authfile too -- chown pgbouncer:postgres /var/lib/pgsql/pgbouncer.txt cat /var/lib/pgsql/pgbouncer.txt MYSITE_MYSITE md5 pass MYSITE_MYSITE raw pass postgres md5fd6313191fec7887f88c31a85c43df21 So now. What? Why is this otherwise very useful tool coded so poorly that there's reams of such permissions and all of these threads online? Would love to have some help or guidance. Thanks. -- 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
On 10/05/2012 07:00 PM, Phoenix Kiula wrote: On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack w...@uen.org wrote: You need to have a pgbouner directory in /var/log and have the owner pgbouncer. This is easy to test try creating a file in /var/log as the user pgbouncer. It should fail because pgbouncer does not have writer permissions to /var/log. As root create a directory /var/log/pgbouncer, change owner to pgbouncer. Set your parameter for pgbouncer.log to /var/log/pgbouncer. Then test by creating a file in /var/log/pgbouncer as user pgbouncer Wolf, I think you missed the earlier posts in this thread. The /var/log/pgbouncer.log already has those permissions. Note this important fact: the same permissions have been working for nearly 2 years. Anyway, I created a directory: /var/log/pgbouncer/, put the pgbouncer.log file in it. chown -R pgbouncer:postgres /var/log/pgbouncer chown pgbouncer:postgres /var/log/pgbouncer/pgbouncer.log chmod 777 /var/log/pgbouncer/pgbouncer.log As was already happening, pgbouncer starts. No problem. It's now that I cannot connect to PSQL via pgbouncer (of course I can connect to psql directly) because it fails with this error: psql: ERROR: No such user: MYSITE_MYSITE Which is weird, because that user does exist. Both inside the postgres database when I do \du as you suggested, and of course in the pgbouncer authfile too -- chown pgbouncer:postgres /var/lib/pgsql/pgbouncer.txt cat /var/lib/pgsql/pgbouncer.txt MYSITE_MYSITE md5 pass MYSITE_MYSITE raw pass postgres md5fd6313191fec7887f88c31a85c43df21 So now. What? Why is this otherwise very useful tool coded so poorly that there's reams of such permissions and all of these threads online? Would love to have some help or guidance. What are the contents of your pgbouncer.ini file? Thanks. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer
On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver adrian.kla...@gmail.com wrote: ...snip... What are the contents of your pgbouncer.ini file? Thanks Adrian. I mentioned the full ini file details above in the thread, but here they are again. (Please do not comment about port numbers. This is a public list so I change the numbers, but they are very much on track everywhere they need to be.) Thanks for any pointers... [databases] * = host=127.0.0.1 port=5432 [pgbouncer] listen_addr = * listen_port = 6389 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt logfile = /var/log/pgbouncer/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid admin_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer stats_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer,stats,root pool_mode = transaction server_reset_query = DISCARD ALL; server_check_query = select 1 server_check_delay = 10 max_client_conn = 800 default_pool_size = 20 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 unix_socket_dir = /tmp ignore_startup_parameters = application_name -- 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
On 10/05/2012 07:23 PM, Phoenix Kiula wrote: On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver adrian.kla...@gmail.com wrote: ...snip... What are the contents of your pgbouncer.ini file? Thanks Adrian. I mentioned the full ini file details above in the thread, but here they are again. (Please do not comment about port numbers. This is a public list so I change the numbers, but they are very much on track everywhere they need to be.) Thanks for any pointers... [databases] * = host=127.0.0.1 port=5432 One thing I see above: http://pgbouncer.projects.postgresql.org/doc/config.html \* acts as fallback database Notice the backslash. [pgbouncer] listen_addr = * listen_port = 6389 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt logfile = /var/log/pgbouncer/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid admin_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer stats_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer,stats,root pool_mode = transaction server_reset_query = DISCARD ALL; server_check_query = select 1 server_check_delay = 10 max_client_conn = 800 default_pool_size = 20 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 unix_socket_dir = /tmp ignore_startup_parameters = application_name -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer
On Sat, Oct 6, 2012 at 10:24 AM, Adrian Klaver adrian.kla...@gmail.com wrote: One thing I see above: http://pgbouncer.projects.postgresql.org/doc/config.html \* acts as fallback database Notice the backslash. Ok, but: (1) The exact same INI file was working so far. (2) Why do I need a fallback database? I want to be precise about database names if possible. (3) I did try and change the config to have the backslash, but when I restart, I get this error: 2012-10-05 22:30:06.882 27442 ERROR syntax error in configuration (/etc/pgbouncer/pgbouncer.ini:2), stopping loading Now? -- 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
On 10/05/2012 07:30 PM, Phoenix Kiula wrote: On Sat, Oct 6, 2012 at 10:24 AM, Adrian Klaver adrian.kla...@gmail.com wrote: One thing I see above: http://pgbouncer.projects.postgresql.org/doc/config.html \* acts as fallback database Notice the backslash. Ok, but: (1) The exact same INI file was working so far. (2) Why do I need a fallback database? I want to be precise about database names if possible. So why was a database name not specified? (3) I did try and change the config to have the backslash, but when I restart, I get this error: 2012-10-05 22:30:06.882 27442 ERROR syntax error in configuration (/etc/pgbouncer/pgbouncer.ini:2), stopping loading Well it did not like that. Now? What is the connection string you are using to make the connection to pPgbouncer? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [Pgbouncer-general] [GENERAL] Again, problem with pgbouncer
On Sat, Oct 6, 2012 at 10:07 AM, Adrian Klaver adrian.kla...@gmail.com wrote: On 10/05/2012 07:00 PM, Phoenix Kiula wrote: On Sat, Oct 6, 2012 at 12:01 AM, Wolf Schwurack w...@uen.org wrote: You need to have a pgbouner directory in /var/log and have the owner pgbouncer. This is easy to test try creating a file in /var/log as the user pgbouncer. It should fail because pgbouncer does not have writer permissions to /var/log. As root create a directory /var/log/pgbouncer, change owner to pgbouncer. Set your parameter for pgbouncer.log to /var/log/pgbouncer. Then test by creating a file in /var/log/pgbouncer as user pgbouncer Wolf, I think you missed the earlier posts in this thread. The /var/log/pgbouncer.log already has those permissions. Note this important fact: the same permissions have been working for nearly 2 years. Anyway, I created a directory: /var/log/pgbouncer/, put the pgbouncer.log file in it. chown -R pgbouncer:postgres /var/log/pgbouncer chown pgbouncer:postgres /var/log/pgbouncer/pgbouncer.log chmod 777 /var/log/pgbouncer/pgbouncer.log As was already happening, pgbouncer starts. No problem. It's now that I cannot connect to PSQL via pgbouncer (of course I can connect to psql directly) because it fails with this error: psql: ERROR: No such user: MYSITE_MYSITE Which is weird, because that user does exist. Both inside the postgres database when I do \du as you suggested, and of course in the pgbouncer authfile too -- chown pgbouncer:postgres /var/lib/pgsql/pgbouncer.txt cat /var/lib/pgsql/pgbouncer.txt MYSITE_MYSITE md5 pass MYSITE_MYSITE raw pass postgres md5fd6313191fec7887f88c31a85c43df21 So now. What? Why is this otherwise very useful tool coded so poorly that there's reams of such permissions and all of these threads online? Would love to have some help or guidance. What are the contents of your pgbouncer.ini file? Mentioned above in the thread, but here they are again. Please do not comment about port numbers. This is a public list so I change the numbers, but they are very much on track everywhere they need to be. Thanks for any pointers. [databases] * = host=127.0.0.1 port=5432 [pgbouncer] listen_addr = * listen_port = 6389 auth_type = trust auth_file = /var/lib/pgsql/pgbouncer.txt logfile = /var/log/pgbouncer/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid admin_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer stats_users = postgres,MYSITE_MYSITE,MYSITE_pgbouncer,stats,root pool_mode = transaction server_reset_query = DISCARD ALL; server_check_query = select 1 server_check_delay = 10 max_client_conn = 800 default_pool_size = 20 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 unix_socket_dir = /tmp ignore_startup_parameters = application_name -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] CTE materializing sets?
Hi, If I run a CTE does that materialize the resulting data in the same (or a similar) way as if I created a temp table and referred to that instead? Or does the CTE keep the set in memory? Regards Liam
