CODEWITHSUNDEEP
databasepostgresqlpostgresql-9.4autovacuum
James
James

Reputation: 18379

Postgres autovacuum high cpu/disk after database import

I have migrated a 2tb Postgres 9.4 database from one server to another, and after the import the autovacuum is going crazy with high cpu and disk for several days. The database has over 40,000 schemas/tables.

I have tried shutting down the database and restarting, but still high usage. I have had issues with vacuum after imports before, it seems to be a common Postgres issue, is there any way to resolve it? The database that was exported was fine, no vacuum issues, it seems to be caused by the import.

I tried:

vacuumdb --all --full -w

But if failed to finish because of a statement timeout. Other than disabling autovacuum, is there any solution?

g

Upvotes: 2

Views: 3476

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45910

Database have to re create statistics and visibility map after import. So it is expected behaviour. If you don't run VACUUM manually or autovacuum was disabled, then Postgres will not work effectively.

Export is a read operation, there is not need to do VACUUM. If you import 2TB data, then it is expected high CPU and IO until VACUUM is done. You can run VACUUM manually (with higher speed), but it should not fail because of timeouts (you should to increase timeouts).

Upvotes: 4

Related Questions