PostgreSQL Database Migration

Posted on

Question :

How can I migrate data from on PostgreSQL database to another PostgreSQL database? Is there any technique to migrate data with SQL scripting?

Answer :

If you migrate to a new version of PostgreSQL use pg_dump from the latest version. Anyway, this is a simple way to dump and restore :

pg_dump -U username -h hostname source_database | psql -U username target_database

If your destination database has only one additional column, I think the simplest thing to do would be to pg_dump the existing database, load that dump into a completely new database, then add the new column.

An alternative might be to use the --column-inserts option (among others) for pg_dump. That will dump data as SQL INSERT statements with explicit column names. Those INSERT statements should work fine on tables that have had columns added.

Leave a Reply

Your email address will not be published. Required fields are marked *