PostgreSQL upgrade to 12 failed due to earth distance extension

While upgrading RDS PostgreSQL from 9.5 to 12 got below error, which is little confusing because it is not listed in the prerequisites documentation.

"/rdsdbbin/postgres/bin/pg_restore" --host /rdsdbdata/log/upgrade --port 8193 --username rdsadmin --create --exit-on-error --verbose --dbname template1 "pg_upgrade_dump_1.custom" >> "pg_upgrade_dump_1.log" 2>&1

*failure*
There were problems executing ""/rdsdbbin/postgres/bin/pg_restore" --host /rdsdbdata/log/upgrade --port 8193 --username rdsadmin --create --exit-on-error --verbose --dbname template1 "pg_upgrade_dump_16395.custom" >> "pg_upgrade_dump_16395.log" 2>&1"
Consult the last few lines of "pg_upgrade_dump_16395.log" for
the probable cause of the failure.
"/rdsdbbin/postgres/bin/pg_ctl" -w -D "/rdsdbdata/db" -o "--config_file=/rdsdbdata/config_new/postgresql.conf" -m fast stop >> "pg_upgrade_server.log" 2>&1
child process exited abnormally: status 256
"/rdsdbbin/postgres/bin/pg_ctl" -w -D "/rdsdbdata/db" -o "--config_file=/rdsdbdata/config_new/postgresql.conf" -m fast stop >> "pg_upgrade_server.log" 2>&1
----------------------- END OF LOG ----------------------

atabase instance is in a state that cannot be upgraded: pg_restore: from TOC entry 4241; 1259 62347 INDEX addresses_earthdistance_ix f0187642fc72b3a pg_restore: error: could not execute query: ERROR: type "earth" does not exist LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth ^ QUERY: SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth CONTEXT: SQL function "ll_to_earth" during inlining Command was: -- For binary upgrade, must preserve pg_class oids SELECT pg_catalog.binary_upgrade_set_next_index_pg_class_oid('62347'::pg_catalog.oid); CREATE INDEX "addresses_earthdistance_ix" ON "publicw9l8ylklm8dupgr4zedbfssfgjmnfzwf"."addresses" USING "gist" ("publicw9l8ylklm8dupgr4zedbfssfgjmnfzwf"."ll_to_earth"("latitude", "longitude"));

after digging more found that there is a bug which is causing this issue. So we dropped the index “addresses_earthdistance_ix” and recreated it after upgradation.

PostgreSQL: Re: BUG #15112: Unable to run pg_upgrade with earthdistance extension

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s