Re: Upgrading Database: need to dump and restore?

We will probably pg_dump the data for backups and look at using Slony for
replication. We thank you and Gregor for the time that you spent sharing
your insights with us.

"Bill Moran" <wmoran(at)potentialtech(dot)com> wrote in message
news:20090604104302(dot)50e23318(dot)wmoran(at)potentialtech(dot)com(dot)(dot)(dot)
> In response to "Carlos Oliva" <carlos(at)pbsinet(dot)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(at)potentialtech(dot)com> wrote in message
>> news:20090604095554(dot)c2d57008(dot)wmoran(at)potentialtech(dot)com(dot)(dot)(dot)
>> > In response to "Carlos Oliva" <carlos(at)pbsinet(dot)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(at)postgresql(dot)org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-general
>> >
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>