WormBase / caltech-curation-services

Modular curation services for Caltech WormBase data
1 stars 0 forks source link

Errors while restoring postgres dump #4

Closed valearna closed 2 years ago

valearna commented 2 years ago

Restored testdb.dump.latest with psql using the following command:

psql -h cervino.caltech.edu -p 5432 -d caltech_curation -U postgres -f testdb.dump.latest

The command returns some permission errors:

psql:testdb.dump.latest:32212248: ERROR: role "acedb" does not exist psql:testdb.dump.latest:32212249: ERROR: role "apache" does not exist psql:testdb.dump.latest:32212250: ERROR: role "azurebrd" does not exist psql:testdb.dump.latest:32212251: ERROR: role "cecilia" does not exist psql:testdb.dump.latest:32212252: ERROR: role "www-data" does not exist REVOKE REVOKE GRANT psql:testdb.dump.latest:32212262: ERROR: role "acedb" does not exist psql:testdb.dump.latest:32212263: ERROR: role "apache" does not exist psql:testdb.dump.latest:32212264: ERROR: role "azurebrd" does not exist psql:testdb.dump.latest:32212265: ERROR: role "cecilia" does not exist psql:testdb.dump.latest:32212266: ERROR: role "www-data" does not exist

@azurebrd Is there a way to dump roles together with data? Can you try to backup tazendra with pg_dumpall?

From pg_dumpall doc (https://www.postgresql.org/docs/9.1/app-pg-dumpall.html):

"pg_dumpall also dumps global objects that are common to all databases. (pg_dump does not save these objects.) This currently includes information about database users and groups, tablespaces, and properties such as access permissions that apply to databases as a whole."

azurebrd commented 2 years ago

Does this work ? http://tazendra.caltech.edu/~postgres/out/pg_roles.pg Created with pg_dumpall --roles-only > pg_roles.pg To only generate the roles separate from the database data (There's another database that shouldn't be dumped/copied with pg_dumpall, and this lets the daily dump keep using the same pg_dump so the link to testdb.dump.latest can keep getting auto-generated the same way). Probably load that first into any new postgres instance, then the database can be dropped-reloaded from the testdb.dump.latest (and we get to keep the new database name instead of restoring as testdb)

azurebrd commented 2 years ago

Oops, meant to @valearna you.

valearna commented 2 years ago

It worked, thanks @azurebrd! Now we have a copy of tazendra's postgres db on cervino. The new database name is 'caltech_curation' instead of testdb

azurebrd commented 2 years ago

@valearna awesome !