Clear CPanel cache of Postgres databases?

[Samuraid]

Hello.

I'm running into a problem where CPanel thinks a postgres database exists when it really doesn't. The database is listed in CPanel with a size of 0.00MB. Trying to delete it from CPanel results in a postgresql error (since the database doesn't actually exist). Trying to re-create the database with the same name causes a CPanel error since it thinks the database exists already (when it really doesn't).

Does cpanel have some internal cache of postgresql databases? If so, how can I delete the cache? I cannot find any option to do this in WHM.

(I tried deleting the ~/.cpanel/datastore/postgres-db-count and ~/.cpanel/datastore/pg_info.db files, but they just get recreated again with wrong info.)

Thanks.

 

[cPanelMichael]

Hello

Do you notice any particular error messages in /usr/local/cpanel/logs/error_log when attempting to delete the database?

Thank you.

 

[Samuraid]

Here's the error I see in the CPanel web interface when trying to delete the non-existent database:

Deleted the database xxx_yyy [a fatal error or timeout occurred while processing this directive] Postgres query failed: DROP DATABASE "xxx_yyy";: DBD::PgPP::db do failed: ERROR: database "xxx_yyy" does not exist Failed to drop database “xxx_yyy,” with error “ERROR: database "xxx_yyy" does not exist ”

/usr/local/cpanel/logs/error_log contains the following relevant lines (I would have attached this as a text file, but the "Manage Attachments" button isn't working ):

[2014-04-02 20:55:38 -0400] warn [postgres] Could not connect to PostgresSQL server: “localhost”: “DBI connect('dbname=xxx_yyy','postgres',...) failed: FATAL:  database "xxx_yyy" does not exist
” at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 1128
	Cpanel::PostgresAdmin::_log_error_and_output(Cpanel::PostgresAdmin=HASH(0x2b3a900), 'Could not connect to PostgresSQL server: \xE2\x80\x9C[_1]\xE2\x80\x9D: \xE2\x80\x9C[_2]\xE2\x80\x9D', 'localhost', 'DBI connect(\'dbname=xxx_yyy\',\'postgres\',...) failed: FATAL:  database "xxx_yyy" does not exist\x0A') called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 123
	Cpanel::PostgresAdmin::_get_dbh(Cpanel::PostgresAdmin=HASH(0x2b3a900), 'localhost', 'xxx_yyy', 'postgres', '<password>') called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 915
	Cpanel::PostgresAdmin::exec_psql_on_database(Cpanel::PostgresAdmin=HASH(0x2b3a900), 'xxx_yyy', 'SELECT \'GRANT ALL PRIVILEGES ON \' || quote_ident(schemaname) || \'.\' || quote_ident(tablename) || \' TO "xxx_yyy";\'\x0A          FROM pg_tables\x0A          WHERE tableowner IN (E\'xxx\') OR schemaname=\'public\';') called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 339
	Cpanel::PostgresAdmin::setupdbrole(Cpanel::PostgresAdmin=HASH(0x2b3a900), ARRAY(0x11d5550)) called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 248
	Cpanel::PostgresAdmin::updateprivs(Cpanel::PostgresAdmin=HASH(0x2b3a900)) called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 785
	Cpanel::PostgresAdmin::dbcache(Cpanel::PostgresAdmin=HASH(0x2b3a900)) called at bin/admin/Cpanel/postgres line 150
[2014-04-02 20:55:38 -0400] die [Internal Death while parsing [stdin] 5812] ERROR:  database "xxx_yyy" does not exist
	DBD::PgPP::ProtocolStatement::execute('DBD::PgPP::ProtocolStatement=HASH(0x2b80520)') called at /usr/local/cpanel/perl/DBD/PgPP.pm line 464
	eval {...} called at /usr/local/cpanel/perl/DBD/PgPP.pm line 461
	DBD::PgPP::st::execute('DBI::st=HASH(0x2b7fd90)') called at /usr/local/cpanel/perl/DBI.pm line 1564
	DBD::_::db::do('DBI::db=HASH(0x2b90d00)', 'GRANT ALL PRIVILEGES on DATABASE "xxx_yyy" TO "xxx...', undef) called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 886
	eval {...} called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 886
	Cpanel::PostgresAdmin::exec_psql('Cpanel::PostgresAdmin=HASH(0x2b3a900)', 'GRANT ALL PRIVILEGES on DATABASE "xxx_yyy" TO "xxx...') called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 362
	Cpanel::PostgresAdmin::setupdbrole('Cpanel::PostgresAdmin=HASH(0x2b3a900)', 'ARRAY(0x11d5550)') called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 248
	Cpanel::PostgresAdmin::updateprivs('Cpanel::PostgresAdmin=HASH(0x2b3a900)') called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 785
	Cpanel::PostgresAdmin::dbcache('Cpanel::PostgresAdmin=HASH(0x2b3a900)') called at bin/admin/Cpanel/postgres line 150


[2014-04-02 20:55:38 -0400] warn [postgres] Postgres query failed: GRANT ALL PRIVILEGES on DATABASE "xxx_yyy" TO "xxx_yyy";: DBD::PgPP::db do failed: ERROR:  database "xxx_yyy" does not exist
 at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 1128
	Cpanel::PostgresAdmin::_log_error_and_output(Cpanel::PostgresAdmin=HASH(0x2b3a900), 'Postgres query failed: GRANT ALL PRIVILEGES on DATABASE "xxx_yyy" TO "xxx_yyy";: DBD::PgPP::db do failed: ERROR:  database "xxx_yyy" does not exist\x0A') called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 888
	Cpanel::PostgresAdmin::exec_psql(Cpanel::PostgresAdmin=HASH(0x2b3a900), 'GRANT ALL PRIVILEGES on DATABASE "xxx_yyy" TO "xxx_yyy";') called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 362
	Cpanel::PostgresAdmin::setupdbrole(Cpanel::PostgresAdmin=HASH(0x2b3a900), ARRAY(0x11d5550)) called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 248
	Cpanel::PostgresAdmin::updateprivs(Cpanel::PostgresAdmin=HASH(0x2b3a900)) called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 785
	Cpanel::PostgresAdmin::dbcache(Cpanel::PostgresAdmin=HASH(0x2b3a900)) called at bin/admin/Cpanel/postgres line 150
[2014-04-02 20:55:42 -0400] die [Internal Death while parsing [stdin] 5831] ERROR:  database "xxx_yyy" does not exist
	DBD::PgPP::ProtocolStatement::execute('DBD::PgPP::ProtocolStatement=HASH(0x260e200)') called at /usr/local/cpanel/perl/DBD/PgPP.pm line 464
	eval {...} called at /usr/local/cpanel/perl/DBD/PgPP.pm line 461
	DBD::PgPP::st::execute('DBI::st=HASH(0x21c65b0)') called at /usr/local/cpanel/perl/DBI.pm line 1564
	DBD::_::db::do('DBI::db=HASH(0x21c5cf0)', 'DROP DATABASE "xxx_yyy";', undef) called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 886
	eval {...} called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 886
	Cpanel::PostgresAdmin::exec_psql('Cpanel::PostgresAdmin=HASH(0x21bf3d0)', 'DROP DATABASE "xxx_yyy";') called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 201
	Cpanel::PostgresAdmin::drop_db('Cpanel::PostgresAdmin=HASH(0x21bf3d0)', 'xxx_yyy') called at bin/admin/Cpanel/postgres line 306


[2014-04-02 20:55:42 -0400] warn [postgres] Postgres query failed: DROP DATABASE "xxx_yyy";: DBD::PgPP::db do failed: ERROR:  database "xxx_yyy" does not exist
 at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 1128
	Cpanel::PostgresAdmin::_log_error_and_output(Cpanel::PostgresAdmin=HASH(0x21bf3d0), 'Postgres query failed: DROP DATABASE "xxx_yyy";: DBD::PgPP::db do failed: ERROR:  database "xxx_yyy" does not exist\x0A') called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 888
	Cpanel::PostgresAdmin::exec_psql(Cpanel::PostgresAdmin=HASH(0x21bf3d0), 'DROP DATABASE "xxx_yyy";') called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 201
	Cpanel::PostgresAdmin::drop_db(Cpanel::PostgresAdmin=HASH(0x21bf3d0), 'xxx_yyy') called at bin/admin/Cpanel/postgres line 306
[2014-04-02 20:55:42 -0400] warn [postgres] Failed to drop database “xxx_yyy,” with error “ERROR:  database "xxx_yyy" does not exist
” at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 1128
	Cpanel::PostgresAdmin::_log_error_and_output(Cpanel::PostgresAdmin=HASH(0x21bf3d0), 'Failed to drop database \xE2\x80\x9C[_1],\xE2\x80\x9D with error \xE2\x80\x9C[_2]\xE2\x80\x9D', 'xxx_yyy', 'ERROR:  database "xxx_yyy" does not exist\x0A') called at /usr/local/cpanel/Cpanel/PostgresAdmin.pm line 215
	Cpanel::PostgresAdmin::drop_db(Cpanel::PostgresAdmin=HASH(0x21bf3d0), 'xxx_yyy') called at bin/admin/Cpanel/postgres line 306

I'm running WHM 11.42.0 build 23

 

[cPanelMichael]

Could you open a support ticket using the link in my signature so we can take a closer look? You can post the ticket number here so we can update this thread with the outcome.

Thanks.

 

[Samuraid]

Thanks. The ticket number is: 4791135

 

[Samuraid]

Support responded.

There's cache/config of databases in /var/cpanel/databases/

I did the following to fix my problem:

Delete: /var/cpanel/databases/<username>.cache
Edit this to remove the database from the list: /var/cpanel/databases/<username>.yaml

 

[cPanelMichael]

I am happy to see the issue is now resolved. Thank you for updating us with the outcome.