On Tue, Jan 12, 2016 at 4:20 PM, Andreas Joseph Krogh <andreas@visena.com> wrote:
I'm moving a > 500GB DB to another server which is initdb'ed with a different locale (nb_NO.UTF-8 to get correct collation when ORDER BY).
Is there another option than pg_dump/restore for doing this?
I recently used slony to move data stored in an SQL_ASCII db to one that was strict UTF-8. I wrote up a program that would fix any invalid byte sequences in the original db first. It went very well, and we had very minimal downtime.
It seems to me you could use slony to accomplish your goal as well of just copying the data into a new DB with the different collation setting. Once done, just stop your application for a minute or two to switch the DB it uses, and drop the slony replication. This process will work if you can assure that the dump/restore would have worked too. That is, there are no invalid data.