PostgreSQL WAL file database system identifier wrong

linuxpostgresql

I'm having a problem restoring a copy of a PostgreSQL database to another server.

I have taken a backup using the pg_basebackup tool and can restore that but when I restore WAL logs from the archive I get this error

WAL file is from different database system: WAL file database system identifier is 6355383437015733568, pg_control database system identifier is 6366125487137379747

Though the source database reports it's identifier as WAL file is from different database system: WAL file database system identifier is 6355383437015733568, pg_control database system identifier as 6366125487137379747

I am 100% certain the the WAL archive is from the correct source it just looks like the WAL files have the wrong id . any ideas how to resolve ? a pg_xlogreset in the source db made no difference

Version 9.6 on Amazon Linux all fully patched

Thanks

Best Answer

Can it be what archive already filled with WAL from another installation? Postgres documentation specifies that archive_command should not overwrite WAL files in archive (https://www.postgresql.org/docs/9.6/static/continuous-archiving.html):

The archive command should generally be designed to refuse to overwrite any pre-existing archive file. This is an important safety feature to preserve the integrity of your archive in case of administrator error (such as sending the output of two different servers to the same archive directory).

So if archive is set up correctly it will refuse to override WAL and restore will fail.

Related Question