Wrong tablespace

[Eric Poker]

I forget to save my database on my spare disk
how can I move them to the spare tablespace ?

And then
how can I change the default location (pg_default)

[kraada]

This post goes through how to set up a tablespace.

To set a default tablespace, at the PostgreSQL command prompt type:
SET default_tablespace = space1;

where space1 is the name of some tablespace you've created. Thereafter all new tables created will default to that tablespace. (See the PostgreSQL documentation for more on Tablespaces.)

[Eric Poker]

This post goes through how to set up a tablespace.

To set a default tablespace, at the PostgreSQL command prompt type:
SET default_tablespace = space1;

where space1 is the name of some tablespace you've created. Thereafter all new tables created will default to that tablespace. (See the PostgreSQL documentation for more on Tablespaces.)

thank you
I know how to setup a tablespace but I forget to create few databases de proper way 8-)
Anyway, i have export/delete/recreate/import my database to correct way

thank you for the command :D

[LVgamb00ler]

I have had the same problem as the original poster. My solution was to create a backup of the DB that was accidently in the wrong tablespace, then use pgAdmin to create another database in the correct tablespace, then restore the backup to the new DB.

I was wondering as I read the posts in this thread whether it would be possible to:
- create the desired tablespace
- copy the hard disk files to the desired tablespace retaining the same naming conventions as in the original storage location
- modify the row in pg_database (postgreSQL system catalog) to change the tablespace name
- delete DB files from original storage

Does anyone know how to write sql commands to access the postgreSQL system catalogs?

G'luck all,
LVgamb00ler

[APerfect10]

I have had the same problem as the original poster. My solution was to create a backup of the DB that was accidently in the wrong tablespace, then use pgAdmin to create another database in the correct tablespace, then restore the backup to the new DB.

I was wondering as I read the posts in this thread whether it would be possible to:
- create the desired tablespace
- copy the hard disk files to the desired tablespace retaining the same naming conventions as in the original storage location
- modify the row in pg_database (postgreSQL system catalog) to change the tablespace name
- delete DB files from original storage

Does anyone know how to write sql commands to access the postgreSQL system catalogs?

G'luck all,
LVgamb00ler

I am not sure if that would work or not. Running the SQL directly through pgAdmin III, you can access the system catalogs for the selected database the exact same way you would query any other table.

You can view all of the system tables and views by expending the "Catalogs" -> "PostgreSQL" tree.

Bets regards,

Derek