Обсуждение: Regarding(Read Replica in RDS)
I have a setup in which there is one main database and one sharded database.
Seeing the requirement regarding MIS, we plan to have a read replica of this setup, so we launch two read replicas, one for the main database and another for the sharded database.
Now that I want to change the hostname in Postgres fdw in a read replica, I am getting this error:.
ALTER SERVER shard_stg OPTIONS (SET host 'hostanme', SET port '5432');
ERROR: cannot execute ALTER SERVER in a read-only transaction
SQL state: 25006
Can anyone let me know how to change the read replica to write so that I can execute this query once?
Regards
Rohit Sachdeva
Hi Team,I have a setup in which there is one main database and one sharded database.
Seeing the requirement regarding MIS, we plan to have a read replica of this setup, so we launch two read replicas, one for the main database and another for the sharded database.
Now that I want to change the hostname in Postgres fdw in a read replica,
I am getting this error:.
ALTER SERVER shard_stg OPTIONS (SET host 'hostanme', SET port '5432');ERROR: cannot execute ALTER SERVER in a read-only transaction
SQL state: 25006Can anyone let me know how to change the read replica to write so that I can execute this query once?
On Thu, Mar 7, 2024 at 7:43 AM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:Hi Team,I have a setup in which there is one main database and one sharded database.
Seeing the requirement regarding MIS, we plan to have a read replica of this setup, so we launch two read replicas, one for the main database and another for the sharded database.
Now that I want to change the hostname in Postgres fdw in a read replica,But keep the Primary definition of shard_stg the same as it was?I am getting this error:.
ALTER SERVER shard_stg OPTIONS (SET host 'hostanme', SET port '5432');ERROR: cannot execute ALTER SERVER in a read-only transaction
SQL state: 25006Can anyone let me know how to change the read replica to write so that I can execute this query once?
Changing a Read Replica to writable mode means that it's not a Read Replica anymore.
aws rds promote-read-replica \ --db-instance-identifier test-instance-repl
Dan Smith
You can promote a Read Replica via AWS CLI or AWS Web Console.Reference:aws rds promote-read-replica \ --db-instance-identifier test-instance-repl
On Thu, Mar 7, 2024 at 9:26 AM Dan Smith <j.daniel.smith1@gmail.com> wrote:You can promote a Read Replica via AWS CLI or AWS Web Console.Reference:aws rds promote-read-replica \ --db-instance-identifier test-instance-replWhich breaks replication. You can't "demote".
Any other option.Bcz if I promote can I rollback again.RegardsRohit SachdevaOn Thu, 7 Mar, 2024, 8:00 pm Ron Johnson, <ronljohnsonjr@gmail.com> wrote:On Thu, Mar 7, 2024 at 9:26 AM Dan Smith <j.daniel.smith1@gmail.com> wrote:You can promote a Read Replica via AWS CLI or AWS Web Console.Reference:aws rds promote-read-replica \ --db-instance-identifier test-instance-replWhich breaks replication. You can't "demote".
Any other option.
Bcz if I promote can I rollback again.
RegardsRohit SachdevaOn Thu, 7 Mar, 2024, 8:00 pm Ron Johnson, <ronljohnsonjr@gmail.com> wrote:On Thu, Mar 7, 2024 at 9:26 AM Dan Smith <j.daniel.smith1@gmail.com> wrote:You can promote a Read Replica via AWS CLI or AWS Web Console.Reference:aws rds promote-read-replica \ --db-instance-identifier test-instance-replWhich breaks replication. You can't "demote".