search

biocore / american-gut-web

The website for the American Gut Project participant portal
BSD 3-Clause "New" or "Revised" License
5 stars 24 forks source link

Test database population fails for Postgres version 11 #721

Open fedarko opened 5 years ago

fedarko commented 5 years ago

I get an error from pg_restore that schema "public" already exists. In turn, this causes populate_test_db() (in amgut/lib/data_access/env_management.py) to raise an error.

I encountered this error just when using Postgres version 11. Moving down to Postgres version 10.5 (by adjusting my system's PATH and opening a new terminal instance) fixes the error.

Per this Stack Overflow post and Daniel Vérité's answer to it, this sort of problem has been documented as impacting different versions of Postgres (although oddly enough the problematic version here -- 11 -- is later than 9.2, the apparent problematic version from that post). In particular, it looks like the version of the Postgres binaries in use (pg_restore, etc.) is what impacts the error -- I tried versions 11, 10.5, 9.6, and 9.5 of the binaries all with the version 11 Postgres server, and the error only came up when I had my PATH set to use the version 11 binaries.

Here's a transcript of the output/errors I get (the pertinent part starts after Populating the test database) --

/Users/mfedarko/Software/american-gut-web/amgut/lib/config_manager.py:34: UserWarning: Extra main section option(s) found: 'base_log_dir'
  warnings.warn("Extra %s found: %r" % (set_type, extra))
/anaconda3/envs/amgut/lib/python2.7/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)
Creating database
Populating the test database
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8; 2615 2200 SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" already exists
    Command was: CREATE SCHEMA public;

WARNING: errors ignored on restore: 1
Traceback (most recent call last):
  File "scripts/ag", line 110, in <module>
    cli()
  File "/anaconda3/envs/amgut/lib/python2.7/site-packages/click/core.py", line 610, in __call__
    return self.main(*args, **kwargs)
  File "/anaconda3/envs/amgut/lib/python2.7/site-packages/click/core.py", line 590, in main
    rv = self.invoke(ctx)
  File "/anaconda3/envs/amgut/lib/python2.7/site-packages/click/core.py", line 936, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/anaconda3/envs/amgut/lib/python2.7/site-packages/click/core.py", line 782, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/anaconda3/envs/amgut/lib/python2.7/site-packages/click/core.py", line 416, in invoke
    return callback(*args, **kwargs)
  File "scripts/ag", line 65, in make
    populate_test_db()
  File "/Users/mfedarko/Software/american-gut-web/amgut/lib/data_access/env_management.py", line 147, in populate_test_db
    (AMGUT_CONFIG.database, retcode))
RuntimeError: Could not populate test database ag_test: retcode 1

As shown at the top I got a few warnings about my system's installation of psycopg2. However, I'm pretty sure this isn't the cause of the error, since I was able to avoid the problem with non-v11 versions of Postgres.

For reference, this is on a Mac, version 10.13.6.

wasade commented 5 years ago

Thanks for tracking that down! What do you think about noting a version constraint in the readme as a solution right now?

On Wed, Nov 7, 2018, 6:57 PM Marcus Fedarko <notifications@github.com wrote:

I get an error from pg_restore that schema "public" already exists. In turn, this causes populate_test_db() (in amgut/lib/data_access/env_management.py) to raise an error.

I encountered this error just when using Postgres version 11. Moving down to Postgres version 10.5 (by adjusting my system's PATH and opening a new terminal instance) fixes the error.

Per this Stack Overflow post https://dba.stackexchange.com/q/90258 and Daniel Vérité's answer to it, this sort of problem has been documented as impacting different versions of Postgres (although oddly enough the problematic version here -- 11 -- is later than 9.2, the apparent problematic version from that post). In particular, it looks like the version of the Postgres binaries in use (pg_restore, etc.) is what impacts the error -- I tried versions 11, 10.5, 9.6, and 9.5 of the binaries all with the version 11 Postgres server, and the error only came up when I had my PATH set to use the version 11 binaries.

Here's a transcript of the output/errors I get (the pertinent part starts after Populating the test database) --

/Users/mfedarko/Software/american-gut-web/amgut/lib/config_manager.py:34: UserWarning: Extra main section option(s) found: 'base_log_dir' warnings.warn("Extra %s found: %r" % (set_type, extra)) /anaconda3/envs/amgut/lib/python2.7/site-packages/psycopg2/init.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: http://initd.org/psycopg/docs/install.html#binary-install-from-pypi. """) Creating database Populating the test database pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 8; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists Command was: CREATE SCHEMA public;

WARNING: errors ignored on restore: 1 Traceback (most recent call last): File "scripts/ag", line 110, in cli() File "/anaconda3/envs/amgut/lib/python2.7/site-packages/click/core.py", line 610, in call return self.main(args, kwargs) File "/anaconda3/envs/amgut/lib/python2.7/site-packages/click/core.py", line 590, in main rv = self.invoke(ctx) File "/anaconda3/envs/amgut/lib/python2.7/site-packages/click/core.py", line 936, in invoke return _process_result(sub_ctx.command.invoke(sub_ctx)) File "/anaconda3/envs/amgut/lib/python2.7/site-packages/click/core.py", line 782, in invoke return ctx.invoke(self.callback, ctx.params) File "/anaconda3/envs/amgut/lib/python2.7/site-packages/click/core.py", line 416, in invoke return callback(args, **kwargs) File "scripts/ag", line 65, in make populate_test_db() File "/Users/mfedarko/Software/american-gut-web/amgut/lib/data_access/env_management.py", line 147, in populate_test_db (AMGUT_CONFIG.database, retcode)) RuntimeError: Could not populate test database ag_test: retcode 1

As shown at the top I got a few warnings about my system's installation of psycopg2. However, I'm pretty sure this isn't the cause of the error, since I was able to avoid the problem with non-v11 versions of Postgres.

For reference, this is on a Mac, version 10.13.6.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/biocore/american-gut-web/issues/721, or mute the thread https://github.com/notifications/unsubscribe-auth/AAc8sixniBIHZUAY29fSVGxppCutUw5Wks5us52jgaJpZM4YT54i .

fedarko commented 5 years ago

Sure, that'd work! I'm not sure how trivial it'd be to solve this, so just making note of it is a good idea. I'll make a PR.