Re: Need help in transferring FP to Int64 DateTime

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 not so painful, as our data as rather small. As our business grows, it becomes harder and harder to do any upgrade that requires downtime. There is never a good time to do this type of changes. However, a better time would be earlier rather than later, as our data size is growing steadily. The early we do it, the less downtime we will have to bear. I guess my question is 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(at)gmail(dot)com>
To: "Benson Jin" <benson(dot)jin(at)troo(dot)com>
Cc: pgsql-general(at)postgresql(dot)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(dot)jin(at)troo(dot)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