Re: [GENERAL] Setup pgpool-II with streaming replication

On 9/6/2016 2:10 PM, Ashish Chauhan wrote: ...Cannot afford downtime on db servers... thats an impossible target in general unless you expect computer systems to run perfectly forever without ever needing hardware or software maintenance.there are strategies to /minimize/ downtime, such a

Re: [GENERAL] Setup pgpool-II with streaming replication

I gone through below articles but some or other way require DB server > restart which is not possible in our case. Could you please provide some > documentation or steps how to achive connection pooling without any > downtime? > DB server restart ? Why do you think you need to restart the database

Re: [GENERAL] What limits Postgres performance when the whole database lives in cache?

> > I don't think this is quite true. The mechanism he proposes has a > small window in which committed transactions can be lost, and this > should be addressed by replication or by a small amount of UPC (a few > seconds). > > Except that's the entire point where all those kind of solutions > *com

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

2016-09-08 11:49 GMT+12:00 Jim Nasby : > Please include the mailing list in replies... > > On 9/7/16 6:10 PM, David Gibbons wrote: > >> That is NOT safe. The problem is it allows rsync to use mtime alone >> to decide that a file is in sync, and that will fail if Postgres >> writes to a

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

Please include the mailing list in replies... On 9/7/16 6:10 PM, David Gibbons wrote: That is NOT safe. The problem is it allows rsync to use mtime alone to decide that a file is in sync, and that will fail if Postgres writes to a file in the same second that the first rsync reads fr

Re: [GENERAL] What limits Postgres performance when the whole database lives in cache?

On 9/2/16 7:39 PM, dandl wrote: I don't think this is quite true. The mechanism he proposes has a small window in which committed transactions can be lost, and this should be addressed by replication or by a small amount of UPC (a few seconds). Except that's the entire point where all those k

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On 09/07/2016 04:25 PM, Jim Nasby wrote: On 9/7/16 6:07 PM, Ken Tanzer wrote: ERROR: PL/Python functions cannot accept type record Ugh, yeah... that won't work. plperl might be able to do it, but I suspect you're going to be stuck pulling the size info out of info_schema or the catalog. Actu

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On 9/7/2016 1:36 PM, Ken Tanzer wrote: No transformation is needed, except for padding the fields out to their maximum lengths. without accessing metadata, how would you know what those maximum lengths are?? and how would the calling program even know what the fields are if its not aware of

Re: [GENERAL] IDE for function/stored proc development.

On 9/3/16 7:49 AM, Tim Uckun wrote: I was hoping there was some IDE which made that process seamless. Something like PgAdmin but better editing features and features like "find definition" or "find usages" and such. The jetbrains products come close but as I said they are buggy and don't work ve

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On 9/7/16 6:07 PM, Ken Tanzer wrote: ERROR: PL/Python functions cannot accept type record Ugh, yeah... that won't work. plperl might be able to do it, but I suspect you're going to be stuck pulling the size info out of info_schema or the catalog. Actually, there is a way you could hack thi

Re: [GENERAL] Clustered index to preserve data locality in a multitenant application?

On 9/6/16 11:21 AM, Nicolas Grilly wrote: It looks like I can move forward with our migration from MySQL to PostgreSQL, without worrying about the lack of clustered indexes, because there are better solutions to keep tenant data contiguous! First rule of performance tuning: don't. :) There are

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On 09/07/2016 03:38 PM, Jim Nasby wrote: On 9/7/16 5:32 PM, Ken Tanzer wrote: SELECT my_cat(ebh_gain,'ebh_gain') FROM ebh_gain; I know TCL and probably Python and others can work with a record as a trigger function. But TCL doesn't seem to accept a record as an argument. Can any of the other

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On Wed, Sep 7, 2016 at 3:38 PM, Jim Nasby wrote: > On 9/7/16 5:32 PM, Ken Tanzer wrote: > >> SELECT my_cat(ebh_gain,'ebh_gain') FROM ebh_gain; >> >> I know TCL and probably Python and others can work with a record as a >> trigger function. But TCL doesn't seem to accept a record as an >> argumen

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On Wed, Sep 7, 2016 at 3:46 PM, Adrian Klaver wrote: > On 09/07/2016 03:32 PM, Ken Tanzer wrote: > >> >> >> On Wed, Sep 7, 2016 at 3:18 PM, Adrian Klaver > > wrote: >> >> On 09/07/2016 01:36 PM, Ken Tanzer wrote: >> >> >> >> On Wed, Sep 7, 2016 at 1:2

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

On 9/2/16 11:44 AM, David Gibbons wrote: rsync -va /var/lib/pgsql/ /var/lib/pgsql2/ service postgres stop rsync -va /var/lib/pgsql/ /var/lib/pgsql2/ The second rsync will only copy the deltas from the first, it still has to go in and determine what needs to be copied/what changed but the bulk of

Re: [GENERAL] Privileges on public schema can't be revoked?

On 9/6/16 3:16 PM, Greg Fodor wrote: It seems that functionality that lets a superuser quickly audit the privileges for a user (including those granted via PUBLIC) would be really helpful for diagnosing cases where that user can do something they shouldn't be allowed to. That's actually relativ

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

> > > Or if you want, upgrade on your existing provider first, then setup > streaming replication. But no matter what, pg_upgrade will require some > form of downtime. > > You could also use either Slony or londiste to directly migrate from > Rackspace to an EC2 instance on 9.5; no need for DMS at

Re: [GENERAL] Materialized view auto refresh

On 9/6/16 9:47 PM, Nguyễn Trần Quốc Vinh wrote: I'm sorry. Please check it again at http://it.ued.udn.vn/myprojects/pgTriggerGen/: http://it.ued.udn.vn/myprojects/pgTriggerGen/PgSQLMVTriggersGenerator_v2_src.rar. FWIW, people would be much more likely to find this if you put it on GitHub (and

Re: [GENERAL] Postgres UPGRADE from 9.2 to 9.4

On 9/6/16 10:53 PM, John R Pierce wrote: 1) setup new streaming slave at new provider using 9.2, wait for replication to complete and satisfy yourself that the database is complete and intact. 2) shut down master at old provider (and any other slaves), promote new provider 9.2 to master. 3) pg_up

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On 09/07/2016 03:32 PM, Ken Tanzer wrote: On Wed, Sep 7, 2016 at 3:18 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 09/07/2016 01:36 PM, Ken Tanzer wrote: On Wed, Sep 7, 2016 at 1:22 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>

Re: [GENERAL] How to assemble all fields of (any) view into a string?

Jim Nasby writes: > On 9/7/16 5:32 PM, Ken Tanzer wrote: >> I know TCL and probably Python and others can work with a record as a >> trigger function. But TCL doesn't seem to accept a record as an >> argument. Can any of the other languages that could also accomplish >> this function? Or some ot

Re: [GENERAL] a column definition list is required for functions returning "record"

Jim Nasby writes: > On 9/7/16 2:02 PM, Merlin Moncure wrote: >> We have that already, it's named 'json_each_text' > Apparently you haven't looked at json parse/deparse costs ;P Well, a PL function is gonna be none too cheap either. Using something like JSON definitely has lots to recommend it -

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On 9/7/16 5:32 PM, Ken Tanzer wrote: SELECT my_cat(ebh_gain,'ebh_gain') FROM ebh_gain; I know TCL and probably Python and others can work with a record as a trigger function. But TCL doesn't seem to accept a record as an argument. Can any of the other languages that could also accomplish this

Re: [GENERAL] PostgreSQL Database performance

On 9/6/16 2:08 PM, Scott Marlowe wrote: checkpoint_completion_target = 0.9 Too high of a checkpoint completion target may cause buffers to get written out more often than needed. but it varies based on load etc. The odds on that don't seem to be terribly high. Even if that is a common occurre

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On Wed, Sep 7, 2016 at 3:18 PM, Adrian Klaver wrote: > On 09/07/2016 01:36 PM, Ken Tanzer wrote: > >> >> >> On Wed, Sep 7, 2016 at 1:22 PM, Adrian Klaver > > wrote: >> >> On 09/07/2016 01:15 PM, Ken Tanzer wrote: >> >> Hi. Using version 9.2. I'm try

Re: [GENERAL] a column definition list is required for functions returning "record"

On 9/7/16 2:02 PM, Merlin Moncure wrote: >> Exactly. You can pass anonymous record types around today, as long as you >> don't do anything that requires knowing what their contents are, either in >> the function or in the calling query: > > What I was thinking of is something (like a function)

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On 09/07/2016 01:36 PM, Ken Tanzer wrote: On Wed, Sep 7, 2016 at 1:22 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 09/07/2016 01:15 PM, Ken Tanzer wrote: Hi. Using version 9.2. I'm trying to create a function that will take a record from any vie

Re: [GENERAL] joined tables with USING and GROUPBY on the USING() column

Thanks for the suggestions. Turns out I was right: I _was_ missing something obvious - results had token as char(4), tokens had token as varchar(4). Because the columns aren't the same they aren't treated as identical so the query builder won't accept the unqualified name. Next task is to work out

Re: [GENERAL] pgAdmin 4 records limit of 2000

On Wed, Sep 7, 2016 at 3:23 PM, Adrian Klaver wrote: > On 09/06/2016 12:58 PM, dudedoe01 wrote: > >> Hi Adrian, >> >> The query tool toolbar is disabled in my pgAdmin 4. Is there anyway I can >> enable it. >> > > I don't know. I rarely use pgAdmin3 and have only looked at the new > pgAdmin once o

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On Wed, Sep 7, 2016 at 1:22 PM, Adrian Klaver wrote: > On 09/07/2016 01:15 PM, Ken Tanzer wrote: > >> Hi. Using version 9.2. I'm trying to create a function that will take >> a record from any view and assemble it into a string, for export to >> another system. For example, this view: >> >> \d

Re: [GENERAL] How to assemble all fields of (any) view into a string?

On 09/07/2016 01:15 PM, Ken Tanzer wrote: Hi. Using version 9.2. I'm trying to create a function that will take a record from any view and assemble it into a string, for export to another system. For example, this view: \d ebh_gain View "public.ebh_gain" Column |

[GENERAL] How to assemble all fields of (any) view into a string?

Hi. Using version 9.2. I'm trying to create a function that will take a record from any view and assemble it into a string, for export to another system. For example, this view: > \d ebh_gain View "public.ebh_gain" Column | Type | Modifiers ---+-

Re: [GENERAL] joined tables with USING and GROUPBY on the USING() column

On 09/07/2016 01:11 PM, Geoff Winkless wrote: On 7 Sep 2016 9:01 p.m., "Adrian Klaver" mailto:adrian.kla...@aklaver.com>> wrote: What happens if you table qualify all the references to token? Oh it definitely fixes it; I was more confused why it works on one server and not another. I thought p

Re: [GENERAL] joined tables with USING and GROUPBY on the USING() column

On 7 Sep 2016 9:01 p.m., "Adrian Klaver" wrote: > What happens if you table qualify all the references to token? Oh it definitely fixes it; I was more confused why it works on one server and not another. I thought perhaps there was a config option to allow more lax naming in this way. If not I'll

Re: [GENERAL] joined tables with USING and GROUPBY on the USING() column

On 9/7/2016 12:53 PM, Geoff Winkless wrote: I'll start by saying that I'm sure I'm missing something obvious... I have a query that is working fine on all my servers except one. The only obvious difference is that the failing one is running 9.5.3, while most are running 9.5.4, but since the quer

Re: [GENERAL] joined tables with USING and GROUPBY on the USING() column

On 09/07/2016 12:53 PM, Geoff Winkless wrote: I'll start by saying that I'm sure I'm missing something obvious... I have a query that is working fine on all my servers except one. The only obvious difference is that the failing one is running 9.5.3, while most are running 9.5.4, but since the qu

[GENERAL] joined tables with USING and GROUPBY on the USING() column

I'll start by saying that I'm sure I'm missing something obvious... I have a query that is working fine on all my servers except one. The only obvious difference is that the failing one is running 9.5.3, while most are running 9.5.4, but since the query works on a 9.5.1 box I also have I can't ima

Re: [GENERAL] PostgreSQL Database performance

On 9/2/2016 8:38 PM, Pradeep wrote: ... In task bar it is showing 2.7GB Utilization ... odd, the task bar doesn't show any sort of memory utilization on any of my windows systems.are you referring instead to the Task Manager ? Note the Windows Task Manager by default doesn't show shared

Re: [GENERAL] PostgreSQL Database performance

On Tue, Sep 6, 2016 at 11:12 PM, Pradeep wrote: > Dear Naveed, > > I am using PostgreSQL 9.3 version on Windows .After changing these > parameters, I have not seen any resource management utilization. > > I have observed before and after changing the parameter values ,it is not > reflecting the m

Re: [GENERAL] pgAdmin 4 records limit of 2000

On 09/06/2016 12:58 PM, dudedoe01 wrote: Hi Adrian, The query tool toolbar is disabled in my pgAdmin 4. Is there anyway I can enable it. I don't know. I rarely use pgAdmin3 and have only looked at the new pgAdmin once or twice. I would say you will find a more informed audience here: https

Re: [GENERAL] a column definition list is required for functions returning "record"

Fri, Sep 2, 2016 at 6:55 PM, Jim Nasby wrote: > On 8/29/16 6:28 AM, Tom Lane wrote: >> >> Pavel Stehule writes: >>> >>> > 2016-08-29 1:59 GMT+02:00 Jim Nasby : >> It would be nice if there was a way to pass dynamically formed >> records >> around, similar to how you can pass

Re: [GENERAL] PostgreSQL Database performance

Hello Pradeep, After making the changes have you restarted the cluster? --- Warm Regards, -- Naveed Shaikh On Wed, Sep 7, 2016 at 9:42 AM, Pradeep wrote: > Dear Naveed, > > > > I am using PostgreSQL 9.3 version on Windows .After changing these > parameters, I have not seen any resour

Re: [GENERAL] Restricted access on DataBases

Hello I did oversee the additional questions... On 09/07/2016 06:45 PM, Charles Clavadetscher wrote: Hello On 09/07/2016 03:24 PM, Durumdara wrote: Dear Everybody! I read the documentation based on your example. First reactions. 2016-09-05 18:25 GMT+02:00 Charles Clavadetscher mailto:clavade

Re: [GENERAL] Restricted access on DataBases

Hello On 09/07/2016 03:24 PM, Durumdara wrote: Dear Everybody! I read the documentation based on your example. First reactions. 2016-09-05 18:25 GMT+02:00 Charles Clavadetscher mailto:clavadetsc...@swisspug.org>>: GRANT us_a, us_b, us_c TO main_admin; Ah, it's good. I can merge the

Re: [GENERAL] PostgreSQL Database performance

Dear Naveed, I am using PostgreSQL 9.3 version on Windows .After changing these parameters, I have not seen any resource management utilization. I have observed before and after changing the parameter values ,it is not reflecting the memory level. Maximum utilization of RAM is 3GB only. So

[GENERAL] Setup pgpool-II with streaming replication

Hi, We do have Postgres 9.4 streaming replication in Prod environment, currently we are having issue with our DB connections and planning to implement PgPool-II for database pooling with streaming replication. Key Challenges: * Cannot afford downtime on db servers. * Add PgPo

Re: [GENERAL] pgAdmin 4 records limit of 2000

Hi Adrian, The query tool toolbar is disabled in my pgAdmin 4. Is there anyway I can enable it. -- View this message in context: http://postgresql.nabble.com/pgAdmin-4-records-limit-of-2000-tp5919751p5919825.html Sent from the PostgreS

Re: [GENERAL] PGDATA / data_directory

Re: Jehan-Guillaume de Rorthais 2016-09-07 <20160907140816.3e13eaa3@firost> > Indeed. I never noticed data_directory was set in postgresql.conf file... > > But then, why starting PostgreSQL with the following command ? > > /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/9.4/main \ >

Re: [GENERAL] Passing varchar parameter to INTERVAL

Thank you, this works well now and comments about IN is appreciated too :-)

Re: [GENERAL] Passing varchar parameter to INTERVAL

On 7 September 2016 at 15:05, Alexander Farber wrote: > Good afternoon, > > when trying to create a custom function to temporary ban a user: > > CREATE OR REPLACE FUNCTION words_ban_user( > IN in_uid integer, > IN in_until varchar,-- '1 week' OR '1 month' OR '1 year' >

Re: [GENERAL] Passing varchar parameter to INTERVAL

On 09/07/2016 06:05 AM, Alexander Farber wrote: Good afternoon, when trying to create a custom function to temporary ban a user: CREATE OR REPLACE FUNCTION words_ban_user( IN in_uid integer, IN in_until varchar,-- '1 week' OR '1 month' OR '1 year' IN in_reason varcha

Re: [GENERAL] Passing varchar parameter to INTERVAL

Alexander Farber writes: > unfortunately, I can not call INTERVAL 'in_until', that wouldn't work. No, because the syntax TYPENAME 'LITERAL' only works for simple string literals. What you want here is a run-time cast, either CAST(in_until AS interval) or in_until::interval. > I would prefer to

Re: [GENERAL] pgadmin4 rc1 query tool performance

> Am 07.09.2016 um 08:29 schrieb Martijn Tonies (Upscene Productions) > : > i testing the latest release of pgadmin4 (rc1) and noticed that the query tool is significantly slower than the query tool in pgadmin3. i am not sure if this occurs only on my computer or only under os x

Re: [GENERAL] Passing varchar parameter to INTERVAL

Hello Rob, On Wed, Sep 7, 2016 at 3:24 PM, rob stone wrote: > > I think the interval values need to be quoted. > In any event I always use the P or T syntax. 'P1D' means add one day, > unfortunately, I can not call INTERVAL 'in_until', that wouldn't work. Also 'P1D' vs. '1 day' seems to be jus

Re: [GENERAL] Passing varchar parameter to INTERVAL

On Wed, Sep 07, 2016 at 03:05:38PM +0200, Alexander Farber wrote: > ERROR: syntax error at or near "in_until" > LINE 69: ... banned_until = CURRENT_TIMESTAMP + INTERVAL in_until, >^ > Is there please a better way here? Why don'

Re: [GENERAL] Passing varchar parameter to INTERVAL

Hello, On Wed, 2016-09-07 at 15:05 +0200, Alexander Farber wrote: > Good afternoon, > > when trying to create a custom function to temporary ban a user: > > CREATE OR REPLACE FUNCTION words_ban_user( >     IN in_uid integer, >     IN in_until varchar,    -- '1 week' OR '1 month' OR '1 yea

Re: [GENERAL] Restricted access on DataBases

Dear Everybody! I read the documentation based on your example. First reactions. 2016-09-05 18:25 GMT+02:00 Charles Clavadetscher : > >> GRANT us_a, us_b, us_c TO main_admin; >> > Ah, it's good. I can merge the "owner" rights to one. :-) It's like "doubling"! :-) > >> > > Here an example (obv

Re: [GENERAL] pgadmin4 rc1 query tool performance

Hello, On Tue, 2016-09-06 at 18:40 +0200, Attila Soki wrote: > > > > Am 06.09.2016 um 15:23 schrieb Adrian Klaver > .com>: > > > > On 09/06/2016 02:35 AM, Attila Soki wrote: > > > > > > Hi, > > > > > > i testing the latest release of pgadmin4 (rc1) and noticed that > > > the query tool is sign

[GENERAL] Passing varchar parameter to INTERVAL

Good afternoon, when trying to create a custom function to temporary ban a user: CREATE OR REPLACE FUNCTION words_ban_user( IN in_uid integer, IN in_until varchar,-- '1 week' OR '1 month' OR '1 year' IN in_reason varchar) RETURNS void AS $func$ BEGIN

Re: [GENERAL] PGDATA / data_directory

On Sun, 4 Sep 2016 11:40:38 +0200 Christoph Berg wrote: > Re: Benoit Lobréau 2016-08-31 > > > Hi, > > > > My company is using PGDATA to store configuration files and the guc > > data_directory to give the path to the instance directory. > > > > They would use it like this: > > > > pg_ctl star