PostgreSQL Database Migration

migrationpostgresqlscripting

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

Best 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

Related Solutions

Sql-server – Copying the database from SQL Server to PostgreSQL

For the person who's done this before I've looked at many tools and there are many pitfalls in doing that. The cheapest way I found to do this was to do a text dump, convert, and load.

My setup was MSSQL 2005 to PostgreSQL 8.x on Linux. The reason I say convert is because in the bulk bcp dump there is no way to distinguish between '' and NULL so the COPY command I used to load was complaining about NULL value for a table that had '' as a part of the primary key.

Dumping the schema also required conversion since the types are not exactly compatible, so it's been a bit of work but no buying of data migration tools which can be expensive for what may be just one time use.

There are other tools like this one but from what I have tested(2 years ago) has limitations.

Sql-server – Migration of SQL Server to Oracle Database

Global temporary tables in Oracle are very different, as they have a permanent logical definition and transient storage, so practically the reverse of your situation.

I would look at trying to avoid temporary tables at all, in favour of Common Table Expressions (WITH clauses). They are very powerful stuff and can transiently use temporary disk space if it aids performance.

Otherwise you'll have to manually code unique table names etc -- it'll be unpleasant to support in future.

Best Answer

Related Solutions

Sql-server – Copying the database from SQL Server to PostgreSQL

Sql-server – Migration of SQL Server to Oracle Database

Related Question