Search Postgresql Archives

Re: Tablespace column value null on select * from pg_tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux