restore fails - cannot drop the currently open database on PostgreSQL 10.1

[abitrolly]

stellar restore which is run inside postgres:10.1 docker image fails.

root@5462d488e282:/app# cat stellar.yaml
project_name: postgres
tracked_databases: ['postgres']
url: 'postgres://postgres@localhost/postgres'
stellar_url: 'postgres://postgres@localhost/stellar_data'
logging: 0
root@5462d488e282:/app# psql -U postgres -tA -c "SELECT exists (SELECT FROM information_schema.tables WHERE table_name = 'subject')"
f
root@5462d488e282:/app# stellar snapshot
DEBUG:stellar.operations:database_exists('stellar_data')
2021-10-12 12:13:51.423 UTC [93] FATAL:  database "stellar_data" does not exist
DEBUG:stellar.operations:create_database('stellar_data')
Snapshotting database postgres
DEBUG:stellar.app:Copying postgres to stellar_d328d5216bbace5f
DEBUG:stellar.operations:copy_database('postgres', 'stellar_d328d5216bbace5f')
DEBUG:stellar.operations:terminate_database_connections('postgres')
DEBUG:stellar.app:Starting background slave copy
DEBUG:stellar.operations:database_exists('stellar_data')
DEBUG:stellar.operations:copy_database('stellar_d328d5216bbace5f', 'stellar_639750380b4b3847')
DEBUG:stellar.operations:terminate_database_connections('stellar_d328d5216bbace5f')
root@5462d488e282:/app# psql -U postgres -tA -c "SELECT exists (SELECT FROM information_schema.tables WHERE table_name = 'subject')"
f
root@5462d488e282:/app# psql -U postgres -c "CREATE TABLE subject ();"
CREATE TABLE
root@5462d488e282:/app# psql -U postgres -tA -c "SELECT exists (SELECT FROM information_schema.tables WHERE table_name = 'subject')"
t
root@5462d488e282:/app# stellar restore
DEBUG:stellar.operations:database_exists('stellar_data')
Restoring database postgres
DEBUG:stellar.operations:database_exists('stellar_639750380b4b3847')
DEBUG:stellar.operations:remove_database('postgres')
DEBUG:stellar.operations:terminate_database_connections('postgres')
2021-10-12 12:17:33.368 UTC [139] FATAL:  terminating connection due to administrator command
2021-10-12 12:17:33.369 UTC [143] ERROR:  cannot drop the currently open database
2021-10-12 12:17:33.369 UTC [143] STATEMENT:  DROP DATABASE postgres
Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.ObjectInUse: cannot drop the currently open 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>
    load_entry_point('stellar', 'console_scripts', 'stellar')()
  File "/app/stellar/command.py", line 280, in main
    stellar()
  File "/usr/local/lib/python3.5/dist-packages/click/core.py", line 829, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.5/dist-packages/click/core.py", line 782, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.5/dist-packages/click/core.py", line 1259, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.5/dist-packages/click/core.py", line 1066, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.5/dist-packages/click/core.py", line 610, in invoke
    return callback(*args, **kwargs)
  File "/app/stellar/command.py", line 115, in restore
    app.restore(snapshot)
  File "/app/stellar/app.py", line 161, in restore
    self.operations.remove_database(table.table_name)
  File "/app/stellar/operations.py", line 118, in remove_database
    get_engine_url(raw_conn, database)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy_utils/functions/database.py", line 656, in drop_database
    connection.execute(text)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1003, in execute
    return self._execute_text(object_, multiparams, params)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1178, in _execute_text
    parameters,
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1317, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1511, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.errors.ObjectInUse) cannot drop the currently open database

[SQL: DROP DATABASE postgres]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

Some context

• The DB running as a background job and timestamped messages are coming from it
• stellar is a slightly updated version by @maxtacu and me

Possible cause

2021-10-12 12:17:33.368 UTC [139] FATAL:  terminating connection due to administrator command

I am not sure if there is a workaround against this. Probably stellar should somehow detect if PostgreSQL user doesn't have permissions to issue administrator commands. Not sure how to configure it.

@maxtacu any hints?

[abitrolly]

My postgres user has all the superpowers to do any weird stuff.

# psql -U postgres
psql (10.1)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

[abitrolly]

According to this https://stackoverflow.com/questions/36502401/postgres-drop-database-error-pq-cannot-drop-the-currently-open-database/36503031#3650303 attempt to drop connected database will always fail, so need to reconnect to another DB to drop this one. There is always template1 https://www.postgresql.org/docs/current/manage-ag-templatedbs.html but I am not sure every user can connect to it.

[tvuotila]

This is a bit late. Problem is here:

tracked_databases: ['postgres']
url: 'postgres://postgres@localhost/postgres'

Your tracked_databases and url must reference different databases. The url is the "working" database, which Stellar connects to issue commands. It needs to drop the tracked_databases databases as part of the restore. Thus, it tries to drop the database while it is connected to the same database. That will fail.

Try to change the url to reference "template1".

[abitrolly]

@tvuotila is it possible for Stellar to handle that case automatically?

Is template1 accessible to any user?

[tvuotila]

@tvuotila is it possible for Stellar to handle that case automatically?

I think it kinda does, but the instructions are misleading. For example, following input to stellar init

Please enter the url for your database.

For example:
PostgreSQL: postgresql://localhost:5432/
MySQL: mysql+pymysql://root@localhost/: postgresql://localhost:5432/
You have the following databases: postgres, tvuotila, awesomedb
Please enter the name of the database (eg. projectdb): awesomedb
Please enter your project name (used internally, eg. awesomedb) [awesomedb]:
Wrote stellar.yaml

Tip: You probably want to take a snapshot: stellar snapshot

Will result the following in stellar.yaml

project_name: 'awesomedb'
tracked_databases: ['awesomedb']
url: 'postgresql://localhost:5432/template1'
stellar_url: 'postgresql://localhost:5432/stellar_data'

So, the "Please enter the url for your database." is misleading as it actually means the database server url and should not contain the database name. (You can provide the database name if you want, but it should be different from the tracked databases.) Suggestions for better text are welcome!

Stellar could also show an error if user tries to use the same database in both places.

Is template1 accessible to any user?

Seems like as long as user can create databases, it is accessible (source)

[tvuotila]

I investigated the code a bit more. Actually, the template1 should be used automatically. I tested the stellar init with following input:

Please enter the url for your database.

For example:
PostgreSQL: postgresql://localhost:5432/
MySQL: mysql+pymysql://root@localhost/: postgresql://localhost:5432/awesomedb
Please enter your project name (used internally, eg. awesomedb) [awesomedb]:
Wrote stellar.yaml

Tip: You probably want to take a snapshot: stellar snapshot

stellar.yaml

project_name: 'awesomedb'
tracked_databases: ['awesomedb']
url: 'postgresql://localhost:5432/template1'
stellar_url: 'postgresql://localhost:5432/stellar_data'

Maybe this was fixed at some point.

[abitrolly]

Maybe this was fixed at some point.

That's good to know. Made a note to myself to check it when I have to touch DB migrations again.