Replicating a postgres database for testing I want to replicate a live server postgres database to our dev server for testing purposes but the problem is in inbuilt postgres replication feature the slave is always read only. Is there any better way to replicate live server database to dev server, It need not to be real time but at least daily.
Unless the DB is huge, your best bet will be to clone it periodically.

Here's what I'd do if you're on PostgreSQL 9.3, which supports cascading replication:


On the live server, add a streaming replica that keeps an up to date copy on the dev server. You don't use this DB directly, it's just there so you can copy the DB without disrupting the master.
Use pg_basebackup -X stream to take a copy of the replica when you want to refresh the dev db. You can do this while still using the dev db so long as you send the base backup to a separate location.
Stop the dev db, delete its data directory, move the data directory you just copied with pg_basebackup to replace the old dev db, and start the dev db again. This ensures minimal downtime for the dev db.


The downside of this approach is that your dev server needs space for three copies of the master db - the streaming replica, the base backup you're copying, and the current dev db.

You can reduce that to two copies by deleting the dev db before doing the base backup, in exchange for a longer dev outage. Or by stopping dev and the replica, rsync'ing them, then starting them back up, instead of doing a basebackup and directory swap.

You can reduce that to one copy by doing away with the streaming replica and doing the base backup direct from live after deleting the dev db. Or you could stop the dev db, pg_start_backup() the master, rsync master to dev, and pg_stop_backup() on master. However, these approaches impose greater disk I/O on live while the base backup runs.

There are many possible variants; for example, you can stop the streaming replica and copy it at the file system level rather than using pg_basebackup. 

One alternative I'd be tempted to use if my dev db was quite big would be to make a file-system or drive-level snapshot of the streaming replica while it's running, then mount that snapshot copy-on-write on a different directory and start it as the dev DB. That way you only have to actually copy data that changes. This approach is supported by LVM and by AWS EC2 EBS at the volume level, or by zfs and btrfs at the file system level.

Do not attempt to "sync" the master to the replica in real time. Not only will it impose an unreasonable load on the master and possibly cause issues on the master,