Diag/scalixdb
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.
Host-based access control
In the interests of maintaing the highest level of security, the default Postgresql configuration uses host-based access control (HBA) to limit access to the ScalixDB. Only clients connecting from the instance IP address are allowed in. In addition, Postgresql is configured to only listen on the instance IP address. Therefore, an attempt to connect from the same machine using localhost will fail:
[root@goat bin]# telnet localhost 5733 Trying 127.0.0.1... telnet: connect to address 127.0.0.1: Connection refused telnet: Unable to connect to remote host: Connection refused
While an attempt to connect using the instance IP address (which in smaller deployments is usually the same as 'hostname'), succeeds:
[root@goat bin]# telnet goat 5733 Trying xx.xx.xx.xx... Connected to goat.example.com (xx.xx.xx.xx). Escape character is '^]'. ^] telnet> quit Connection closed.
Note also that for ScalixDB, Postgresql is configured to listen on port 5733 (not the standard port 5432). Often it is useful to run a Postgresql client utility on a machine other than the server. For example the Windows PGAdmin tool might be used. In that case, the file pg_hba.conf must be edited to allow the client access. Remember to revert back to the original configuration once any database diagnosis has been completed.
Connecting to the Database with psql
The psql command-line Postgresql client utility can be used to insepect ScalixDB content. Invoke psql with the appropriate host name, the ScalixDB port number 5733, the 'scalix' database and the user 'scalix':
[root@goat ~]# psql -h goat -p 5733 -d scalix scalix
Password for user scalix:
psql: FATAL: password authentication failed for user "scalix"
[root@goat ~]# psql -h goat -p 5733 -d scalix scalix
Password for user scalix:
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
scalix=#
The password was originally supplied at the time Scalix was installed. Scalix Messaging Services stores message data in the database using one Postgresql schema per user. The schema names always begin with the prefix "sx_". Use the \dn command to list the schemas:
scalix=# \dn sx*
List of schemas
Name | Owner
-------------------------------------+--------
sx_93e5be502d5a44ca48459ac0d2ff621f | scalix
(1 row)
scalix=#
It is not easy to identify the schema belonging to a specific user. Use this script to translate user ids to their corresponding schema name. psql's search path can be changed to avoid typing the schema name for each query:
scalix=# set search_path to sx_93e5be502d5a44ca48459ac0d2ff621f; SET scalix=#
Having set the search path to that particular user's schema, we can now see their tables with the \dt command:
scalix=# \dt
List of relations
Schema | Name | Type | Owner
-------------------------------------+---------------+-------+--------
sx_93e5be502d5a44ca48459ac0d2ff621f | deleted | table | scalix
sx_93e5be502d5a44ca48459ac0d2ff621f | folder_status | table | scalix
sx_93e5be502d5a44ca48459ac0d2ff621f | message | table | scalix
sx_93e5be502d5a44ca48459ac0d2ff621f | version | table | scalix
(4 rows)
Similarly table content can be displayed:
scalix=# select * from folder_status;
direct_ref | last_synched
------------------+---------------
00011c00664eb912 | 1235756595556
(1 row)
scalix=#
Using pgAdmin
pgAdmin provides a graphical alternative to pgadmin and is available for a wide range of platforms, including Windows and MacOSX. The screen shot below shows pgAdmin accessing the same ScalixDB instance used in the psql examples above:
pgAdmin is particularly useful for displaying message table content (due to the large number of columns):
