alter database in a read-replica

[wilk]

Hi,

I found this strange error in the log of a read-replica database (PostgreSQL 13). It's trying to alter a database and of course it's not possible in a read-replica.

It's append regulary one time each hour even on a new replica. I use this replica only for backup, without regular connections.

db=cloudsqladmin,user=cloudsqladmin ERROR: cannot execute ALTER DATABASE in a read-only transaction

Any idea ?

[ms4446]

For Cloud SQL PostgreSQL read-only replicas write operations like ALTER DATABASE are not allowed because the database is set to be read-only to maintain data integrity and synchronization with the primary database.

The ALTER DATABASE command is likely part of a script or application that is running against your database. It could be part of a maintenance job, backup job, or some other automated process.

Since you mentioned that this error occurs regularly every hour, it's likely that there's a scheduled task or cron job that's trying to perform this operation.

Here are a few steps you can take to troubleshoot this issue:

1. Check Scheduled Tasks or Cron Jobs: Look for any scheduled tasks or cron jobs that might be running against your database. This could be on the database server itself or on another server that has access to your database.

2. Review Application Logs: If you have an application that interacts with your database, check its logs to see if it's trying to perform the ALTER DATABASE operation.

3. Audit Database Logs: Review your database logs to see if you can find more information about what is triggering the ALTER DATABASE command. The logs might contain information about the source of the command.

4. Check Backup Scripts: Since you mentioned that you use this replica for backup, check your backup scripts or software. Some backup solutions might try to perform an ALTER DATABASE operation as part of the backup process.

[wilk]

I just created a new read-replica (the precedent has the issue just few days ago even if in use since months) and never interacted with it. In fact we can see in the log that it's not my user db=cloudsqladmin,user=cloudsqladmin.

The statement just before is db=cloudsqladmin,user=cloudsqladmin STATEMENT: ALTER DATABASE template0 ALLOW_CONNECTIONS TRUE CONNECTION LIMIT 0

When i said a backup, I mean a disaster recovery read replica in an other region, I don't make a dump from this,

Do you think I could have a script accessing the source database that could generate this statement in the replica ?

[ms4446]

Upon further research and belief this error message might be related to a recent bug relating to system maintenance attempting to patch read only replicas. The issue has been addressed. Either case this message shouldn't affect the operation of your read replica for its intended purpose, which is to handle read queries and offload some of the traffic from the primary instance. 

[wilk]

Thanks, it's resolved now.

[Rutger-Verdify]

Hi all, we're still seeing this problem occur every 15 minutes. Although it might not cause any malfunctions, it is triggering a monitoring notification on our dashboard every time - which is kind of annoying. Will the problem be automatically resolved?

[wilk]

Unfortunately it's triggering a notification on my side also again...

[ananda123]

Hi all,

we're also seeing this problem which occur every 15 minutes. Although it might not cause any malfunctions, it is triggering a monitoring notification on our dashboard every time. Will the problem be automatically resolved?

STATEMENT: "ALTER DATABASE template0 ALLOW_CONNECTIONS TRUE CONNECTION LIMIT 0",

error log: db=cloudsqladmin,user=cloudsqladmin ERROR: cannot execute ALTER DATABASE in a read-only transaction"

[mikearnold_msc]

We are also seeing this error in our read-replica of our CloudSQL Postgres instance.  When will this issue be resolved?

[ms4446]

The fix is planned to be rolled out next week. We appreciate everyone's patience. 

[ananda123]

Hi ms4446, 

I coudn't see the  above error in prod from 1:45 pm EST but still flooding in QA. Did the fix applied for this, 

Regards,

Ananda T 

[ms4446 · Accepted solution]

As mentioned, fix is being rolled out this week.