Install RBT: PostgreSQL

PostgreSQL stores RBT configuration and operational data. This step installs the database service and documents the supported maintenance operations that may be needed during an upgrade.

This page is one step in the complete RBT installation guide.

Before you start

Choose database storage with adequate capacity and reliable backups.
Record any non-default schema or collation decision before changing it.
Restrict database network access to the RBT host and trusted administrators.

Expected result

PostgreSQL should be installed, active and ready for the RBT database configuration used by the application installation step.

Verify this step

psql --version
systemctl is-active postgresql

Install PostgreSQL

apt install -y postgresql

su -l postgres

psql

DROP DATABASE IF EXISTS rbt;
DROP USER IF EXISTS rbt;
CREATE DATABASE rbt;
CREATE USER rbt WITH ENCRYPTED PASSWORD 'rbt';
GRANT ALL ON DATABASE rbt TO rbt;
\c rbt;
GRANT ALL ON SCHEMA public TO rbt;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

Changing the scheme (only if you understand exactly what it is and why)

CREATE SCHEMA <!---YOUR SCHEMA-->;
GRANT ALL ON SCHEMA <!---YOUR SCHEMA--> TO rbt;

Upgrade PostgreSQL (only if you understand exactly what it is and why)

su -l postgres
pg_dropcluster <NEWVERSION> main --stop
pg_upgradecluster <OLDVERSION> main
pg_dropcluster <OLDVERSION> main

apt-get purge postgresql-<OLDVERSION> postgresql-client-<OLDVERSION>
systemctl restart postgresql

Sometimes updating collation is required

CREATE COLLATION <!-- YOUR COLLATION (for example: russian) --> (provider = libc, locale = '<!-- YOUR LOCALE (for example: ru_RU.utf8) -->');
ALTER DATABASE rbt REFRESH COLLATION VERSION;