1

I know the way to set up a Master/Slave DB in Postgres is having 2 DB servers, but unfortunately i have only one server for now.

How can i mirror my production db into another "backup db" in "real_time"? I want to give access to another user to the mirrored db, so even if he does something there it will not affect production.

Improve this question

2 Answers 2

Reset to default
4

Nothing stops you setting up hot standby streaming replication, or another replication option like Londiste, between two PostgreSQL instances on the same computer.

The two copies of PostgreSQL must use different ports, but that's the only real restriction.

How to set up the second PostgreSQL instance depends on your operating system and how you installed PostgreSQL, which you have not mentioned.

You'll want to use streaming replication with hot standby if you want a read-only replica. If you want it to be read/write, then you can do a one-off copy of the database with pg_basebackup and not keep them in sync after that. Or you can use a tool like Londiste to replicate changes selectively.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Craig, thanks for the reply. The server is running CentOS and Postgres 9.3, and yes i want to to read and write, in the meaning that i am writing to both databases for different sources and i want them to be in sync, so if new data added to one, it will be added to the other and vice versa.
@Leon If you want the replica to be read/write it cannot also be in sync. You'd need a conflict resolution strategy of some kind.
I am trying to use a gem called rubyrep, but seems not supported anymore and it throws a pg connection error. Not sure why.
What if we want the replica to be read/write, but the write operations made on the replicas aren't forwarded to the master database ?
@user626921 You could do that with Londiste, but it'd quickly conflict with the master and stop syncing. How would you handle it if the replica had a row deleted, then it was updated on the origin server? It's not going to work.
|
0

You can run multiple instances of PostgreSQL on the same computer, by using different ports.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.