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.

7

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 | |
  • 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 – alternated direction Oct 25 '16 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 Jul 13 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 Jul 20 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 Jul 21 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 | |
  • 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.