Verify server version for a restore in postgresql_db #866
Description
This is a draft for collecting ideas and information.
SUMMARY
In PostgreSQL it is advised that a restore in a newer version only works reliably if the backup was taken with a pg_dump of the newer version.
Example: Backup a PG 14 database and restore in a PG 16, for doing this use the pg_dump tool from PG 16 to backup the PG 14 database. If the backup was taken with the pg_dump from PG 14 and then restored into PG 16 this might not work.
ISSUE TYPE
- Idea
- Improvement
COMPONENT NAME
community.postgresql.postgresql_db
ADDITIONAL INFORMATION
For doing backups we don't really know what will be the new target database. Can be the same server, can be a different one. However during restore we can identify potential problems ahead of the restore. This is the header of a plain text backup file:
--
-- PostgreSQL database dump
--
-- Dumped from database version 17.4
-- Dumped by pg_dump version 17.4
If this was dumped by pg_dump from PG 17 and is now restored into a PG 18, this might lead to unexpected errors down the road. Syntax errors, deprecated and removed features, ... What exactly is going to happen is not necessarily known at the time the restore starts.
The most basic idea is a warning if such a situation is happening. Test the server version before restore, and extract the version from the backup.
Another idea is to use the force parameter in combination with state=restore. Reject the restore unless this parameter is set.