Sql-server – Restore multiple databases quickly – practicing disaster recovery

It sounds like what you're talking about is more aligned with High Availability.

If you are already on SQL Server 2012, or you have the ability to upgrade then it sounds like AlwaysOn Availability Groups could be right up your alley. This is a technology that will allow you to create a cluster and you could have two geographically separate servers (i.e. your current database server and your remote database server that you're planning on standing up). If the network latency isn't a performance impact between the two servers you could set up a two node cluster and have each of those nodes hosting a replica of the Availability Group (or Groups). With synchronous commit, you could configure automatic failover when there is a network outage or some other service-hindering event.

By creating an Availability Group Listener you could have the client applications connecting to the listener which will direct traffic to the current primary replica.

If you don't have the ability to get onto SQL Server 2012 to leverage AlwaysOn Availability Groups, then Database Mirroring could also be another high availability solution for you. If the session is operating synchronously with a witness server, you could also have the ability for automatic failover in the event of the principal becoming disconnected.

The reason I am recommending these two is two-fold: replication is pushing out articles, and it sounds like you want your entire database (or databases) to be ready for a failover. Traditional failover clusters could also be another solution but you would have to also consider shared storage between the two nodes. That might get complicated with the geographically separate servers.

if the network fails or the software throws a bug, it is very hard to come back to office and fix the problem.

What do you mean by "the software throws a bug"? There will need to be the proper error handling and error recovery in order to ensure this type of behavior doesn't cause an outage.

First, repeat after me:

A replica is not a backup

That is your first problem. Do not use replication as a substitute for backups. Replication is a useful part of a disaster recovery strategy. It is not a useful disaster recovery strategy.

For PostgreSQL 9.0.x I would recommend that you take a look at barman, an open source backup and archive manager for PostgreSQL by 2ndQuadrant. This helps manage some of the details so you don't have to. (Full disclosure: I am a principal consultant for 2ndQuadrant.)

This being said, barman is only helpful if you are relatively familiar already with the base backup/wal shipping approach (which is what you want so you can roll back human errors like this). This is necessary for PITR to work.

In this approach you set up two things. The first is a base backup (see the pg_basebackup documentation for how). Note you cannot use a logical backup (i.e. from pg_dump for this). The second is to set up continuous archiving so that you can take your WAL segments and use them for PITR. The specifics here depend highly on your environment and so you want to take a look at the docs and design something that works for you. True to the UNIX way, these are largely tools for building your backup and recovery solution. There isn't much more that can be said about this without something more specific, but start there and build a solution for your business out from it.