Unable to delete postgresql instance (read replica)

[kvg]

Hi Team,

I am unable to delete the postgresql instance as the read replica is always in "under maintenance" state. Please help with deleting the read-replica and the instance. Thank you!

[usman317]

I think you first need to promote your database. Open the database and promote it so that it's in a constant state and not reading data from anywhere 

[kvg]

Thanks for checking. I am unable to promote the read-replica as it is always stuck in "under-maintenance" state. I can not perform any actions on it. Let me know if I understood that correct or is there something else I can do?

[usman317]

It I think some other process is going on which this is dependent. I cannot tell it from here if you need me to check it mail me muhammadusman319320@gmail.com, I will try to set it up for you 

[kumards]

Hi @kvg 

If this problem still exists, can you try the following? Configure a "deny maintenance" period for the primary instance that the replica is for. I don't know whether or not this action will "force" the replica out of the maintenance mode. But it might be worth a try. Please do post what you find out.

[kvg]

Good news: The status of the replica finally changed to "replication failed" and I was able to delete the replica along with the instance.

Not so good news: I don't know what fixed the problem.

couple of things I did are: It was a private instance, initially I followed this link adding https://cloud.google.com/sql/docs/mysql/configure-private-ip#existing-private-instance
Later I added public IP to the instance. There were a bunch of backups, I deleted them to save some money. 

Morning when I logged in, I was able to delete the replica. I think none of my actions did anything but the hunch I have is that, when a replica creation fails, Google retries at regular intervals (once a day I guess?) and gives up after 10 days. This  instance was created on 4th Nov 2022. It was in "Under Maintenance" for the last 10 days and today morning it was in "replication failed" state where I was able to delete it (or make any edits to it). Can anyone confirm that my assumption true?

[kumards]

Great to know that your problem was resolved, @kvg !

You mentioned that you deleted backups. Did you by any chance delete all the backups or disable automatic backups. I'm enquiring about this because, per the docs, enabling automatic backups & having at least one backup are prereqs for replicas.

[yatabe]

how long it take? I have same problem. My Cloud SQL replica is under maintenance for 2 hours.

[kvg]

Thanks for the pointer @kumards, that might be it then! 
I have disabled the automatic backups and deleted all the backups available. There were around 23 if I remember correctly. I remember deleting each one of them.

While we play around more with CloudSQL this week, I will see if we can reproduce this. Thank you @kumards @usman317  for your time and help.

[kumards]

You're welcome, @kvg !  Btw, in my earlier message I asked whether you had disabled auto backups. Please scratch that part... I did some testing, and learned that auto backups can't be turned off when replicas exist.

As to replication failing because all the backups were deleted, there's some room to improve the backup-deletion feature IMO -- i.e., prevent deletion of all backups when replicas exist. I suggest creating a feature request for this.