Converter databases¶

DBoD¶

We are using:

Type	host	port	users
prod	dbod-proddoc	6600	admin,doconverter*
dev	dbod-devdoc	6603	admin,doconverter*

same password for both acounts, but different rights!

Create user, database...¶

Run following SQL in prod/dev, connect as admin:

--prod
C:\Program Files\PostgreSQL\9.6\bin>psql.exe -h dbod-docprod -p 6600 admin admin

CREATE USER doconverter WITH
    PASSWORD 'XXXXXXXX'
    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1;


--create schema
CREATE DATABASE doconverter
    WITH 
    OWNER = admin
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

GRANT ALL privileges ON DATABASE doconverter TO admin;

grant all privileges on database doconverter to doconverter;

--dev
C:\Program Files\PostgreSQL\9.6\bin>psql.exe -h dbod-docdev -p 6600 admin admin

CREATE USER doconverter WITH
    PASSWORD 'XXXXXXXX'
    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1;


--create schema
CREATE DATABASE doconverter
    WITH 
    OWNER = admin
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;


GRANT ALL privileges ON DATABASE doconverter TO admin;

grant all privileges on database doconverter to doconverter;

--Connect as doconverter on docprod and docdev and create schema
CREATE SCHEMA doconverter AUTHORIZATION doconverter;

Using the dbod site, to to the instance, and then file managente (dont forget to check upload a new pg_hba.conf file to allow doconverter to connect to the database. Initially we allow from any host, dont forget to force the server to take the changes:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# DB On Demand related -- DO NOT EDIT!
local   all             postgres                trust
local   dod_dbmon       dod_dbmon               trust
local   postgres        dod_dbmon               trust
host    all             postgres            0.0.0.0/0   md5
host    dod_dbmon       dod_dbmon           0.0.0.0/0   md5
host    postgres        dod_dbmon           0.0.0.0/0   md5

# User related -- PLEASE EDIT HERE
host    all     admin       0.0.0.0/0       md5
host    doconverter doconverter     0.0.0.0/0       md5

In order to create the tables and indexes in the doconverter schema, do:

Configure the doconverter.ini:

 --prod
[default]
...
[database]
host=dbod-docprod.cern.ch
port=6600
db=doconverter
user=postgresql://doconverter
password=XXXXXXX
[test]
...

--dev
[default]
...
[database]
host=dbod-docdev.cern.ch
port=6603
db=doconverter
user=postgresql://doconverter
password=XXXXXXX
[test]
...    
 ```

  - Enable the environment where you would like to run the program:

c:\Program Files\PostgreSQL\9.6\bin>workon converter3464

The run:

(converter3464) c:\Program Files\PostgreSQL\9.6\bin>python Python 3.4.2 (v3.4.2:ab2c023a9432, Oct 6 2014, 22:16:31) [MSC v.1600 64 bit (AMD64)] on win32 Type "help", "copyright", "credits" or "license" for more information.

from doconverter.models.extensions import db from doconverter.models.Result_Conversion import Result_Conversion from doconverter.models.Taskdb import Taskdb db.create_all()

-- in case you need to start from scratch just do (be aware that all data will be lost, if any): db.drop_all() ```

Last update: October 4, 2021