PostgreSQL master/slave Streaming replication is working fine .I also changed slave as master using trigger file.
I need some clarification on the below 2 scenarios:
1. once slave become master whatever the tables are created in new master it’s not replicated to old master, how to replicate the same from new master to old master
The tables created in the new database won't be replicated to the old master unless you set up replication from the new master to the old master.
2. How to make old master as slave (which should accept read only transactions)
Can you please help me regarding this and also tell me is the above approach is valid or not.
I have tried 3 nodes(master/slave)—master(bring down master)/make it slave as master/and created new slave and it is working fine.
There is nothing wrong in using the old master as a slave and the new one as master from now on, if the machine on which new master is as good as the one on which the old master was running (memory,processor, disk speed etc). The process to be followed is the same as what you followed when you set up the earlier master-slave environment. It sounds as though you have the new set up working fine.
If you want to automate the process, you could take a look at a tool like pgpool-II. It does add some overhead and you have to learn a new tool. Look under the section Pgpool Failback here
