I have a large (100GB+) database that I need to transfer to multiple machines over the next 6 months or so. This database can't be shrunk and had any data dropped to modify the size (unfortunately).

Is there any way to transfer the database from my machine to another other (Windows) machine without providing an import dump script and the output of pg_dump?

Optimally I'd just be able to plop the file in a directory and it'll work, or have something similar to SQL Server's attach database option where I don't need to make the user spend 12+ hours importing the database.

  • 1
    Take a look at pg_basebackup. Do both machines (the source and the target have the same OS)? – ypercubeᵀᴹ Feb 2 '17 at 11:41
  • Host and target have the same OS, but have no communication between themselves. The database unfortunately has to be local to the machine rather than on a central server that they query. Thanks for the reference to basebackup, having a read now. – Jay Gould Feb 2 '17 at 11:45
  • If there is 'no communication', you will be left with good old pg_dump, I'm afraid. – dezso Feb 2 '17 at 11:50
  • pg_dump with the custom format is your best option. Note that the dump is smaller then the actual database size because only the definition of indexes is stored in the dump, but not the index data. The same is true for materialized views. If you have a lot of indexes the dump will be substantially smaller then the 100GB (plus the custom format is also compressed) – a_horse_with_no_name Feb 2 '17 at 11:51
  • @dezso why not pg_basebackup? I'd say both are options (with differences, one of them as a_horse points is that pg_dump doesn't store the index data, only definitions, and so the result file is smaller) – ypercubeᵀᴹ Feb 2 '17 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.