State of Beta 2

[Ron Johnson]

Re: need for in-place upgrades (was Re: State of

On Sun, 2003-09-14 at 14:17, Christopher Browne wrote:[color=blue]
> After a long battle with technology,mart in@bugs.unl.edu .ar (Martin Marques), an earthling, wrote:[color=green]
> > El Dom 14 Sep 2003 12:20, Lincoln Yeoh escribió:[color=darkred]
> >> >At 07:16 PM 9/13/2003 -0400, Lamar Owen wrote:[/color][/color][/color]
[snip][color=blue]
> Certainly there are backup systems designed to cope with those sorts
> of quantities of data. With 8 tape drives, and a rack system that
> holds 200 cartridges, you not only can store a HUGE pile of data, but
> you can push it onto tape about as quickly as you can generate it.
>
> <http://spectralogic.co m> discusses how to use their hardware and
> software products to do terabytes of backups in an hour. They sell a
> software product called "Alexandria " that knows how to (at least
> somewhat) intelligently backup SAP R/3, Oracle, Informix, and Sybase
> systems. (When I was at American Airlines, that was the software in
> use._[/color]

HP, Hitachi, and a number of other vendors make similar hardware.

You mean the database vendors don't build that parallelism into
their backup procedures?
[color=blue]
> Generally, this involves having a bunch of tape drives that are
> simultaneously streaming different parts of the backup.
>
> When it's Oracle that's in use, a common strategy involves
> periodically doing a "hot" backup (so you can quickly get back to a
> known database state), and then having a robot tape drive assigned to
> regularly push archive logs to tape as they are produced.[/color]

Rdb does the same thing. You mean DB/2 can't/doesn't do that?

[snip][color=blue]
> None of this is particularly cheap or easy; need I remind gentle
> readers that if you can't afford that, then you essentially can't
> afford to claim "High Availability?"[/color]

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@c ox.net
Jefferson, LA USA

"(Women are) like compilers. They take simple statements and
make them into big productions."
Pitr Dubovitch


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Christopher Browne]

Re: need for in-place upgrades (was Re: State of

In the last exciting episode, ron.l.johnson@c ox.net (Ron Johnson) wrote:[color=blue]
> On Sun, 2003-09-14 at 14:17, Christopher Browne wrote:[color=green]
>> <http://spectralogic.co m> discusses how to use their hardware and
>> software products to do terabytes of backups in an hour. They sell a
>> software product called "Alexandria " that knows how to (at least
>> somewhat) intelligently backup SAP R/3, Oracle, Informix, and Sybase
>> systems. (When I was at American Airlines, that was the software in
>> use._[/color]
>
> HP, Hitachi, and a number of other vendors make similar hardware.
>
> You mean the database vendors don't build that parallelism into
> their backup procedures?[/color]

They don't necessarily build every conceivable bit of possible
functionality into the backup procedures they provide, if that's what
you mean.

Of thee systems mentioned, I'm most familiar with SAP's backup
regimen; if you're using it with Oracle, you'll use tools called
"brbackup" and "brarchive" , which provide a _moderately_ sophisticated
scheme for dealing with backing things up.

But if you need to do something wild, involving having two servers
each having 8 tape drives on a nearby servers that are used to manage
backups for a whole cluster of systems, including a combination of OS
backups, DB backups, and application backups, it's _not_ reasonable to
expect one DB vendor's backup tools to be totally adequate to that.

Alexandria (and similar software) certainly needs tool support from DB
makers to allow them to intelligently handle streaming the data out of
the databases.

At present, this unfortunately _isn't_ something PostgreSQL does, from
two perspectives:

1. You can't simply keep the WALs and reapply them in order to bring
a second database up to date;

2. A pg_dump doesn't provide a way of streaming parts of the
database in parallel, at least not if all the data is in
one database. (There's some nifty stuff in eRServ that
might eventually be relevant, but probably not yet...)

There are partial answers:

- If there are multiple databases, starting multiple pg_dump
sessions provides some useful parallelism;

- A suitable logical volume manager may allow splitting off
a copy atomically, and then you can grab the resulting data
in "strips" to pull it in parallel.

Life isn't always perfect.
[color=blue][color=green]
>> Generally, this involves having a bunch of tape drives that are
>> simultaneously streaming different parts of the backup.
>>
>> When it's Oracle that's in use, a common strategy involves
>> periodically doing a "hot" backup (so you can quickly get back to a
>> known database state), and then having a robot tape drive assigned
>> to regularly push archive logs to tape as they are produced.[/color]
>
> Rdb does the same thing. You mean DB/2 can't/doesn't do that?[/color]

I haven't the foggiest idea, although I would be somewhat surprised if
it doesn't have something of the sort.
--
(reverse (concatenate 'string "moc.enworb bc" "@" "enworbbc") )

http://www.ntlug.org/~cbbrowne/wp.html

Rules of the Evil Overlord #139. "If I'm sitting in my camp, hear a
twig snap, start to investigate, then encounter a small woodland
creature, I will send out some scouts anyway just to be on the safe
side. (If they disappear into the foliage, I will not send out another
patrol; I will break out napalm and Agent Orange.)"
<http://www.eviloverlor d.com/>

[Network Administrator]

Re: State of Beta 2

Quoting Tom Lane <tgl@sss.pgh.pa .us>:
[color=blue]
> Network Administrator <netadmin@vcsn. com> writes:[color=green]
> > The abstraction I am talking about would be a logical layer that would[/color]
> handle[color=green]
> > disk I/O including the format of that data (lets call this the ADH).[/color]
>
> This sounds good in the abstract, but I don't see how you would define
> such a layer in a way that was both thin and able to cope with large
> changes in representation. In a very real sense, "handle disk I/O
> including the format of the data" describes the entire backend. To
> create an abstraction layer that will actually give any traction for
> maintenance, you'd have to find a way to slice it much more narrowly
> than that.[/color]

*nod* I thought that would probably be the case. The "thickness" of that layer
would be directly related to how the backend was sliced. However it seems to me
that right now that this might not be possible while the backend is changing
between major releases. Perhaps once that doesn't fluxate as much it might be
feasible to create these layer so that it is not too fat.

Maybe the goal is too aggressive. To ask (hopefully) a simpler question. Would
it be possible to at compile time choose the on disk representation? I'm not
sure but I think that might reduce the complexity since the abstraction would
only exist before the application is built. Once compiled there would be no
ambiguity in what representation is chosen.
[color=blue]
> Even if the approach can be made to work, defining such a layer and then
> revising all the existing code to go through it would be a huge amount
> of work.
>
> Ultimately there's no substitute for hard work :-(
>
> regards, tom lane[/color]

True, which is why I've never been bothered about going through a process to
maintain my database's integrity and performance. However, over time, that
across my entire client base I will eventually reach a point where I will need
to do an "in place" upgrade or at least limit database downtime to a 60 minute
window- or less.



--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.

http://vcsn.com

_______________ _______________ ______
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Re: State of Beta 2

Network Administrator <netadmin@vcsn. com> writes:[color=blue]
> ... However it seems to me that right now that this might not be
> possible while the backend is changing between major releases.
> Perhaps once that doesn't fluxate as much it might be feasible to
> create these layer so that it is not too fat.[/color]

Yeah, that's been in the back of my mind also. Once we have tablespaces
and a couple of the other basic features we're still missing, it might
be a more reasonable proposition to freeze the on-disk representation.

At the very least we could quantize it a little more --- say, group
changes that affect user table representation into every third or fourth
release.

But until we have a production-quality "pg_upgrade " this is all moot.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Ron Johnson]

Table spaces (was Re: State of Beta 2)

On Sun, 2003-09-14 at 23:08, Tom Lane wrote:[color=blue]
> Network Administrator <netadmin@vcsn. com> writes:[color=green]
> > ... However it seems to me that right now that this might not be
> > possible while the backend is changing between major releases.
> > Perhaps once that doesn't fluxate as much it might be feasible to
> > create these layer so that it is not too fat.[/color]
>
> Yeah, that's been in the back of my mind also. Once we have tablespaces
> and a couple of the other basic features we're still missing, it might
> be a more reasonable proposition to freeze the on-disk representation.[/color]

I think that every effort should be made so that the on-disk struct-
ure (ODS) doesn't have to change when tablespaces is implemented.
I.e., oid-based files live side-by-side with tablespaces.

At a minimum, it should be "ok, you don't *have* to do a dump/restore
to migrate to v7.7, but if you want the tablespaces that are brand
new in v7.7, you must dump data, and recreate the schema with table-
spaces before restoring".

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@c ox.net
Jefferson, LA USA

"(Women are) like compilers. They take simple statements and
make them into big productions."
Pitr Dubovitch


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Re: Table spaces (was Re: State of Beta 2)

Ron Johnson <ron.l.johnson@ cox.net> writes:[color=blue]
> On Sun, 2003-09-14 at 23:08, Tom Lane wrote:[color=green]
>> Yeah, that's been in the back of my mind also. Once we have tablespaces
>> and a couple of the other basic features we're still missing, it might
>> be a more reasonable proposition to freeze the on-disk representation.[/color][/color]
[color=blue]
> I think that every effort should be made so that the on-disk struct-
> ure (ODS) doesn't have to change when tablespaces is implemented.[/color]

That's not going to happen --- tablespaces will be complex enough
without trying to support a backwards-compatible special case.

If we have a workable pg_upgrade by the time tablespaces happen, it
would be reasonable to expect it to be able to rearrange the user data
files of an existing installation into the new directory layout. If
we don't, the issue is moot anyway.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Lamar Owen]

Re: need for in-place upgrades (was Re: State of

Martin Marques wrote:[color=blue]
> El Dom 14 Sep 2003 12:20, Lincoln Yeoh escribió:[color=green][color=darkred]
>>>At 07:16 PM 9/13/2003 -0400, Lamar Owen wrote:
>>>'migration ' server. And I really don't want to think about dump/restore
>>>of 100TB (if PostgreSQL actually stores the image files, which it might).[/color]
>>Hmm. Just curious, do people generally backup 100TB of data, or once most
>>reach this point they have to hope that it's just hardware failures they'll
>>deal with and not software/other issues?[/color]
> Normally you would have a RAID with mirroring and CRC, so that if one of the
> disks in the array of disks falls, the system keeps working. You can even
> have hot-pluggable disks, so you can change the disk that is broken without
> rebooting.[/color]

I did mention a SAN running Fibre Channel. I would have a portion of
the array in one building, and a portion of the array in another
building 1500 feet away. I have lots of fiber between buildings, a
portion of which I am currently using. So I can and will be doing RAID
over FC in a SAN, with spatial separation between portions of the array.
Now whether it is geographically separate _enough_, well that's a
different question. But I have thought through those issues already.

Using FC as a SAN in this way will complement my HA solution, which may
just be a hot failover server connected to the same SAN. I am still
investigating the failover mechanism; having two separate database data
stores has its advantages (software errors can render a RAID worse than
useless, since the RAID will distribute file corruption very
effectively). But I am not sure how it will work at present.

The buildings in question are somewhat unique, being that the portions
of the buildings I would be using were constructed by the US Army Corps
of Engineers. See www.pari.edu for more information.
--
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Lamar Owen]

Re: need for in-place upgrades (was Re: State of Beta 2)

Marc G. Fournier wrote:[color=blue]
> On Sat, 13 Sep 2003, Lamar Owen wrote:[color=green]
>>Can eRserver replicate a 7.3.x to a 7.2.x? Or 7.4.x to 7.3.x?[/color]
> I thought we were talking about upgrades here?[/color]

If eRserver can be used as a funnel for upgrading, then it by definition
must be able to replicate an older version to a newer. I was just
asking to see if indeed eRserver has that capability. If so, then that
may be usefule for those who can deal with a fully replicated datastore,
which might be an issue for various reasons.
--
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Joshua D. Drake]

Re: need for in-place upgrades (was Re: State of

[color=blue]
>
> 100TB sounds like a lot of backup media and time... Not to mention
> ensuring that the backups will work with available and functioning
> backup hardware.[/color]

It is alot but is is not a lot for something like an Insurance company
or a bank. Also 100TB is probably non-compressed although 30TB is still
large.

[color=blue]
>
> Head hurts just to think about it,
>
> Link.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org[/color]


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandpromp t.com - http://www.commandprompt.com
The most reliable support for the most reliable Open Source database.



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Joshua D. Drake]

Re: State of Beta 2

[color=blue]
> Strawmen. If we provide a good upgrade capability, we would just
> simply have to think about upgrades before changing features like
> that. The upgrade code could be cognizant of these sorts of things;
> and shoud be, in fact.[/color]

Sure but IMHO it would be more important to fix bugs like the parser not
correctly using indexes on bigint unless the value is quoted...

I think everyone would agree that not having to use initdb would be nice
but I think there is much more important things to focus on.

Besides if you are upgrading PostgreSQL in a production environment I
would assume there would be an extremely valid reason. If the reason is
big enough to do a major version upgrade then an initdb shouldn't be all
that bad of a requirement.

J



--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandpromp t.com - http://www.commandprompt.com
The most reliable support for the most reliable Open Source database.



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Lamar Owen]

Re: need for in-place upgrades (was Re: State of

Joshua D. Drake wrote:[color=blue]
> It is alot but is is not a lot for something like an Insurance company
> or a bank. Also 100TB is probably non-compressed although 30TB is still
> large.[/color]

Our requirements are such that this figure is our best guess after
compression. The amount of data prior to compression is much larger,
and consists of highly compressible astronomical observations in FITS
format.
--
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Lamar Owen]

Re: State of Beta 2

Joshua D. Drake wrote:[color=blue]
> Sure but IMHO it would be more important to fix bugs like the parser not
> correctly using indexes on bigint unless the value is quoted...[/color]
[color=blue]
> I think everyone would agree that not having to use initdb would be nice
> but I think there is much more important things to focus on.[/color]

Important is relative.
[color=blue]
> Besides if you are upgrading PostgreSQL in a production environment I
> would assume there would be an extremely valid reason. If the reason is
> big enough to do a major version upgrade then an initdb shouldn't be all
> that bad of a requirement.[/color]

I'm not going to rehash the arguments I have made before; they are all
archived. Suffice to say you are simply wrong. The number of
complaints over the years shows that there IS a need.
--
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Andrew Rawnsley]

Re: State of Beta 2


When I started this thread I made comment on the fact that this initdb
issue was treated somewhat "casually" on the lists. Not trying to
flame, or be an ass or anything, but this is kind of what I meant.

Yes, I know there many important issues the developers (bless their
overworked fingers) want/need to address that affect many people, and
I'm not going to presume to fault their choices. Some things make more
difference to others (the bigint indexing issue means little to me, for
example), so we try to point out these things in the hope that someone
may pick up on it, or the discussion may bear fruitful solutions that
no one had considered.

The initdb situation is a significant problem/obstacle to many people.
Avoiding it would be far more than 'nice' for us.

On Monday, September 15, 2003, at 02:24 PM, Joshua D. Drake wrote:
[color=blue]
>[color=green]
>> Strawmen. If we provide a good upgrade capability, we would just
>> simply have to think about upgrades before changing features like
>> that. The upgrade code could be cognizant of these sorts of things;
>> and shoud be, in fact.[/color]
>
> Sure but IMHO it would be more important to fix bugs like the parser
> not correctly using indexes on bigint unless the value is quoted...
>
> I think everyone would agree that not having to use initdb would be
> nice but I think there is much more important things to focus on.
>
> Besides if you are upgrading PostgreSQL in a production environment I
> would assume there would be an extremely valid reason. If the reason
> is big enough to do a major version upgrade then an initdb shouldn't
> be all that bad of a requirement.
>
> J
>
>
>
> --
> Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
> Postgresql support, programming shared hosting and dedicated hosting.
> +1-503-222-2783 - jd@commandpromp t.com - http://www.commandprompt.com
> The most reliable support for the most reliable Open Source database.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>[/color]
--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114

https://www.ravensfield.com

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Joshua D. Drake]

Re: State of Beta 2

[color=blue]
> I'm not going to rehash the arguments I have made before; they are all
> archived. Suffice to say you are simply wrong. The number of
> complaints over the years shows that there IS a need.[/color]


I at no point suggested that there was not a need. I only suggest that
the need may not be as great as some suspect or feel. To be honest -- if
your arguments were the "need" that everyone had... it would have been
implemented some how. It hasn't yet which would suggest that the number
of people that have the "need" at your level is not as great as the
number of people who have different "needs" from PostgreSQL.

Sincerely,

Joshua Drake




--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandpromp t.com - http://www.commandprompt.com
The most reliable support for the most reliable Open Source database.



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Vivek Khera]

Re: need for in-place upgrades

>>>>> "MGF" == Marc G Fournier <scrappy@postgr esql.org> writes:

MGF> On Sat, 13 Sep 2003, Lamar Owen wrote:
[color=blue][color=green]
>> Can eRserver replicate a 7.3.x to a 7.2.x? Or 7.4.x to 7.3.x?[/color][/color]

MGF> I thought we were talking about upgrades here?


I'm *really* interested in how eRServer works on migrating from 7.2 to
7.4 (either eRServer 1.2 or 1.3 :-) ) I have hopes of doing this once
7.4 goes gold. More testing for me, I guess.


--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.c om Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)