Обсуждение: Upgrading Database: need to dump and restore?
If I were to upgrade the database version, would I need to dump and restore all the data?
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... > 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 >
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 >
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... > 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 >
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/
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 >
""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.
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 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
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 >
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
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
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
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/
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 >
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.
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/
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 >
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.