PostgreSQL Migration – Migrating Postgres Server from Windows to Linux

I've done this with a linked server (though on a smaller scale) so here's what I did:

1. Set up the linked server using postgresql odbc driver (create odbc dns)
2. Fetch table information on the linked server using some combination of sp_tables_ex and this SO post
3. Iterate through those tables using your chosen method of pain: Cursor, Loop, script of some sort and build a query with SELECT * INTO db.schema.@TABLE_NAME FROM OPENQUERY('LINKEDSERVER', 'SELECT * FROM @TABLE_NAME' where @TABLE_NAME is retrieved from the info captured in step 2.

I tried doing this with SSIS but couldn't get the components to play nicely. Again, this is just a rough sketch of what I did. If you'd like more details just ask in a comment and I'd be happy to give them.

EDIT (I just added the code anyway):

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'POSTGRES', @srvproduct=N'',
 @provider=N'MSDASQL', @datasrc=N'postgres'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'POSTGRES',@useself=N'False',
@locallogin=NULL,
@rmtuser=N'username',@rmtpassword='########'

GO

create database workdb
GO

use workdb
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO workdb.dbo.pg_tables 
    FROM OPENROWSET('SQLNCLI', 
    'Server=(local);Trusted_Connection=yes;','EXEC sp_tables_ex ''POSTGRES''');
GO

select * from pg_tables;

declare @tablename as nvarchar(150)
declare @sql as nvarchar(1000);

declare tablegetter cursor for 
    select table_name from pg_tables

open tablegetter
fetch next from tablegetter
    into @tablename

while @@FETCH_STATUS = 0
    begin
        set @sql = 'select * into [' + @tablename + 
            '] from OPENQUERY(POSTGRES, ''SELECT * FROM "' + 
            @tablename + '"'');';
        print @sql;
        execute(@sql);
        fetch next from tablegetter
            into @tablename;
    end

close tablegetter;
deallocate tablegetter;

I'd consider backup/restore of a mix of full and differential backups from prod to dev. Then synch between this restored copy to your actual dev databases

SQL Server 2008 R2+ supports backup compression in Standard Edition (In SQL Server 2008 it was Enterprise Edition only) which makes this easier.

Reasons:

• You test your backup integrity and restore capability
• You can reset your dev database easily if some code monkey bollixes it
• You can compare "before" and "after" DDL, performance etc
• You can snapshot the restored database as needed to roll this back if you make changes

I've used this before and will use it again for these reasons

I suggested this in my answer to The smallest backup possible ... with SQL Server