Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_dump & table space

pg_dump & table space

From
Luca Ferrari
Date:
Hi,
is there a way to dump an entire database which has a specific table space
without having in the sql file any reference to the tablespace? This can be
useful when moving the database from one machine to another (that does not use
the tablespace). Any way to achieve that with pg_dump?

Thanks,
Luca

Re: pg_dump & table space

From
John R Pierce
Date:
Luca Ferrari wrote:
> Hi,
> is there a way to dump an entire database which has a specific table space
> without having in the sql file any reference to the tablespace? This can be
> useful when moving the database from one machine to another (that does not use
> the tablespace). Any way to achieve that with pg_dump?
>

dump in ascii format, edit the resultant SQL file and remove any
references to the tablespace.   this could probably be done with a perl
or sed script if the file is too large for a conventional text editor.

Re: pg_dump & table space

From
John R Pierce
Date:
John R Pierce wrote:
> Luca Ferrari wrote:
>> Hi,
>> is there a way to dump an entire database which has a specific table
>> space without having in the sql file any reference to the tablespace?
>> This can be useful when moving the database from one machine to
>> another (that does not use the tablespace). Any way to achieve that
>> with pg_dump?
>>
>
> dump in ascii format, edit the resultant SQL file and remove any
> references to the tablespace.   this could probably be done with a
> perl or sed script if the file is too large for a conventional text
> editor.


oh, dump the schema only first, edit that, then dump the data only to a
seperate dumpfile, thats probably simplest.

Re: pg_dump & table space

From
Tom Lane
Date:
Luca Ferrari <fluca1978@infinito.it> writes:
> is there a way to dump an entire database which has a specific table space
> without having in the sql file any reference to the tablespace? This can be
> useful when moving the database from one machine to another (that does not use
> the tablespace). Any way to achieve that with pg_dump?

Recent versions have

  --no-tablespaces            do not dump tablespace assignments

However, this is really just cosmetic, as the dump is set up like this:

SET default_tablespace = whatever;
CREATE TABLE whichever(...);

If tablespace 'whatever' doesn't exist, you'll get an error on the SET
but the CREATE will succeed anyway.  (I guess this only works cleanly
if the destination machine has *none* of the source's tablespaces,
else things might get assigned to unexpected tablespaces.  But it's
definitely possible to restore a dump without having the same
tablespaces.)

            regards, tom lane

Re: pg_dump & table space

From
Alvaro Herrera
Date:
Tom Lane wrote:

> However, this is really just cosmetic, as the dump is set up like this:
>
> SET default_tablespace = whatever;
> CREATE TABLE whichever(...);
>
> If tablespace 'whatever' doesn't exist, you'll get an error on the SET
> but the CREATE will succeed anyway.  (I guess this only works cleanly
> if the destination machine has *none* of the source's tablespaces,
> else things might get assigned to unexpected tablespaces.  But it's
> definitely possible to restore a dump without having the same
> tablespaces.)

Maybe it would be better if the dump has a RESET default_tablespace
before the SET.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support