Error in Geo on PostgreSQL 10.2 with a "function pg_last_xlog_receive_location() does not exist" and replication slots
Running Geo on PostgreSQL 10.2, we get the following in the Geo Node Status
Could not connect to Geo node - HTTP Status Code: 500 Internal Server Error 500 Internal Server Error
that leads to the following error in the console:
17:46:09 postgresql.1 | 2018-02-19 17:46:09.886 CET [33996] ERROR: function pg_last_xlog_receive_location() does not exist at character 46
17:46:09 postgresql.1 | 2018-02-19 17:46:09.886 CET [33996] HINT: No function matches the given name and argument types. You might need to add explicit type casts.
17:46:09 postgresql.1 | 2018-02-19 17:46:09.886 CET [33996] STATEMENT:
17:46:09 postgresql.1 | SELECT CASE
17:46:09 postgresql.1 | WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
17:46:09 postgresql.1 | THEN 0
17:46:09 postgresql.1 | ELSE
17:46:09 postgresql.1 | EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INTEGER
17:46:09 postgresql.1 | END
17:46:09 postgresql.1 | AS replication_lag
Looks like the function pg_last_xlog_receive_location() no longer exists in 10.2 (or earlier? it's available in 9.6...).
There is also an issue with pg_current_xlog_insert_location() on the primary:
18:00:10 postgresql.1 | 2018-02-19 18:00:10.471 CET [35034] ERROR: function pg_current_xlog_insert_location() does not exist at character 43
18:00:10 postgresql.1 | 2018-02-19 18:00:10.471 CET [35034] HINT: No function matches the given name and argument types. You might need to add explicit type casts.
18:00:10 postgresql.1 | 2018-02-19 18:00:10.471 CET [35034] STATEMENT: SELECT COALESCE(MAX(pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn)), 0)
We need to review all replication slot functions for 10.2...
Edited by Brett Walker