Tell me more ×
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.
up vote 1 down vote favorite

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

share|improve this question
There is a bulk loader utility maintained through the PostgreSQL foundation called pg_bulkload. This might do what you want. – ConcernedOfTunbridgeWells Apr 5 '12 at 10:12
The Navicat for PostgreSQL database admin tool can also do this sort of thing, amongst many others. – kfuglsang Apr 5 '12 at 10:55
2  
What's wrong with pg_dump/pg_restore? – a_horse_with_no_name Apr 5 '12 at 12:51
1  
Or good old SQL COPY? – Erwin Brandstetter Apr 6 '12 at 0:14
I am new to database migration and the problem is that the destination database has one additional field. – Atul Makwana Apr 9 '12 at 13:40

2 Answers

up vote 2 down vote

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.

share|improve this answer
Since plain pg_dump specifies the column names in the copy command and it is the destination database that has the added column then you shouldn't need to use the --column-inserts option, or an additional database either. – gsiems Sep 6 '12 at 20:08
up vote 2 down vote

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
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.