Need to migrate from PostgreSQL 8.3.x to 9.2
Constraint 1: 8.4 and above have made datetime format to be integer by default. Wasn't so in 8.3.x. As a result pg_upgrade can't be used. Unless server is rebuilt with --disable_integer_datetime. This implies all future migrations would also have to retain this setting. This isn't acceptable in our case.
Constraint 2: The application using the 8.3.x database can't go down (there isn't any allowed downtime for at least a portion of its function). We could, however, hold off all the db updates for a short while. This gives us a window of about an hour at most.
Proposed solution (or actually - can the experts out there validate if this idea will work or if it makes any sense): The idea is to move tables over in parallel (processes or threads) using the copy command from the old to the new version. Assuming that both old and new servers are running (on different ports) ... Question is whether the copy command on the old version can be sent "directly" into the new version? Optimally, if it can be done without an intermediate file. Has anyone tried anything like this before? Is it practical? Does it even make sense?
Thanks in advance for your replies.