Re: Upgrading Database: need to dump and restore?

In response to "Carlos Oliva" <carlos@pbsinet.com>:

> Thank you for the link to the document.  It provides  a wealth of
> information that re-inforces your stements.  It is still somewhat unclear to
> me what it is that would change in the database for tables that are never
> updated (not inserts, updates, or deltes) after a certain point in time.
> That is, if a table is unchanged after a week, what in the database would
> change for the table later on?  We have some tables that we will use as a
> type of archive into which we woudl just insert some data for about a week
> or so and that will never again be updated.

Your question is ambiguous, thus it's difficult to answer.  What do you mean
by "change"?  At what level are you looking a things?

If you're talking about doing a pg_dump, then nothing changes.  If you don't
update/delete from that table, then it's going to be the same table every
time you pg_dump it.

If you're talking about doing a filesystem-level backp, then I wouldn't
assume anything.  Depending on various maintenance schedules, a vacuum
or reindex could change the files around (although the data doesn't
change).

Hope that clarifies.

> "Bill Moran" <wmoran@potentialtech.com> wrote in message
> news:20090604095554.c2d57008.wmoran@potentialtech.com...
> > In response to "Carlos Oliva" <carlos@pbsinet.com>:
> >
> >> I think that I understand.  Would we need to stop the databse and then do
> >> the copy?  Is this the state to which you are refering?  If the tables
> >> never
> >> changed after a week or so, what else would change in the database for
> >> these
> >> tables after a month, two months, or a year?  Would we need to put the
> >> databse in the correct state a week later, a month later, a year later?
> >
> > You really need to work on your posting etiquette a bit.  This thread is
> > painful to read because everything is jumbled together.
> >
> > There are two supported methods for backing up data.  These are separate,
> > you can do either or both, they have advantages and disadvantages.
> >
> > You should really read this chapter:
> > http://www.postgresql.org/docs/8.3/static/backup.html
> >
> > It seems to me that all of the questions you're asking are answered in
> > there.
> >
> > But, specifically, if you're using pg_dump, you can specify to only back
> > up certain tables, or to back up everything _except_ certain tables, and
> > that would allow you to back up tables that don't change much infrequently
> > and tables that change a lot more often.  That will work fine from a
> > database server standpoint.  Whether it works for you data in particular,
> > is a question that only someone familiar with your data can answer.  My
> > opinion: if you can't answer that question yourself, just back up
> > everything
> > to be safe.
> >
> > With filesystem level backup (or PITR, which is just filesystem backup
> > without having to stop the sever and a few other cool perks) you back up
> > the entire database or nothing.
> >
> > Hope this helps.
> >
> > --
> > Bill Moran
> > http://www.potentialtech.com
> > http://people.collaborativefusion.com/~wmoran/
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/