[GENERAL] Wiki editor privilege

2017-03-22 Thread Charles Clavadetscher
Hello I would like to upload the slides of my presentation at Nordic PGDay 2017. Could you please grant me edit privilege on the wiki? My user name is cclavadetscher Thank you and bye Charles -- Swiss PostgreSQL Users Group c/o Charles Clavadetscher Treasurer Motorenstrasse 18 CH – 8005 Zürich

Re: [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

2017-03-22 Thread Andreas Kretschmer
Glen Huang wrote: > Hello, > > If I have a table like > > CREATE TABLE relationship ( > obj1 INTEGER NOT NULL REFERENCES object, > obj2 INTEGER NOT NULL REFERENCES object, > obj3 INTEGER NOT NULL REFERENCES object, > ... > ) > > And I want to constrain that if 1,2,3 is already in the

Re: [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

2017-03-22 Thread David G. Johnston
Maybe try combining them into a single array then performing array comparisons... On Wednesday, March 22, 2017, Glen Huang wrote: > Thanks. > > Didn't realize it could be implemented with a exclusion constraint. The > comparing between any two row definitely sounds like the right direction. > Bu

Re: [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

2017-03-22 Thread Glen Huang
Thanks. Didn't realize it could be implemented with a exclusion constraint. The comparing between any two row definitely sounds like the right direction. But I'm still having a hard time figuring out how i should write the `exclude_element WITH operator` part, which I think, should detect if sp

[GENERAL] pg_last_xact_replay_timestamp() sometimes reports unlikely, very large delays

2017-03-22 Thread Toby Corkindale
Hi all, I wondered if any experts can help me out? I currently monitor Postgresql's replication status by looking at two metrics. First I check to see if the current slave xlog replay is equal to the master -- if so, it's up to date. If it's not equal, then I look at pg_last_xact_replay_timestamp

Re: [GENERAL] Incremental / Level -1 backup in PG

2017-03-22 Thread rakeshkumar464
>note postgres' WAL archive is by block, not by transaction. My understanding is that only the first time a block is updated after a checkpoint, is the entire block is written to the WAL logs. And for that full_page_writes has to be set to ON. The only other time PG writes entire block to th

Re: [GENERAL] Incremental / Level -1 backup in PG

2017-03-22 Thread John R Pierce
On 3/22/2017 10:34 AM, Rakesh Kumar wrote: When the PITR is far apart from the time of base backup (far apart as in, let us say 4 to 5 days), the first approach beats the second approach hands down. This coming from experience. Reason is simple. In the second approach every transaction (from the

Re: [GENERAL] Incremental / Level -1 backup in PG

2017-03-22 Thread Rakesh Kumar
>> Yes John I do know about using WAL archive. IMO that will not be as fast as >> restoring using the incremental backup. >That's an opinion, have you tried measuring? Because normally I've found that >1.- Incremental backups are slow and impose a greater runtime penalty >on the system than log-c

Re: [GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

2017-03-22 Thread David G. Johnston
On Wed, Mar 22, 2017 at 9:54 AM, Glen Huang wrote: > Hello, > > If I have a table like > > CREATE TABLE relationship ( > obj1 INTEGER NOT NULL REFERENCES object, > obj2 INTEGER NOT NULL REFERENCES object, > obj3 INTEGER NOT NULL REFERENCES object, > ... > ) > > And I want to constrain tha

[GENERAL] How to create unique index on multiple columns where the combination doesn't matter?

2017-03-22 Thread Glen Huang
Hello, If I have a table like CREATE TABLE relationship ( obj1 INTEGER NOT NULL REFERENCES object, obj2 INTEGER NOT NULL REFERENCES object, obj3 INTEGER NOT NULL REFERENCES object, ... ) And I want to constrain that if 1,2,3 is already in the table, rows like 1,3,2 or 2,1,3 shouldn't b

Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv

2017-03-22 Thread Alexander Farber
Hi David, On Tue, Mar 21, 2017 at 8:21 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > > On Tuesday, March 21, 2017, Alexander Farber wrote: >> >> words=> COPY words_reviews (uid, author, nice, review, updated) FROM stdin FORMAT csv; > > > What did you read that lead you to think the

Re: [GENERAL] Google Cloud Platform, snapshots and WAL

2017-03-22 Thread Moreno Andreo
Il 20/03/2017 17:45, Ben Chobot ha scritto: On Mar 20, 2017, at 6:31 AM, Moreno Andreo wrote: Hi everyone,    I have my PostgreSQL

Re: [GENERAL] Incremental / Level -1 backup in PG

2017-03-22 Thread Thomas Kellerer
Rakesh Kumar schrieb am 22.03.2017 um 01:27: > PG does not have a concept of incremental backup. Postgres doesn't, but external tools can. e.g. Barman can do incremental backups: https://blog.2ndquadrant.com/incremental-backup-barman-1-4-0/ -- Sent via pgsql-general mailing list (pgsql-g

Re: [GENERAL] Incremental / Level -1 backup in PG

2017-03-22 Thread Stephen Frost
Greetings, * rakeshkumar464 (rakeshkumar...@outlook.com) wrote: > >The short answer is 'no'. There are complications around this, > >particularly at the edges and because files can be written and rewritten > >as you're reading them. > >Basically, no file with a timestamp after the > >checkpoint

Re: [GENERAL] Incremental / Level -1 backup in PG

2017-03-22 Thread Stephen Frost
Greetings, * rakeshkumar464 (rakeshkumar...@outlook.com) wrote: > If first choice is lot faster in Oracle,DB2, I have reasons to believe that > the same should be true for PG also. But as someone explained, the PG > technology can not support this. This statement isn't correct. There are, in fac

Re: [GENERAL] Incremental / Level -1 backup in PG

2017-03-22 Thread Karsten Hilbert
On Wed, Mar 22, 2017 at 01:40:49AM -0700, rakeshkumar464 wrote: > upto Thu afternoon, which one do you think will be faster :- All in all, perhaps it is more a question of which one *came out* to be faster on your hardware with your load with your data aft

Re: [GENERAL] Incremental / Level -1 backup in PG

2017-03-22 Thread Karsten Hilbert
On Tue, Mar 21, 2017 at 08:43:00PM -0400, Stephen Frost wrote: > Do not try to implement an incremental backup solution using > simple/naive tools like rsync with timestamp-based incrementals. It is > not safe. ... as long as the server is *running*. So, "stop" the server when using $RSYNC for

Re: [GENERAL] Incremental / Level -1 backup in PG

2017-03-22 Thread Francisco Olarte
On Wed, Mar 22, 2017 at 9:40 AM, rakeshkumar464 wrote: > basebackup + WAL archive lets you do just exactly this. . > Yes John I do know about using WAL archive. IMO that will not be as fast as > restoring using the incremental backup. That's an opinion, have you tried measuring? Because norm

Re: [GENERAL] Incremental / Level -1 backup in PG

2017-03-22 Thread rakeshkumar464
Greetings, >The short answer is 'no'. There are complications around this, >particularly at the edges and because files can be written and rewritten >as you're reading them. >Basically, no file with a timestamp after the >checkpoint before the backup can be omitted from an incremental backup.

Re: [GENERAL] Incremental / Level -1 backup in PG

2017-03-22 Thread rakeshkumar464
basebackup + WAL archive lets you do just exactly this. you can restore to any transaction between when that basebackup was taken, and the latest entry in the WAL archive, its referred in the documentation as PITR, Point in Time Recovery. Yes John I do know about using WAL archive. IMO tha