Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-08-30 Thread Bruce Momjian
On Thu, Jul 26, 2012 at 10:36:59AM -0400, Bruce Momjian wrote: Pg_upgrade already creates a script to analyze the cluster, so we could create another script to upgrade a standby. However, the problem with a script is that I have no idea what command people would use to do the copy.

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-28 Thread Bruce Momjian
On Fri, Jul 27, 2012 at 08:29:20AM -0400, Robert Haas wrote: Yes, that would be a problem because the WAL records are deleted by pg_upgrade. Does a shutdown of the standby not already replay all WAL logs? We could also just require them to just start the standby in master mode and shut

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-27 Thread Robert Haas
On Thu, Jul 26, 2012 at 2:17 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Jul 26, 2012 at 01:24:19PM -0400, Robert Haas wrote: On Thu, Jul 26, 2012 at 12:06 PM, Bruce Momjian br...@momjian.us wrote: I don't see the don't modify the user files behavior changing anytime soon, and it is

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-27 Thread Robert Haas
On Thu, Jul 26, 2012 at 7:24 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Jul 26, 2012 at 02:17:22PM -0400, Bruce Momjian wrote: Is that sufficient? Well, at the very least, you need to guarantee that the standby is caught up - i.e. that it replayed all the WAL records that were

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Bruce Momjian
On Mon, Jul 23, 2012 at 11:11:27PM +0300, Peter Eisentraut wrote: On mån, 2012-07-23 at 10:08 -0400, Robert Haas wrote: Relying on the number of hard links seems very fragile. For example, it'll break if you are using copy mode. And it won't work on Windows, either. pg_upgrade could

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Bruce Momjian
On Thu, Jul 26, 2012 at 08:30:40AM -0400, Bruce Momjian wrote: On Mon, Jul 23, 2012 at 11:11:27PM +0300, Peter Eisentraut wrote: On mån, 2012-07-23 at 10:08 -0400, Robert Haas wrote: Relying on the number of hard links seems very fragile. For example, it'll break if you are using copy

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Peter Eisentraut
On tor, 2012-07-26 at 08:30 -0400, Bruce Momjian wrote: On Mon, Jul 23, 2012 at 11:11:27PM +0300, Peter Eisentraut wrote: On mån, 2012-07-23 at 10:08 -0400, Robert Haas wrote: Relying on the number of hard links seems very fragile. For example, it'll break if you are using copy mode.

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Robert Haas
On Thu, Jul 26, 2012 at 9:59 AM, Peter Eisentraut pete...@gmx.net wrote: I think I could create a list and pass that into a loop so only the command has to be modified, but again, how do we do that on Windows? Can we create a shell function in Windows and pass the file name as an argument? I

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Andrew Dunstan
On 07/26/2012 09:59 AM, Peter Eisentraut wrote: I think I could create a list and pass that into a loop so only the command has to be modified, but again, how do we do that on Windows? Can we create a shell function in Windows and pass the file name as an argument? I don't know, but I assume

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Bruce Momjian
On Thu, Jul 26, 2012 at 04:59:46PM +0300, Peter Eisentraut wrote: On tor, 2012-07-26 at 08:30 -0400, Bruce Momjian wrote: On Mon, Jul 23, 2012 at 11:11:27PM +0300, Peter Eisentraut wrote: On mån, 2012-07-23 at 10:08 -0400, Robert Haas wrote: Relying on the number of hard links seems very

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Bruce Momjian
On Thu, Jul 26, 2012 at 10:26:53AM -0400, Robert Haas wrote: Well, then that would call for another list of files. I cannot escape the feeling that if we go down this route in any form we're going to spend years tracking down data-loss-inducing bugs. The ones we have on the master are bad

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Robert Haas
On Thu, Jul 26, 2012 at 10:40 AM, Bruce Momjian br...@momjian.us wrote: On Thu, Jul 26, 2012 at 10:26:53AM -0400, Robert Haas wrote: Well, then that would call for another list of files. I cannot escape the feeling that if we go down this route in any form we're going to spend years tracking

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Bruce Momjian
On Thu, Jul 26, 2012 at 11:03:15AM -0400, Robert Haas wrote: IMV, pg_upgrade is not yet sufficiently reliable that we should be looking for new projects that seem certain to make it less reliable. The script has to make the primary/standby identical, and guarantee that. That is why one

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Robert Haas
On Thu, Jul 26, 2012 at 12:06 PM, Bruce Momjian br...@momjian.us wrote: I don't see the don't modify the user files behavior changing anytime soon, and it is documented, so I feel pretty confident that those files were not modified on the primary or standby cluster, and are hence the same, or

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Bruce Momjian
On Thu, Jul 26, 2012 at 01:24:19PM -0400, Robert Haas wrote: On Thu, Jul 26, 2012 at 12:06 PM, Bruce Momjian br...@momjian.us wrote: I don't see the don't modify the user files behavior changing anytime soon, and it is documented, so I feel pretty confident that those files were not

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Jeff Davis
On Thu, 2012-07-26 at 14:17 -0400, Bruce Momjian wrote: Yes, that would be a problem because the WAL records are deleted by pg_upgrade. Does a shutdown of the standby not already replay all WAL logs? There is no notion of all WAL logs because the WAL is infinite. Do you mean all WAL

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Daniel Farina
On Thu, Jul 26, 2012 at 2:26 PM, Jeff Davis pg...@j-davis.com wrote: I was originally thinking that we would require users to run pg_upgrade on the standby, where you need to first switch into master mode. That sounds a little strange to me. If the original master has generated WAL that the

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Daniel Farina
On Thu, Jul 26, 2012 at 3:01 PM, Daniel Farina dan...@heroku.com wrote: For example: suppose pg_upgrade emitted full-page-write records in the format of the new postgres version on an unoccupied timeline. One can use PG.next tools to report on the first txid and by txid I meant WAL position,

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Bruce Momjian
On Thu, Jul 26, 2012 at 02:17:22PM -0400, Bruce Momjian wrote: Is that sufficient? Well, at the very least, you need to guarantee that the standby is caught up - i.e. that it replayed all the WAL records that were generated on the master before it was shut down for the final time. I

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-26 Thread Bruce Momjian
On Thu, Jul 26, 2012 at 02:26:16PM -0700, Jeff Davis wrote: On Thu, 2012-07-26 at 14:17 -0400, Bruce Momjian wrote: Yes, that would be a problem because the WAL records are deleted by pg_upgrade. Does a shutdown of the standby not already replay all WAL logs? There is no notion of all

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-23 Thread Robert Haas
On Sat, Jul 21, 2012 at 8:57 AM, Bruce Momjian br...@momjian.us wrote: On Sat, Jul 21, 2012 at 11:24:21AM +0300, Peter Eisentraut wrote: On fre, 2012-07-20 at 13:11 -0400, Bruce Momjian wrote: I think the commands to run after pg_upgrade --link completes on both primary and standby might be

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-23 Thread Peter Eisentraut
On mån, 2012-07-23 at 10:08 -0400, Robert Haas wrote: Relying on the number of hard links seems very fragile. For example, it'll break if you are using copy mode. And it won't work on Windows, either. pg_upgrade could remember the list of files that the user would need to copy to the remote

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-21 Thread Peter Eisentraut
On fre, 2012-07-20 at 13:11 -0400, Bruce Momjian wrote: I think the commands to run after pg_upgrade --link completes on both primary and standby might be as easy as: cd /u/pg/pgsql.old/data find . -links 1 -exec cp {} /u/pgsql/data \; Why would we want anything more

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-21 Thread Bruce Momjian
On Sat, Jul 21, 2012 at 11:24:21AM +0300, Peter Eisentraut wrote: On fre, 2012-07-20 at 13:11 -0400, Bruce Momjian wrote: I think the commands to run after pg_upgrade --link completes on both primary and standby might be as easy as: cd /u/pg/pgsql.old/data find . -links

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-20 Thread Bruce Momjian
On Tue, Jul 17, 2012 at 06:02:40PM -0400, Bruce Momjian wrote: Second, the user files (large) are certainly identical, it is only the system tables (small) that _might_ be different, so rsync'ing just those would add the guarantee, but I know of no easy way to rsync just the system tables.

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-20 Thread Aidan Van Dyk
If you're wanting to automatically do some upgrades wouldn't an easier route be: 1) run pg_upgrade, up to the point where it actually start's copying/linking in old cluster data files, and stop the new postmaster. 2) Take a base backup style copy (tar, rsync, $FAVOURITE) of the new cluster

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-20 Thread Bruce Momjian
On Fri, Jul 20, 2012 at 12:39:12PM -0400, Aidan Van Dyk wrote: If you're wanting to automatically do some upgrades wouldn't an easier route be: 1) run pg_upgrade, up to the point where it actually start's copying/linking in old cluster data files, and stop the new postmaster. 2) Take a

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-19 Thread Bruce Momjian
On Wed, Jul 18, 2012 at 09:36:51AM -0400, Robert Haas wrote: On Tue, Jul 17, 2012 at 6:02 PM, Bruce Momjian br...@momjian.us wrote: However, I have two ideas. First, I don't know _why_ the primary/standby would be any different after pg_upgrade, so I added the documentation mention because

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-19 Thread Robert Haas
On Thu, Jul 19, 2012 at 2:38 AM, Bruce Momjian br...@momjian.us wrote: No, the point is they run pg_upgrade on the stopped primary and stopped standbys. Are those the same? I am not really sure. Of course not. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-19 Thread Bruce Momjian
On Thu, Jul 19, 2012 at 09:41:29AM -0400, Robert Haas wrote: On Thu, Jul 19, 2012 at 2:38 AM, Bruce Momjian br...@momjian.us wrote: No, the point is they run pg_upgrade on the stopped primary and stopped standbys. Are those the same? I am not really sure. Of course not. OK, but why?

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-19 Thread Robert Haas
On Thu, Jul 19, 2012 at 12:02 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Jul 19, 2012 at 09:41:29AM -0400, Robert Haas wrote: On Thu, Jul 19, 2012 at 2:38 AM, Bruce Momjian br...@momjian.us wrote: No, the point is they run pg_upgrade on the stopped primary and stopped standbys. Are

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-19 Thread Bruce Momjian
On Thu, Jul 19, 2012 at 12:43:23PM -0400, Robert Haas wrote: On Thu, Jul 19, 2012 at 12:02 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Jul 19, 2012 at 09:41:29AM -0400, Robert Haas wrote: On Thu, Jul 19, 2012 at 2:38 AM, Bruce Momjian br...@momjian.us wrote: No, the point is they run

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-18 Thread Daniel Farina
On Tue, Jul 17, 2012 at 9:16 PM, Bruce Momjian br...@momjian.us wrote: WAL is not guaranteed to be the same between PG major versions, so doing anything with WAL is pretty much a no-go. I understand that the WAL format changes, sometimes dramatically between versions. What I'm suggesting that

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-18 Thread Robert Haas
On Tue, Jul 17, 2012 at 6:02 PM, Bruce Momjian br...@momjian.us wrote: However, I have two ideas. First, I don't know _why_ the primary/standby would be any different after pg_upgrade, so I added the documentation mention because I couldn't _guarantee_ they were the same. Actually, if people

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-17 Thread Daniel Farina
On Mon, Jul 16, 2012 at 5:29 PM, Jeff Davis pg...@j-davis.com wrote: On Tue, 2012-07-10 at 11:50 -0400, Bruce Momjian wrote: I don't think we can assume that because pg_upgrade was run on the master and standby that they are binary identical, can we? Technically the user file are identical,

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-17 Thread Jeff Davis
On Tue, 2012-07-17 at 01:02 -0700, Daniel Farina wrote: Could pg_upgrade emit WAL segment(s) to provide continuity of a timeline? So something like: By segments did you mean records? * Take down the writable primary for pg_upgrade * Some WAL is emitted and possibly archived * The old

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-17 Thread Bruce Momjian
On Mon, Jul 16, 2012 at 05:29:26PM -0700, Jeff Davis wrote: On Tue, 2012-07-10 at 11:50 -0400, Bruce Momjian wrote: I don't think we can assume that because pg_upgrade was run on the master and standby that they are binary identical, can we? Technically the user file are identical, but the

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-17 Thread Daniel Farina
On Tue, Jul 17, 2012 at 11:55 AM, Jeff Davis pg...@j-davis.com wrote: On Tue, 2012-07-17 at 01:02 -0700, Daniel Farina wrote: Could pg_upgrade emit WAL segment(s) to provide continuity of a timeline? So something like: By segments did you mean records? Yes. It would be nicer not to have to

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-17 Thread Bruce Momjian
On Tue, Jul 17, 2012 at 04:49:39PM -0700, Daniel Farina wrote: On Tue, Jul 17, 2012 at 11:55 AM, Jeff Davis pg...@j-davis.com wrote: On Tue, 2012-07-17 at 01:02 -0700, Daniel Farina wrote: Could pg_upgrade emit WAL segment(s) to provide continuity of a timeline? So something like: By

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-16 Thread Jeff Davis
On Tue, 2012-07-10 at 11:50 -0400, Bruce Momjian wrote: I don't think we can assume that because pg_upgrade was run on the master and standby that they are binary identical, can we? Technically the user file are identical, but the system catalogs and WAL might be different, hence my

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-10 Thread Tom Lane
Bruce Momjian br...@momjian.us writes: +While a Log-Shipping Standby Server (xref linkend=warm-standby) can +be upgraded, the server must be in changed to a primary server to allow +writes, and after the upgrade it cannot be reused as a standby server. +(Running commandrsync/

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-10 Thread Bruce Momjian
On Tue, Jul 10, 2012 at 12:04:50PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: +While a Log-Shipping Standby Server (xref linkend=warm-standby) can +be upgraded, the server must be in changed to a primary server to allow +writes, and after the upgrade it cannot

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-10 Thread Magnus Hagander
On Tue, Jul 10, 2012 at 6:17 PM, Bruce Momjian br...@momjian.us wrote: On Tue, Jul 10, 2012 at 12:04:50PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: +While a Log-Shipping Standby Server (xref linkend=warm-standby) can +be upgraded, the server must be in changed

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-10 Thread Bruce Momjian
On Tue, Jul 10, 2012 at 06:21:35PM +0200, Magnus Hagander wrote: On Tue, Jul 10, 2012 at 6:17 PM, Bruce Momjian br...@momjian.us wrote: On Tue, Jul 10, 2012 at 12:04:50PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: +While a Log-Shipping Standby Server (xref

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-10 Thread Magnus Hagander
On Tue, Jul 10, 2012 at 6:26 PM, Bruce Momjian br...@momjian.us wrote: On Tue, Jul 10, 2012 at 06:21:35PM +0200, Magnus Hagander wrote: On Tue, Jul 10, 2012 at 6:17 PM, Bruce Momjian br...@momjian.us wrote: On Tue, Jul 10, 2012 at 12:04:50PM -0400, Tom Lane wrote: Bruce Momjian

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-10 Thread Bruce Momjian
On Tue, Jul 10, 2012 at 06:29:24PM +0200, Magnus Hagander wrote: Testing maybe? I feel we have just avoided saying what you can and can't do with the standbys and pg_upgrade, so I think we have to state something. If we just want to say recreate, let's say that. Well, the bottom line is

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-10 Thread Magnus Hagander
On Tue, Jul 10, 2012 at 6:59 PM, Bruce Momjian br...@momjian.us wrote: On Tue, Jul 10, 2012 at 06:29:24PM +0200, Magnus Hagander wrote: Testing maybe? I feel we have just avoided saying what you can and can't do with the standbys and pg_upgrade, so I think we have to state something. If

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-10 Thread Bruce Momjian
On Tue, Jul 10, 2012 at 07:06:39PM +0200, Magnus Hagander wrote: rsync where and how? What are you actually trying to suggest people do? Updated docs attached. I suggest just removing the rsync part completely. You're basically saying you ca nset up a new standby after you're

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-10 Thread Magnus Hagander
On Tue, Jul 10, 2012 at 7:27 PM, Bruce Momjian br...@momjian.us wrote: On Tue, Jul 10, 2012 at 07:06:39PM +0200, Magnus Hagander wrote: rsync where and how? What are you actually trying to suggest people do? Updated docs attached. I suggest just removing the rsync part completely.

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

2012-07-10 Thread Bruce Momjian
On Tue, Jul 10, 2012 at 09:10:25PM +0200, Magnus Hagander wrote: On Tue, Jul 10, 2012 at 7:27 PM, Bruce Momjian br...@momjian.us wrote: On Tue, Jul 10, 2012 at 07:06:39PM +0200, Magnus Hagander wrote: rsync where and how? What are you actually trying to suggest people do?