Skip to content

Database

How to connect to database

Using Docker

This section refers to Docker and will only work if Platform 6 was installed with the Platform 6 Install Project in Github

Cf. https://github.com/amalto/platform6-install

List Docker containers

First of all, you need a running instance of Docker. To view all the running containers use the folowwing command:

home$ docker ps
CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS                    PORTS                                                                                            NAMES
6d07b43e248f        postgres:11.2                 "docker-entrypoint.s…"   32 seconds ago      Up 31 seconds (healthy)   0.0.0.0:5432->5432/tcp                                                                           pgsql

You should see a container named pgsql

Connect to a container

To be able to execute queries on the database, you need to connect the the pgsql container.

home$ docker exec -it pgsql bash
root@6d07b43e248f:/#

The prompt should change and correspond to <user>@<container_id>:/#

Connect to database

root@6d07b43e248f:/$ psql -U postgres b2box
psql (11.2 (Debian 11.2-1.pgdg90+1))
Type "help" for help.

b2box=#

Using local installation

server$ psql -U postgres b2box
psql (11.2 (Debian 11.2-1.pgdg90+1))
Type "help" for help.

b2box=#

If you want to connect to a distant server you can specify the host using the -h localhost argument to the psql tool.

Cf. https://www.postgresql.org/docs/11/app-psql.html

Installing the PostgreSQL client application

In order to connect to the database using a terminal you need the PostgreSQL client application

Ubuntu
server$ sudo apt-get install postgresql-client
MacOS with Homebrew
server$ brew install libpq
````

There's a small catch though: libpq won't install itself in the `/usr/local/bin` directory. To make that happen, you need to run:

```bash
server$ brew link --force libpq
Other OS

Take a look at this article https://www.compose.com/articles/postgresql-tips-installing-the-postgresql-client/

How to change the database password

Postgres

Connect to the database and use the following query

b2box=# ALTER ROLE b2box WITH PASSWORD 'secretPassword';

Cf. https://www.postgresql.org/docs/11/sql-alterrole.html

B2BOX/Platform 6

You need to update the B2BOX/Platform 6 configuration files to match the new password defined for the user b2box.

Inside the conf folder of your instance, create or edit the pgsqljdbc.properties file and set the property jdbc.password to your new password value

jdbc.driverClassName=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:5432/b2box
jdbc.username=b2box
jdbc.password=secretPassword

When it’s done, restart your instance

Database Schema

B2BOX (Branch 5.24)

The schema b2head is where B2BOX stores its data.

Tables

Table Name Description
activeroutingorder Contains the active routing orders
completedroutingorder Contains the completed routing orders
failedroutingorder Contains the failed routing orders
routingrule Contains the routing rules
item Contains the entries of type log, cross-referencing and transaction
rawbytes Contains rawbytes entries (Platform 6 cluster persistence)
serviceconfig Contains the configuration of all services
meta Contains the meta information of the database tables
configurationinfo Contains the serialized configuration info
datacluster Contains the serialized dataclusters
datamodel Contains the serialized datamodels
menu Contains the serialized menus
role Contains the serialized roles
transformer Contains the serialized transformers
universe Contains the serialized universes

Platform 6 (Branch 6.0)

The schema p6core is where Platform 6 stores its data.

Tables

Table Name Description
activeroutingorder Contains the active routing orders
completedroutingorder Contains the completed routing orders
failedroutingorder Contains the failed routing orders
item Contains the entries of the sub tables log, table_data and transaction
log Partition of the item table, contains all the log entries
table_data Partition of the item table, contains all the table data entries (previously known as cross-reference)
transaction Partition of the item table, contains all the transactions entries
rawbytes Contains rawbytes entries (Platform 6 cluster persistence)
serviceconfig Contains the configuration of all services
flyway_schema_history System table for the schema management

Partitioning

The item table has been partitioned into three seperated sub tables: log, table_data and transaction. Any modification on the main table will impact the sub tables and reciprocally.

Cf. https://www.postgresql.org/docs/11/ddl-partitioning.html

Reporting

All the tables used for the reporting and audit are stored into the public schema and are prefixed by ds_.

Useful queries

  • Provide common useful query examples.

Dealing with xml field

In the following examples, the content column is of type xml

Search for a node

SELECT *
FROM p6core.item
WHERE xpath('/path/field/text()' , content)[1] = 'value';

Add a node

There is no mechanism on PostgreSQL to add a node to an XML. To do it we must use string remplacement methods.

UPDATE p6core.item
SET content = regexp_replace(content::text, '(</OtherField>)', '<FieldName>.*</FieldName>\1')::xml;

Remove a node

There is no mechanism on PostgreSQL to remove XML nodes. To do it we must use string remplacement methods.

UPDATE p6core.item
SET content = regexp_replace(content::text, '<FieldName>.*</FieldName>', '')::xml;

Dealing with jsonb field

In the following examples, the content column is of type jsonb

Search for a field

SELECT *
FROM p6core.serviceconfig
WHERE content @> '{"field": "value"}';

Add or update a field

UPDATE p6core.serviceconfig
SET content = content || '{"field": "value"}'

Remove a field

UPDATE p6core.serviceconfig
SET content = content - 'fieldName'

Database cleanup

Data management

There is no automatic deletion process of any log data in the application. Here are the tables that can be cleaned up without side effects: - completedroutingorder - failedroutingorder - log

The following queries could be added to a Stored Procedure in Platform 6 and scheduled to be executed each day. The interval in the queries (30 and 90 days) are indicative values and could be modified to fit client needs.

Completed routing order
DELETE FROM p6core.completedroutingorder
WHERE array_to_string_i(xpath('/routingOrder/timeLastRunCompleted/text()'::text, content) :: TEXT[], ' ') :: numeric
          <
      extract(epoch from (now() - interval '30' day))*1000;

Will clear the completed routing order older than 30 days

Failed routing order

Usually a failed routing order should be reprocessed but if for some reason you want to clean them you can.

DELETE FROM p6core.failedroutingorder
WHERE array_to_string_i(xpath('/routingOrder/timeCreated/text()'::text, content) :: TEXT[], ' ') :: numeric
          <
      extract(epoch from (now() - interval '90' day))*1000;

Will clear the failed routing order older than 90 days

Logs
DELETE FROM p6core.log
WHERE inserttime < extract(epoch from (now() - interval '30' day))*1000;

Will clear the logs older than 30 days

Queries

  • Provide queries to cleanup the database and explain how they can be scheduled to run on a regular basis.
VACUUM VERBOSE ANALYZE [ table [, ...] ]

Performs a VACUUM and then an ANALYZE for each selected table (if none, all tables will be selected). ANALYZE updates statistics used by the planner to determine the most efficient way to execute a query.

It is also possible to make a VACUUM FULL which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn’t release the old copy until the operation is complete. Usually this should only be used when a significant amount of space needs to be reclaimed from within the table.

Cf. https://www.postgresql.org/docs/11/sql-vacuum.html

Database storage

Cf. http://www.postgresqltutorial.com/postgresql-database-indexes-table-size/

Database size

SELECT pg_size_pretty(pg_database_size('b2box'))

Table size

SELECT tablename,
       pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size_pretty,
       pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename))  AS indexes_size_pretty
FROM pg_tables
WHERE schemaname = 'p6core'
ORDER BY 1

Index size

SELECT
       c.relname AS tablename,
       ipg.relname AS indexname,
       pg_size_pretty(pg_relation_size(psai.schemaname || '.' || ipg.relname))
from pg_index x
         JOIN pg_class c ON c.oid = x.indrelid
         JOIN pg_class ipg ON ipg.oid = x.indexrelid
         JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid AND psai.schemaname = 'p6core'
ORDER BY pg_relation_size(psai.schemaname || '.' || ipg.relname) DESC

Note: For B2BOX version the schemaname is b2head