CODEWITHSUNDEEP
postgresqldatabase-backupsbackup-strategies
Maciej
Maciej

Reputation: 10825

Postgresql - restore SQL dump with tablespaces

I'm planning to move some tables to different tablespaces (folders) on my PROD Linux box. Overnight DB backups are done using pg_dumpall

I have also DEV environment working under Windows OS Im usually restoring sql dump (made on Linux). Im worrying now how to restore such sql dumps, having pointers to Linux partition, in Linux notation.

I read on various webpages that same folder structure has to be created in order to restore non-standard tablespaces. But folder paths in Windows and Linux looks totally different (c:\... vs /opt/...)

Is there any command line switch allowing remap tablespace to other (Windows-like location) during restore? If not how you guys manage that scenario ?

I guess I shoud be able to archieve that by editing this SQL dump file - but it's huge, few hundred gigs file, also it is a bit problematic to automate

Upvotes: 0

Views: 2760

Answers (2)

wildplasser
wildplasser

Reputation: 44250

You can retrieve the actual tablespace definitions with a separate pg_dumpall command. You still need to do some editing, but the output is not that large. (similar for users)

pg_dumpall --tablespaces-only mydatabasename >stuff.out

Upvotes: 2

user330315
user330315

Reputation:

There is no option to remap tablespace names during import, so you will need to create them in your Windows installation with the same name - the actual location physical location ("folder structure") is irrelevant as the SQL dump only references them by name.

If the script contains the create tablespace command you need to change that command to use a directory/path name that exists on your system before you can run the SQL script. But you only need to change that, all other places will refer to the tablespace name, not the folder path.

Typically pg_dump is easier than pg_dumpall for moving databases around (e.g. because of tablespaces).

Upvotes: 1

Related Questions