Folks, I am considering moving from SQL Server to Postgres. The per core licensing of SQL Server is too expensive for my needs.

My one concern is restoring Postgres from a dump. I believe that the pg_dump creates a file of SQL commands to build back up the DB and that this can be quite slow.

I am used to restoring large SQL Server databases in quite a short period of time.

Can anyone advise me here on what folks do for performant disaster recover solutions?

I am installing postgres on windows, so barman etc are probably not an option and without some backup software I would be concerned about doing WAL point in time restores manually. I believe you need to shut down the server and restore everything and that seems severe.

  • pg_dump can also create a dump file that is not a SQL script using the "custom" or "directory" formats. You can easily setup a slave that is replicated to support desaster recovery. Alternatively use pg_basebackup to clone your server – a_horse_with_no_name Jul 4 '18 at 16:12
  • wal-e and omnipitr are implemented in Pyhon so you might get those to run on Windows. But implementing WAL archiving is pretty easy in Windows and the "manual restore" isn't that complicated either using some batch files – a_horse_with_no_name Jul 4 '18 at 16:12
  • pghoard is another Python based tool – a_horse_with_no_name Jul 4 '18 at 16:18
  • I don't know, horse... That WAL archive restore looks a bit mental in the official docs. Do you know if anyone successfully used any of these python backup tools on a windows machine – mikelus Jul 4 '18 at 17:56
  • 1
    WAL archive restore is essentially a copy command. There is no "magic" to it – a_horse_with_no_name Jul 4 '18 at 18:28

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.