Re: Upgrading Database: need to dump and restore?

In response to Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>:

> On Wed, Jun 3, 2009 at 8:14 PM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
> > In response to "Carlos Oliva" <carlos(at)pbsinet(dot)com>:
> >
> >> Woudl it be possible to keep the current postgresql version running in a
> >> different port, install a new version of postgresql, and copy the data from
> >> one version to the other while both versions are running?  This might give
> >> us time to copy the tables and databases one at a time and reconfigure the
> >> database access for parts of the application until we complete the migration
> >> to the new version.
> >
> > Your best bet would be to install Slony-I.  One of the main design goals
> > for Slony is to allow interruption-free upgrades.
>
> I don't think it is "easy", but will do if you need to synchronize
> data before switching.

"easy" was not the point. I gathered from his comments that downtime is
an issue, and I know (from experience) that Slony provides the ability
to upgrade with almost no downtime, even with very large databases.

His plan of migrating tables one at a time may work, but it's frighteningly
error-prone. If he copies a table, how does he know the data hasn't
changed during the copy? What if he doesn't quite get all the clients
switched over all at once? How do you do a JOIN when one table is in
one database and the other somewhere else?

Once the DBs are in sync with Slony, a single command will switch to the
new server. If it doesn't go well (because he has a client compatibility
problem, for example -- casts anyone?) it's a simple process to switch
back, all with a guarantee that his data will never be lost, out of sync
or corrupted.

And if his application requires small downtime windows, this is a process
he will benefit from getting familiar with anyway.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/