Postgresql – How to export a subset of tabledata from a prodution database into the local testing database

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;

My guess would be going with some benchmarking, measuring the time it's taking you to recreate the full index, the time it takes to insert 10k rows without index, with index. then I'd try with 1000 rows, 5000 rows, 10000 rows, to try to figure out a trend.

Because even if you try to reduce the amount of factors that could affect the operation time (as Auto-Vacuum) it still depends on your hardware and your whole postgres.conf

BONUS: if you share with us some benchmarks, we could gladly help you to figure out the trend.