PostgreSQL administering

Logging in to the database container

cd 7cart/docker/ sudo docker ps

You will see a list of containers CONTAINER ID IMAGE 2aa75adce8dd bitnami/nginx:latest dcdb7be8910d docker_phpfpm fada3f0c1ff2 bitnami/postgresql:9.6 - this is the container with the database 26b4cec173a3 bitnami/node:8

sudo docker exec -it <CONTAINER ID of YOUR bitnami/postgresql container> /bin/bash

Connecting to PostgreSQL

The default project database credentials are:

• DB name: scart
• user: scart
• password: scart

To connect to DB via the command prompt:

1. log in to bitnami/postgresql container
2. run psql -U scart

If you see scart=> invitation, you're good to go.

PostgreSQL logging

1. Log in to postgres:11 container (see above)

2. Add logging setting to PostgreSQL config file:

To log ALL the requests echo "log_statement = 'all'" > /var/lib/postgresql/data/postgresql.conf

To log just INSERT, UPDATE and DELETE requests `echo "log_statement = 'mod'" > /var/lib/postgresql/data/postgresql.conf

3.Reload configuration files pg_ctl reload -D /var/lib/postgresql/data/

If you see a message server signaled, you're done.

Constraints for JSONB fields

You can make a JSONB field value unique across other rows. This is accomplished by adding UNIQUE INDEX.

For a table "nodes", JSONB column "attributes" and unique JSONB field 'SKU' adding the index looks like this:

CREATE UNIQUE INDEX sku_index ON nodes ((attributes->>'SKU'));

Indexes for JSONB fields

For a specific key withing jsonb field, you can create generic index:

CREATE INDEX price_index ON nodes (((attributes->>'price')::NUMERIC)); This index is the fastest.

To index all the keys found in jsonb field, use GIN indexes. See PostgreSQL page on jsonb Indexing.

CRUD on JSONB fields

Will appear soon.

Performance optimizations

Will appear soon.