Unilateral replication in Postgresql

Question

I have two servers. One is only and exclusively for testing. The other is for production itself. I need the test database to contain the same data from the production database. I can’t do a mirror, since if I do, the changes in the test server will obviously affect the production data, and that would be catastrophic!

I then need a way to only do a Server -> Client synchronization, without ever doing the reverse (Client -> Server). I need this synchronization to happen every night.

What can be done in this case?

Answer 1

If it is just a test server I strongly recommend to make the copy of the data on demand and not do it online. You will have to mount scripts to do this for you. In general it is a simple thing, to do the dump of the database as if to make a backup, put it in the right place and restore it to the other server. You probably don’t need it anymore. There are several ways to get the same result.

But if you have a real reason to replicate.

In fact most solutions can, even if optionally, replicate in only one direction. I would say that most can only do in one direction.

It seems to me you need at most a replication by log. I think it’s the simplest. A Postgresql documentation talks about this. You have the option of streaming but in your case is exaggeration.

The log Shipping has bass overhead on the main server and works well without much complication. It does not offer high availability or immediate redundancy capabilities, nor can it be using in snail swing but does not seem to be what you need.

Also has the Slony. I think it’s a cannon to kill bird but it’s an option.

Another similar option is the Bucardo.

There are other solutions that doesn’t suit you (although I don’t think these are good ideas either) or I don’t know. One of them would be the Pgpool-II which frees the main server only to meet production requests but requires additional hardware to coordinate the sending of darlings for servers, it is no advantage in your case.

Postgresql has evolved a lot and already has native solutions for replication, take a look at the current state before considering the options presented here.

I think other solutions can be used independently of the database. It is possible to use utilities running through the operating system that replicate the content. But I also doubt they are better.