I have a "large" PostgreSQL (9.6 with timescale plugin) database (about 1Tb). And would like to migrate it to a Linux machine. The naive approach would be pg_dump and pg_restore. (Not sure how long that might take on a powerful machine with SSD on both sides, maybe you have an idea?)

Just wanted to ask if this is the way to go or if there might be a better more convenient solution. What I saw so far were pgBackRestore and barman.

I'm asking this question since I already had some trouble migrating a MongoDB database which took really very long. So now I would like to avoid errors : )

Thanks a lot for any hint!

  • Maybe just what one might expect. Lets say if pg_restore/dump takes more then 24hours then I would go with a complete different approach. Something like fetching and inserting the most recent data (which is relevant) and doing restore/dump on small tables only. – MichaelRazum Aug 4 '19 at 20:03
  • Using pg_upgrade with the --link option will be the fastest solution. Make sure you have a backup before doing that. – a_horse_with_no_name Aug 5 '19 at 6:46
  • @horse_with_no_name Don't understand your comment. The asker wants to move PG from Windows to Linux. – Colin 't Hart Aug 6 '19 at 11:30

With the directory format (-F d) of pg_dump, you can parallelize dump and restore, which will help if there are several large tables.

  • Thanks a lot. I was going to use the -Fc command, which would take much longer. Will post later how long it took! – MichaelRazum Aug 5 '19 at 7:05
  • As promised here a short update. The -F d command worked and took about 1 hour to dump and a bit longer to restore. Unfortunately, timescale had some problems with the -j command. So I ended up using -Fc as a command. All together it took more the 6 hours...and after that some manual work. In the end it worked. – MichaelRazum Aug 8 '19 at 23:01

You should be able to setup a streaming replica on your Linux machine. Then promote it. Use pg_basebackup to take the initial copy, but you can do this while the master is running. Promoting the replica to be the new master will only take seconds, if that.

Not the answer you're looking for? Browse other questions tagged or ask your own question.