7

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.

Improve this question

2 Answers 2

Reset to default
8

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.

Improve this answer
5
  • Used this approach once again today; unfortunately cannot upvote own post :)
    – saabeilin
    Commented Dec 10, 2015 at 7:59
  • worked for an upgrade from 9.4 -> 9.6 as well
    – alternate direction
    Commented Oct 25, 2016 at 23:18
  • I get an error pg_restore: [archiver] unsupported version (1.14) in file header. With your provided option it appears this method only works for minor postgres changes. I'm moving from 9.6 to 11.
    – sqwale
    Commented Jul 13, 2020 at 17:47
  • @sqwale thanks for the heads up. We will be moving a couple of 9.6 databases to 12 this year (the other ones are now just event projections from a Kafka streams)
    – saabeilin
    Commented Jul 20, 2020 at 8:54
  • I finally managed to resolve the issue. The source DB was 9.6 so realised I needed the pg_dump to be the same version as the database I would be restoring to. Also noticed an odd situation where my postgres running was 12 but by pg_dump was still 9.6. So ensure you'r pg_dump -v returns what you expect.
    – sqwale
    Commented Jul 21, 2020 at 13:22
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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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