I am going to move a database from the old server to a new one.

  • the old one runs PostgreSQL 9.1, the new one has 9.3.
  • I want to change the database name.
  • I want the new database to be in a dedicated tablespace (the old one is in default) - including indices, etc.

Unfortunately, I have no chance to experiment (and HDDs of the old server start to fail) so I ask for the options for pg_dump/pg_restore that would work in my case.


I have completed the migration with no problems.

Creating the dump is easy:

sudo -u postgres pg_dump --verbose --no-tablespaces --format=directory --file=/backup/path old_database_name

Restoring on a new instance: first, create a new tablespace, and a target database in that tablespace. Then import your dump like this:

sudo -u postgres pg_restore --verbose --dbname=new_database_name --jobs=8 /backup/path/

You can adjust number of concurrent jobs dependent on Your hardware - processor cores, I/O subsystem performance, etc.

Now we have all the data in another database, with different name, in a dedicated tablespace, on a new server running newer PostgreSQL.

  • Used this approach once again today; unfortunately cannot upvote own post :) – saabeilin Dec 10 '15 at 7:59
  • worked for an upgrade from 9.4 -> 9.6 as well – lennart Oct 25 '16 at 23:18

Since you are using pg_dump it will dump the schema and the data.

pg_dump -d mydb -f dump_file_name.sql

To restore you have to createdb first and then restore it so, the new name issue can be solved when creating the new database.

createdb mynewdb

For the tablespace, you first have to create the tablespace in the newly created database.

create tablespace 'your_tablespace' location 'your_tablespace_location'

Then edit the dump file (dump_file_name.sql) created with pg_dump and edit SET default_tablespace = '' to SET default_tablespace = 'your_tablespace'.

Finally you can restore with:

psql -d mynewdb -f dump_file_name.sql

  • Thanks Fabrizio! So, editing sql dump is required... I thought to use directory+tar to make import fatser. – saabeilin Mar 13 '14 at 8:43
  • What do you mean by directory + tar? Moving the whole postgres dir? I don't think that's a good idea! – Fabrizio Mazzoni Mar 13 '14 at 8:44
  • Remember that when you upgrade major versions a dump and restore is always required (as per docs) – Fabrizio Mazzoni Mar 13 '14 at 8:45
  • I mean pg_dump --format=directory. – saabeilin Mar 13 '14 at 8:50
  • Oh ok! Sorry but no experience with directory format. In any case if you need to change the tablespace the plain text dump is the way to go. – Fabrizio Mazzoni Mar 13 '14 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.