Re: Tablespace column value null on select * from pg_tables

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(dot)klaver(at)aklaver(dot)com