Drop and recreate the database on dump load

[denzelem]

Currently geordi restores a PostgreSQL dump with the following options [1]:

pg_restore --no-owner --clean --no-acl -username=foo --port=foo --host=foo --dbname=foo foo.dump

When the current database schema and the new database schema differs, you might not be able to run your database migrations [1] after the dump is loaded:

• --clean just drops all tables it knows in the dump file
• Having orphaned tables from before the restore will prevent migrations to success (table already exist exceptions)

My current workaround is:

rake db:drop db:create
geordi dump -l staging

We could make this workaround obsolete with the pg_restore flag ---create [2].

Begin the output with a command to create the database itself and reconnect to the created database. (With a script of this form, it doesn't matter which database in the destination installation you connect to before running the script.) If --clean is also specified, the script drops and recreates the target database before reconnecting to it.

With --create, the output also includes the database's comment if any, and any configuration variable settings that are specific to this database, that is, any ALTER DATABASE ... SET ... and ALTER ROLE ... IN DATABASE ... SET ... commands that mention this database. Access privileges for the database itself are also dumped, unless --no-acl is specified.

This option is ignored when emitting an archive (non-text) output file. For the archive formats, you can specify the option when you call pg_restore.

Wdyt?

[1] https://github.com/makandra/geordi/blob/cab2391c712a15107b3e26573c1676699b9e2a25/lib/geordi/dump_loader.rb#L63-L71 [2] https://www.postgresql.org/docs/current/app-pgdump.html

[brunosedler]

Sounds good, can you prepare a MR for this?

[denzelem]

It seems impossible to use --create with a target database having a different name than the source database.

Another option would be to use dropdb and createdb before the pg_restore command. But you will still get errors like this:

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 6; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;

And you have to verify, if the database exists before you drop it. @brunosedler Any chances this change request can be done by Team A?

[brunosedler]

Alright, thanks for your work so far. We'll look into it.

[brunosedler]

Hi @denzelem just a short notice, it will probably take a bit until we'll get around to work on this issue. Hope it's not an urgent problem for you and you can live with your workaround for a bit longer.

[codener]

Solved by https://github.com/makandra/geordi/pull/213.