[Pkg-postgresql-public] Bug#755908: dbconfig-common: fails to purge active PostgreSQL database

[Thorsten Glaser]

Package: dbconfig-common
Version: 1.8.47+nmu1
Severity: normal

Hi,

I’m working on packaging a web application that uses PostgreSQL
(well, will do once I finish converting it from SQLite2 to it).

When I 'apt-get purge packagename', dbconfig-common tries to
remove the database (as I tell it to), but fails to do that.

I discovered the reason for this is that I cannot purge the
database manually “the easy way” either:

postgres=# DROP DATABASE simkolab;
ERROR:  database "simkolab" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Some related reading:
- http://stackoverflow.com/a/5109190/2171120
- http://stackoverflow.com/a/7489673/2171120

The reason here is that Apache has not yet been restarted/reloaded
and that one of its children is holding the connection open.

I’m currently trying to shuffle the dbc_go call in postrm in
between do_webserver_config() / apache2_invoke disconf and the
rest of the cleanup, but that means duplicating it, etc.

It would be really nice if dbconfig-common could be patched to
tear down a database using the method shown in the above SO link:

1) REVOKE CONNECT ON DATABASE dbname FROM PUBLIC, username;
2) SELECT
    pg_terminate_backend(procpid)
   FROM
    pg_stat_activity
   WHERE
    -- don't kill my own connection!
    procpid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'database_name'
   ; -- for psql 8.4 to 9.1
     -- in psql 9.2 the column procpid was renamed to pid
3) DROP DATABASE dbname;

Might be useful to have the psql maintainers say something to
this as well; perhaps there’s an easier way?

-- System Information:
Debian Release: jessie/sid
  APT prefers unstable
  APT policy: (500, 'unstable')
Architecture: amd64 (x86_64)
Foreign Architectures: i386

Kernel: Linux 3.14-2-amd64 (SMP w/2 CPU cores)
Locale: LANG=C, LC_CTYPE=en_GB.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/lksh

Versions of packages dbconfig-common depends on:
ii  debconf [debconf-2.0]  1.5.53
ii  ucf                    3.0030

dbconfig-common recommends no packages.

Versions of packages dbconfig-common suggests:
ii  postgresql-client-9.3 [postgresql-client]  9.3.4-2

-- debconf information:
  dbconfig-common/db/basepath:
* dbconfig-common/remember-admin-pass: false
* dbconfig-common/remote-questions-default: false
  dbconfig-common/db/dbname:
  dbconfig-common/purge: false
  dbconfig-common/mysql/admin-user: root
  dbconfig-common/internal/skip-preseed: false
  dbconfig-common/pgsql/authmethod-admin: ident
  dbconfig-common/internal/reconfiguring: false
  dbconfig-common/install-error: abort
  dbconfig-common/dbconfig-upgrade: true
  dbconfig-common/upgrade-error: abort
  dbconfig-common/missing-db-package-error: abort
  dbconfig-common/passwords-do-not-match:
  dbconfig-common/remote/port:
  dbconfig-common/db/app-user:
  dbconfig-common/mysql/method: unix socket
  dbconfig-common/upgrade-backup: true
  dbconfig-common/database-type:
  dbconfig-common/remove-error: abort
  dbconfig-common/remote/host:
  dbconfig-common/pgsql/changeconf: false
  dbconfig-common/pgsql/no-empty-passwords:
  dbconfig-common/pgsql/admin-user: postgres
  dbconfig-common/dbconfig-remove: true
  dbconfig-common/pgsql/method: unix socket
  dbconfig-common/dbconfig-reinstall: false
  dbconfig-common/pgsql/manualconf:
  dbconfig-common/pgsql/no-user-choose-other-method:
  dbconfig-common/dbconfig-install: true
  dbconfig-common/pgsql/authmethod-user:
  dbconfig-common/pgsql/revertconf: false
  dbconfig-common/remote/newhost: