Search code examples
postgresqlindexingdatabase-backups

PostgreSQL index usage, backing up pg_catalog data

I'm currently reviewing the usage of indexes in a complex PostgreSQL database. One of the queries that look useful is this

SELECT idstat.schemaname AS schema_name, idstat.relname AS table_name,
indexrelname AS index_name,
idstat.idx_scan AS times_used,
pg_size_pretty(pg_relation_size(idstat.relid)) AS table_size, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
n_tup_upd + n_tup_ins + n_tup_del as num_writes,
indexdef AS definition
FROM pg_stat_user_indexes AS idstat JOIN pg_indexes ON (indexrelname = indexname AND idstat.schemaname = pg_indexes.schemaname)
JOIN pg_stat_user_tables AS tabstat ON idstat.relid = tabstat.relid
WHERE idstat.idx_scan  < 200
    AND indexdef !~* 'unique'
ORDER BY idstat.relname, indexrelname;

It tells me how often the index was used, how much space it uses etc.

However:

I get the database backup from the client site. When I restore the database, the query returns zeros for times_used. I suspect that all indexes are rebuild on restore.

Finally, question:

What is the easiest way to capture (backup) the data from pg_catalog so that actual client data on index use can be restored and analyzed?

Solution

  • Provide an SQL script that the client can run with psql -f report_on_live_db.sql live_db_name . Have it SELECT the desired data.

    Alternately, you might be able to get a raw file-system level copy of the client database from the client and then fire it up on your local machine. This will only work if you can run PostgreSQL on the same operating system and architecture; you can't use a 64-bit PostgreSQL to access a 32-bit database, nor can you use a Linux PostgreSQL to access a Windows or BSD database. The same PostgreSQL major version must be used; you can't use 9.1 to access a 9.0 database or vice versa.