Re: [GENERAL] help with moving tablespace

[rob stone]

> Bonus question: I found an ER diagram of some of the pg_* tables at h
> ttp://www.slideshare.net/oddbjorn/Get-to-know-PostgreSQL. Is there an
> ERD of all of them so a person can better understand how to use them
> when one must? I suppose the same question applies to
> information_schema since I probably should be using that over the
> pg_* tables when possible (and as the above example shows, sometimes
> you have to go look at the pg_* tables).
> 
> Thanks!
> Kevin
> 
> 

Hello,

ExecuteQuery has an ER diagram tool. You can download the jar file from
www.executequery.org and obtain the JDBC driver from the Postgres site.
You set up separate connections to all databases that you wish to
access.
It generates the ER diagram but prior to printing it you need to drag
and drop the "boxes" around to make it readable. I have not tried it
(yet) over information_schema.

HTH,
Rob


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] help with moving tablespace

> On Thu, Nov 17, 2016  wrote:
> > On Thu, Nov 17, 2016 at 9:16 AM,  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

Re: [GENERAL] help with moving tablespace

[David G. Johnston]

On Thu, Nov 17, 2016 at 9:16 AM,  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.


> SELECT c.relname, t.spcname
> FROM   pg_class c JOIN pg_tablespace t ON c.reltablespace = t.oid
> WHERE  t.spcname = 'old_name';
>
> But that always returns 0 rows. So how do I track this down?
>

​pg_class displays relative to the current database only so you need to log
into the others to check them.​


> 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.​

​David J.​

[GENERAL] help with moving tablespace

Back in the early dawn of time (before I came here :) the tablespace for our DB 
was put under the data dir, which gives us the warning "tablespace location 
should not be inside the data directory", which I'm trying to fix -- and I'm 
also trying to fix ownerships so everything isn't owned by postgres.

So in our application "upgrade" script (in bash), I'm doing:

ts_location=/db/tablespaces
mkdir $ts_location
chmod 700 $ts_location
chown postgres:dba $ts_location
echo "CREATE TABLESPACE new_ts OWNER ourowner LOCATION '$ts_location';
  ALTER DATABASE ourdb OWNER TO ourowner;
  ALTER DATABASE ourdb SET TABLESPACE new_ts;
  DROP TABLESPACE old_ts; " |
$PGPATH/psql template1
echo "ALTER SCHEMA public OWNER TO ourowner;" |
$PGPATH/psql ourdb
# the only reason this should fail is if the above command failed
rmdir $PGDATA/ourdb || { echo "ERROR: Failed to remove $PGDATA/ourdb; the 
alters must have failed."; exit 1; }

Also, $PGDATA (and therefore the old tablespace) is under /db.

I appreciate the ease of the above commands, but sadly there are 2 issues with 
it that I need help with.

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:

SELECT c.relname, t.spcname 
FROM   pg_class c JOIN pg_tablespace t ON c.reltablespace = t.oid 
WHERE  t.spcname = 'old_name';

But that always returns 0 rows. So how do I track this down?

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?

Bonus question: I found an ER diagram of some of the pg_* tables at 
http://www.slideshare.net/oddbjorn/Get-to-know-PostgreSQL. Is there an ERD of 
all of them so a person can better understand how to use them when one must? I 
suppose the same question applies to information_schema since I probably should 
be using that over the pg_* tables when possible (and as the above example 
shows, sometimes you have to go look at the pg_* tables).

Thanks!
Kevin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general