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

[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.
> 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
> specified columns consist of the same items, regardless the order? could
> `exclude_element` contains multiple columns? (from the syntax it looks like
> it's impossible) And is there such an operator to compare multiple columns?
>
> On 23 Mar 2017, at 1:04 AM, David G. Johnston  > wrote:
>
> 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 that if 1,2,3 is already in the table, rows like
>> 1,3,2 or 2,1,3 shouldn't be allowed.
>>
>> Is there a general solution to this problem?
>>
>> Sorry if the question is too basic, but I couldn't find the answer in the
>> doc, at least not in the chapter on unique index.
>>
>
> The most direct option to consider is a exclusion constraint.
>
> https://www.postgresql.org/docs/current/static/ddl-constraints.html
> (bottom of page)
>
> David J.
>
>
>

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

[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 specified 
columns consist of the same items, regardless the order? could 
`exclude_element` contains multiple columns? (from the syntax it looks like 
it's impossible) And is there such an operator to compare multiple columns?

> On 23 Mar 2017, at 1:04 AM, David G. Johnston  
> wrote:
> 
> 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 that if 1,2,3 is already in the table, rows like 
> 1,3,2 or 2,1,3 shouldn't be allowed.
> 
> Is there a general solution to this problem?
> 
> Sorry if the question is too basic, but I couldn't find the answer in the 
> doc, at least not in the chapter on unique index.
> 
> The most direct option to consider is a exclusion constraint.
> 
> https://www.postgresql.org/docs/current/static/ddl-constraints.html 
>  (bottom 
> of page)
> 
> David J.

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

[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().

I can compare this with now() to get a duration, which I believe should 
indicate how far behind the slave is tracking.

Most of the time this works quite well -- the slave might fall behind by some 
seconds under heavy load, but that's fine.

However, occasionally this replay timestamp will report times many hours or 
days behind! This goes on for a few minutes, then suddenly recovers.


My best guess for what is going on is:
 - There has been no activity for hours or days, and so the oldest replayed 
transaction on the slave is genuinely quite old.
 - Something has happened on the master that causes its 
pg_current_xlog_location() to be updated, but not in a way that is sent to the 
slave until the end of a long-running transaction.


Could anyone suggest how to do this in a manner that avoids the problem?


It's annoying because when it happens, because for 5-10 minutes monitoring 
alerts get fired off about catastrophic amounts of lag on the read-only slave!

Cheers
Toby


-- 
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] Incremental / Level -1 backup in PG

[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 the WAL is during the time of
backup regardless of full_page_writes setting.

AFAIK rest of the time, WAL takes only row changes.  Otherwise PG will be
generating large number
of WAL logs.  

I hope I am right :-)

> also note that postgres effectively does copy-on-write, since update's are
> treated 
>as insert+delete, so the same blocks aren't written over and over nearly 
>as much as they might be in the oracle storage model.

Good point. 



--
View this message in context: 
http://www.postgresql-archive.org/Incremental-Level-1-backup-in-PG-tp5951072p5951343.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

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

[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 time of base backup) has to applied to roll-foward to PIT. In 
incremental backup,
a block is only applied once, regardless of how many times it changed after the 
base backup.


note postgres' WAL archive is by block, not by transaction.  also note 
that postgres effectively does copy-on-write, since update's are treated 
as insert+delete, so the same blocks aren't written over and over nearly 
as much as they might be in the oracle storage model.



--
john r pierce, recycling bits in santa cruz



--
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] Incremental / Level -1 backup in PG

[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-change-archiving methods.

I assume you are talking about other RDBMS where we can do PITR restore either:

 Restore base backup
 Apply  incremental backup
 Rollfoward WAL logs to the point you want to recover
vs
 Restore base backup
Rollfoward WAL logs to the point you want to recover

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 time of base backup) has to applied to roll-foward to PIT. In 
incremental backup,
a block is only applied once, regardless of how many times it changed after the 
base backup.

The diff may not be much if PITR is close to the time of base backup.

Note: I have never tried option (1) for PG. 

>You are assuming your backup product does direct-diff to base. Those
>are gonna be costly when friday arrives.

You mean costly as in finding more and more blocks changed since weekend. that 
is
correct. However Oracle keeps track of location of all changed blocks since 
last base
backup and it helps in quick backup. It does not scan entire tablespace to 
figure which
blocks changed.


>Is it really testable / a lot faster ? ( bear in mind if a product
>just supports one strategy there is a huge interest in telling it is
>the faster one )

Nope. Incremental backup is not the only way to reach PITR until Thu afternoon
from a base backup on Sunday. You can always apply redo logs after restoring
from the base backup.
And yes, it is much faster.  We do it to prove to our clients why incremental 
backup
will benefit them more.



-- 
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] How to create unique index on multiple columns where the combination doesn't matter?

[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 that if 1,2,3 is already in the table, rows like
> 1,3,2 or 2,1,3 shouldn't be allowed.
>
> Is there a general solution to this problem?
>
> Sorry if the question is too basic, but I couldn't find the answer in the
> doc, at least not in the chapter on unique index.
>

The most direct option to consider is a exclusion constraint.

https://www.postgresql.org/docs/current/static/ddl-constraints.html (bottom
of page)

David J.

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

[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 be allowed.

Is there a general solution to this problem?

Sorry if the question is too basic, but I couldn't find the answer in the doc, 
at least not in the chapter on unique index.


-- 
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] COPY ... FROM stdin WITH FORMAT csv

[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 above shoud work?
>

ok thanks, I had misunderstood your text -

"The above means the entire "WITH" section is optional, as is the word
WITH.  However, if you want to add "with" options they must appear within
parentheses, those are not optional.  Multiple options can appear within
the single set of parentheses."

I think the root cause is that it is difficult to understand an english
sentence trying to describe a keyword "with" :-)

Regards
Alex

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

[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 9.5 server running on a VM
  instance on Google Compute Engine (Google Cloud Platform)
  on Debian Jessie (8.3), and I have another dedicated VM
  instance that, every night at 3.00, takes a snapshot of
  the whole disk, without stopping the PG instance itself.
  Snapshots are stored and kept by Google in an incremental
  way, and we keep the last 2 weeks of history.
  The question is: Keeping all two weeks worth of pg_xlog
  files, I don't think I still need a periodic pg_basebackup
  to perform PITR, do I?

  

  
  
  You need a base backup to apply your wals to. So
long as you have one from after the start of your wal stream,
you should be good for PITR.

Hmmm... I went back in the docs and noticed I missed something. To
achieve PITR, the cluster needs to checkpoint, and this can be
obtained with the pg_start_backup() function... so if I try to get a
snapshot and start a recover (creating recovery.conf etc.) it will
not even start recovery, right?

Now I'm gonna try 2 approaches:
1. (straightforward) barman with basebackup and WAL archiving
2. (GoogleCloud-oriented) disk snapshot between pg_start_backup and
pg_stop_backup (so the snapshot is taken just after the checkpoint),
WAL archiving

I will report the results.


   That said, replaying 2 weeks of wal files can take
a long time. For that reason alone, it might well make sense to
have more than a single basebackup snapshot.

That's right, my (wrong) thought was to have a snapshot per day and
all 14 days worth of WALs, but after the meeting with the Google
Specialist, I'm oriented to make a base backup per day and then
store it on Nearline, making it expire after 14 days. Same for WAL
files.


  Also, I cannot stress enough how important it is to
actually test your recovery strategy. 

I totally agree... that's why I'm here. I don't want to prepare a
backup strategy when I already need to recover

Thanks
Moreno.

  

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

[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-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[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 before the backup can be omitted from an incremental backup.
> 
> what you have written above applies to oracle/db2 too.  In case you are not
> aware, during backup, those products have a mechanism to save the image
> of any changing block as it existed before the start of the backup. that is
> used
> to reconstruct the PIT image of the block.

That is WAL archiving, which PG already does, but is different from
backups.

> Anyhow looks like this can't be done in PG.

PG does support WAL archiving, as discussed on this thread, and it works
exactly as you describe above.  As I mentioned, there are also tools for
performing incremental backups, which isn't quite the same as straight
WAL archiving.

Thanks!

Stephen


signature.asc
Description: Digital signature

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

[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 fact, tools for doing
incremental backup and restore with PG and they work quite well, as I
mentioned.  Currently they operate at a file-level, which means 1G
chunks instead of block-level, but there is work being done to change
that too.  There isn't anything in "PG technology" that makes this
impossible or, really, even that difficult, it's more that there hasn't
been effort put into it simply because the file-level incremental
solution works quite well in most cases.

Thanks!

Stephen


signature.asc
Description: Digital signature

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

[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
after testing

> I have reasons to believe that
> the same should be true for PG also.

As would be ?

Best regards,
Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] Incremental / Level -1 backup in PG

[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 $BACKUP.

After which the OPs question becomes entirely
independant from PostgreSQL as such, of course.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] Incremental / Level -1 backup in PG

[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 normally I've found that

1.- Incremental backups are slow and impose a greater runtime penalty
on the system than log-change-archiving methods.

2.- Incremental restores are not that fast.

> Eg:
> It is common to take a full backup on weekends and incremental on
> weeknights.  If we have to restore
> upto Thu afternoon, which one do you think will be faster :-
>
> 1 -  Restore from basebackup.
> 2 -  Restore from wed night backup
> 3 - Apply WAL logs after wed night backup until the time we want to restore.

You are assuming your backup product does direct-diff to base. Those
are gonna be costly when friday arrives.

> vs
> 1 - Restore from basebackup
> 2 - Apply WAL logs from weekend until the time we want to restore.

> If first choice is lot faster in Oracle,DB2,

Is it really testable / a lot faster ? ( bear in mind if a product
just supports one strategy there is a huge interest in telling it is
the faster one )

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

I fear incremental backup capabilities will make postgres slower.

Anyway, with base backup + wal archive you always have the option of
making incremental. Just start a recovery on the backup each time you
receive a wal segment wal and you are done. In fact, you can treat a
replication slave as a very low lag backup.

Francisco Olarte.


-- 
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] Incremental / Level -1 backup in PG

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

what you have written above applies to oracle/db2 too.  In case you are not
aware, during backup, those products have a mechanism to save the image
of any changing block as it existed before the start of the backup. that is
used
to reconstruct the PIT image of the block.

Anyhow looks like this can't be done in PG.

thanks all.






--
View this message in context: 
http://www.postgresql-archive.org/Incremental-Level-1-backup-in-PG-tp5951072p5951148.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

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

[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 that will not be as fast as
restoring
using the incremental backup. Eg:

It is common to take a full backup on weekends and incremental on
weeknights.  If we have to restore
upto Thu afternoon, which one do you think will be faster :-

1 -  Restore from basebackup.
2 -  Restore from wed night backup
3 - Apply WAL logs after wed night backup until the time we want to restore.
vs
1 - Restore from basebackup
2 - Apply WAL logs from weekend until the time we want to restore.

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.

Anyhow it was an academical question.




--
View this message in context: 
http://www.postgresql-archive.org/Incremental-Level-1-backup-in-PG-tp5951072p5951147.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

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

[Oleg Bartunov]

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 changed since
> the last full backup, incremental backup will be only for 10%.
> I am wondering whether it is technically feasible to implement it like
> this:
>

Have you seen pg_probackup ? It's in development stage and needs somebody
to test it.



>
> 1 - At the time of full backup, note the last modified time of each data
> file in a repository.
> 2 - Next time when incremental backup runs, for every data file it will
> check the last modified time of it with the one in the repository to
> determine whether it has changed since last full backup. If yes, back it up.
>
> Now on to restore:
>
> 1 - First restore full backup.
> 2 - Restore incremental backup.
>
> My question: Will it work in PG?
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>