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