Обсуждение: Migrating from 8.3 to 8.4 on the same server

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

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

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

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

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).

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).

-----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-----

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

-----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-----