> On Thu, Nov 17, 2016 <david.g.johns...@gmail.com> wrote:
> > On Thu, Nov 17, 2016 at 9:16 AM, <kbran...@pwhome.com> 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
