Postgres Migrate Data To New Database

Question

I have two Postgres databases, one old and one new.

How do I move the data from old to new while keeping the existing data that's in new (and of course the data that's being migrated from old) in tact? Also, what if I want to add mappings from old table names to new table names?

Taking both databases offline is a possibility, if truly necessary.

Patrick Narkinsky · Accepted Answer · 2016-05-01 01:19:51Z

1

Check out the --data option to pg_dump. This should produce a dump that won't drop or recreate tables or overwrite existing data.

Something like:

pg_dump --data database_name > dump.sql

This can be restored to the database using something like:

psql database_name << dump.sql

I'd be very careful using this if you have important data in "new", and I'd make sure I had a dump of the new database (with schema included, not just data) in case I messed it up.

NOTE: you tagged this with rails, so be aware that this approach will bypass any ActiveRecord validations you have (including validations for uniqueness, etc.

answered May 1, 2016 at 1:19

Patrick Narkinsky

5613 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Baub Over a year ago

Thanks! What if I want to add mappings from old table names to new table names? Editing original question to reflect this requirement.

Patrick Narkinsky Over a year ago

The easiest way to do that would be to load your original dump in a text editor and edit the table names by hand. It depends on how much mapping of column names you need to do -- at some point, you'll want to actually use a script to transfer the data.

Collectives™ on Stack Overflow

Postgres Migrate Data To New Database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related