Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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

share|improve this answer
    
Thanks Fabrizio! So, editing sql dump is required... I thought to use directory+tar to make import fatser. –  saabeilin Mar 13 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 at 8:44
    
Remember that when you upgrade major versions a dump and restore is always required (as per docs) –  Fabrizio Mazzoni Mar 13 at 8:45
    
I mean pg_dump --format=directory. –  saabeilin Mar 13 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 at 8:52
up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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