hughcapet
commented
1 year ago This happens because the output parameters for the native pg_stat_statements function have changed between the pg versions. To prevent this error Spilo actually deletes metric_helpers.pg_stat_statements() and this view before running upgrade. So to mitigate the problem you can do the same and re-create them after the restore. You will maybe need to delete even more incompatible objects, btw
Hi,
We got the following error when attempting to restore a backup taken on Postgres 13 and restored on Postgres 15. If we remove the "pg_stat_statements(userid, dbid, queryid, query, plans, total_plan_time, min_plan_time, max_plan_time, mean_plan_time, stddev_plan_time, calls, total_exec_time, min_exec_time, max_exec_time, mean_exec_time, stddev_exec_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time, wal_records, wal_fpi, wal_bytes);" part from the FROM clause it will work, but this part is generated by Postgres backup and we have no control over it.
What is the best way to overcome this issue?
Thanks
pg_restore: from TOC entry 451; 1259 31983 VIEW pg_stat_statements postgres pg_restore: error: could not execute query: ERROR: column reference "wal_records" is ambiguous LINE 31: pg_stat_statements.wal_records, ^ Command was: CREATE VIEW metric_helpers.pg_stat_statements AS SELECT pg_stat_statements.userid, pg_stat_statements.dbid, pg_stat_statements.queryid, pg_stat_statements.query, pg_stat_statements.plans, pg_stat_statements.total_plan_time, pg_stat_statements.min_plan_time, pg_stat_statements.max_plan_time, pg_stat_statements.mean_plan_time, pg_stat_statements.stddev_plan_time, pg_stat_statements.calls, pg_stat_statements.total_exec_time, pg_stat_statements.min_exec_time, pg_stat_statements.max_exec_time, pg_stat_statements.mean_exec_time, pg_stat_statements.stddev_exec_time, pg_stat_statements.rows, pg_stat_statements.shared_blks_hit, pg_stat_statements.shared_blks_read, pg_stat_statements.shared_blks_dirtied, pg_stat_statements.shared_blks_written, pg_stat_statements.local_blks_hit, pg_stat_statements.local_blks_read, pg_stat_statements.local_blks_dirtied, pg_stat_statements.local_blks_written, pg_stat_statements.temp_blks_read, pg_stat_statements.temp_blks_written, pg_stat_statements.blk_read_time, pg_stat_statements.blk_write_time, pg_stat_statements.wal_records, pg_stat_statements.wal_fpi, pg_stat_statements.wal_bytes FROM metric_helpers.pg_stat_statements(true) pg_stat_statements(userid, dbid, queryid, query, plans, total_plan_time, min_plan_time, max_plan_time, mean_plan_time, stddev_plan_time, calls, total_exec_time, min_exec_time, max_exec_time, mean_exec_time, stddev_exec_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time, blk_write_time, wal_records, wal_fpi, wal_bytes);
pg_restore: error: could not execute query: ERROR: relation "metric_helpers.pg_stat_statements" does not exist Command was: ALTER TABLE metric_helpers.pg_stat_statements OWNER TO postgres;