> On Thu, Nov 17, 2016 <david.g.johnston@xxxxxxxxx> wrote:
> > On Thu, Nov 17, 2016 at 9:16 AM, <kbrannen@xxxxxxxxxx> wrote:
> > First, the above works only *most* of the time in our testing on multiple servers. When it fails, it's because not everything was moved out of the old tablespace and I don't understand why. An "ls $PGDATA/ourdb/PG*/" shows files are still present. According to some searching, I should be able to do:
>
>
> Likely more than one database in the cluster is using $PGDATA/ourdb as its default tablespace location so you need to alter all of them.
Sigh, it's so easy to overlook the obvious; thanks for pointing that out. Knowing what to look for and with some research, doing:
select datname,dattablespace,spcname from pg_database join pg_tablespace on dattablespace = pg_tablespace.oid;
shows there is indeed an extra schema using that tablespace that I'll need to drop or move. Hopefully that helps someone else.
> pg_class displays relative to the current database only so you need to log into the others to check them.
Right, something else I didn't consider.
> > Second, the "ALTER DATABASE ourdb SET TABLESPACE new_ts" which does the move is slow even on our smaller test DBs, almost as if it is having to dump and reload (or more likely copy) the data. This raises the concern of how long this is going to take on our bigger DBs. Is there a faster way to accomplish the same thing especially since the new and old tablespaces are on the same disk partition?
> >
> > For example, from what I can see the data is sitting in a dir and there is a symlink to it in $PGDATA/pg_tblspc.
> >
> > Could I shut down PG, move the DB dir, recreate the symlink in pg_tblspc, then restart PG and all would be well in only a few seconds?
>
>
> I think this would work - all the SQL commands do is invoke O/S commands on your behalf and I'm reasonably certain this is what they end up doing. Given that you are indeed testing you should try this and make sure. Its either going to work, or not, I don't foresee (in my limited experience...) any delayed reaction that would be likely to arise.
Thanks! That gives me confidence to give that method a try.
Kevin
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
