Обсуждение: Discrepancy in --no-tablespaces behavior between Tar and Plain-text formats
Hi,
[edb@1a1c15437e7c bin]$ ./psql postgres
psql (19devel)
Type "help" for help.
I observed that pg_restore attempts to set default_tablespace from a Tar archive even if --no-tablespaces was used during pg_dump.
Please refer to this scenario:
psql (19devel)
Type "help" for help.
postgres=# \! mkdir /tmp/tbsp
postgres=# create tablespace a location '/tmp/tbsp';
CREATE TABLESPACE
postgres=#
postgres=# create table t(n int) tablespace a;
CREATE TABLE
postgres=# insert into t values (1);
INSERT 0 1
postgres=# \q
[edb@1a1c15437e7c bin]$ ./pg_dump --no-tablespace -Ft -f tp.tar postgres
CREATE TABLESPACE
postgres=#
postgres=# create table t(n int) tablespace a;
CREATE TABLE
postgres=# insert into t values (1);
INSERT 0 1
postgres=# \q
[edb@1a1c15437e7c bin]$ ./pg_dump --no-tablespace -Ft -f tp.tar postgres
Now, try to restore to another cluster
[edb@1a1c15437e7c bin]$ ./pg_restore -Ft -C -d postgres -p 9000 tp.tar
pg_restore: error: could not execute query: ERROR: database "postgres" already exists
Command was: CREATE DATABASE postgres WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'C.UTF-8';
pg_restore: error: could not set "default_tablespace" to a: ERROR: invalid value for parameter "default_tablespace": "a"
DETAIL: Tablespace "a" does not exist.
pg_restore: warning: errors ignored on restore: 2
[edb@1a1c15437e7c bin]$
pg_restore: error: could not execute query: ERROR: database "postgres" already exists
Command was: CREATE DATABASE postgres WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'C.UTF-8';
pg_restore: error: could not set "default_tablespace" to a: ERROR: invalid value for parameter "default_tablespace": "a"
DETAIL: Tablespace "a" does not exist.
pg_restore: warning: errors ignored on restore: 2
[edb@1a1c15437e7c bin]$
If we do like this - using -Fp format, then no error
./pg_dump --no-tablespace -Fp -f tp.txt postgres
\i tp.txt
regards,
Re: Discrepancy in --no-tablespaces behavior between Tar and Plain-text formats
От
Nathan Bossart
Дата:
On Wed, Feb 18, 2026 at 04:13:11PM +0530, tushar wrote:
> I observed that pg_restore attempts to set default_tablespace from a Tar
> archive even if --no-tablespaces was used during pg_dump.
Correct. The documentation for pg_dump --no-tablespaces states the
following:
This option is ignored when emitting an archive (non-text) output file.
For the archive formats, you can specify the option when you call
pg_restore.
--
nathan
On Wed, Feb 18, 2026 at 8:43 PM Nathan Bossart <nathandbossart@gmail.com> wrote:
On Wed, Feb 18, 2026 at 04:13:11PM +0530, tushar wrote:
> I observed that pg_restore attempts to set default_tablespace from a Tar
> archive even if --no-tablespaces was used during pg_dump.
Correct. The documentation for pg_dump --no-tablespaces states the
following:
This option is ignored when emitting an archive (non-text) output file.
For the archive formats, you can specify the option when you call
pg_restore.
Thanks for pointing that out. Since this appears to be the expected behavior, should we leave it as is rather than attempting to 'fix' or improve it?
if --not-tablespace already specified at the time of pg_dump ( not sure why it is ignored; it should throw an error if not supported)
then it makes sense that it wouldn't be necessary for pg_restore time, seems redundant.
regards,
Re: Discrepancy in --no-tablespaces behavior between Tar and Plain-text formats
От
Nathan Bossart
Дата:
On Thu, Feb 19, 2026 at 11:35:24AM +0530, tushar wrote: > Thanks for pointing that out. Since this appears to be the expected > behavior, should we leave it as is rather than attempting to 'fix' or > improve it? > if --not-tablespace already specified at the time of pg_dump ( not sure why > it is ignored; it should throw an error if not supported) > then it makes sense that it wouldn't be necessary for pg_restore time, > seems redundant. I'm not sure. While I agree that it's a little weird, AFAICT it's behaved this way for a long time, so changing it risks breaking existing scripts. I suspect that trade-off is not worth it in this case. -- nathan