Обсуждение: problematic upgrade
Hi! One of my colleagues upgraded his postgresql 7.3.x installation to 7.4 without pg_(dump/restore) cycle. Will he encounter any problems with working with such db? Is there a way to correct it? Regards, -Marcin Gil
Marcin Gil wrote: > Hi! > > One of my colleagues upgraded his postgresql 7.3.x installation > to 7.4 without pg_(dump/restore) cycle. Will he encounter any > problems with working with such db? Is there a way to correct it? No, there is not. I assume the server does not work because the version stamps in PG_VERSION will disallow such a server from starting. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
----Original Message---- From: Bruce Momjian [mailto:pgman@candle.pha.pa.us] Sent: Monday, November 29, 2004 2:13 PM To: Marcin Gil Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] problematic upgrade > No, there is not. I assume the server does not work because > the version stamps in PG_VERSION will disallow such a server > from starting. You might be surprised but server works. I believe he installed new postgres, initiated it and copied database folders - but I'll have to confirm that. Regards, -Marcin Gil -- OIS "Audax" Sp. z o.o., Tomaszow Mazowiecki, ul. Barlickiego 4 tel. (+48 44) 7247530 ext. 22, fax. (+48 44) 7247530 ext. 25, mobile +48 509229662
"Marcin Gil" <marcin.gil@audax.com.pl> writes:
>> No, there is not. I assume the server does not work because
>> the version stamps in PG_VERSION will disallow such a server
>> from starting.
> You might be surprised but server works. I believe he installed new
> postgres,
> initiated it and copied database folders - but I'll have to confirm that.
For sufficiently small values of "works", that might be true, but this
procedure strikes me as unbelievably dangerous. He'll certainly get
zero sympathy from here if he finds any database corruption.
regards, tom lane
> For sufficiently small values of "works", that might be true, > but this procedure strikes me as unbelievably dangerous. He'll > certainly get zero sympathy from here if he finds any database > corruption. > > regards, tom lane I know that there are a few ways to back up a PostgreSQL database but is there any one method that is generally recommended above others? Although my usage of PostgreSQL is strictly for personal use and as a learning tool, I don't want to lose any data. Thanks, -- Jeff Flowers
"Jeff Flowers" <duckfoo@fastmail.fm> writes: > I know that there are a few ways to back up a PostgreSQL database but is > there any one method that is generally recommended above others? You can either use pg_dump or do filesystem backups *with the postmaster stopped*. See the Administrator's Guide: http://www.postgresql.org/docs/7.4/static/backup.html PG 8.0 will allow a third basic approach, on-line backup: http://developer.postgresql.org/docs/postgres/backup.html The "File system level backup" subsection in that chapter explains why piecemeal copying of data files (which is what I understood Marcin to say his friend did) doesn't work. Add to that the issues associated with a cross-version changeover (such as, usually, changes in system catalog contents and layout), and I'm amazed that the thing appeared to work at all. regards, tom lane
----Original Message---- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Tom Lane Sent: Monday, November 29, 2004 8:31 PM To: Jeff Flowers Cc: PGSQL-Novice Subject: Re: [NOVICE] problematic upgrade > layout), and I'm amazed that the thing appeared to work at all. > That shows only one thing: the highest quality of PostgreSQL. Even if user did something as stupid, these piece still works just like a Glock 17 (shots under water, dirtied with sand). Now he will have to "bite the bullet" ;) Now is there a way to recover (I have to give him a solution)? My thought: downgrade to 7.3.x, pg_dump, upgrade to 7.4, pg_restore. Will this work? Regards, -Marcin Gil -- OIS "Audax" Sp. z o.o., Tomaszów Mazowiecki, ul. Barlickiego 4 tel. (+48 44) 7247530 ext. 22, fax. (+48 44) 7247530 ext. 25, mobile +48 509229662
"Marcin Gil" <marcin.gil@audax.com.pl> writes:
> Now is there a way to recover (I have to give him a solution)?
> My thought: downgrade to 7.3.x, pg_dump, upgrade to 7.4, pg_restore.
> Will this work?
I don't think the 7.3 server will start up against the hacked-up
database, if that's what you mean. If he can go back to the way things
were just before he started messing with the database, then certainly he
can pg_dump from the 7.3 installation and go forward from there.
Another possibility is to try pg_dump'ing from what he's got, but
I can't say that I'd have a lot of faith in the result. You'd want
to examine the dump very carefully by hand before assuming it's good.
regards, tom lane
----Original Message---- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Tom Lane Sent: Tuesday, November 30, 2004 5:04 PM To: Marcin Gil Cc: 'PGSQL-Novice' Subject: Re: [NOVICE] problematic upgrade > I don't think the 7.3 server will start up against the > hacked-up database, if that's what you mean. If he can go > back to the way things were just before he started messing > with the database, then certainly he can pg_dump from the 7.3 > installation and go forward from there. > I think it's the only reasonable solution. Thrash all data for now, recover from an old backup @ 7.3 and dump; restore @ 7.4 and refill missing data. Lotta job but it is better then hand-check all dumps from this 7.4-hacked. Thank all of you, -Marcin Gil