Обсуждение: correcting tablespaces inside data folder
Hello.
Yes I'm one of those guys who only recently realized the mess of having tablespaces inside the data directory... now I want to use pg_upgrade and it will make things even worse...
Does anyone have a best approach to this problem? Fastest/safest?
pg 9.3.x on win
Thanks,
Duarte
On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira <dncarreira@gmail.com> wrote:
Hello.Yes I'm one of those guys who only recently realized the mess of having tablespaces inside the data directory... now I want to use pg_upgrade and it will make things even worse...Does anyone have a best approach to this problem? Fastest/safest?pg 9.3.x on win
If you can afford to shut the server down, the easiest is to shut it down, move the tablespaces (with mv, and as long as you stay within the partition it should be almost instant), update the symlinks to point to the new location, and start it up again.
Magnus,
You mean changing the symlinks inside pg_tblspc?
On Fri, Jul 6, 2018 at 5:49 PM Magnus Hagander <magnus@hagander.net> wrote:
On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira <dncarreira@gmail.com> wrote:Hello.Yes I'm one of those guys who only recently realized the mess of having tablespaces inside the data directory... now I want to use pg_upgrade and it will make things even worse...Does anyone have a best approach to this problem? Fastest/safest?pg 9.3.x on winIf you can afford to shut the server down, the easiest is to shut it down, move the tablespaces (with mv, and as long as you stay within the partition it should be almost instant), update the symlinks to point to the new location, and start it up again.--
(please don't top-post. It makes it hard to follow discussions)
--
On Fri, Jul 6, 2018 at 7:05 PM, Duarte Carreira <dncarreira@gmail.com> wrote:
Magnus,You mean changing the symlinks inside pg_tblspc?
Yes. As long as the server is shut down, you can modify those symlinks.
On Fri, Jul 6, 2018 at 5:49 PM Magnus Hagander <magnus@hagander.net> wrote:On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira <dncarreira@gmail.com> wrote:Hello.Yes I'm one of those guys who only recently realized the mess of having tablespaces inside the data directory... now I want to use pg_upgrade and it will make things even worse...Does anyone have a best approach to this problem? Fastest/safest?pg 9.3.x on winIf you can afford to shut the server down, the easiest is to shut it down, move the tablespaces (with mv, and as long as you stay within the partition it should be almost instant), update the symlinks to point to the new location, and start it up again.--
On Fri, Jul 6, 2018 at 6:15 PM Magnus Hagander <magnus@hagander.net> wrote:
(please don't top-post. It makes it hard to follow discussions)On Fri, Jul 6, 2018 at 7:05 PM, Duarte Carreira <dncarreira@gmail.com> wrote:Magnus,You mean changing the symlinks inside pg_tblspc?Yes. As long as the server is shut down, you can modify those symlinks.
Ok, I'll try on my laptop and see how it goes.
Thanks.
On Fri, Jul 6, 2018 at 5:49 PM Magnus Hagander <magnus@hagander.net> wrote:On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira <dncarreira@gmail.com> wrote:Hello.Yes I'm one of those guys who only recently realized the mess of having tablespaces inside the data directory... now I want to use pg_upgrade and it will make things even worse...Does anyone have a best approach to this problem? Fastest/safest?pg 9.3.x on winIf you can afford to shut the server down, the easiest is to shut it down, move the tablespaces (with mv, and as long as you stay within the partition it should be almost instant), update the symlinks to point to the new location, and start it up again.----
Hi.
On Fri, Jul 6, 2018 at 6:30 PM Duarte Carreira <dncarreira@gmail.com> wrote:
On Fri, Jul 6, 2018 at 6:15 PM Magnus Hagander <magnus@hagander.net> wrote:(please don't top-post. It makes it hard to follow discussions)On Fri, Jul 6, 2018 at 7:05 PM, Duarte Carreira <dncarreira@gmail.com> wrote:Magnus,You mean changing the symlinks inside pg_tblspc?Yes. As long as the server is shut down, you can modify those symlinks.Ok, I'll try on my laptop and see how it goes.Thanks.
Update: everything seems to be working fine.
On windows you can't change the symlinks (called junctions on windows). You have to delete them with rd symlimk_id, and then recreate them pointing to the new location with:
mklink /J symlink_id path_to_new_location
Have to be specially carefull to recreate the symlinks correctly!
Thanks again.
Duarte
On Fri, Jul 6, 2018 at 5:49 PM Magnus Hagander <magnus@hagander.net> wrote:On Fri, Jul 6, 2018 at 6:42 PM, Duarte Carreira <dncarreira@gmail.com> wrote:Hello.Yes I'm one of those guys who only recently realized the mess of having tablespaces inside the data directory... now I want to use pg_upgrade and it will make things even worse...Does anyone have a best approach to this problem? Fastest/safest?pg 9.3.x on winIf you can afford to shut the server down, the easiest is to shut it down, move the tablespaces (with mv, and as long as you stay within the partition it should be almost instant), update the symlinks to point to the new location, and start it up again.----