pg_dump/pg_restore: different PostgreSQL version, another database name, another tablespace

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.

StackExchange Database Administrators Q#60812, answer score: 8