Re: DBMS upgrade and backups

On Mon, Mar 14, 2011 at 1:38 PM, Vick Khera <vivek(at)khera(dot)org> wrote:

> On Mon, Mar 14, 2011 at 10:06 AM, Alexander Pyhalov <alp(at)rsu(dot)ru> wrote:
> > Am I right that on PostgreSQL upgrade to new major version (e.g. from 9.0
> to
> > 9.1) I'll loose my backups (base backups and wal files will be useless)?
> So
> > to go to past after DB upgrade I had to install old version(9.0),
> recover
> > data and then upgrade DBMS software...
> > What is the preferred way to deal with this issue?
>
> Your file-system based backups will be useless (but they probably were
> useless to start with, unless you shut down postgres while you took
> them).
>

I have used the system level backups and WAL files to clone a database on a
second server running the same binaries for PostgreSQL numerous times.

As long as you do a ' pg_start_backup ' before starting the system level
file copies
and ' pg_stop_backup ' afterwards, you should be fine.

But you are correct that you can't do that to move from one release to
another.

Your pg_dump generated backups will generally be useful. There have
> been occasional cases where an old pg_dump won't load into a current
> modern postgres install, but I think there were several major versions
> gap in those cases.
>

I recently tried loading a pg_dump of a large database dump file (around 20
GB) from 8.2 into 9.0 and it failed.

However restoring the 22GB pg_dumpall file worked.
--
Mike Nolan
nolan(at)tssi(dot)com