PostgreSQL migrando uma database de tablespace

    No PostgreSQL diferente do Oracle uma tablespace é um local de armazenamento de dados do banco, deste modo imagine o seguinte cenário: 

Dois HDs, um onde está a pg_default(tablespace padrão do PostgreSQL) com 99% de uso e o outro HD com uma boa capacidade e performance com 1% de uso. Então chegou a hora de migrar uma de suas databases para outra tablespace.

Primeiro criamos uma nova tablespace apontando para o path do novo HD: 

CREATE TABLESPACE "external_data" OWNER postgres

LOCATION '/data/Archives1/pg_external_data';

Onde '/data/Archives1/pg_external_data' é o caminho absoluto da nova tablespace. 

Verificação das tablespaces existentes:

SELECT spcname AS Tablespace, pg_size_pretty(pg_tablespace_size (spcname)) AS Tamanho, spclocation AS Caminho

FROM pg_tableSpace;

tablespace | tamanho | caminho

---------------+---------+----------------------------------

pg_default | 53 GB |

pg_global | 609 kB |

external_data | 4 bytes | /data/Archives1/pg_external_data

(3 rows)

A saída acima é um exemplo.

Agora o índio veio tem dois caminhos, um migra-se objeto por objeto(tabela, índice e la la la), ou faz um dump da database e se importa depois, vamos pelo caminho mais fácil, que segue:

Criação do dump:

$ nohup /opt/pgsql/bin/pg_dump -h 127.0.0.1 -p 5432 -U postgres -f /data/Archives1/mydump-12-08-2010.dmp my_db &

Criação da database auxiliar:

CREATE DATABASE test

WITH ENCODING='UTF8'

OWNER=postgres

CONNECTION LIMIT=-1

TABLESPACE=external_data;

Agora vamos importar as crianças:

$ nohup /opt/pgsql/bin/psql -U postgres -h 127.0.0.1 test < /data/Archives1/mydump-12-08-2010.dmp &

Virando a chave:

Ativando database auxiliar para produção:

ALTER DATABASE my_db RENAME TO my_db-old;

ALTER DATABASE test RENAME TO my_db;

Liberação de espaço ao dropar database em desuso:

DROP DATABASE my_db-old;