Thread: Migrating from 8.3 to 8.4 on the same server

Migrating from 8.3 to 8.4 on the same server

From
Lewis Kapell
Date:
In the documentation under the heading "Migration Between Releases" we
read:

<<It is also possible to use replication methods, such as Slony, to
create a slave server with the updated version of PostgreSQL. The slave
can be on the same computer or a different computer. [...] Such a
switch-over results in only several seconds of downtime for an upgrade. >>

In the section "Warm Standby Servers for High Availability" it says:

<< For testing purposes, it is possible to run both primary and standby
servers on the same system. >>

Although this section does not use the term Point-In-Time Recovery, I
understand this is what it refers to.  Could the WAL/PITR method then be
used to upgrade from 8.3 to 8.4 on the same server and avoid having the
significant downtime of dump/restore?

If so, I understand the two servers would have to run on different
ports.  Are there any other issues/traps to be aware of?

--

Thank you,

Lewis Kapell
Computer Operations
Seton Home Study School


Re: Migrating from 8.3 to 8.4 on the same server

From
"Kevin Grittner"
Date:
Lewis Kapell <lkapell@setonhome.org> wrote:

> Could the WAL/PITR method then be used to upgrade from 8.3 to 8.4
> on the same server and avoid having the significant downtime of
> dump/restore?

No, but you might want to check out pg_migrator.

http://pgfoundry.org/projects/pg-migrator/

-Kevin

Re: Migrating from 8.3 to 8.4 on the same server

From
Kenneth Marshall
Date:
On Wed, Apr 14, 2010 at 01:29:23PM -0400, Lewis Kapell wrote:
> In the documentation under the heading "Migration Between Releases" we
> read:
>
> <<It is also possible to use replication methods, such as Slony, to create
> a slave server with the updated version of PostgreSQL. The slave can be on
> the same computer or a different computer. [...] Such a switch-over results
> in only several seconds of downtime for an upgrade. >>
>
> In the section "Warm Standby Servers for High Availability" it says:
>
> << For testing purposes, it is possible to run both primary and standby
> servers on the same system. >>
>
> Although this section does not use the term Point-In-Time Recovery, I
> understand this is what it refers to.  Could the WAL/PITR method then be
> used to upgrade from 8.3 to 8.4 on the same server and avoid having the
> significant downtime of dump/restore?
>
> If so, I understand the two servers would have to run on different ports.
> Are there any other issues/traps to be aware of?
>
> --
>
> Thank you,
>
> Lewis Kapell
> Computer Operations
> Seton Home Study School
>
I believe that the server versions must be the same in PITR so
you cannot use it to upgrade.

Cheers,
Ken

Re: Migrating from 8.3 to 8.4 on the same server

From
Scott Mead
Date:

On Wed, Apr 14, 2010 at 1:42 PM, Kenneth Marshall <ktm@rice.edu> wrote:
On Wed, Apr 14, 2010 at 01:29:23PM -0400, Lewis Kapell wrote:
> In the documentation under the heading "Migration Between Releases" we
> read:
>
> <<It is also possible to use replication methods, such as Slony, to create
> a slave server with the updated version of PostgreSQL. The slave can be on
> the same computer or a different computer. [...] Such a switch-over results
> in only several seconds of downtime for an upgrade. >>
>
> In the section "Warm Standby Servers for High Availability" it says:
>
> << For testing purposes, it is possible to run both primary and standby
> servers on the same system. >>
>
> Although this section does not use the term Point-In-Time Recovery, I
> understand this is what it refers to.  Could the WAL/PITR method then be
> used to upgrade from 8.3 to 8.4 on the same server and avoid having the
> significant downtime of dump/restore?
>
> If so, I understand the two servers would have to run on different ports.
> Are there any other issues/traps to be aware of?
>
> --
>
> Thank you,
>
> Lewis Kapell
> Computer Operations
> Seton Home Study School
>
I believe that the server versions must be the same in PITR so
you cannot use it to upgrade.

  Exactly, remember, one of the most important steps in PITR is the 'base' backup.  Your PITR slaves uses the same data files as its starting point as the master.  Since those aren't upgradeable (except in a few cases with pg_migrator) you're really out.  Even if you did get pg_migrator to upgrade the base files, I wouldn't use them in a PITR setup though.  I'm not 100% sure if it changed or not, but I would assume that the WAL record format changed from version to version.

--Scott
 

Cheers,
Ken

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Migrating from 8.3 to 8.4 on the same server

From
Iñigo Martinez Lasala
Date:
How big is your database?

If not very big, a pg_dump/pg_restore will be your best option. 8.3 to 8.4 is not a traumatic upgrade. In fact, it's really easy and probably you won't need to change your database schema.
And pg_restore in 8.4 is really FAST (compared with previous versions).

-----Original Message-----
From: Lewis Kapell <lkapell@setonhome.org>
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Migrating from 8.3 to 8.4 on the same server
Date: Wed, 14 Apr 2010 13:29:23 -0400

In the documentation under the heading "Migration Between Releases" we 
read:

<<It is also possible to use replication methods, such as Slony, to 
create a slave server with the updated version of PostgreSQL. The slave 
can be on the same computer or a different computer. [...] Such a 
switch-over results in only several seconds of downtime for an upgrade. >>

In the section "Warm Standby Servers for High Availability" it says:

<< For testing purposes, it is possible to run both primary and standby 
servers on the same system. >>

Although this section does not use the term Point-In-Time Recovery, I 
understand this is what it refers to.  Could the WAL/PITR method then be 
used to upgrade from 8.3 to 8.4 on the same server and avoid having the 
significant downtime of dump/restore?

If so, I understand the two servers would have to run on different 
ports.  Are there any other issues/traps to be aware of?

-- 

Thank you,

Lewis Kapell
Computer Operations
Seton Home Study School



Re: Migrating from 8.3 to 8.4 on the same server

From
Lewis Kapell
Date:
The database is about 12gb, the dump file is about 8gb.

I tested dump/restore into 8.4 on our test server, and it took an hour.
  This is a virtualized server, but my sysadmin thinks the performance
on our live server (not virtual) would be comparable.

It wouldn't kill us to have two or three hours of down time, but I would
like to avoid it.

Thank you,

Lewis Kapell
Computer Operations
Seton Home Study School



On 4/14/2010 4:28 PM, Iñigo Martinez Lasala wrote:
> How big is your database?
>
> If not very big, a pg_dump/pg_restore will be your best option. 8.3 to
> 8.4 is not a traumatic upgrade. In fact, it's really easy and probably
> you won't need to change your database schema.
> And pg_restore in 8.4 is really FAST (compared with previous versions).


Re: Migrating from 8.3 to 8.4 on the same server

From
Iñigo Martinez Lasala
Date:
I've never tested pg_migrator... it appears a good candidate for your upgrade, although I think it will work over datafiles, so test it carefully with a replica of your database.

Slony is always an option. It's not very difficult to deploy, but you have to manually create the schema in target database and all tables must include a primary key or an unique index. So, it's not  fire and forget. :-)

We have recently migrated our Gforge database from 8.1 to 8.4. It took about 50 minutes for dump (20GB database into a 7.8GB dump) and 40 minutes for restore. You can speed your restore process if you restore first your schema WITHOUT indexes, then restore data, and finally create indexes. 

-----Original Message-----
From: Lewis Kapell <lkapell@setonhome.org>
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Migrating from 8.3 to 8.4 on the same server
Date: Wed, 14 Apr 2010 16:36:11 -0400

The database is about 12gb, the dump file is about 8gb.

I tested dump/restore into 8.4 on our test server, and it took an hour.  This is a virtualized server, but my sysadmin thinks the performance 
on our live server (not virtual) would be comparable.

It wouldn't kill us to have two or three hours of down time, but I would 
like to avoid it.

Thank you,

Lewis Kapell
Computer Operations
Seton Home Study School



On 4/14/2010 4:28 PM, Iñigo Martinez Lasala wrote:
> How big is your database?
>
> If not very big, a pg_dump/pg_restore will be your best option. 8.3 to
> 8.4 is not a traumatic upgrade. In fact, it's really easy and probably
> you won't need to change your database schema.
> And pg_restore in 8.4 is really FAST (compared with previous versions).



Re: Migrating from 8.3 to 8.4 on the same server

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> It wouldn't kill us to have two or three hours of down time,
> but I would like to avoid it.

If it wouldn't kill you, I'd suck up the time. All other solutions
are going to take additional expertise, prep, and testing.

Keep in mind that if you have tables and/or databases that don't
change frequently, you can dump those beforehand to minimize
the final downtime.

You can also put your db in readonly mode to keep it available
while the export is going on. Depending on your server layout,
(e.g. available partitions) this may or may not be worth it.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201004141705
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkvGL0UACgkQvJuQZxSWSshrFgCgxQJGmwW/GEHDysaYqBzWD2PM
KiUAoMaq/nMeQWV3hbE8uoQzoF/FZVlx
=3b+F
-----END PGP SIGNATURE-----



Re: Migrating from 8.3 to 8.4 on the same server

From
Lewis Kapell
Date:
On 4/14/2010 5:11 PM, Greg Sabino Mullane wrote:
> If it wouldn't kill you, I'd suck up the time. All other solutions
> are going to take additional expertise, prep, and testing.
>
> Keep in mind that if you have tables and/or databases that don't
> change frequently, you can dump those beforehand to minimize
> the final downtime.

That's true, I had thought about that also.  I guess I would save the
contents of the biggest tables to flat files using the COPY command,
then delete the copied rows, before doing the dump.  Is what what you
had in mind?

Lewis


Re: Migrating from 8.3 to 8.4 on the same server

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


>> Keep in mind that if you have tables and/or databases that don't
>> change frequently, you can dump those beforehand to minimize
>> the final downtime.

> That's true, I had thought about that also.  I guess I would save the
> contents of the biggest tables to flat files using the COPY command,
> then delete the copied rows, before doing the dump.  Is what what you
> had in mind?

No need to delete the copied rows: just use the handy -t and -T flags
for pg_dump to include/exclude specific tables. You also don't need
to save to a flat file first unless you need/want a separate backup.
For large tables foo and bar, assuming 8.3 on port 5432 and 8.4 on port
5840, you could do:

* pg_dump -t foo -t bar -p 5432 | psql -p 5840 -f -

Then your final migration steps would be:

* Stop the app
* pg_dump -T foo -T bar -p 5432 | psql -p 5840 -f -
* Turn fsync back on and change the port to 5432 for 8.4
* Stop the 8.3 database
* Restart 8.4
* Start the app

All off the top of my head, requires heavy testing by QA first,
assumes foo and bar are not used, etc. /disclaimer

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201004151050
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkvHKDkACgkQvJuQZxSWSsjasACgmepXTrkz/rsoHsm/qKWxsyB9
YlYAoJHHeyd768IJC5RqNn2aXPD/80fp
=6yRN
-----END PGP SIGNATURE-----