2015-02-24 10:06 GMT-05:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:
On 02/24/2015 05:06 AM, Guillaume Drolet wrote:
2015-02-24 7:07 GMT-05:00 Guillaume Drolet <droletguillaume@xxxxxxxxx
<mailto:droletguillaume@gmail.com>>:
2015-02-23 14:14 GMT-05:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@aklaver.com>>:
On 02/23/2015 10:08 AM, Guillaume Drolet wrote:
Hello,
I moved all my tables and indexes from one tablespace to
pg_default using
ALTER TABLE ... SET TABLESPACE pg_default;
ALTER INDEX ... SET TABLESPACE pg_default;
Some 2500 files were moved to pg_default but 461 files
remain in the
tablespace and so I cannot drop it.
When I query, for example:
SELECT oid, relname, relkind FROM pg_catalog.pg_class
WHERE oid IN (943602, 2650968, 2650971);
I see that most of these files are sequences. Why didn't
they get moved
and how can I move them to pg_default (and all other
remaining files) so
that I can drop the tablespace?
Well round file my previous suggestion. Just tried it and it did
not work.
Thanks Adrian.
So, anybody else have some piece of advice on this?
Thanks!
Digging a little more, I found that not only sequences were not moved
but also many tables in pg_catalog are still in my old tablespace. This
is expected since the query in the SQL files I used to move the tables
and indexes had a WHERE clause like this:
SELECT ' ALTER TABLE ' || schemaname || '.' || tablename || ' SET
TABLESPACE pg_default;'
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
So I tried removing the WHERE clause and running the script again:
psql -U postgres -d mydb < move_tables_to_pg_default.sql | findstr /R
/C:"[ALTER]" | psql -d mydb -U postgres
I got many errors like this one:
ERROR: permission denied: "pg_event_trigger" is a system catalog
If I can't move tables from pg_catalog, how will I be able to drop that
tablespace I don't want to use anymore?
I am thinking that maybe using "ALTER DATABASE mydb SET TABLESPACE
pg_default;" instead would take care of all this, no?
But when I tried it last week, I got a message like: some relations
already in target tablespace...
Any help will be much appreciated.
So how did all this stuff get into the non-default tablespace in the first place?
Stuff got there using the ALTER TABLE.../ALTER INDEX... SQL files mentionned in my previous post.
The answer would seem to be just reverse whatever you did in answer to the question above.
That makes sense. I will give it a try. Thanks.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@aklaver.com>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
