Difference between revisions of "Diag/scalixdb"
m (→Postgresql log files) |
m (→Postgresql log files) |
||
Line 57: | Line 57: | ||
scalix 2009-02-27 10:42:53.120 MST NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "deleted_pkey" for table "deleted" | scalix 2009-02-27 10:42:53.120 MST NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "deleted_pkey" for table "deleted" | ||
</pre> | </pre> | ||
+ | ===Direct access to the database=== | ||
+ | Sometimes it is useful to inspect and even change the contents of the database directly. This requires three things: | ||
+ | # Configuration that allows the appropriate client IP address access to the database. | ||
+ | # A database client utility. | ||
+ | # Some understanding of the database schema used by Scalix Messaging Services. |
Revision as of 05:07, 20 March 2008
Contents
Diagnosing ScalixDB
Configure enhanced Postgresql logging
The default Postgresql error logging configuration is very terse. Errors are logged, but not time-stamped. The SQL statement that caused the error is not logged. This makes it hard to determine what caused a database error, or even when it happened. Edit the configuration file at /var/opt/scalix/<instance>/postgres/data/posgresql.conf, modifying the logging configuration as follows:
# panic #log_error_verbosity = default # terse, default, or verbose messages -#log_min_error_statement = panic # Values in order of increasing severity: +log_min_error_statement = error # Values in order of increasing severity: # debug5 # debug4 # debug3 # debug2 @@ -294,9 +294,9 @@ #debug_pretty_print = off #log_connections = off #log_disconnections = off #log_duration = off -#log_line_prefix = '' # Special values: +log_line_prefix = '%d %m ' # Special values: # %u = user name # %d = database name # %r = remote host and port # %h = remote host
Note that because the Postgresql package is inherited from the operating system distribution, and changes may occur in this file between revisions, your file may not look exactly like this. The new log_min_error_statement directive tells Postgresql to log the SQL statement that led to any error. The log_line_prefix directive adds a time stamp and the name of the database to every log record.
Postgresql log files
Postgresql log files are found at /var/opt/scalix/<instance>/postgres/data/pg_log:
[root@goat pg_log]# pwd /var/opt/scalix/gt/postgres/data/pg_log [root@goat pg_log]# ls -l total 20 -rw------- 1 postgres postgres 7355 Feb 27 10:55 postgresql-Fri.log -rw------- 1 postgres postgres 0 Feb 23 00:00 postgresql-Mon.log -rw------- 1 postgres postgres 1459 Feb 22 02:22 postgresql-Sun.log -rw------- 1 postgres postgres 0 Feb 26 00:00 postgresql-Thu.log -rw------- 1 postgres postgres 0 Feb 24 00:00 postgresql-Tue.log -rw------- 1 postgres postgres 4387 Feb 25 23:42 postgresql-Wed.log
After making the logging configuration changes described above, you should see log output similar to this:
[root@goat pg_log]# cat postgresql-Fri.log scalix 2009-02-27 10:42:52.435 MST ERROR: schema "sx_93e5be502d5a44ca48459ac0d2ff621f" does not exist scalix 2009-02-27 10:42:52.435 MST STATEMENT: set search_path to sx_93e5be502d5a44ca48459ac0d2ff621f scalix 2009-02-27 10:42:52.437 MST ERROR: current transaction is aborted, commands ignored until end of transaction block scalix 2009-02-27 10:42:52.437 MST STATEMENT: SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema' WHEN true THEN CASE WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW' WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPORARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' ELSE NULL END END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW' ELSE NULL END ELSE NULL END AS TABLE_TYPE, d.description AS REMARKS FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0) LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') WHERE c.relnamespace = n.oid AND c.relname LIKE 'PROBABLYNOT' AND (false OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ) ) ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME scalix 2009-02-27 10:42:52.948 MST ERROR: schema "sx_93e5be502d5a44ca48459ac0d2ff621f" does not exist scalix 2009-02-27 10:42:52.948 MST STATEMENT: set search_path to sx_93e5be502d5a44ca48459ac0d2ff621f scalix 2009-02-27 10:42:52.981 MST ERROR: current transaction is aborted, commands ignored until end of transaction block scalix 2009-02-27 10:42:52.981 MST STATEMENT: SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema' WHEN true THEN CASE WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW' WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPORARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' ELSE NULL END END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW' ELSE NULL END ELSE NULL END AS TABLE_TYPE, d.description AS REMARKS FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0) LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') WHERE c.relnamespace = n.oid AND c.relname LIKE 'PROBABLYNOT' AND (false OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ) ) ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME scalix 2009-02-27 10:42:53.073 MST NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "folder_status_pkey" for table "folder_status" scalix 2009-02-27 10:42:53.115 MST NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "message_pkey" for table "message" scalix 2009-02-27 10:42:53.120 MST NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "deleted_pkey" for table "deleted"
Direct access to the database
Sometimes it is useful to inspect and even change the contents of the database directly. This requires three things:
- Configuration that allows the appropriate client IP address access to the database.
- A database client utility.
- Some understanding of the database schema used by Scalix Messaging Services.