In lieu of a direct way of cloning one database to another, it was suggested by support that we download a daily backup of our prod database and then manually import that into staging with the following:
Is there a prior step we should take. I didn’t think we’d want to wipe the database (or should need to) entirely before running this, but maybe we do? That would kill database users which presumably would have unintended consequences for our consuming application.
I believe I see what the issue is, and I believe in order to move the dump over, I will be able to do that on your behalf. Let’s continue the conversation in private messaging since I might need to ask you about your database names. I will message you.
I have a similar question about how to use a database backup file to seed an application running in a local environment. When I try to load the file with psql I get this error:
could not open extension control file "/Applications/Postgres.app/Contents/Versions/12/share/postgresql/extension/pg_similarity.control": No such file or directory
I assume this is because the database service has an extension installed that my local postgres service does not. Is there any way to clone data to a local database without installing every extension used in the service?
See similar conversation here: Missing file: pg_similarity.control. If you’re not using the extensions that are referenced in the error messages, it may be safe to ignore the errors.
I was able to get this to work eventually, but in our case (same team as tfwright above) we had to delete a whole lot of stuff to get the import to work:
REVOKE ALL ON TABLE public.geometry_columns FROM postgres;
REVOKE SELECT ON TABLE public.geometry_columns FROM PUBLIC;
GRANT ALL ON TABLE public.geometry_columns TO our_app_s76r_user;
GRANT SELECT ON TABLE public.geometry_columns TO PUBLIC;
In which case I replaced our_app_s76r_user with the postgres username for our local database. Alternatively I imagine you could just delete all the revoke/grant sections, I imagine, but that was more work than a search and replace on the name.
I am not sure if it is specific to Ecto (Elixir’s database wrapper), but in our case we couldn’t just ignore the errors, they actually halt the import. Had to fix all of the above in order for it to succeed.
I wonder if, moving forward, Render might be able to provide an option for an export that doesn’t include all of this stuff, or if there is a way to exclude it via the pg_dump command perhaps because otherwise I think we’re looking at writing a script to strip all of this stuff out. Thoughts?
This is really good feedback @bmfay. I’ve passed it on to our database team and they will take it into consideration as they work on revamping our managed postgres offering. We are striving to provide convenient options for exporting and restoring backups.
That’s a good thought re: pg_dump. Both pg_dump and pg_restore let you specify a particular schema with -n. If you use -n public that appears to cut out most of the extension-specific stuff. Let us know if you end up trying this out. Thanks!
I have been thinking along the same lines. Since I have test database on render. When a merge request is made I need to seed the database in the Preview Environment.
One solution would be to specify in render.yaml the database to clone for the Preview Environment. That means that when the Preview Environment is created and a seedDatabase is specified, render would automatically dump the specified database and restore it into the instance running in the Preview Environment.