0

I'm trying to get a backup of my database using https://devcenter.heroku.com/articles/heroku-postgres-backups

But when I run this command heroku pg:backups:capture --app [app_name], I get the following error:

 ▸    An error occurred and the backup did not finish.
 ▸
 ▸    waiting for upload to complete
 ▸    pg_dump: error: query was: LOCK TABLE "topology"."topology" IN ACCESS SHARE MODE
 ▸    upload finished successfully
 ▸    waiting for pg_dump to complete
 ▸    pg_dump finished with errors

When I look at the logs, I get the following:

=== Backup Logs
2020-07-15 20:39:51 +0000 pg_dump: last built-in OID is 16383
2020-07-15 20:39:51 +0000 pg_dump: reading extensions
2020-07-15 20:39:51 +0000 pg_dump: identifying extension members
2020-07-15 20:39:51 +0000 pg_dump: reading schemas
2020-07-15 20:39:51 +0000 pg_dump: reading user-defined tables
2020-07-15 20:39:51 +0000 pg_dump: error: query failed: ERROR:  permission denied for schema topology
2020-07-15 20:39:51 +0000 waiting for upload to complete
2020-07-15 20:39:51 +0000 pg_dump: error: query was: LOCK TABLE "topology"."topology" IN ACCESS SHARE MODE
2020-07-15 20:39:52 +0000 upload finished successfully
2020-07-15 20:39:52 +0000 waiting for pg_dump to complete
2020-07-15 20:39:52 +0000 pg_dump finished with errors

I don't have a table called "topology", which is why I'm confused why I'm having this error. I really would like to get backups using Heroku rather than pg_dump, because then I can simply plug the backup's Database URL into a new Hasura GraphQL engine instance.

Does anyone know why I might be getting this error>

Vaibhav Verma
  • 937
  • 1
  • 8
  • 25

1 Answers1

0

That table must come from the postgis_topology extension.

pg_dump does not dump objects from an extension, but since topology.topology is marked as an extension configuration table, its contents are dumped.

You get the error because the user has no SELECT rights on that table.

Several options you can try:

  •  

    DROP EXTENSION postgis_topology;

    You need to be a superuser for that, but I am sure that Heroku offers a way to do that.

  • Run pg_dump with a user that has permissions to SELECT from the table.

  • Try to exclude the table from the backup; not sure if that will work:

    pg_dump --exclude-table topology.topology dbname
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Heroku doesn't allow access to superuser unfortunately: https://help.heroku.com/E10ZZ6IJ/why-can-t-i-use-third-party-tools-to-replicate-my-heroku-postgres-database-to-a-non-heroku-database How do I create a user with the permission to SELECT from the topology table? – Vaibhav Verma Jul 18 '20 at 05:26
  • If I DROP EXTENSION, will I lose all my postgis_topology data? From https://www.postgresql.org/docs/9.1/sql-dropextension.html, "Dropping an extension causes its component objects to be dropped as well." – Vaibhav Verma Jul 18 '20 at 06:09
  • Probably. You can try to drop the extension without `CASCADE`, then you will get an error message that contains objects that would be removed along with the extension. – Laurenz Albe Aug 10 '20 at 06:20
  • I'm in the exact same boat with PostGIS on Heroku Postgres. Did you ever find a solution? – SeanAye Aug 12 '20 at 02:57