Search for the string "somewhere" in the database

Question

Here is my problem: this is the first time I am looking at a Postgresql-based database application and trying to find what causes certain warnings / errors in the system log file. I don't know anything about the database schema. I don't know anything about the source code. But I need to track the problem.

I can easily find the string contents of a text file based on code, such as php and perl, using the UNIX command 'grep'; even for compiled binaries, I can use the UNIX "find" and "string" commands.

My problem is that part of the text created in the log file comes from the database itself. Checking the error log file for the database does not give anything useful, since there are no problems with the queries used by the application.

I would like to do a full search of all columns and all database tables for a row. Is this possible and how?

Thanks in advance for any pointers. Your environment is Postgresql 8.2, but it would be useful to know how to do this in other versions of relational databases.

Answer 1

This may not be optimal, but since I already know how grep is a text file, I would just hide the database before the text file and grep. Converting the database to a text file in this case would mean dumping the data with pg_dump .

The fastest / easiest / most efficient way is not always elegant ...

Answer 2

I am not familiar with Postgresql, but I would have thought that, like SQL Server, it has metadata for tables / views describing the database schema (for SQL Server 2005+, I would call you sys.tables and sys.columns). The idea would be to create a series of special queries based on the table layout, each of which found matches in a specific combination of tables and fields and pumping matches into the “journal” table.

Answer 3

I have used variations of this in the past.