Re: Moving databases

"Jay Hodges" <jay(at)dracodigital(dot)com> writes:
> Now that my database is becoming quite large, it has outgrown the /var/ par=
> ition and I would like to move at least one database to /usr partition, or =
> to a NFS.
> can this be done easily, and if so, what would be the procedure to move the=
> files with no losses (all databases are currently active on the Internet t=
> o the public, but downtime is acceptable for moving them)

As long as you can afford downtime, it's not too tough to move a
specific database to another partition:

1. Shutdown database (stop postmaster).

2. Move target database to new location (tar and untar, or cp -r -p
should do). You want to transfer the whole subdirectory
$PGDATA/base/yourdb/ to /some/place/else.

3. Make a symlink at $PGDATA/base/yourdb pointing to /some/place/else.

4. Restart postmaster, et voila.

(Or, if you want to move the whole installation to a larger partition,
just copy the whole $PGDATA tree, and adjust your postmaster start
script to supply the new PGDATA location.)

This is of course a tad ungraceful, but it's plenty effective as long
as your space-management needs can be met by shifting whole databases.
There are discussions in the archives about moving individual tables
around via symlinks, but that's a lot more tedious to maintain.

One thing to keep in mind is that a dump/restore will not preserve
the symlink structures; you should keep notes so that you can manually
reconstruct the layout if necessary.

Better space management tools are on the TODO list, but that's where
things stand for now.

regards, tom lane

PS: keeping a database on an NFS server is NOT recommended --- see
past discussions in the archives, most recently in pgsql-general
around 9-Feb.