I am using CloudSQL - postgresql12 version of GCP.

We are currently entering billions of data, and we do not want the input to be interrupted in the middle.

However, I set the autovacuum setting to off in the CloudSQL flag, but it seems to be done automatically about every day.

As autovacuum proceeds, input using python sqlalchemy causes an error.

sqlalchemy.exc.OperationalError: (psycopg2.errors.ProgramLimitExceeded) multixact "members" limit exceeded
DETAIL: This command would create a multixact with 2 members, but the remaining space is only enough for 1 member.
HINT: Execute a database-wide VACUUM in database with OID 16427 with reduced vacuum_multixact_freeze_min_age and vacuum_multixact_freeze_table_age settings.

What is the solution?

CodePudding user response：

The solution is to follow the hint. This is necessary to keep your database from suffering data corruption. The underlying reason is that a multixact contains transaction numbers, and if those wrap around (after around 2 billion transactions), some table rows might suddenly appear to be locked.

As the documentation says:

As a safety device, an aggressive vacuum scan will occur for any table whose multixact-age (see Section 25.1.5.1) is greater than autovacuum_multixact_freeze_max_age. Also, if the storage occupied by multixacts members exceeds 2GB, aggressive vacuum scans will occur more often for all tables, starting with those that have the oldest multixact-age. Both of these kinds of aggressive scans will occur even if autovacuum is nominally disabled.

Obviously those autovacuum runs that the database desperately launches again and again to prevent the worst from happening did not succeed. You should look into the log file to find out why.

Take the down time, save your database. Then figure out why anti-wraparound autovacuum failed in the first place and fix that problem. And enable autovacuum! If you want, you can temporarily disable it on a single table while you load data into it.