Thread: Attempting upgrade path; is this possible?

Attempting upgrade path; is this possible?

From
"Shaun Thomas"
Date:
I'm in charge of a very large database, and we're using a highly decrepit version of Postgresql currently.  After
searchingthrough the archives, Google, and trying out several replication engines, I have a question. 

I had originally considered Slony-I, as it doesn't seem to require version compatibility between nodes like pgCluster,
soupgrading from 7.4.2 to 8.1.3 would be a possible, if slow process.  But after looking into the level of
micro-managementnecessary, such as defining sets of every table on a per-database level, then having it add artificial
primary-keysto applicable tables, it just doesn't seem like a good choice.  Not a fault of Slony-I, but several
multi-gigdatabases hosting hundreds of tables would be a nightmare to use with Slony-I. 

Then I thought about the backup/recovery system and the WAL files.  Would this scenario be possible:

1. Do a pg_dumpall on the existing database running 7.4.2.
2. Do a psql -f foo template1 on the new database running 8.1.3.
3. Wait a very long time while the new database loads.
4. Shut down old database.
5. Start the new database in restore mode, and point it to the WAL  files from the old database.
6. Wait for restore to finish.
7. Restart the new database.

I wondered about this, as the pg_dumpall/restore would take a very long time for a 50GB database cluster, but
theoreticallythe WAL files would continue to accumulate on the old db while this loading was taking place.  If the WAL
formatswere compatible, the total upgrade time would only be restricted to how long it took to replay the WAL files in
thenew database.  Does the current format of the WAL files make this possible?  If not, is such an option for the
future?

Thanks in advance.
--

Shaun Thomas
Database Administrator
Leapfrog Online
847-440-8253

CONFIDENTIALITY NOTE
The document(s) accompanying this e-mail transmission, if any, and the e-mail transmittal message contain information
fromLeapfrog Online Customer Acquisition, LLC is confidential or privileged. The information is intended to be for the
useof the individual(s) or entity(ies) named on this e-mail transmission message. If you are not the intended
recipient,be aware that any disclosure, copying, distribution or use of the contents of this e-mail is prohibited. If
youhave received this e-mail in error, please immediately delete this e-mail and notify us by telephone of the error.  



Re: Attempting upgrade path; is this possible?

From
Hannu Krosing
Date:
Ühel kenal päeval, K, 2006-02-22 kell 16:02, kirjutas Shaun Thomas:
> I'm in charge of a very large database, and we're using a highly decrepit version of Postgresql currently.  
> After searching through the archives, Google, and trying out several replication engines, I have a question.
> 
> I had originally considered Slony-I, as it doesn't seem to require version compatibility between nodes 
> like pgCluster, so upgrading from 7.4.2 to 8.1.3 would be a possible, if slow process.  But after 
> looking into the level of micro-management necessary, such as defining sets of every table on a per-database level, 
> then having it add artificial primary-keys to applicable tables, it just doesn't seem like a good choice.  
> Not a fault of Slony-I, but several multi-gig databases hosting hundreds of tables would be a nightmare to use with
Slony-I.

Unfortunately Slony is still the only solution, if you need to upgrade
between major versions with minimal (i.e tens of seconds) downtime.

You could try to script the subscription process in Slony. There should
even be some ready-made perl scripts for that bundled with slony.

The requirement for (candidate) primary key's may still be prohibiting,
as currently creating an index on large table locks that table for
writes for the duration of create index.

> Then I thought about the backup/recovery system and the WAL files.  Would this scenario be possible:
> 
> 1. Do a pg_dumpall on the existing database running 7.4.2.
> 2. Do a psql -f foo template1 on the new database running 8.1.3.
> 3. Wait a very long time while the new database loads.
> 4. Shut down old database.
> 5. Start the new database in restore mode, and point it to the WAL
>    files from the old database.
> 6. Wait for restore to finish.
> 7. Restart the new database.
> 
> I wondered about this, as the pg_dumpall/restore would take a very long time for a 50GB database 
> cluster, but theoretically the WAL files would continue to accumulate on the old db while this 
> loading was taking place.  If the WAL formats were compatible, the total upgrade time would only 
> be restricted to how long it took to replay the WAL files in the new database. 

This works not!

WAL files store actual page images, so if the WAL formats were
compatible, you would not need to do any data manipulation at all, as
the on-disk formats would be compatible.

Also, WAL is done on physical page image level, so the above scenario
would not even work for 7.4.2->7.4.2 copy.

-------------
Hannu



Re: Attempting upgrade path; is this possible?

From
Gavin Sherry
Date:
On Wed, 22 Feb 2006, Shaun Thomas wrote:

> I'm in charge of a very large database, and we're using a highly
> decrepit version of Postgresql currently.  After searching through the
> archives, Google, and trying out several replication engines, I have a
> question.
>
> I had originally considered Slony-I, as it doesn't seem to require
> version compatibility between nodes like pgCluster, so upgrading from
> 7.4.2 to 8.1.3 would be a possible, if slow process.  But after looking
> into the level of micro-management necessary, such as defining sets of
> every table on a per-database level, then having it add artificial
> primary-keys to applicable tables, it just doesn't seem like a good
> choice.  Not a fault of Slony-I, but several multi-gig databases hosting
> hundreds of tables would be a nightmare to use with Slony-I.

There are tools in the /tools directory. In particular, take a look at
/tools/altperl. You can use to set up slony and replicate all tables with
very little hassle. Slony adds the primary keys for you.

>
> Then I thought about the backup/recovery system and the WAL files.
> Would this scenario be possible:
>
> 1. Do a pg_dumpall on the existing database running 7.4.2.
> 2. Do a psql -f foo template1 on the new database running 8.1.3.
> 3. Wait a very long time while the new database loads.
> 4. Shut down old database.
> 5. Start the new database in restore mode, and point it to the WAL
>    files from the old database.
> 6. Wait for restore to finish.
> 7. Restart the new database.

This is not possible. On your 7.4 systems tables have a unique object ID
to identify them. When you restore the dump on the 8.1 system they will
have different object IDs. There are several other issues of this
nature.

Also, the binary format of the log files has changed and the whole process
would be significantly more difficult than using slony to upgrade. The
size of your databases does not sound like an issue - lots of people have
done what you're doing with GB range databases.

>
> I wondered about this, as the pg_dumpall/restore would take a very long
> time  for a 50GB database cluster, but theoretically the WAL files would
> continue to accumulate on the old db while this loading was taking
> place.
> If the WAL formats were compatible, the total upgrade time would only be
> restricted to how long it took to replay the WAL files in the new
> database.  Does the current format of the WAL files make this possible?
> If not, is such an option for the future?

It is possible that someone could write a translation tool which
translates WAL entries into SQL, but there is some messiness to deal with
(backup blocks, create table foo; insert into foo; drop table foo; and
more). I think the best solution is an inplace upgrade tool which does all
the binary conversions, additions and subtractions itself. This could be
quite cheap because the conversion will often only affect system catalogs
not user tables.

Thanks,

Gavin


Which func tells the tuple size of a given relation?

From
"John"
Date:
I am hacking the parser to do multi-class-classification. I am wondering 
which function can tell the size of a relation. I tried to do something like
          int row_sz = 0;         pgstat_count_heap_fetch(&rel->pgstat_info);         row_sz = ((PgStat_TableEntry 
*)((&rel->pgstat_info)->tabentry))->t_tuples_fetched;

But the system does not like it and the DB broke down halfway.

Thanks,
John 


Re: Attempting upgrade path; is this possible?

From
"Jim C. Nasby"
Date:
On Thu, Feb 23, 2006 at 12:51:29AM +0200, Hannu Krosing wrote:
> The requirement for (candidate) primary key's may still be prohibiting,
> as currently creating an index on large table locks that table for
> writes for the duration of create index.

Plus, if the tables already have OIDs, you *might* be able to create a
unique index on those (watch out for OID rollover, though) and tell
slony to use that (actually, one of the perl tools should pick it up
automagically).

Or, like Hannu suggests, just let slony create the keys for you. You can
always drop them later. Actually, I believe Slony will drop them for you
if you drop the node.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461