[GENERAL] Online backup of PostgreSQL data.

Hi All, I need a small clarification. We are using PostgreSQL version 9.4.1 We have a requirement to take the online backup of a running PostgreSQL system (with out stopping the database). As per the design / architecture of PostgreSQL system, is it technically allowed to dynamically copy the

Re: [GENERAL] Online backup of PostgreSQL data.

On 9/17/2015 12:31 AM, Raman, Karthik IN BLR STS wrote: I need a small clarification. We are using PostgreSQL version 9.4.1 We have a requirement to take the online backup of a running PostgreSQL system (with out stopping the database). As per the design / architecture of PostgreSQL system, is

Re: [GENERAL] Import Problem

On Thu, Sep 17, 2015 at 1:27 AM, Ramesh T wrote: > Hi All, >I'm using or2pg tool ,I exported data but I have to change the > schema import to postgres database.exported data more than gb. > Can you please let me know, how to do the change the name in

Re: [GENERAL] BDR truncate and replication sets

Le 15/09/2015 18:56, Alvaro Herrera a écrit : Sylvain MARECHAL wrote: [...] The exception is with TRUNCATE: In case it is called, data is removed on both nodes. Is it a feature or a bug? I think it's an oversight. Replication sets were added later than the TRUNCATE trigger, so the design for

Re: [GENERAL] Online backup of PostgreSQL data.

John R Pierce wrote: > to copy the data directory and have it be useful you need to bracket the copy > with calls to > pg_start_backup() and pg_stop_backup() this ensures the data files are > coherent. this is in > fact what pg_basebackup does for you I apologize for my fussiness,

Re: [GENERAL] clone_schema function

Von: Melvin Davidson [melvin6...@gmail.com] Gesendet: Donnerstag, 17. September 2015 17:11 An: Marc Mamin Cc: pgsql-general@postgresql.org Betreff: Re: [GENERAL] clone_schema function Thanks, >I'm not sure why you had trouble with the REPLACE(), as I did

Re: [GENERAL] BDR truncate and replication sets

Sylvain MARECHAL wrote: > Le 15/09/2015 18:56, Alvaro Herrera a écrit : > >Sylvain MARECHAL wrote: > >>[...] The exception is with TRUNCATE: In case it is called, data is removed > >>on > >>both nodes. > >> > >>Is it a feature or a bug? > >I think it's an oversight. Replication sets were added

Re: [GENERAL] clone_schema function

Attached is hopefully the final version of FUNCTION clone_schema(text, text, boolean) This function now does the following: 1. Checks that the source schema exists and the destination does not. 2. Creates the destination schema 3. Copies all sequences, tables, indexes, rules, triggers,

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

On 09/17/2015 06:54 AM, Tom Lane wrote: Adrian Klaver writes: On 09/17/2015 06:32 AM, Albe Laurenz wrote: I guess it is dependent on data type as it requires an equality operator, and type "point" doesn't have one. To echo the OP, why is that?

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

On Thu, Sep 17, 2015 at 9:14 AM, pinker wrote: > I've tried to write audit trigger which fires only when data changed, so I > used "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause as described in > documentation > . >

Re: [GENERAL] clone_schema function

Hello, I had to make 2 changes to get it running: line 193: - REPLACE(column_default::text, quote_ident(source_schema) || '.', quote_ident(dest_schema || '.') ) + REPLACE(column_default::text, quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.' ) line 319 - SELECT

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

On 09/17/2015 07:51 AM, Tom Lane wrote: Adrian Klaver writes: On 09/17/2015 07:34 AM, Tom Lane wrote: Ah, sorry, actually what IS [NOT] DISTINCT FROM looks up is the "=" operator. So the docs should be changed? As they stand now:

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

On Thu, Sep 17, 2015 at 9:39 AM, Adrian Klaver wrote: > On 09/17/2015 06:32 AM, Albe Laurenz wrote: > >> pinker wrote: >> >>> I've tried to write audit trigger which fires only when data changed, so >>> I used "WHEN (OLD.* IS >>> DISTINCT FROM NEW.*)" clause as

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

Adrian Klaver writes: > On 09/17/2015 06:54 AM, Tom Lane wrote: >> Well, that's true: the parser actually looks up the operator named "<>" >> for the given data types, and IS DISTINCT FROM is just a prefilter on >> that to do the right thing with nulls. So because type

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

On 09/17/2015 07:34 AM, Tom Lane wrote: Adrian Klaver writes: On 09/17/2015 06:54 AM, Tom Lane wrote: Well, that's true: the parser actually looks up the operator named "<>" for the given data types, and IS DISTINCT FROM is just a prefilter on that to do the right

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

pinker wrote: > I've tried to write audit trigger which fires only when data changed, so I > used "WHEN (OLD.* IS > DISTINCT FROM NEW.*)" clause as described in documentation > . Should > this clause be independent > from data

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

On 09/17/2015 06:32 AM, Albe Laurenz wrote: pinker wrote: I've tried to write audit trigger which fires only when data changed, so I used "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause as described in documentation . Should this

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

Adrian Klaver writes: > On 09/17/2015 06:32 AM, Albe Laurenz wrote: >> I guess it is dependent on data type as it requires an equality operator, >> and type "point" doesn't have one. > To echo the OP, why is that? >

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

Adrian Klaver writes: > On 09/17/2015 07:34 AM, Tom Lane wrote: >> Ah, sorry, actually what IS [NOT] DISTINCT FROM looks up is the "=" >> operator. > So the docs should be changed? > As they stand now: >

Re: [GENERAL] clone_schema function

Thanks, I'm not sure why you had trouble with the REPLACE(), as I did extensive testing and it was working as coded. As for SET STATISTICS, I'm guessing that is a glitch in the CREATE TABLE LIKE option. On Thu, Sep 17, 2015 at 11:05 AM, Marc Mamin wrote: > Hello, >

[GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

I've tried to write audit trigger which fires only when data changed, so I used "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause as described in documentation . Should this clause be independent from data type? because an error

[GENERAL] Re: Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

Yes I will try to do something else like casting, hashing or using another operator but in documentation this example stands as a model audit trigger without any warnings... -- View this message in context:

[GENERAL] @ operator

Hello, google nothing for @ operator =(, what does this mean field1 <@ ( subquery ) ? ps: sorry for my english

Re: [GENERAL] Import Problem

Actually ,oracle have the *qa *schema i have to import to this schema to postgres database.But in postgres database already have the *qa *schema.My problem is that i want to change the name of the oracle schema while import to the postgres database using or2pg.because same schema name not accept

Re: [GENERAL] Import Problem

with out hanging how to open data script of the oracle schema to change the name. On Thu, Sep 17, 2015 at 6:31 PM, Ramesh T wrote: > Actually ,oracle have the *qa *schema i have to import to this schema to > postgres database.But in postgres database already have

Re: [GENERAL] @ operator

Your English is fine. @< is an "array is contained by" operator: http://www.postgresql.org/docs/9.4/interactive/functions-array.html I assume that field1 is an array, as opposed to just a simple value variable. A simple value variable would be tested with something like: field1 IN ( subquery) .

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

Tom Lane wrote: > Since type record *does* have btree/hash opclasses, it is not negotiable > that the component column types obey btree or at least hash semantics. > The only way to fix this would be to provide such opclasses for point. > Btree has the probably-fatal obstacle that there's no

Re: [GENERAL] Hiding name and version

"Joshua D. Drake" writes: > On 09/17/2015 10:32 AM, Quiroga, Damian wrote: >> Is it possible to prevent users from running the “version” function or >> all system information functions? If so, how? > You could probably revoke access to the function(s) (I haven't tried it

Re: [GENERAL] Hiding name and version

Thanks for your answer. I've tried something like "REVOKE EXECUTE ON FUNCTION version() FROM someuser;" and it doesn't work. Apparently you can only revoke permissions that you have explicitly granted before. Any other ideas? Damian -Original Message- From: Joshua D. Drake

[GENERAL] Hiding name and version

Hi, Is it possible to prevent users from running the "version" function or all system information functions? If so, how? Thank you, Damian

Re: [GENERAL] Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type?

Alvaro Herrera writes: > Tom Lane wrote: >> Since type record *does* have btree/hash opclasses, it is not negotiable >> that the component column types obey btree or at least hash semantics. >> The only way to fix this would be to provide such opclasses for point. >>

Re: [GENERAL] Hiding name and version

On 09/17/2015 10:32 AM, Quiroga, Damian wrote: Hi, Is it possible to prevent users from running the “version” function or all system information functions? If so, how? You could probably revoke access to the function(s) (I haven't tried it because it seems very silly). JD -- Command

Re: [GENERAL] Hiding name and version

"Quiroga, Damian" writes: > I've tried something like "REVOKE EXECUTE ON FUNCTION version() FROM > someuser;" and it doesn't work. > Apparently you can only revoke permissions that you have explicitly granted > before. The default behavior for built-in functions is as

Re: [GENERAL] @ operator

On Thursday, September 17, 2015, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thursday, September 17, 2015, Антон Бушмелев > wrote: > >> Hello, google nothing for @ operator =(, what does this mean

Re: [GENERAL] BDR: cannot drop database even after parting the node

This procedure seems to work for dismantling the replication cluster after I'm done and cleaning up test databases. If there might be any issues with these steps, please let me know. Thanks. -- Disconnect node2 from cluster -- -- on node1 run: SELECT bdr.bdr_part_by_node_names('{node2}'); --

Re: [GENERAL] @ operator

On Thursday, September 17, 2015, Антон Бушмелев wrote: > Hello, google nothing for @ operator =(, what does this mean field1 <@ > ( subquery ) ? > > It would depend on what field1 is. Typically <@ is a contains/contained-by operator. Is this context the subquery is

Re: [GENERAL] Import Problem

On 09/17/2015 06:03 AM, Ramesh T wrote: with out hanging how to open data script of the oracle schema to change the name. Like Postgres, or2pg has documentation: http://ora2pg.darold.net/config.html#oracle_schema_to_export On Thu, Sep 17, 2015 at 6:31 PM, Ramesh T

[GENERAL] search_path not reloaded via unix socket connections

Can anybody explain why the search_path setting, with several config reloads, would not change via local connections? We struggled with our production settings on Postgres 9.3 today, only to realize, after a while, that the search_path change actually took effect via TCP/IP, but not unix

Re: [GENERAL] Hiding name and version

Tom Lane writes: > "Joshua D. Drake" writes: > >> On 09/17/2015 10:32 AM, Quiroga, Damian wrote: >>> Is it possible to prevent users from running the “version” function or >>> all system information functions? If so, how? > >> You could probably

Re: [GENERAL] Hiding name and version

Good to know. Thanks everyone. -Original Message- From: Jerry Sievers [mailto:gsiever...@comcast.net] Sent: Thursday, September 17, 2015 11:03 PM To: Tom Lane Cc: Joshua D. Drake; Quiroga, Damian; pgsql-general@postgresql.org Subject: Re: [GENERAL] Hiding name and version Tom Lane

Re: [GENERAL] search_path not reloaded via unix socket connections

Kong Man writes: > Can anybody explain why the search_path setting, with several config reloads, > would not change via local connections? We struggled with our production > settings on Postgres 9.3 today, only to realize, after a while, that the > search_path

Re: [GENERAL] search_path not reloaded via unix socket connections

On Thursday, September 17, 2015, Kong Man wrote: > Can anybody explain why the search_path setting, with several config > reloads, would not change via local connections? We struggled with our > production settings on Postgres 9.3 today, only to realize, after a

[GENERAL] Broken primary key after backup restore.

Hi, In Production, I have a DB2 which is replicated partially using Londiste from DB1. I make file-system backups nightly on both DBs. Last Monday, when I restored the backup made from DB2 to a test server, Postgres(9.3.5) started up fine. But, I found out that the primary key of one of the

Re: [GENERAL] Broken primary key after backup restore.

On 09/17/2015 04:31 PM, Michael Chau wrote: Hi, In Production, I have a DB2 which is replicated partially using Londiste from DB1. Well I think the above needs more explanation to help understand how the DB2 backup got into this state and possibly prevent it in the future. I make

Re: [GENERAL] Broken primary key after backup restore.

1) > > In Production, I have a DB2 which is replicated partially using Londiste > from DB1. > Well I think the above needs more explanation to help understand how the DB2 backup got into this state and possibly prevent it in the future. A: So, the DB1 has several schemas in the database. We use

Re: [GENERAL] search_path not reloaded via unix socket connections

On Thursday, September 17, 2015, Tom Lane wrote: > "David G. Johnston" > writes: > > Or not, since it does appear that the reload signal is propagated to > active > > sessions and take effect after the most recent command finishes. >

Re: [GENERAL] search_path not reloaded via unix socket connections

"David G. Johnston" writes: > As an aside the alter user/database commands do end up requiring the user > to disconnect and reconnect. Is there a hard limitation why an > administrator can't send some kind of signal to cause a re-read of those by > an active session?

Re: [GENERAL] search_path not reloaded via unix socket connections

On Thursday, September 17, 2015, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thursday, September 17, 2015, Kong Man > wrote: > >> Can anybody explain why the search_path setting, with

Re: [GENERAL] search_path not reloaded via unix socket connections

"David G. Johnston" writes: > Or not, since it does appear that the reload signal is propagated to active > sessions and take effect after the most recent command finishes. Yeah. I had been wondering about long-lived open transactions, but AFAICS from the code,

Re: [GENERAL] Broken primary key after backup restore.

On 09/17/2015 05:37 PM, Michael Chau wrote: 1) In Production, I have a DB2 which is replicated partially using Londiste from DB1. Well I think the above needs more explanation to help understand how the DB2 backup got into this state and possibly prevent it in the future. A: So, the

Re: [GENERAL] BDR truncate and replication sets

On 17 September 2015 at 15:17, Sylvain MARECHAL wrote: > Le 15/09/2015 18:56, Alvaro Herrera a écrit : >> >> Sylvain MARECHAL wrote: >>> >>> [...] The exception is with TRUNCATE: In case it is called, data is >>> removed on >>> both nodes. >>> >>> Is it a feature or a