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

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

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

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

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

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

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

>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

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

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

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

>> 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

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

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

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

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

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

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

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

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

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

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

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

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 >

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

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

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

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

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

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

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

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

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

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

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

On Wed, Mar 22, 2017 at 3:27 AM, Rakesh Kumar wrote: > PG does not have a concept of incremental backup. The way it works in > Oracle and other RDBMS is that incremental backup only backups up changed > blocks since the last full backup. So if only 10% of blocks