Обсуждение: Need help in transferring FP to Int64 DateTime
Hi All,
I am sure this question has been asked before, however, I failed to find any related topics in the internet. We have a database about 100GB in size. It was started back in 7.x days and has been upgraded along the way to 9.0. Because of the historical reason, all timestamps are stored in FloatingPoint. To use pg9, we had to compile it with FP support instead of the default Int64. Some recent research shows that PG team will eventually dump FP support in favor of int64 in future, we figured we need to make the move to Int64 before the database gets even larger. The challenge we now face is how to "convert" the database fast enough, so that required down time is minimized. Searching through the internet for 2 days yields little fruit so far... Can someone please provide enlighten us?
Cheers,
I am sure this question has been asked before, however, I failed to find any related topics in the internet. We have a database about 100GB in size. It was started back in 7.x days and has been upgraded along the way to 9.0. Because of the historical reason, all timestamps are stored in FloatingPoint. To use pg9, we had to compile it with FP support instead of the default Int64. Some recent research shows that PG team will eventually dump FP support in favor of int64 in future, we figured we need to make the move to Int64 before the database gets even larger. The challenge we now face is how to "convert" the database fast enough, so that required down time is minimized. Searching through the internet for 2 days yields little fruit so far... Can someone please provide enlighten us?
Cheers,
Bo Jin
Operating/IT Manager
Troo Corporation [www.troo.com]
43 Auriga Drive, Suite 102, Ottawa, ON K2E 7Y8
Ph: +1 877.702.8766 x156
Fax: +1 855.726.8766
Troo Corporation [www.troo.com]
43 Auriga Drive, Suite 102, Ottawa, ON K2E 7Y8
Ph: +1 877.702.8766 x156
Fax: +1 855.726.8766
On Wed, Jun 6, 2012 at 9:20 AM, Benson Jin <benson.jin@troo.com> wrote: > I am sure this question has been asked before, however, I failed to find any > related topics in the internet. We have a database about 100GB in size. It > was started back in 7.x days and has been upgraded along the way to 9.0. > Because of the historical reason, all timestamps are stored in > FloatingPoint. To use pg9, we had to compile it with FP support instead of > the default Int64. I take it you had to compile 9.0 with --disable-integer-datetimes because you wanted to use pg_upgrade to perform one of your database upgrades, yes? Otherwise you would have been able to just dump-and-restore into a 9.0 database with integer timestamps. At any rate, you must have performed a dump-and-restore at some point since your "7.x days", since pg_upgrade can handle databases only back to 8.3. > Some recent research shows that PG team will eventually > dump FP support in favor of int64 in future, we figured we need to make the > move to Int64 before the database gets even larger. FWIW, I don't think there's any urgent push to get rid of float timestamps as a compile-time option, even though [1] claims the option is "deprecated". Float timestamps were the default through 8.3, which isn't quite ancient history yet, and it seems likely there are many users in the same boat who would be upset about not being able to use pg_upgrade if we removed that option. Josh [1] http://www.postgresql.org/docs/current/static/datatype-datetime.html
On 06/06/2012 09:20 AM, Benson Jin wrote: > Hi All, > > I am sure this question has been asked before, however, I failed to find > any related topics in the internet. We have a database about 100GB in > size. It was started back in 7.x days and has been upgraded along the > way to 9.0. Because of the historical reason, all timestamps are stored > in FloatingPoint. To use pg9, we had to compile it with FP support > instead of the default Int64. Some recent research shows that PG team > will eventually dump FP support in favor of int64 in future, we figured > we need to make the move to Int64 before the database gets even larger. > The challenge we now face is how to "convert" the database fast enough, > so that required down time is minimized. Searching through the internet > for 2 days yields little fruit so far... Can someone please provide > enlighten us? A thought. Dependent on sufficient disk space. Use one of the trigger based(non-binary) replication tools i.e. Slony, Bucardo, etc. Compile a Postgres 9.0 instance using the default of integer timestamps. Set up replication PG 9.0 FP --> PG 9.0 integer Once the standby is close to the primary, shut off access to the primary and let the standby completely catch up. Shut down the primary and promote the standby as the new primary. I have not actually done this, which is why I propose it as a thought. Hopefully, others will weigh in on the advisability of the above. > > Cheers, > > > Bo Jin > -- Adrian Klaver adrian.klaver@gmail.com
Hi Adrian, Thanks for the suggestion! We thought about that too, but concerned about the possible performance penalty trigger basedreplication would bring. If there is no other alternative, we will give your suggestion a try... Cheers, Benson ----- Original Message ----- From: "Adrian Klaver" <adrian.klaver@gmail.com> To: "Benson Jin" <benson.jin@troo.com> Cc: pgsql-general@postgresql.org Sent: Wednesday, June 6, 2012 10:02:02 PM Subject: Re: [GENERAL] Need help in transferring FP to Int64 DateTime On 06/06/2012 09:20 AM, Benson Jin wrote: > Hi All, > > I am sure this question has been asked before, however, I failed to find > any related topics in the internet. We have a database about 100GB in > size. It was started back in 7.x days and has been upgraded along the > way to 9.0. Because of the historical reason, all timestamps are stored > in FloatingPoint. To use pg9, we had to compile it with FP support > instead of the default Int64. Some recent research shows that PG team > will eventually dump FP support in favor of int64 in future, we figured > we need to make the move to Int64 before the database gets even larger. > The challenge we now face is how to "convert" the database fast enough, > so that required down time is minimized. Searching through the internet > for 2 days yields little fruit so far... Can someone please provide > enlighten us? A thought. Dependent on sufficient disk space. Use one of the trigger based(non-binary) replication tools i.e. Slony, Bucardo, etc. Compile a Postgres 9.0 instance using the default of integer timestamps. Set up replication PG 9.0 FP --> PG 9.0 integer Once the standby is close to the primary, shut off access to the primary and let the standby completely catch up. Shut down the primary and promote the standby as the new primary. I have not actually done this, which is why I propose it as a thought. Hopefully, others will weigh in on the advisability of the above. > > Cheers, > > > Bo Jin > -- Adrian Klaver adrian.klaver@gmail.com
Hi Josh, Thanks for your prompt reply! Yes, you were right about the parameter. We have upgraded a few times in the past. It was notso painful, as our data as rather small. As our business grows, it becomes harder and harder to do any upgrade that requiresdowntime. There is never a good time to do this type of changes. However, a better time would be earlier rather thanlater, as our data size is growing steadily. The early we do it, the less downtime we will have to bear. I guess my questionis how to do the change without downtime or with the least downtime? Is there a recommended steps we should take? Also, is there an installer for Windows 64bit with --disable-integer-datetimes available? Cheers, Benson ----- Original Message ----- From: "Josh Kupershmidt" <schmiddy@gmail.com> To: "Benson Jin" <benson.jin@troo.com> Cc: pgsql-general@postgresql.org Sent: Wednesday, June 6, 2012 8:01:35 PM Subject: Re: [GENERAL] Need help in transferring FP to Int64 DateTime On Wed, Jun 6, 2012 at 9:20 AM, Benson Jin <benson.jin@troo.com> wrote: > I am sure this question has been asked before, however, I failed to find any > related topics in the internet. We have a database about 100GB in size. It > was started back in 7.x days and has been upgraded along the way to 9.0. > Because of the historical reason, all timestamps are stored in > FloatingPoint. To use pg9, we had to compile it with FP support instead of > the default Int64. I take it you had to compile 9.0 with --disable-integer-datetimes because you wanted to use pg_upgrade to perform one of your database upgrades, yes? Otherwise you would have been able to just dump-and-restore into a 9.0 database with integer timestamps. At any rate, you must have performed a dump-and-restore at some point since your "7.x days", since pg_upgrade can handle databases only back to 8.3. > Some recent research shows that PG team will eventually > dump FP support in favor of int64 in future, we figured we need to make the > move to Int64 before the database gets even larger. FWIW, I don't think there's any urgent push to get rid of float timestamps as a compile-time option, even though [1] claims the option is "deprecated". Float timestamps were the default through 8.3, which isn't quite ancient history yet, and it seems likely there are many users in the same boat who would be upset about not being able to use pg_upgrade if we removed that option. Josh [1] http://www.postgresql.org/docs/current/static/datatype-datetime.html
On 06/07/2012 08:29 AM, Benson Jin wrote: > Hi Adrian, > > Thanks for the suggestion! We thought about that too, but concerned about the possible performance penalty trigger based replication would bring. If there is no other alternative, we will give your suggestion a try... AFAIK there is no direct way to do a binary fp timestamp --> integer timestamp conversion, otherwise pg_upgrade would handle it. To make that jump it needs to go through a text based representation. That leaves a dump/restore cycle or non-binary replication. > > Cheers, > > Benson > -- Adrian Klaver adrian.klaver@gmail.com