Обсуждение: Regarding(Read Replica in RDS)

Поиск
Список
Период
Сортировка

Regarding(Read Replica in RDS)

От
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: 25006

Can anyone let me know how to change the read replica to write so that I can execute this query once?


--
Have a Good day !!!

Regards
Rohit Sachdeva

Re: Regarding(Read Replica in RDS)

От
Ron Johnson
Дата:
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: 25006

Can 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.

Re: Regarding(Read Replica in RDS)

От
ROHIT SACHDEVA
Дата:
Its a requirement as we have taken two read replicas one for main database and another for sharing instance.



On Thu, 7 Mar, 2024, 7:17 pm Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
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: 25006

Can 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.

Re: Regarding(Read Replica in RDS)

От
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


Best regards,

Dan Smith

Re: Regarding(Read Replica in RDS)

От
Ron Johnson
Дата:
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-repl
 Which breaks replication.  You can't "demote".

Re: Regarding(Read Replica in RDS)

От
ROHIT SACHDEVA
Дата:
Any other option.

Bcz if I promote can I rollback again.

Regards
Rohit Sachdeva

On 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-repl
 Which breaks replication.  You can't "demote".

Re: Regarding(Read Replica in RDS)

От
Keith
Дата:
If your goal here is to have an FDW connection to another server ONLY on the replica, or different than the SERVER definition on the primary, I don't believe that is possible based on the error that you are getting. Whether you use a replica as a read-only or not, PG always considers a replica as a potential failover target, so that sort of data cannot be different within the replication cluster.

Once you promote that replica to a primary to allow writes, you can only turn it back into a replica again by re-syncing it with the target primary which would undo any changes you did to it after promotion.

Off the top of my head, I'm not sure if FDW connections created on the primary are actually usable on the replica as well. Would have to test and see. If so, and if you wanted another FDW connection to use on your replica, just create it on the primary and then only actually use it on the replica.

Keith


On Thu, Mar 7, 2024 at 11:25 AM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:
Any other option.

Bcz if I promote can I rollback again.

Regards
Rohit Sachdeva

On 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-repl
 Which breaks replication.  You can't "demote".

Re: Regarding(Read Replica in RDS)

От
Ron Johnson
Дата:
On Thu, Mar 7, 2024 at 11:25 AM ROHIT SACHDEVA <sachdeva.rohit648@gmail.com> wrote:
Any other option.

Create a second fdw server (on the primary?), with the new host name.  That SERVER definition should be replicated to the Secondary, where you can then use it.
 
Bcz if I promote can I rollback again.

When you roll back... the altered server definition will also be rolled back.
 

Regards
Rohit Sachdeva

On 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-repl
 Which breaks replication.  You can't "demote".