Re: Using pg_upgrade on log-shipping standby servers

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 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 server (i.e., the list of files pg_upgrade itself
> > > copied or linked) and write that to a file.
> >
> > Good idea. A list of file names seems best, but wouldn't that list
> > exceed the maximum size of an argument list? How could we pass that
> > list to a command like scp?
>
> xargs

Yes, good idea.

> > 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.
>
> Exactly. Perhaps an example wouldn't hurt, but I wouldn't go too far.

Agreed.

> > 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 that somewhere in the known universe there is
> a way on Windows to say, here is a list of files, copy them to that
> host.

No idea.

> > Another problem is that the standby cluster might create _new_ files
> > that don't exist on the master, e.g. WAL files, and those have to be
> > removed. I am not clear how to do that either, except by removing all
> > files with a hard link count of 1, and again, this is difficult on
> > Windows.
>
> Well, then that would call for another list of files.

Well, not really. If we create a list of all user table/index files,
then any file not on the list would be removed on the standby, then all
the files in the primary not on the list are copied to the standby.
One list is less error-prone. This is easy in Unix shell and Perl, but
hard on Windows without Perl.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +