There is 1 other session using the database error

[orlando]

I'm getting this error when trying to snapshot a postgres database

/usr/local/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>.
  """)

Traceback (most recent call last):
  File "/usr/local/bin/stellar", line 11, in <module>
    sys.exit(main())
  File "/usr/local/lib/python2.7/site-packages/stellar/command.py", line 279, in main
    stellar()
  File "/usr/local/lib/python2.7/site-packages/click/core.py", line 722, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/click/core.py", line 697, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python2.7/site-packages/click/core.py", line 1066, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python2.7/site-packages/click/core.py", line 895, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python2.7/site-packages/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/stellar/command.py", line 60, in snapshot
    app = get_app()
  File "/usr/local/lib/python2.7/site-packages/stellar/command.py", line 28, in get_app
    app = Stellar()
  File "/usr/local/lib/python2.7/site-packages/stellar/app.py", line 45, in __init__
    self.init_database()
  File "/usr/local/lib/python2.7/site-packages/stellar/app.py", line 64, in init_database
    tables_missing = self.create_stellar_database()
  File "/usr/local/lib/python2.7/site-packages/stellar/app.py", line 72, in create_stellar_database
    self.operations.create_database('stellar_data')
  File "/usr/local/lib/python2.7/site-packages/stellar/operations.py", line 57, in create_database
    get_engine_url(raw_conn, database)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy_utils/functions/database.py", line 554, in create_database
    result_proxy = engine.execute(text)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2075, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 942, in execute
    return self._execute_text(object, multiparams, params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1104, in _execute_text
    statement, parameters
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) source database "template1" is being accessed by other users

DETAIL:  There is 1 other session using the database.
 [SQL: "CREATE DATABASE stellar_data ENCODING 'utf8' TEMPLATE template1"] (Background on this error at: http://sqlalche.me/e/e3q8)

The source database is not being used when doing the backup.

I'm running Mac OS 10.13.4, probably with the default python version (2.7.14)

[kaedroho]

I'm getting this too. It appears the issue is caused by an update to SQLAlchemy.

You can work around this by running: pip install SQLAlchemy==1.2.5 SQLAlchemy-Utils==0.33.1

[jalada]

Hmm, I still get this error even after installing the versions of SQLAlchemy @kaedroho suggests.

[quantus]

Thanks for reporting the issue.

I quickly reinstalled stellar on my Mac but failed to reproduce this issue. The error message says that there is some other session going on that database. The other session can come from stellar itself(=same process), or from some other process(=stuck? stellar background process). Can you try to check if there was some sql process going before running the stellar command? So run psql -c "select * from pg_stat_activity where datname='stellar_data'". If that doesn't reveal anything then the stellar itself is likely the cause.

Can you also just paste pip freeze so that I can install the exactly same dependencies incase those are somehow related to this issue?

[changchichung]

I have the same problem . here is the log

psql -c "select * from pg_stat_activity where datname='stellar_data'"

datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type 
-------+---------+-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+------------+-------------+--------------+-----------------+------------+-------+-------------+--------------+-------+--------------
(0 rows)

stellar snapshot

es@dc075:~$ stellar snapshot
Traceback (most recent call last):
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
context)
File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 507, in do_execute
cursor.execute(statement, parameters)
psycopg2.OperationalError: source database "template1" is being accessed by other users
DETAIL:  There is 1 other session using the database.

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "/usr/local/bin/stellar", line 11, in load_entry_point('stellar==0.4.5', 'console_scripts', 'stellar')() File "/usr/local/lib/python3.6/dist-packages/stellar/command.py", line 279, in main stellar() File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 722, in call return self.main(args, kwargs) File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 697, in main rv = self.invoke(ctx) File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 1066, in invoke return _process_result(sub_ctx.command.invoke(sub_ctx)) File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 895, in invoke return ctx.invoke(self.callback, ctx.params) File "/usr/local/lib/python3.6/dist-packages/click/core.py", line 535, in invoke return callback(args, *kwargs) File "/usr/local/lib/python3.6/dist-packages/stellar/command.py", line 60, in snapshot app = get_app() File "/usr/local/lib/python3.6/dist-packages/stellar/command.py", line 28, in get_app app = Stellar() File "/usr/local/lib/python3.6/dist-packages/stellar/app.py", line 45, in init self.init_database() File "/usr/local/lib/python3.6/dist-packages/stellar/app.py", line 64, in init_database tables_missing = self.create_stellar_database() File "/usr/local/lib/python3.6/dist-packages/stellar/app.py", line 72, in create_stellar_database self.operations.create_database('stellar_data') File "/usr/local/lib/python3.6/dist-packages/stellar/operations.py", line 57, in create_database get_engine_url(raw_conn, database) File "/usr/local/lib/python3.6/dist-packages/sqlalchemy_utils/functions/database.py", line 554, in create_database result_proxy = engine.execute(text) File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 2075, in execute return connection.execute(statement, multiparams, **params) File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 942, in execute return self._execute_text(object, multiparams, params) File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1104, in _execute_text statement, parameters File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context context) File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception exc_info File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/util/compat.py", line 186, in reraise raise value.with_traceback(tb) File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context context) File "/usr/local/lib/python3.6/dist-packages/sqlalchemy/engine/default.py", line 507, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) source database "template1" is being accessed by other users DETAIL: There is 1 other session using the database. [SQL: "CREATE DATABASE stellar_data ENCODING 'utf8' TEMPLATE template1"] (Background on this error at: http://sqlalche.me/e/e3q8)

> pip freeze

pip3 freeze acme==0.22.2 apturl==0.5.2 asn1crypto==0.24.0 Brlapi==0.6.6 certbot==0.23.0 certbot-nginx==0.23.0 certifi==2018.1.18 chardet==3.0.4 click==6.7 command-not-found==0.3 ConfigArgParse==0.11.0 configobj==5.0.6 cryptography==2.1.4 cupshelpers==1.0 defer==1.0.6 distro-info==0.18 ewmh==0.1.5 future==0.15.2 httplib2==0.9.2 humanize==0.5.1 idna==2.6 josepy==1.1.0 keyring==10.6.0 keyrings.alt==3.0 language-selector==0.1 launchpadlib==1.10.6 lazr.restfulclient==0.13.5 lazr.uri==1.0.3 louis==3.5.0 macaroonbakery==1.1.3 Mako==1.0.7 MarkupSafe==1.0 mock==2.0.0 notify2==0.3 oauth==1.0.1 olefile==0.45.1 parsedatetime==2.4 pbr==3.1.1 pexpect==4.2.1 Pillow==5.1.0 protobuf==3.0.0 psutil==5.4.2 psycopg2==2.7.4 psycopg2-binary==2.7.4 pycairo==1.16.2 pycrypto==2.6.1 pycups==1.9.73 pygobject==3.26.1 PyICU==1.9.8 pymacaroons==0.13.0 PyNaCl==1.1.2 pyOpenSSL==17.5.0 pyparsing==2.2.0 pyRFC3339==1.0 python-apt==1.6.0 python-debian==0.1.32 python-xapp==1.0.0 python-xlib==0.20 pytz==2018.3 pyudev==0.21.0 pyxattr==0.6.0 pyxdg==0.25 PyYAML==3.12 reportlab==3.4.0 requests==2.18.4 requests-unixsocket==0.1.5 schema==0.6.7 SecretStorage==2.3.1 setproctitle==1.1.10 simplejson==3.13.2 six==1.11.0 SQLAlchemy==1.2.6 SQLAlchemy-Utils==0.33.2 ssh-import-id==5.7 stellar==0.4.5 system-service==0.3 systemd-python==234 ubuntu-drivers-common==0.0.0 ufw==0.35 unattended-upgrades==0.1 urllib3==1.22 usb-creator==0.3.3 virtualenv==15.1.0 wadllib==1.3.2 xkit==0.0.0 youtube-dl==2018.3.14 zope.component==4.3.0 zope.event==4.2.0 zope.hookable==4.0.4 zope.interface==4.3.2

[kaedroho]

@jalada Sorry, I posted the wrong version. I've updated my original comment.

SQLAlchemy 1.2.6 and above have this issue. It can be worked around for now by downgrading to 1.2.5 (pip install SQLAlchemy==1.2.5 SQLAlchemy-Utils==0.33.1).

[changchichung]

yes ,pip install SQLAlchemy==1.2.5 SQLAlchemy-Utils==0.33.1 works! , @kaedroho thanks

[jamesstonehill]

I'm also getting this issue, but @kaedroho's fix worked!

[jalada]

Snap, thanks @kaedroho!

[quantus]

I tried to install the mentioned version of SQLAlchemy but I can't still reproduce the issue for some reason.

~/Documents/stellar-test pip freeze|grep SQLA
SQLAlchemy==1.2.7
SQLAlchemy-Utils==0.33.3
~/Documents/stellar-test stellar snapshot asd   
Snapshotting database foobar
~/Documents/stellar-test stellar list
asd: 15 seconds ago
~/Documents/stellar-test 

[madis]

Well I can reproduce it very vividly:

❯ stellar list
/usr/local/lib/python3.6/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>.
  """)
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
psycopg2.OperationalError: source database "template1" is being accessed by other users
DETAIL:  There is 1 other session using the database.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/bin/stellar", line 11, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.6/site-packages/stellar/command.py", line 279, in main
    stellar()
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 722, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 697, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 1066, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 895, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/stellar/command.py", line 76, in list
    snapshots = get_app().get_snapshots()
  File "/usr/local/lib/python3.6/site-packages/stellar/command.py", line 28, in get_app
    app = Stellar()
  File "/usr/local/lib/python3.6/site-packages/stellar/app.py", line 45, in __init__
    self.init_database()
  File "/usr/local/lib/python3.6/site-packages/stellar/app.py", line 64, in init_database
    tables_missing = self.create_stellar_database()
  File "/usr/local/lib/python3.6/site-packages/stellar/app.py", line 72, in create_stellar_database
    self.operations.create_database('stellar_data')
  File "/usr/local/lib/python3.6/site-packages/stellar/operations.py", line 57, in create_database
    get_engine_url(raw_conn, database)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy_utils/functions/database.py", line 554, in create_database
    result_proxy = engine.execute(text)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2075, in execute
    return connection.execute(statement, *multiparams, **params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 942, in execute
    return self._execute_text(object, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1104, in _execute_text
    statement, parameters
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) source database "template1" is being accessed by other users
DETAIL:  There is 1 other session using the database.
 [SQL: "CREATE DATABASE stellar_data ENCODING 'utf8' TEMPLATE template1"] (Background on this error at: http://sqlalche.me/e/e3q8)

❯ pip install SQLAlchemy==1.2.5 SQLAlchemy-Utils==0.33.1
Collecting SQLAlchemy==1.2.5
  Downloading https://files.pythonhosted.org/packages/7e/57/435347429f4ff5016b72c9b179c70a6cc67e5f9b4d3f3e9b51362c40b356/SQLAlchemy-1.2.5.tar.gz (5.6MB)
    100% |████████████████████████████████| 5.6MB 6.9MB/s
Collecting SQLAlchemy-Utils==0.33.1
  Downloading https://files.pythonhosted.org/packages/fc/3d/0e7146d6c017e2aa16f2530d31d73c9de715ab823c86a988776b251374ca/SQLAlchemy-Utils-0.33.1.tar.gz (124kB)
    100% |████████████████████████████████| 133kB 6.6MB/s
Requirement already satisfied: six in /usr/local/lib/python3.6/site-packages (from SQLAlchemy-Utils==0.33.1) (1.11.0)
Building wheels for collected packages: SQLAlchemy, SQLAlchemy-Utils
  Running setup.py bdist_wheel for SQLAlchemy ... done
  Stored in directory: /Users/madis/Library/Caches/pip/wheels/7f/47/19/9d21adcd1f537063d81e00b933df6c58c9cd4d278bfb500bb5
  Running setup.py bdist_wheel for SQLAlchemy-Utils ... done
  Stored in directory: /Users/madis/Library/Caches/pip/wheels/10/b3/40/f1295dc376cb87cdbda3b44b20a50a2b8b622bafd8396c7039
Successfully built SQLAlchemy SQLAlchemy-Utils
Installing collected packages: SQLAlchemy, SQLAlchemy-Utils
  Found existing installation: SQLAlchemy 1.2.7
    Uninstalling SQLAlchemy-1.2.7:
      Successfully uninstalled SQLAlchemy-1.2.7
  Found existing installation: SQLAlchemy-Utils 0.33.3
    Uninstalling SQLAlchemy-Utils-0.33.3:
      Successfully uninstalled SQLAlchemy-Utils-0.33.3
Successfully installed SQLAlchemy-1.2.5 SQLAlchemy-Utils-0.33.1

❯ stellar list
/usr/local/lib/python3.6/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>.
  """)

❯ python --version
Python 3.6.4

❯ pip --version
pip 10.0.1 from /usr/local/lib/python3.6/site-packages/pip (python 3.6)

[frjo]

The difference may be the Postgresql version.

I had SQLAlchemy1.2.9 and SQLAlchemy-Utils 0.33.3 and they worked without issue using Postgresql 9.6.

After I upgraded to Postgresql 10.5 I got the same "source database "template1" is being accessed by other users" error as above. (via Homebrew on macOS 10.13.6 using Stellar 0.4.5)

After finding this issue I tested do downgraded to SQLAlchemy 1.2.5 and SQLAlchemy-Utils 0.33.1 as suggested and I got a working stellar again.

[shaicoleman]

Also happens to me with stellar 0.4.5/Python 3.6.7/Ubuntu 18.04/pip 18.1/PostgreSQL 10.6 Downgrading to SQLAlchemy==1.2.5 and SQLAlchemy-Utils==0.33.1 fixed it

[Enceradeira]

Same here.... stellar 0.4.5/Python 3.6.7/pip3 9.0.1/psql 10.8 Downgrading as mentioned above fixed it too.

[nick4u]

This is workaround for me: connect to postgres db, ie. : su - postgres then connect to db: psql and execute CREATE DATABASE stellar_data ENCODING 'utf8' TEMPLATE template1 owner **$USER**;

where $USER is your DB user name stellar is working with (needs corresponding privileges obviously) Stellar will create missing tables at first snapshot and it work's for me with SQLAlchemy ==1.3.6

[simon-ondrej]

@nick4u, thank you, that seems to be the most correct solution.