Upgrading Database: need to dump and restore?

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.

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/

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...

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...

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

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...

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/

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.

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

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/

""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.

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 "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/

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...

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

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

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

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

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