Re: [GENERAL] WAL archive on slave
James Sewell wrote: My understanding is that WAL archiving can not be enabled on the slave in a streaming replication pair. It can be enabled. Did you try it? If this is correct, is there a reason behind it? I can see logs showing up in pg_xlog, so could they not be archived? These are files containing the WAL data replicated from the master. They won't be archived. The reason I ask is if this happened it would allow the following with a streaming replication pair (A,B): 1.Start A as master 2.Attach B as slave using basebackup 3.work 4.Promote B to master 5.Restore A from a scheduled backup to a time before promotion 6.Attach A as slave pointing at B's WAL archive If we used A's WAL archive in this case and A had writes after the promotion then we would get timeline errors. You shouldn't with 9.3, because in that case A would follow the timeline switch introduced by B's promotion rather than its old timelime. http://www.postgresql.org/message-id/e1tjcrc-00084r...@gemulon.postgresql.org I may be missing something there since I have never tried it. As far as I can tell, using the WAL archive from B would resolve this issue. That should work in any event. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] client encoding that psql command sets
[CC'ed -hackers]
Tsubasa Sakamoto wrote:
Not sure that it makes a difference but the docs say psql looks at
LC_CTYPE not LANG for Unix systems. You did not say what OS you are
working on though from the examples I am guessing some form of Unix.
The LC_CTYPE environment variable was set up and re-verified.
The result of psql command is following.
[Result]
% setenv LC_CTYPE ja_JP.eucJP
% psql postgres -f test.txt -o result.txt EUC_JP
% psql postgres -f test.txt result.txt
UTF8
% psql postgres -o result.txt test.txt
UTF8
% psql postgres test.txt result.txt
UTF8
Even when a LC_CTYPE environment variable was set up, the result did not
change.
What do you think?
I think that the documentation contradicts the code.
In bin/psql/settings.h:
typedef struct _psqlSettings
{
[...]
boolnotty; /* stdin or stdout is not a tty (as determined
* on startup) */
[...]
} PsqlSettings;
extern PsqlSettings pset;
In bin/psql/command.c and bin/psql/startup.c:
keywords[6] = client_encoding;
values[6] = (pset.notty || getenv(PGCLIENTENCODING)) ? NULL : auto;
That matches your observations: client_encoding=auto is only
used if both stdin and stdout are attached to a tty.
I suggest the attached documentation fix.
Yours,
Laurenz Albe
psql-doc.patch
Description: psql-doc.patch
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Better Connection Statistics
Hi everyone, So, I haven't ever heard of this, but I could be missing something. Is there a module or extension for PostgreSQL keep connection statistics? I don't mean what's in pg_stat_activity, but cumulative. And not like what's in pg_stat_statements, but about the connections themselves. Nor even pg_stat_database, since that's only granular at the database level. For instance, I want the number of transactions a specific connection has submitted. The number of queries. Total amount of CPU time consumed, etc. So far as I know, there is no module, statistic, or view that provides any of this. It occurred to me after one of our NOC guys asked us if a certain machine was spamming us with queries, and I realized I didn't know, and had no way of finding out. The best I can do is see if any are currently, this very second, executing something. If the connection happens to be between transactions when I poll pg_stat_statements, I get nothing. I know pg_pool and pg_bouncer provide info like this, but we don't use those. Is there anything internal to PG that can... eventually get it? -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suite 500 | Chicago IL, 60604 312-676-8870 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] password-less access, without using pg_hba
On 02/06/2014 06:07 PM, Reece Hart wrote: I'd like to provide public access, without a password, to a database hosted on Amazon RDS. I'm familiar with using pg_hba.conf to enable trust (no) authentication for a user. pg_hba.conf is not available to DBAs on RDS. Is there any other way to achieve password-less login in postgresql? I tried alter user password NULL. Ignoring the scary security issues If you can't access pg_hba.conf how about just sticking pgbouncer or similar in the middle and have your users connect through that? Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full Text Index Scanning
I was looking for an answer to the same problem posted a while back (sorry, not sure how to join that thread): Thanks. pg_trgm looks interesting, but after installing the pg_trgm.sql, I get error messages when following the documentation. sggeeorg= create index test_idx on test using gist(columnname gist_trgm_ops); ERROR: operator class gist_trgm_ops does not exist for access method gist STATEMENT: create index test_idx on test using gist(columnname gist_trgm_ops); ERROR: operator class gist_trgm_ops does not exist for access method gist On Sun, Jan 30, 2011 at 10:36 AM, Tom Lane tgl(at)sss(dot)pgh(dot)pa(dot)us wrote: Matt Warner matt(at)warnertechnology(dot)com writes: If I understand this, it looks like this approach allows me to match the beginnings and endings of words, but not the middle sections. Yeah, probably. You might consider using contrib/pg_trgm instead if you need arbitrary substrings. regards, tom lane I just ran into the same problem using pgsql 9.1 on both Windows and Linux. It happened when I tried to create an index on a new table in a new schema of a database that already had the pg_trgm extension loaded. The only way I could get the index to build was to first drop the extension and then re-add it. Once I re-added the extension, I was then able to create the index.
Re: [GENERAL] Better Connection Statistics
O n Fri, 2014-02-07 at 15:00 +, Shaun Thomas wrote:S o, I haven't ever heard of this, but I could be missing something. Is there a module or extension for PostgreSQL keep connection statistics? I don't mean what's in pg_stat_activity, but cumulative. And not like what's in pg_stat_statements, but about the connections themselves. Nor even pg_stat_database, since that's only granular at the database level. For instance, I want the number of transactions a specific connection has submitted. The number of queries. Total amount of CPU time consumed, etc. So far as I know, there is no module, statistic, or view that provides any of this. It occurred to me after one of our NOC guys asked us if a certain machine was spamming us with queries, and I realized I didn't know, and had no way of finding out. The best I can do is see if any are currently, this very second, executing something. If the connection happens to be between transactions when I poll pg_stat_statements, I get nothing. I know pg_pool and pg_bouncer provide info like this, but we don't use those. Is there anything internal to PG that can... eventually get it? -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email Perhaps this might be of use. http://www.postgresql.org/docs/current/static/pgstatstatements.html -- 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] Better Connection Statistics
Perhaps this might be of use. http://www.postgresql.org/docs/current/static/pgstatstatements.html Nope. As I said in the original message, pg_stat_statements only gives query stats for the whole database. What I want to know, is information about each client. Say there's a specific connection from 192.168.1.20. I want to know: * How many queries that connection has executed. * How much CPU time that connection has used since it connected. * How much data was sent to that connection. * How much data that connection sent to the database. And so on. I don't believe that's currently possible. Effectively, it would just be adding a few more columns to pg_stat_activity to track cumulative totals, since it always has the status of all connections. __ 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
[GENERAL] roles inheriting configuration values
I'd like to have join_collapse_limit=20 for all users that belong to a certain group. Is there a way to do that without having to alter all the roles that are in that group? $ psql monkey psql (9.3.1) Type help for help. monkey=# create user f1 login; CREATE ROLE monkey=# create user f2 in role f1 login; CREATE ROLE monkey=# alter role f1 set join_collapse_limit=20; ALTER ROLE $ psql --user f1 monkey psql (9.3.1) Type help for help. monkey= show join_collapse_limit ; join_collapse_limit - 20 (1 row) $ psql --user f2 monkey psql (9.3.1) Type help for help. monkey= show join_collapse_limit ; join_collapse_limit - 8 (1 row)
Re: [GENERAL] Ordering Results by a Supplied Order
On Feb 6, 2014, at 12:57 PM, Gavin Flower wrote: On 07/02/14 05:43, Michael Sacket wrote: On Feb 6, 2014, at 2:23 AM, Vik Fearing wrote: On 02/06/2014 04:16 AM, Michael Sacket wrote: Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be anything really that requires an ordering that can't come from a natural column. Most of the time this involved manipulating a position column from the client application. In any case, I've often found that to be cumbersome, but I think I've come up with a solution that some of you may find useful. Up until 9.4, that's a good way to do it. Starting from 9.4, you can use the WITH ORDINALITY feature. http://www.postgresql.org/docs/devel/static/sql-select.html -- Vik Even better! The development team is always making my work easier in unexpected ways. Thanks! You do realize, that with this new feature, the licence fee for PostgreSQL will dramatically increase? :-) Cheers, Gavin Nope, I missed that. Still says free as far as I can find. :-) I did however find a donate button. I encourage others to find it too! -- 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] roles inheriting configuration values
On 02/07/2014 11:08 AM, Joe Van Dyk wrote: I'd like to have join_collapse_limit=20 for all users that belong to a certain group. Is there a way to do that without having to alter all the roles that are in that group? From what I see in the docs no: http://www.postgresql.org/docs/9.3/interactive/sql-alterrole.html Whenever the role subsequently starts a new session, the specified value becomes the session default, overriding whatever setting is present in postgresql.conf or has been received from the postgres command line. This only happens at login time; executing SET ROLE or SET SESSION AUTHORIZATION does not cause new configuration values to be set. Looks like the settings only apply to the role that logs in. $ psql monkey psql (9.3.1) Type help for help. monkey=# create user f1 login; CREATE ROLE monkey=# create user f2 in role f1 login; CREATE ROLE monkey=# alter role f1 set join_collapse_limit=20; ALTER ROLE $ psql --user f1 monkey psql (9.3.1) Type help for help. monkey= show join_collapse_limit ; join_collapse_limit - 20 (1 row) $ psql --user f2 monkey psql (9.3.1) Type help for help. monkey= show join_collapse_limit ; join_collapse_limit - 8 (1 row) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Better Connection Statistics
On Fri, Feb 7, 2014 at 10:08 AM, Shaun Thomas stho...@optionshouse.comwrote: Perhaps this might be of use. http://www.postgresql.org/docs/current/static/pgstatstatements.html Nope. As I said in the original message, pg_stat_statements only gives query stats for the whole database. What I want to know, is information about each client. Say there's a specific connection from 192.168.1.20. I want to know: * How many queries that connection has executed. * How much CPU time that connection has used since it connected. * How much data was sent to that connection. * How much data that connection sent to the database. And so on. I don't believe that's currently possible. Effectively, it would just be adding a few more columns to pg_stat_activity to track cumulative totals, since it always has the status of all connections. __ 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] Better Connection Statistics
On Fri, Feb 7, 2014 at 2:24 PM, bricklen brick...@gmail.com wrote: On Fri, Feb 7, 2014 at 10:08 AM, Shaun Thomas stho...@optionshouse.comwrote: s I said in the original message, pg_stat_statements only gives query stats for the whole database. What I want to know, is information about each client. Say there's a specific connection from 192.168.1.20. I want to know: * How many queries that connection has executed. * How much CPU time that connection has used since it connected. * How much data was sent to that connection. * How much data that connection sent to the database. And so on. I don't believe that's currently possible. Effectively, it would just be adding a few more columns to pg_stat_activity to track cumulative totals, since it always has the status of all connections. I don't know any tools off-hand, but you might be able to generate partial statistics from the log files with a descriptive log_line_prefix like %m [%p] (user=%u) (db=%d) (rhost=%h) [vxid:%v txid:%x] [%i] . Using the %p and vxi/txid might help to group the queries executed for easier consumption. I don't think that helps much with individual connections though.
Re: [GENERAL] Better Connection Statistics
I don't know any tools off-hand, but you might be able to generate partial statistics from the log files with a descriptive log_line_prefix like %m [%p] (user=%u) (db=%d) (rhost=%h) [vxid:%v txid:%x] [%i] We get 60k queries per second all day long. No way am I turning on query logging to capture the stats I want. :) Last month, I needed to track something down and set log_min_duration_statement to 0, logging everything each connection does. It was only like that for 10 seconds, and I ended up with about 400MB of log output. I shudder to think of what would happen if I left it that way. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suite 500 | Chicago IL, 60604 312-676-8870 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] roles inheriting configuration values
Adrian Klaver-3 wrote On 02/07/2014 11:08 AM, Joe Van Dyk wrote: I'd like to have join_collapse_limit=20 for all users that belong to a certain group. Is there a way to do that without having to alter all the roles that are in that group? From what I see in the docs no: http://www.postgresql.org/docs/9.3/interactive/sql-alterrole.html Whenever the role subsequently starts a new session, the specified value becomes the session default, overriding whatever setting is present in postgresql.conf or has been received from the postgres command line. This only happens at login time; executing SET ROLE or SET SESSION AUTHORIZATION does not cause new configuration values to be set. Looks like the settings only apply to the role that logs in. This does not, by itself, preclude role-inheritance of variable values. It would simply mean that the inheritance resolution routine would only be resolved at logon. Unlike GRANT/REVOKE variable value inheritance has potential for multiple-inheritance resolution ambiguities - which likely increases cost/benefit equation for anyone looking to implement such a feature. There is like a scripting solution to this problem to at least minimize the burden but I do not recall seeing anything already in place that meets this need. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/roles-inheriting-configuration-values-tp5791011p5791036.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
