Re: Need help in transferring FP to Int64 DateTime

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

Cheers,

Benson

----- Original Message -----
From: "Adrian Klaver" <adrian(dot)klaver(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 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(dot)klaver(at)gmail(dot)com