Thread: How to change to Linux from windows when the db has a big size

How to change to Linux from windows when the db has a big size

From
"张得群"
Date:
Hello,I have a database on Windows 7 ,Postgresql 9.3,its size is about more than 1T,now I want to transfer the platform to Linux ,are there any good ways to do it? The pg_dump command is too slow ,what I want is that can I just connect my hd to linux and set some configures about postgresql?Thank you !

Re: How to change to Linux from windows when the db has a big size

From
Shreeyansh dba
Date:


On Tue, Jan 13, 2015 at 7:31 AM, 张得群 <184062459@qq.com> wrote:
Hello,I have a database on Windows 7 ,Postgresql 9.3,its size is about more than 1T,now I want to transfer the platform to Linux ,are there any good ways to do it? The pg_dump command is too slow ,what I want is that can I just connect my hd to linux and set some configures about postgresql?Thank you !


Hi,

Though pg_dump is a better option, but as your database size very large it requires more downtime which results performance of pg_dump slow. Slony replication is the best option for performing large database upgrades which results in less downtime as you are moving out of Windows to Linux.


Hope this will solve your issue.








--
Thanks & Regards
Venkataramana Aitla
Database Administrator

Re: How to change to Linux from windows when the db has a big size

From
Craig Ringer
Date:
Indeed - Slony-I will be your best bet if you wish to avoid extended downtime. However, Slony-I is not the simplest thing to set up and use. You will  need to spend time reading the manual. You will also need to test it first. Do not just attempt it on your live database and hope it will work.

A possibly simpler alternative may be Londiste. Unlike Slony it cannot replicate DDL, but if you do a pg_dump --schema-only and restore that to the new server you can then set up Londiste to replicate the data. I've worked with 100GB+ databases with Londiste, but not 1TB, and I don't know your schema so I can't promise it'll work. It's not very intrusive though, so if you have problems it's easy to remove.

By the way, the BDR project has a component that's designed to help with this (currently called "UDR") but it only works on PostgreSQL 9.4, and not yet on Windows. It's simpler than Slony-I, but both get the same job done.

In any case: if you need low-downtime, use Slony. If you want it to be done faster and simpler, but with downtime, use pg_dump and pg_restore in parallel mode.

You cannot simply copy the data directory. There is no way to in-place convert a Windows PostgreSQL data directory for Linux use or vice versa.



On 13 January 2015 at 12:58, Shreeyansh dba <shreeyansh2014@gmail.com> wrote:


On Tue, Jan 13, 2015 at 7:31 AM, 张得群 <184062459@qq.com> wrote:
Hello,I have a database on Windows 7 ,Postgresql 9.3,its size is about more than 1T,now I want to transfer the platform to Linux ,are there any good ways to do it? The pg_dump command is too slow ,what I want is that can I just connect my hd to linux and set some configures about postgresql?Thank you !


Hi,

Though pg_dump is a better option, but as your database size very large it requires more downtime which results performance of pg_dump slow. Slony replication is the best option for performing large database upgrades which results in less downtime as you are moving out of Windows to Linux.


Hope this will solve your issue.








--
Thanks & Regards
Venkataramana Aitla
Database Administrator



--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services