Thread: Upgrading Database: need to dump and restore?

Upgrading Database: need to dump and restore?

From
"Carlos Oliva"
Date:
If I were to upgrade the database version, would I need to dump and restore
all the data?



Re: Upgrading Database: need to dump and restore?

From
Grzegorz Jaśkiewicz
Date:
if you upgrade to different major version, yes. If between minor
releases (say 8.1.4->8.1.5) than no, with few exceptions. Make sure
you read release notes between versions to find out.

Re: Upgrading Database: need to dump and restore?

From
Bill Moran
Date:
In response to "Carlos Oliva" <carlos@pbsinet.com>:

> If I were to upgrade the database version, would I need to dump and restore
> all the data?

If you upgrade patch releases (i.e. from 8.3.4 -> 8.3.5) then usually no,
but see the release notes for the versions you're upgrading, because there
are occasionally exceptions.

If you're updating to a major release (8.2.x -> 8.3.x), then yes.

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

Re: Upgrading Database: need to dump and restore?

From
"Carlos Oliva"
Date:
Would I need to upgrade the entire cluster or can I just upgrade database
wise?
""Grzegorz Jaskiewicz"" <gryzman@gmail.com> wrote in message
news:2f4958ff0906030845t526db574q464c17072fadd23b@mail.gmail.com...
> if you upgrade to different major version, yes. If between minor
> releases (say 8.1.4->8.1.5) than no, with few exceptions. Make sure
> you read release notes between versions to find out.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



Re: Upgrading Database: need to dump and restore?

From
"Carlos Oliva"
Date:
For large databases or tables, this will take a long time to do.  Are there
any alternatives to benefit from the improved versions of PostgreSQL without
having to dump some large tables that never change?
"Bill Moran" <wmoran@potentialtech.com> wrote in message
news:20090603114544.434edcce.wmoran@potentialtech.com...
> In response to "Carlos Oliva" <carlos@pbsinet.com>:
>
>> If I were to upgrade the database version, would I need to dump and
>> restore
>> all the data?
>
> If you upgrade patch releases (i.e. from 8.3.4 -> 8.3.5) then usually no,
> but see the release notes for the versions you're upgrading, because there
> are occasionally exceptions.
>
> If you're updating to a major release (8.2.x -> 8.3.x), then yes.
>
> --
> 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
>



Re: Upgrading Database: need to dump and restore?

From
Grzegorz Jaśkiewicz
Date:
On Wed, Jun 3, 2009 at 5:11 PM, Carlos Oliva <carlos@pbsinet.com> wrote:
> Would I need to upgrade the entire cluster or can I just upgrade database wise?
Obviously whole installation has to be dumped and restored. Wether you
are going to dump database by database, or do pg_dumpall, is up to
you,and mainly depends on cluster size.
It's just that binary format of stored files changes from major
release to release, and postgresql will refuse to start with old
format data directory (where it stores all databases).

--
GJ

Re: Upgrading Database: need to dump and restore?

From
"Carlos Oliva"
Date:
Woudl it be possible to keep the current postgresql version running in a
different port, install a new version of postgresql, and copy the data from
one version to the other while both versions are running?  This might give
us time to copy the tables and databases one at a time and reconfigure the
database access for parts of the application until we complete the migration
to the new version.
""Grzegorz Jaskiewicz"" <gryzman@gmail.com> wrote in message
news:2f4958ff0906031157v32fb9810j9476fd950e49491d@mail.gmail.com...
> On Wed, Jun 3, 2009 at 5:11 PM, Carlos Oliva <carlos@pbsinet.com> wrote:
>> Would I need to upgrade the entire cluster or can I just upgrade database
>> wise?
> Obviously whole installation has to be dumped and restored. Wether you
> are going to dump database by database, or do pg_dumpall, is up to
> you,and mainly depends on cluster size.
> It's just that binary format of stored files changes from major
> release to release, and postgresql will refuse to start with old
> format data directory (where it stores all databases).
>
> --
> GJ
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



Re: Upgrading Database: need to dump and restore?

From
Bill Moran
Date:
In response to "Carlos Oliva" <carlos@pbsinet.com>:

> Woudl it be possible to keep the current postgresql version running in a
> different port, install a new version of postgresql, and copy the data from
> one version to the other while both versions are running?  This might give
> us time to copy the tables and databases one at a time and reconfigure the
> database access for parts of the application until we complete the migration
> to the new version.

Your best bet would be to install Slony-I.  One of the main design goals
for Slony is to allow interruption-free upgrades.

Basically, you can set up the new database server (using a different port
or whatever), and install/configure Slony.  Slony will then keep your
two database in sync.  Then you can switch over to the new database
whenever suits you:

http://www.slony.info

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

Re: Upgrading Database: need to dump and restore?

From
Grzegorz Jaśkiewicz
Date:
On Wed, Jun 3, 2009 at 8:03 PM, Carlos Oliva <carlos@pbsinet.com> wrote:
> Woudl it be possible to keep the current postgresql version running in a
> different port, install a new version of postgresql, and copy the data from
> one version to the other while both versions are running?  This might give
> us time to copy the tables and databases one at a time and reconfigure the
> database access for parts of the application until we complete the migration
> to the new version.
Yes it is possible. You can setup new version of postgresql with new
data directory, in different location (say /var/pg_new/data), run it
on different port, and have multiple postgresql installations running
side by side.
What OS do you run, and where do you get postgresql binaries from.


_Please stop replying on top_. Top posting is a baad thing on mailing lists.

Re: Upgrading Database: need to dump and restore?

From
Grzegorz Jaśkiewicz
Date:
On Wed, Jun 3, 2009 at 8:14 PM, Bill Moran <wmoran@potentialtech.com> wrote:
> In response to "Carlos Oliva" <carlos@pbsinet.com>:
>
>> Woudl it be possible to keep the current postgresql version running in a
>> different port, install a new version of postgresql, and copy the data from
>> one version to the other while both versions are running?  This might give
>> us time to copy the tables and databases one at a time and reconfigure the
>> database access for parts of the application until we complete the migration
>> to the new version.
>
> Your best bet would be to install Slony-I.  One of the main design goals
> for Slony is to allow interruption-free upgrades.

I don't think it is "easy", but will do if you need to synchronize
data before switching.

--
GJ

Re: Upgrading Database: need to dump and restore?

From
Bill Moran
Date:
In response to Grzegorz Jaśkiewicz <gryzman@gmail.com>:

> On Wed, Jun 3, 2009 at 8:14 PM, Bill Moran <wmoran@potentialtech.com> wrote:
> > In response to "Carlos Oliva" <carlos@pbsinet.com>:
> >
> >> Woudl it be possible to keep the current postgresql version running in a
> >> different port, install a new version of postgresql, and copy the data from
> >> one version to the other while both versions are running?  This might give
> >> us time to copy the tables and databases one at a time and reconfigure the
> >> database access for parts of the application until we complete the migration
> >> to the new version.
> >
> > Your best bet would be to install Slony-I.  One of the main design goals
> > for Slony is to allow interruption-free upgrades.
>
> I don't think it is "easy", but will do if you need to synchronize
> data before switching.

"easy" was not the point.  I gathered from his comments that downtime is
an issue, and I know (from experience) that Slony provides the ability
to upgrade with almost no downtime, even with very large databases.

His plan of migrating tables one at a time may work, but it's frighteningly
error-prone.  If he copies a table, how does he know the data hasn't
changed during the copy?  What if he doesn't quite get all the clients
switched over all at once?  How do you do a JOIN when one table is in
one database and the other somewhere else?

Once the DBs are in sync with Slony, a single command will switch to the
new server.  If it doesn't go well (because he has a client compatibility
problem, for example -- casts anyone?) it's a simple process to switch
back, all with a guarantee that his data will never be lost, out of sync
or corrupted.

And if his application requires small downtime windows, this is a process
he will benefit from getting familiar with anyway.

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

Re: Upgrading Database: need to dump and restore?

From
"Carlos Oliva"
Date:
Can the synchronization with Slony run while the old database is still being
updated daily?  I am wondering if we can just let Slony run until the
databases are fully synchronized and then switch databases.
"Bill Moran" <wmoran@potentialtech.com> wrote in message
news:20090603153556.f05e6bd2.wmoran@potentialtech.com...
> In response to Grzegorz Jaskiewicz <gryzman@gmail.com>:
>
>> On Wed, Jun 3, 2009 at 8:14 PM, Bill Moran <wmoran@potentialtech.com>
>> wrote:
>> > In response to "Carlos Oliva" <carlos@pbsinet.com>:
>> >
>> >> Woudl it be possible to keep the current postgresql version running in
>> >> a
>> >> different port, install a new version of postgresql, and copy the data
>> >> from
>> >> one version to the other while both versions are running? This might
>> >> give
>> >> us time to copy the tables and databases one at a time and reconfigure
>> >> the
>> >> database access for parts of the application until we complete the
>> >> migration
>> >> to the new version.
>> >
>> > Your best bet would be to install Slony-I. One of the main design goals
>> > for Slony is to allow interruption-free upgrades.
>>
>> I don't think it is "easy", but will do if you need to synchronize
>> data before switching.
>
> "easy" was not the point.  I gathered from his comments that downtime is
> an issue, and I know (from experience) that Slony provides the ability
> to upgrade with almost no downtime, even with very large databases.
>
> His plan of migrating tables one at a time may work, but it's
> frighteningly
> error-prone.  If he copies a table, how does he know the data hasn't
> changed during the copy?  What if he doesn't quite get all the clients
> switched over all at once?  How do you do a JOIN when one table is in
> one database and the other somewhere else?
>
> Once the DBs are in sync with Slony, a single command will switch to the
> new server.  If it doesn't go well (because he has a client compatibility
> problem, for example -- casts anyone?) it's a simple process to switch
> back, all with a guarantee that his data will never be lost, out of sync
> or corrupted.
>
> And if his application requires small downtime windows, this is a process
> he will benefit from getting familiar with anyway.
>
> --
> 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
>



Re: Upgrading Database: need to dump and restore?

From
"Carlos Oliva"
Date:
""Grzegorz Jaskiewicz"" <gryzman@gmail.com> wrote in message
news:2f4958ff0906031214k3dfaa4b2mae5733d7345f7aab@mail.gmail.com...
On Wed, Jun 3, 2009 at 8:03 PM, Carlos Oliva <carlos@pbsinet.com> wrote:
> Woudl it be possible to keep the current postgresql version running in a
> different port, install a new version of postgresql, and copy the data
> from
> one version to the other while both versions are running? This might give
> us time to copy the tables and databases one at a time and reconfigure the
> database access for parts of the application until we complete the
> migration
> to the new version.
Yes it is possible. You can setup new version of postgresql with new
data directory, in different location (say /var/pg_new/data), run it
on different port, and have multiple postgresql installations running
side by side.
What OS do you run, and where do you get postgresql binaries from.


_Please stop replying on top_. Top posting is a baad thing on mailing lists.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Probably this will work for us.  We are using RedHat and I think that we may
be getting binaries from a couple of sources.



Re: Upgrading Database: need to dump and restore?

From
Bill Moran
Date:
In response to "Carlos Oliva" <carlos@pbsinet.com>:

> Can the synchronization with Slony run while the old database is still being
> updated daily?  I am wondering if we can just let Slony run until the
> databases are fully synchronized and then switch databases.

Yes, and yes.  That's exactly what Slony is for.

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

Re: Upgrading Database: need to dump and restore?

From
"Carlos Oliva"
Date:
Thank you for your response Grzegorx.  It is helping us a great deal to
understand the issues around backups.  Would any of the pg_xlog, pg_clog,
etc change for a table that has a stable structure and data?  That is, the
table undergoes several inserts and then it is never updated
""Grzegorz Jaskiewicz"" <gryzman@gmail.com> wrote in message
news:2f4958ff0906031217h2a0bfe0t674f266d4397e9ba@mail.gmail.com...
On Wed, Jun 3, 2009 at 8:14 PM, Bill Moran <wmoran@potentialtech.com> wrote:
> In response to "Carlos Oliva" <carlos@pbsinet.com>:
>
>> Woudl it be possible to keep the current postgresql version running in a
>> different port, install a new version of postgresql, and copy the data
>> from
>> one version to the other while both versions are running? This might give
>> us time to copy the tables and databases one at a time and reconfigure
>> the
>> database access for parts of the application until we complete the
>> migration
>> to the new version.
>
> Your best bet would be to install Slony-I. One of the main design goals
> for Slony is to allow interruption-free upgrades.

I don't think it is "easy", but will do if you need to synchronize
data before switching.

--
GJ

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



Re: Upgrading Database: need to dump and restore?

From
"Carlos Oliva"
Date:
Thank you for your response Bill.  It is helping us a great deal to
understand the issues around backups.  Would any of the pg_xlog, pg_clog,
etc change for a table that has a stable structure and data?  That is, the
table undergoes several inserts and then it is never updated
"Bill Moran" <wmoran@potentialtech.com> wrote in message
news:20090603161817.131e706e.wmoran@potentialtech.com...
> In response to "Carlos Oliva" <carlos@pbsinet.com>:
>
>> Can the synchronization with Slony run while the old database is still
>> being
>> updated daily?  I am wondering if we can just let Slony run until the
>> databases are fully synchronized and then switch databases.
>
> Yes, and yes.  That's exactly what Slony is for.
>
> --
> 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
>



Re: Upgrading Database: need to dump and restore?

From
Grzegorz Jaśkiewicz
Date:
pg_xlog and clog is something that is used during operation, and for
point in time recovery.
It doesn't go to database dump at all, not needed.


On Thu, Jun 4, 2009 at 12:32 PM, Carlos Oliva <carlos@pbsinet.com> wrote:
> Thank you for your response Grzegorx.  It is helping us a great deal to
> understand the issues around backups.  Would any of the pg_xlog, pg_clog,
> etc change for a table that has a stable structure and data?  That is, the
> table undergoes several inserts and then it is never updated
> ""Grzegorz Jaskiewicz"" <gryzman@gmail.com> wrote in message
> news:2f4958ff0906031217h2a0bfe0t674f266d4397e9ba@mail.gmail.com...
> On Wed, Jun 3, 2009 at 8:14 PM, Bill Moran <wmoran@potentialtech.com> wrote:
>> In response to "Carlos Oliva" <carlos@pbsinet.com>:
>>
>>> Woudl it be possible to keep the current postgresql version running in a
>>> different port, install a new version of postgresql, and copy the data
>>> from
>>> one version to the other while both versions are running? This might give
>>> us time to copy the tables and databases one at a time and reconfigure
>>> the
>>> database access for parts of the application until we complete the
>>> migration
>>> to the new version.
>>
>> Your best bet would be to install Slony-I. One of the main design goals
>> for Slony is to allow interruption-free upgrades.
>
> I don't think it is "easy", but will do if you need to synchronize
> data before switching.
>
> --
> GJ
>
> --
> 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
>



--
GJ

Re: Upgrading Database: need to dump and restore?

From
"Carlos Oliva"
Date:
Thanks again Grzgorz for your expedicious reply.  Would anything else change
in the database for a table once it ceases to be updated?  We have several
tables for which a number of records is inserted and never again updated --
data is never updated, deleted, or inserted again.  We are moving these
tables into their own tablespace and getting some kind of snapshot copy of
these tables.  We were thinking that we can restore them in the future
without having to back them up several times.
""Grzegorz Jaskiewicz"" <gryzman@gmail.com> wrote in message
news:2f4958ff0906040446s6b5a38b7ka1a4b1f3a4cea8b@mail.gmail.com...
pg_xlog and clog is something that is used during operation, and for
point in time recovery.
It doesn't go to database dump at all, not needed.


On Thu, Jun 4, 2009 at 12:32 PM, Carlos Oliva <carlos@pbsinet.com> wrote:
> Thank you for your response Grzegorx. It is helping us a great deal to
> understand the issues around backups. Would any of the pg_xlog, pg_clog,
> etc change for a table that has a stable structure and data? That is, the
> table undergoes several inserts and then it is never updated
> ""Grzegorz Jaskiewicz"" <gryzman@gmail.com> wrote in message
> news:2f4958ff0906031217h2a0bfe0t674f266d4397e9ba@mail.gmail.com...
> On Wed, Jun 3, 2009 at 8:14 PM, Bill Moran <wmoran@potentialtech.com>
> wrote:
>> In response to "Carlos Oliva" <carlos@pbsinet.com>:
>>
>>> Woudl it be possible to keep the current postgresql version running in a
>>> different port, install a new version of postgresql, and copy the data
>>> from
>>> one version to the other while both versions are running? This might
>>> give
>>> us time to copy the tables and databases one at a time and reconfigure
>>> the
>>> database access for parts of the application until we complete the
>>> migration
>>> to the new version.
>>
>> Your best bet would be to install Slony-I. One of the main design goals
>> for Slony is to allow interruption-free upgrades.
>
> I don't think it is "easy", but will do if you need to synchronize
> data before switching.
>
> --
> GJ
>
> --
> 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
>



--
GJ

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



Re: Upgrading Database: need to dump and restore?

From
Grzegorz Jaśkiewicz
Date:
On Thu, Jun 4, 2009 at 1:07 PM, Carlos Oliva <carlos@pbsinet.com> wrote:
> Thanks again Grzgorz for your expedicious reply.  Would anything else change
> in the database for a table once it ceases to be updated?  We have several
> tables for which a number of records is inserted and never again updated --
> data is never updated, deleted, or inserted again.  We are moving these
> tables into their own tablespace and getting some kind of snapshot copy of
> these tables.  We were thinking that we can restore them in the future
> without having to back them up several times.


Well, if you do pg_dump - that data will be there.
If you do copy of data directory, that isn't going to work, unless you
put db in proper state.
if you do that, you need to copy pg_xlog directory with it.

So you have to specify which backup strategy you are going to use.

--
GJ

Re: Upgrading Database: need to dump and restore?

From
"Carlos Oliva"
Date:
In which state do we need to put the db?  We can use both types of backup
strategy.  We can pg_dump the table and copy the tablespace folder along
with anyhting else that we may need.
""Grzegorz Jaskiewicz"" <gryzman@gmail.com> wrote in message
news:2f4958ff0906040518l190af73dpff180755d567f3a8@mail.gmail.com...
On Thu, Jun 4, 2009 at 1:07 PM, Carlos Oliva <carlos@pbsinet.com> wrote:
> Thanks again Grzgorz for your expedicious reply. Would anything else
> change
> in the database for a table once it ceases to be updated? We have several
> tables for which a number of records is inserted and never again
> updated --
> data is never updated, deleted, or inserted again. We are moving these
> tables into their own tablespace and getting some kind of snapshot copy of
> these tables. We were thinking that we can restore them in the future
> without having to back them up several times.


Well, if you do pg_dump - that data will be there.
If you do copy of data directory, that isn't going to work, unless you
put db in proper state.
if you do that, you need to copy pg_xlog directory with it.

So you have to specify which backup strategy you are going to use.

--
GJ

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



Re: Upgrading Database: need to dump and restore?

From
Grzegorz Jaśkiewicz
Date:
On Thu, Jun 4, 2009 at 1:23 PM, Carlos Oliva <carlos@pbsinet.com> wrote:
> In which state do we need to put the db?  We can use both types of backup
> strategy.  We can pg_dump the table and copy the tablespace folder along
> with anyhting else that we may need.
Well, not quite. Pg_dump is fine, but you can't just copy data itself,
when server is running.


--
GJ

Re: Upgrading Database: need to dump and restore?

From
"Carlos Oliva"
Date:
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?
""Grzegorz Jaskiewicz"" <gryzman@gmail.com> wrote in message
news:2f4958ff0906040549u53bafe7br772033214d43e262@mail.gmail.com...
On Thu, Jun 4, 2009 at 1:23 PM, Carlos Oliva <carlos@pbsinet.com> wrote:
> In which state do we need to put the db? We can use both types of backup
> strategy. We can pg_dump the table and copy the tablespace folder along
> with anyhting else that we may need.
Well, not quite. Pg_dump is fine, but you can't just copy data itself,
when server is running.


--
GJ

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



Re: Upgrading Database: need to dump and restore?

From
Bill Moran
Date:
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/

Re: Upgrading Database: need to dump and restore?

From
"Carlos Oliva"
Date:
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.
"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
>



Re: Upgrading Database: need to dump and restore?

From
Scott Marlowe
Date:
On Thu, Jun 4, 2009 at 5:32 AM, Carlos Oliva <carlos@pbsinet.com> wrote:
> Thank you for your response Grzegorx.  It is helping us a great deal to
> understand the issues around backups.  Would any of the pg_xlog, pg_clog,
> etc change for a table that has a stable structure and data?  That is, the
> table undergoes several inserts and then it is never updated

Wait, are you asking this as a side question, or related to your
upgrading of your database?  Because you can't take any of that stuff
with you with a major version upgrade.

Re: Upgrading Database: need to dump and restore?

From
Bill Moran
Date:
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/

Re: Upgrading Database: need to dump and restore?

From
"Carlos Oliva"
Date:
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@potentialtech.com> wrote in message
news:20090604104302.50e23318.wmoran@potentialtech.com...
> 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/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



Re: Upgrading Database: need to dump and restore?

From
Jasen Betts
Date:
On 2009-06-03, Carlos Oliva <carlos@pbsinet.com> wrote:
> Woudl it be possible to keep the current postgresql version running in a
> different port, install a new version of postgresql, and copy the data from
> one version to the other while both versions are running?  This might give
> us time to copy the tables and databases one at a time and reconfigure the
> database access for parts of the application until we complete the migration
> to the new version.

Yes that's possible. The debian packaged versions since 8.0 are
designed to facilitate multiple clusters on a single host, and if you
install two major versions of postgres it'll automatically set them up
for you using port 5433 for the new version.

On redhat it may not be so easy.