CODEWITHSUNDEEP
postgresql
Lemmes
Lemmes

Reputation: 316

pg_restore fails with missing tablespace eventhough --no-tablespaces is given

I'm trying to import a database into postgres with the following command:

"C:\Program Files\PostgreSQL\9.5\bin\pg_restore.exe" --dbname=postgres --clean --create --format=d --jobs=16 --no-tablespaces --host=localhost --username=postgres "S:\Directory-Dump_Inn"

The fine manual says:

--no-tablespaces

Do not output commands to select tablespaces. With this option, all objects will be created in whichever tablespace is the default during restore.

I thought this means I wouldn't have to create the tablespace, from which the dump orgins. As the import fails with

ERROR: tablespace "ts_inn" does not exist

I guess, this assumption is wrong. So what does the explanation from the manual mean then? The import works fine if I create the tablespace beforehand.

Edit: Version of importing server is 9.5.4, version of exporting server is 9.6.3. Probably the export command is important, too:

"C:\Program Files\PostgreSQL\9.6\bin\pg_dump.exe" --host localhost --port 5432 --username "postgres" --no-password  --format directory --section pre-data --section data --section post-data --compress 0 --jobs 4 --verbose --file "F:\Postgres-Backup\Directory-Dump_Inn" "inn" 2>> %logfile%

Is the culprit that the export omits --no-tablespaces? If it is and if I added --no-tablespaces as an argument to pg_dump, then why would I use --no-tablespaces during import? Obviously then there wouldn't be any CREATE TABLESPACE xxx in the dump file.

Upvotes: 2

Views: 5402

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246578

A problem quite like yours was fixed in this commit in Sep 2016 to fix bug #14315, so make sure that you have PostgreSQL 9.5 or later with the latest patches.

But even with these patches your example will fail, because you didn't add --no-tablespaces to the invocation of pg_dump.

It is kind of surprising, but to suppress the TABLESPACE clause of CREATE DATABASE in the dump, it is not enough to call pg_restore --no-tablespaces. The dump has to be created with that option.

Upvotes: 2

Related Questions