Thread: FATAL: database "a/system_data" does not exist
Hi All, I am observing some weird errors in the postgres logs after upgrading to Postgres 9.2.4. FATAL: database "a/system_data" does not exist We do have a user named "system_data" and a schema with the same name. Even with these error messages, we are actually able to access tables, sequences, indexes, etc from that schema with out explicitly prefixing the schema name to those relations. Could anyone suggest what could be wrong with my setup and how to get past it? Thanks and Regards, Sumita -- View this message in context: http://postgresql.1045698.n5.nabble.com/FATAL-database-a-system-data-does-not-exist-tp5754839.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 05/09/2013 04:28 AM, sumita wrote: > Hi All, > I am observing some weird errors in the postgres logs after upgrading to > Postgres 9.2.4. > FATAL: database "a/system_data" does not exist What else happens right before the above message? > > We do have a user named "system_data" and a schema with the same name. > > Even with these error messages, we are actually able to access tables, > sequences, indexes, etc from that schema with out explicitly prefixing the > schema name to those relations. > > Could anyone suggest what could be wrong with my setup and how to get past > it? > > > Thanks and Regards, > Sumita > > -- Adrian Klaver adrian.klaver@gmail.com
This error is getting logged at an interval of 2 minutes and 10 seconds 2013-05-10 00:22:50 GMT:[4180]FATAL: database "a/system_data" does not exist 2013-05-10 00:25:00 GMT:[4657]FATAL: database "a/system_data" does not exist 2013-05-10 00:27:10 GMT:[5394]FATAL: database "a/system_data" does not exist -- View this message in context: http://postgresql.1045698.n5.nabble.com/FATAL-database-a-system-data-does-not-exist-tp5754839p5754975.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Fri, May 10, 2013 at 5:13 PM, sumita <suday@avaya.com> wrote: > This error is getting logged at an interval of 2 minutes and 10 seconds > 2013-05-10 00:22:50 GMT:[4180]FATAL: database "a/system_data" does not > exist > 2013-05-10 00:25:00 GMT:[4657]FATAL: database "a/system_data" does not > exist > 2013-05-10 00:27:10 GMT:[5394]FATAL: database "a/system_data" does not > exist Sounds like you have an app in a retry loop of some sort. Does the figure 130 seconds call anything to mind? Would you have written anything to delay that long before reconnecting to the database? ChrisA
On 05/10/2013 12:13 AM, sumita wrote: > This error is getting logged at an interval of 2 minutes and 10 seconds > 2013-05-10 00:22:50 GMT:[4180]FATAL: database "a/system_data" does not > exist > 2013-05-10 00:25:00 GMT:[4657]FATAL: database "a/system_data" does not > exist > 2013-05-10 00:27:10 GMT:[5394]FATAL: database "a/system_data" does not > exist > A suggestion, turn up your log_statement to 'all' to see if you can catch what is triggering the error. > > > -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> writes:
> On 05/10/2013 12:13 AM, sumita wrote:
>> This error is getting logged at an interval of 2 minutes and 10 seconds
>> 2013-05-10 00:22:50 GMT:[4180]FATAL: database "a/system_data" does not
>> exist
> A suggestion, turn up your log_statement to 'all' to see if you can
> catch what is triggering the error.
log_connections would probably be more useful, since this looks like
a connection-time failure. That would at least tell you what machine
was issuing the bogus connection attempts.
regards, tom lane
I put the log_statement to 'all' and log_connections too. I get below May 14 09:18:45 LOG: execute <unnamed>: SELECT gid FROM pg_prepared_xacts May 14 09:18:45 LOG: connection received: host=127.0.0.1 port=55618 May 14 09:18:45 LOG: connection authorized: user=postgres database=a/system_data May 14 09:18:45 FATAL: database "a/system_data" does not exist Do you think defining search_path for postgres user role do the trick. Will search_path lead to any other issue? -- View this message in context: http://postgresql.1045698.n5.nabble.com/FATAL-database-a-system-data-does-not-exist-tp5754839p5755384.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Further analysis on my product code does not show this query being fired from the product code at interval. Not sure which application is invoking this at every 130 seconds -- View this message in context: http://postgresql.1045698.n5.nabble.com/FATAL-database-a-system-data-does-not-exist-tp5754839p5755390.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Has anything changed in terms of search_path in postgres 9.2.4 I have upgraded postgres from 9.1.3 where I donot see these errors in the log file -- View this message in context: http://postgresql.1045698.n5.nabble.com/FATAL-database-a-system-data-does-not-exist-tp5754839p5755399.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 05/14/2013 03:30 AM, sumita wrote:
> I put the log_statement to 'all' and log_connections too.
> I get below
> May 14 09:18:45 LOG: execute <unnamed>: SELECT gid FROM pg_prepared_xacts
> May 14 09:18:45 LOG: connection received: host=127.0.0.1 port=55618
> May 14 09:18:45 LOG: connection authorized: user=postgres
> database=a/system_data
^^^^^^^^^^^^^
> May 14 09:18:45 FATAL: database "a/system_data" does not exist
>
>
> Do you think defining search_path for postgres user role do the trick.
> Will search_path lead to any other issue?
I don't think this has anything to do with search_path. search_path is
used to find schema within a database. The error is reporting that it
cannot even find the database.
So:
1) Do you actually have a database named a/system_data?
2) In order for me to create such a name I had to quote it:
test=# create database a/system_data;
ERROR: syntax error at or near "/"
LINE 1: create database a/system_data;
^
test=# create database "a/system_data";
CREATE DATABASE
Are you sure that when it was quoted there was not an empty space put in?
or
That when it was created there was mixed case involved?
From psql do \l to get a listing of the databases.
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/FATAL-database-a-system-data-does-not-exist-tp5754839p5755384.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
The database name is "a" and the "system_data" appears within the Schemas.
When I list the database from psql, I get following report
Name | Owner | Encoding | Collate | Ctype | Access
privileg
es
-----------+----------+----------+-------------+-------------+------------------
-----
a | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/post
gres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres=CTc/post
gres+
| | | | | =c/postgres
Also, You are correct , the database name should have been "a" rather than
"a\system_data" in the query shown in the logs. system_data is the schema
within database "a".
There is no empty space put in, I verified.
Also,so far I have unable to find out which process is invoking this query.
I do see the process id in the logs but I am unable to see this process id
in pg_stat_activity table..
Neither can I see any data with datname a\system_data in pg_stat_activity..
Is there any way , I can deduce which process is invoking this query,
Thanks much! Your inputs have been useful to help dig further into this
issue.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/FATAL-database-a-system-data-does-not-exist-tp5754839p5755577.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 05/15/2013 05:45 AM, sumita wrote: > The database name is "a" and the "system_data" appears within the Schemas. > > When I list the database from psql, I get following report > Name | Owner | Encoding | Collate | Ctype | Access > privileg > es > -----------+----------+----------+-------------+-------------+------------------ > ----- > a | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres > + > | | | | | > postgres=CTc/post > gres > template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > postgres=CTc/post > gres+ > | | | | | =c/postgres > > > Also, You are correct , the database name should have been "a" rather than > "a\system_data" in the query shown in the logs. system_data is the schema > within database "a". > There is no empty space put in, I verified. > > Also,so far I have unable to find out which process is invoking this query. > I do see the process id in the logs but I am unable to see this process id > in pg_stat_activity table.. Actually a\system_data is not part of a query but is in the connection string. You will not find it in pg_stat_activity because the connection never successfully completes. From your previous post the connection is coming from 127.0.0.1(localhost) if that helps. > Neither can I see any data with datname a\system_data in pg_stat_activity.. > Is there any way , I can deduce which process is invoking this query, See above. You may need to grep your scripts/programs for the string, if that is possible. > > Thanks much! Your inputs have been useful to help dig further into this > issue. > > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/FATAL-database-a-system-data-does-not-exist-tp5754839p5755577.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> writes:
> On 05/15/2013 05:45 AM, sumita wrote:
>> Also,so far I have unable to find out which process is invoking this query.
>> I do see the process id in the logs but I am unable to see this process id
>> in pg_stat_activity table..
> Actually a\system_data is not part of a query but is in the connection
> string. You will not find it in pg_stat_activity because the connection
> never successfully completes. From your previous post the connection is
> coming from 127.0.0.1(localhost) if that helps.
Yeah. The log_connection data that you already have is pretty much
everything that Postgres knows about where this problem is coming from,
because the session terminates as soon as this error is discovered.
> See above. You may need to grep your scripts/programs for the string, if
> that is possible.
I'm jumping to a conclusion here, but: I'm suspicious that the cause is
a malformed JDBC-style connection URL, made by some program that thinks
a schema name could be part of the URL. (Perhaps there are other DBMSes
where it actually can be done that way.)
regards, tom lane
I am running greps to find out what could be triggering it. in the scripts and programs. My only other concern is the same version of my product works without this log statements in the Postgres 9.1.3 version.Once upgraded to 9.2.4 , these log statements are appearing. -- View this message in context: http://postgresql.1045698.n5.nabble.com/FATAL-database-a-system-data-does-not-exist-tp5754839p5755740.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 05/16/2013 05:31 AM, sumita wrote: > I am running greps to find out what could be triggering it. in the scripts > and programs. > My only other concern is the same version of my product works without this > log statements in the Postgres 9.1.3 version.Once upgraded to 9.2.4 , these > log statements are appearing. Not sure why, it was a FATAL error in previous versions also. You would have had to have logging levels turned up to PANIC to avoid it. You sure there was not a change made in the application during the upgrade? > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/FATAL-database-a-system-data-does-not-exist-tp5754839p5755740.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@gmail.com
I found the configuration XML file which was creating the problem.By changing the database name , it solved the problem.Since there was also check-valid-connection-sql, it was happening at a particular frequency. Thanks all! -- View this message in context: http://postgresql.1045698.n5.nabble.com/FATAL-database-a-system-data-does-not-exist-tp5754839p5755937.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.