On 7/15/19 11:35 AM, Alex Williams wrote:
Hi,
Server Version 9.5
I found this old thread on something similar to the results I'm getting:
https://www.postgresql.org/message-id/1308615192339-4508750.post%40n5.nabble.com
But in my case, I have a database that's in a user-defined tablespace
(data2) and all the tables/indexes there are also in data2 and I want to
do a select into a table the results of all the tables / tablespaces
they are in that database...when doing this:
SELECT distinct tablespace
FROM pg_tables;
I get 2 rows: null and pg_global (I think to expect null for pg_default,
but if the table is in a user-defined tablespace, should we expect it to
show it, in my case, data2?)
Not if you did:
CREATE DATABASE name ... [ TABLESPACE [=] tablespace_name ]
ALTER DATABASE name SET TABLESPACE new_tablespace
This makes the tablespace the default for the database and the default
shows up as null in pg_tables:
https://www.postgresql.org/docs/9.5/view-pg-tables.html
tablespace name pg_tablespace.spcname Name of tablespace containing
table (null if default for database)
or
SELECT distinct tablespace
FROM pg_indexes
I get 3 rows: null, pg_global and pg_default
and this: SELECT * FROM pg_tablespace;
I get 3 rows: pg_default, pg_global and data2.
Using pgadmin, getting properties for the DB / tables, it shows data2.
What I want to do is move all the tables / indexes from data2 to
pg_default (we added more space to the pg_default mount.)
Now, I did a pg_dump/restore for one database which took a long time and
we now know the process for that, so on the next database we have we
want to do it where we use the following commands:
ALTER DATABASE mydatabase SET TABLESPACE pg_default;
alter table all in tablespace data2 set tablespace pg_default;
But, what I'm trying to accomplish here is, aside from checking the
filesystem, like df- h, to see it was moved or properties on each table
(too many) I just want to run a query that will insert into a table all
the tables and their tablespace names and when the above two commands
(3rd will be moving indexes) run the query again and verify everything
has moved from data2 to pg_default.
Thanks for your help in advance.
Alex
Sent with ProtonMail <https://protonmail.com> Secure Email.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
