Direkt zum Hauptinhalt

Migration from H2 to PostgreSQL

Note that the migration will make you loosing all the OCR extracted text content from files in your documents. If you want to have those OCR strings back you will need to run a re-indexing for all of them.

Stop Teedy and make backup of recent H2 file structure

service jetty9 stop
cd /var/
cp -R docs/ docs.bak/
chown -R jetty:jetty docs.bak/
chmod 777 docs.bak/

Create some test instance of Teedy which is setup with PostgreSQL

Please see Teedy with PostgreSQL to get some points on how to do this.

Dumping scheme data from (filled) existing Teedy PostgreSQL instance to get a starting point for migration

You will need to configure some temporary instance of Sismics to let it create the desired structure for you. This step is only required if you don't want to use the provided SQL statements in this documentation. Do that step if you want to do it for newer releases that will come up.

su - postgres #from root user
pg_dump --schema-only teedy_db > teedy_db.sql #that dump gets splitted in step1 and step2 later on

Wipe away the psql test instance database and create a fresh/empty one again (for production use)

psql
drop database teedy_db;
CREATE DATABASE teedy_db WITH ENCODING 'UNICODE' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
GRANT ALL PRIVILEGES ON DATABASE teedy_db TO teedy;
\q
/*step1.sql*/
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
SET default_tablespace = '';SET default_with_oids = false;CREATE TABLE public.t_acl (
    acl_id_c character varying(36) NOT NULL,
    acl_perm_c character varying(30) NOT NULL,
    acl_sourceid_c character varying(36) NOT NULL,
    acl_targetid_c character varying(36) NOT NULL,
    acl_deletedate_d timestamp without time zone,
    acl_type_c character varying(30) DEFAULT 'USER'::character varying NOT NULL
);
ALTER TABLE public.t_acl OWNER TO teedy;CREATE TABLE public.t_audit_log (
    log_id_c character varying(36) NOT NULL,
    log_identity_c character varying(36) NOT NULL,
    log_classentity_c character varying(50) NOT NULL,
    log_type_c character varying(50) NOT NULL,
    log_message_c character varying(1000),
    log_createdate_d timestamp without time zone,
    log_iduser_c character varying(36) DEFAULT 'admin'::character varying NOT NULL
);
ALTER TABLE public.t_audit_log OWNER TO teedy;CREATE TABLE public.t_authentication_token (
    aut_id_c character varying(36) NOT NULL,
    aut_iduser_c character varying(36) NOT NULL,
    aut_longlasted_b boolean NOT NULL,
    aut_creationdate_d timestamp without time zone NOT NULL,
    aut_lastconnectiondate_d timestamp without time zone,
    aut_ip_c character varying(45),
    aut_ua_c character varying(1000)
);
ALTER TABLE public.t_authentication_token OWNER TO teedy;CREATE TABLE public.t_base_function (
    baf_id_c character varying(20) NOT NULL
);
ALTER TABLE public.t_base_function OWNER TO teedy;CREATE TABLE public.t_comment (
    com_id_c character varying(36) NOT NULL,
    com_iddoc_c character varying(36) NOT NULL,
    com_iduser_c character varying(36) NOT NULL,
    com_content_c character varying(4000) NOT NULL,
    com_createdate_d timestamp without time zone,
    com_deletedate_d timestamp without time zone
);
ALTER TABLE public.t_comment OWNER TO teedy;CREATE TABLE public.t_config (
    cfg_id_c character varying(50) NOT NULL,
    cfg_value_c character varying(250) NOT NULL
);
ALTER TABLE public.t_config OWNER TO teedy;CREATE TABLE public.t_contributor (
    ctr_id_c character varying(36) NOT NULL,
    ctr_iduser_c character varying(36) NOT NULL,
    ctr_iddoc_c character varying(36) NOT NULL
);
ALTER TABLE public.t_contributor OWNER TO teedy;CREATE TABLE public.t_document (
    doc_id_c character varying(36) NOT NULL,
    doc_iduser_c character varying(36) NOT NULL,
    doc_title_c character varying(100) NOT NULL,
    doc_description_c character varying(4000),
    doc_createdate_d timestamp without time zone,
    doc_deletedate_d timestamp without time zone,
    doc_language_c character varying(7) DEFAULT 'eng'::character varying NOT NULL,
    doc_subject_c character varying(500),
    doc_identifier_c character varying(500),
    doc_publisher_c character varying(500),
    doc_format_c character varying(500),
    doc_source_c character varying(500),
    doc_type_c character varying(500),
    doc_coverage_c character varying(500),
    doc_rights_c character varying(500),
    doc_updatedate_d timestamp without time zone NOT NULL,
    doc_idfile_c character varying(36)
);
ALTER TABLE public.t_document OWNER TO teedy;CREATE TABLE public.t_document_metadata (
    dme_id_c character varying(36) NOT NULL,
    dme_iddocument_c character varying(36) NOT NULL,
    dme_idmetadata_c character varying(36) NOT NULL,
    dme_value_c character varying(4000)
);
ALTER TABLE public.t_document_metadata OWNER TO teedy;CREATE TABLE public.t_document_tag (
    dot_id_c character varying(36) NOT NULL,
    dot_iddocument_c character varying(36) NOT NULL,
    dot_idtag_c character varying(36) NOT NULL,
    dot_deletedate_d timestamp without time zone
);
ALTER TABLE public.t_document_tag OWNER TO teedy;CREATE TABLE public.t_file (
    fil_id_c character varying(36) NOT NULL,
    fil_iddoc_c character varying(36),
    fil_iduser_c character varying(36) NOT NULL,
    fil_mimetype_c character varying(100) NOT NULL,
    fil_createdate_d timestamp without time zone,
    fil_deletedate_d timestamp without time zone,
    fil_order_n integer,
    fil_content_c text,
    fil_name_c character varying(200),
    fil_version_n integer DEFAULT 0 NOT NULL,
    fil_latestversion_b boolean DEFAULT true NOT NULL,
    fil_idversion_c character varying(36)
);
ALTER TABLE public.t_file OWNER TO teedy;CREATE TABLE public.t_group (
    grp_id_c character varying(36) NOT NULL,
    grp_idparent_c character varying(36),
    grp_name_c character varying(50) NOT NULL,
    grp_idrole_c character varying(36),
    grp_deletedate_d timestamp without time zone
);
ALTER TABLE public.t_group OWNER TO teedy;CREATE TABLE public.t_metadata (
    met_id_c character varying(36) NOT NULL,
    met_name_c character varying(50) NOT NULL,
    met_type_c character varying(20) NOT NULL,
    met_deletedate_d timestamp without time zone
);
ALTER TABLE public.t_metadata OWNER TO teedy;CREATE TABLE public.t_password_recovery (
    pwr_id_c character varying(36) NOT NULL,
    pwr_username_c character varying(50) NOT NULL,
    pwr_createdate_d timestamp without time zone,
    pwr_deletedate_d timestamp without time zone
);
ALTER TABLE public.t_password_recovery OWNER TO teedy;CREATE TABLE public.t_relation (
    rel_id_c character varying(36) NOT NULL,
    rel_iddocfrom_c character varying(36) NOT NULL,
    rel_iddocto_c character varying(36) NOT NULL,
    rel_deletedate_d timestamp without time zone
);
ALTER TABLE public.t_relation OWNER TO teedy;CREATE TABLE public.t_role (
    rol_id_c character varying(36) NOT NULL,
    rol_name_c character varying(36) NOT NULL,
    rol_createdate_d timestamp without time zone NOT NULL,
    rol_deletedate_d timestamp without time zone
);
ALTER TABLE public.t_role OWNER TO teedy;CREATE TABLE public.t_role_base_function (
    rbf_id_c character varying(36) NOT NULL,
    rbf_idrole_c character varying(36) NOT NULL,
    rbf_idbasefunction_c character varying(20) NOT NULL,
    rbf_createdate_d timestamp without time zone NOT NULL,
    rbf_deletedate_d timestamp without time zone
);
ALTER TABLE public.t_role_base_function OWNER TO teedy;CREATE TABLE public.t_route (
    rte_id_c character varying(36) NOT NULL,
    rte_iddocument_c character varying(36) NOT NULL,
    rte_name_c character varying(50) NOT NULL,
    rte_createdate_d timestamp without time zone NOT NULL,
    rte_deletedate_d timestamp without time zone
);
ALTER TABLE public.t_route OWNER TO teedy;CREATE TABLE public.t_route_model (
    rtm_id_c character varying(36) NOT NULL,
    rtm_name_c character varying(50) NOT NULL,
    rtm_steps_c character varying(5000) NOT NULL,
    rtm_createdate_d timestamp without time zone NOT NULL,
    rtm_deletedate_d timestamp without time zone
);
ALTER TABLE public.t_route_model OWNER TO teedy;CREATE TABLE public.t_route_step (
    rtp_id_c character varying(36) NOT NULL,
    rtp_idroute_c character varying(36) NOT NULL,
    rtp_name_c character varying(200) NOT NULL,
    rtp_type_c character varying(50) NOT NULL,
    rtp_transition_c character varying(50),
    rtp_comment_c character varying(500),
    rtp_idtarget_c character varying(36) NOT NULL,
    rtp_idvalidatoruser_c character varying(36),
    rtp_order_n integer NOT NULL,
    rtp_createdate_d timestamp without time zone NOT NULL,
    rtp_enddate_d timestamp without time zone,
    rtp_deletedate_d timestamp without time zone,
    rtp_transitions_c character varying(2000)
);
ALTER TABLE public.t_route_step OWNER TO teedy;CREATE TABLE public.t_share (
    sha_id_c character varying(36) NOT NULL,
    sha_name_c character varying(36),
    sha_createdate_d timestamp without time zone,
    sha_deletedate_d timestamp without time zone
);
ALTER TABLE public.t_share OWNER TO teedy;CREATE TABLE public.t_tag (
    tag_id_c character varying(36) NOT NULL,
    tag_iduser_c character varying(36) NOT NULL,
    tag_name_c character varying(36) NOT NULL,
    tag_createdate_d timestamp without time zone,
    tag_deletedate_d timestamp without time zone,
    tag_color_c character varying(7) DEFAULT '#3a87ad'::character varying NOT NULL,
    tag_idparent_c character varying(36)
);
ALTER TABLE public.t_tag OWNER TO teedy;CREATE TABLE public.t_user (
    use_id_c character varying(36) NOT NULL,
    use_idrole_c character varying(36) NOT NULL,
    use_username_c character varying(50) NOT NULL,
    use_password_c character varying(60) NOT NULL,
    use_email_c character varying(100) NOT NULL,
    use_createdate_d timestamp without time zone NOT NULL,
    use_deletedate_d timestamp without time zone,
    use_privatekey_c character varying(100) DEFAULT ''::character varying NOT NULL,
    use_storagequota_n bigint DEFAULT '10000000000'::bigint NOT NULL,
    use_storagecurrent_n bigint DEFAULT 0 NOT NULL,
    use_totpkey_c character varying(100),
    use_disabledate_d timestamp without time zone,
    use_onboarding_b boolean DEFAULT true NOT NULL
);
ALTER TABLE public.t_user OWNER TO teedy;CREATE TABLE public.t_user_group (
    ugp_id_c character varying(36) NOT NULL,
    ugp_iduser_c character varying(36) NOT NULL,
    ugp_idgroup_c character varying(36) NOT NULL,
    ugp_deletedate_d timestamp without time zone
);
ALTER TABLE public.t_user_group OWNER TO teedy;CREATE TABLE public.t_vocabulary (
    voc_id_c character varying(36) NOT NULL,
    voc_name_c character varying(50) NOT NULL,
    voc_value_c character varying(500) NOT NULL,
    voc_order_n integer NOT NULL
);
ALTER TABLE public.t_vocabulary OWNER TO teedy;CREATE TABLE public.t_webhook (
    whk_id_c character varying(36) NOT NULL,
    whk_event_c character varying(50) NOT NULL,
    whk_url_c character varying(1024) NOT NULL,
    whk_createdate_d timestamp without time zone NOT NULL,
    whk_deletedate_d timestamp without time zone
);
psql -d teedy_db -U teedy -f step1.sql -L teedy_db_dump_step1.log

Export data from H2 to CSV

Access the H2 database and run the following call statements. See this tutorial on how to open your existing H2 database → Access / view H2 Database

/*get an overview of all tables*/
SHOW TABLES;

The following call statements were built up from the "SHOW TABLES;" output above.

call CSVWRITE ('C:/users/mario/Downloads/h2data/T_ACL.csv', 'SELECT * FROM T_ACL', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_AUDIT_LOG.csv', 'SELECT * FROM T_AUDIT_LOG', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_AUTHENTICATION_TOKEN.csv', 'SELECT * FROM T_AUTHENTICATION_TOKEN', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_BASE_FUNCTION.csv', 'SELECT * FROM T_BASE_FUNCTION', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_COMMENT.csv', 'SELECT * FROM T_COMMENT', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_CONFIG.csv', 'SELECT * FROM T_CONFIG', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_CONTRIBUTOR.csv', 'SELECT * FROM T_CONTRIBUTOR', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_DOCUMENT.csv', 'SELECT * FROM T_DOCUMENT', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_DOCUMENT_METADATA.csv', 'SELECT * FROM T_DOCUMENT_METADATA', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_DOCUMENT_TAG.csv', 'SELECT * FROM T_DOCUMENT_TAG', 'charset=utf8');
/*the column "fil_content_c" is ignored because it contains a lot of weird plain text which is almost impossible to export to CSV in a clean fashion*/
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_FILE.csv', 'SELECT FIL_ID_C, FIL_IDDOC_C, FIL_IDUSER_C, FIL_MIMETYPE_C, FIL_CREATEDATE_D, FIL_DELETEDATE_D, FIL_ORDER_N,NULL AS FIL_CONTENT_C,FIL_NAME_C, FIL_VERSION_N, FIL_LATESTVERSION_B, FIL_IDVERSION_C FROM T_FILE', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_GROUP.csv', 'SELECT * FROM T_GROUP', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_METADATA.csv', 'SELECT * FROM T_METADATA', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_PASSWORD_RECOVERY.csv', 'SELECT * FROM T_PASSWORD_RECOVERY', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_RELATION.csv', 'SELECT * FROM T_RELATION', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_ROLE.csv', 'SELECT * FROM T_ROLE', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_ROLE_BASE_FUNCTION.csv', 'SELECT * FROM T_ROLE_BASE_FUNCTION', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_ROUTE.csv', 'SELECT * FROM T_ROUTE', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_ROUTE_MODEL.csv', 'SELECT * FROM T_ROUTE_MODEL', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_ROUTE_STEP.csv', 'SELECT * FROM T_ROUTE_STEP', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_SHARE.csv', 'SELECT * FROM T_SHARE', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_TAG.csv', 'SELECT * FROM T_TAG', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_USER.csv', 'SELECT * FROM T_USER', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_USER_GROUP.csv', 'SELECT * FROM T_USER_GROUP', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_VOCABULARY.csv', 'SELECT * FROM T_VOCABULARY', 'charset=utf8');
call CSVWRITE ('C:/users/mario/Downloads/h2data/T_WEBHOOK.csv', 'SELECT * FROM T_WEBHOOK', 'charset=utf8');

Upload csv files to server (by SSH/SFTP)

mkdir ~/h2data
#put csv files here

Move files to postgres directory (to grant access)

#as root
mv ~/h2data /var/lib/postgresql/
cd /var/lib/postgresql/
chown -R postgres:postgres h2data/

SQL Import Copying

The folloing copy statements were built up bases on the "call CSVWRITE" statements in the H2 export.

psql
\c teedy_db;
#You are now connected to database "teedy_db" as user "postgres".
COPY T_ACL FROM '/var/lib/postgresql/h2data/T_ACL.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_CONFIG FROM '/var/lib/postgresql/h2data/T_CONFIG.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_GROUP FROM '/var/lib/postgresql/h2data/T_GROUP.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_ROLE FROM '/var/lib/postgresql/h2data/T_ROLE.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_USER FROM '/var/lib/postgresql/h2data/T_USER.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_CONTRIBUTOR FROM '/var/lib/postgresql/h2data/T_CONTRIBUTOR.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_METADATA FROM '/var/lib/postgresql/h2data/T_METADATA.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_WEBHOOK FROM '/var/lib/postgresql/h2data/T_WEBHOOK.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_VOCABULARY FROM '/var/lib/postgresql/h2data/T_VOCABULARY.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_BASE_FUNCTION FROM '/var/lib/postgresql/h2data/T_BASE_FUNCTION.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_AUDIT_LOG FROM '/var/lib/postgresql/h2data/T_AUDIT_LOG.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_RELATION FROM '/var/lib/postgresql/h2data/T_RELATION.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_USER_GROUP FROM '/var/lib/postgresql/h2data/T_USER_GROUP.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_ROLE_BASE_FUNCTION FROM '/var/lib/postgresql/h2data/T_ROLE_BASE_FUNCTION.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_ROUTE_MODEL FROM '/var/lib/postgresql/h2data/T_ROUTE_MODEL.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_AUTHENTICATION_TOKEN FROM '/var/lib/postgresql/h2data/T_AUTHENTICATION_TOKEN.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_TAG FROM '/var/lib/postgresql/h2data/T_TAG.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_PASSWORD_RECOVERY FROM '/var/lib/postgresql/h2data/T_PASSWORD_RECOVERY.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_SHARE FROM '/var/lib/postgresql/h2data/T_SHARE.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_COMMENT FROM '/var/lib/postgresql/h2data/T_COMMENT.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_DOCUMENT FROM '/var/lib/postgresql/h2data/T_DOCUMENT.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_DOCUMENT_METADATA FROM '/var/lib/postgresql/h2data/T_DOCUMENT_METADATA.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_DOCUMENT_TAG FROM '/var/lib/postgresql/h2data/T_DOCUMENT_TAG.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_FILE FROM '/var/lib/postgresql/h2data/T_FILE.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_ROUTE FROM '/var/lib/postgresql/h2data/T_ROUTE.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
COPY T_ROUTE_STEP FROM '/var/lib/postgresql/h2data/T_ROUTE_STEP.csv' DELIMITER ',' CSV HEADER ENCODING 'utf-8';
\q

SQL Step 2 - Adjust foreign keys and indexes

/*step2.sql*/
ALTER TABLE public.t_webhook OWNER TO teedy;ALTER TABLE ONLY public.t_acl
    ADD CONSTRAINT t_acl_pkey PRIMARY KEY (acl_id_c);
ALTER TABLE ONLY public.t_audit_log
    ADD CONSTRAINT t_audit_log_pkey PRIMARY KEY (log_id_c);
ALTER TABLE ONLY public.t_authentication_token
    ADD CONSTRAINT t_authentication_token_pkey PRIMARY KEY (aut_id_c);
ALTER TABLE ONLY public.t_base_function
    ADD CONSTRAINT t_base_function_pkey PRIMARY KEY (baf_id_c);
ALTER TABLE ONLY public.t_comment
    ADD CONSTRAINT t_comment_pkey PRIMARY KEY (com_id_c);
ALTER TABLE ONLY public.t_config
    ADD CONSTRAINT t_config_pkey PRIMARY KEY (cfg_id_c);
ALTER TABLE ONLY public.t_contributor
    ADD CONSTRAINT t_contributor_pkey PRIMARY KEY (ctr_id_c);
ALTER TABLE ONLY public.t_document_metadata
    ADD CONSTRAINT t_document_metadata_pkey PRIMARY KEY (dme_id_c);
ALTER TABLE ONLY public.t_document
    ADD CONSTRAINT t_document_pkey PRIMARY KEY (doc_id_c);
ALTER TABLE ONLY public.t_document_tag
    ADD CONSTRAINT t_document_tag_pkey PRIMARY KEY (dot_id_c);
ALTER TABLE ONLY public.t_file
    ADD CONSTRAINT t_file_pkey PRIMARY KEY (fil_id_c);
ALTER TABLE ONLY public.t_group
    ADD CONSTRAINT t_group_pkey PRIMARY KEY (grp_id_c);
ALTER TABLE ONLY public.t_metadata
    ADD CONSTRAINT t_metadata_pkey PRIMARY KEY (met_id_c);
ALTER TABLE ONLY public.t_password_recovery
    ADD CONSTRAINT t_password_recovery_pkey PRIMARY KEY (pwr_id_c);
ALTER TABLE ONLY public.t_relation
    ADD CONSTRAINT t_relation_pkey PRIMARY KEY (rel_id_c);
ALTER TABLE ONLY public.t_role_base_function
    ADD CONSTRAINT t_role_base_function_pkey PRIMARY KEY (rbf_id_c);
ALTER TABLE ONLY public.t_role
    ADD CONSTRAINT t_role_pkey PRIMARY KEY (rol_id_c);
ALTER TABLE ONLY public.t_route_model
    ADD CONSTRAINT t_route_model_pkey PRIMARY KEY (rtm_id_c);
ALTER TABLE ONLY public.t_route
    ADD CONSTRAINT t_route_pkey PRIMARY KEY (rte_id_c);
ALTER TABLE ONLY public.t_route_step
    ADD CONSTRAINT t_route_step_pkey PRIMARY KEY (rtp_id_c);
ALTER TABLE ONLY public.t_share
    ADD CONSTRAINT t_share_pkey PRIMARY KEY (sha_id_c);
ALTER TABLE ONLY public.t_tag
    ADD CONSTRAINT t_tag_pkey PRIMARY KEY (tag_id_c);
ALTER TABLE ONLY public.t_user_group
    ADD CONSTRAINT t_user_group_pkey PRIMARY KEY (ugp_id_c);
ALTER TABLE ONLY public.t_user
    ADD CONSTRAINT t_user_pkey PRIMARY KEY (use_id_c);
ALTER TABLE ONLY public.t_vocabulary
    ADD CONSTRAINT t_vocabulary_pkey PRIMARY KEY (voc_id_c);
ALTER TABLE ONLY public.t_webhook
    ADD CONSTRAINT t_webhook_pkey PRIMARY KEY (whk_id_c);
CREATE INDEX idx_acl_sourceid_c ON public.t_acl USING btree (acl_sourceid_c);
CREATE INDEX idx_acl_targetid_c ON public.t_acl USING btree (acl_targetid_c);
CREATE INDEX idx_doc_createdate_d ON public.t_document USING btree (doc_createdate_d);
CREATE INDEX idx_doc_language_c ON public.t_document USING btree (doc_language_c);
CREATE INDEX idx_doc_title_c ON public.t_document USING btree (doc_title_c);
CREATE INDEX idx_dot_composite ON public.t_document_tag USING btree (dot_iddocument_c, dot_idtag_c, dot_deletedate_d);
CREATE INDEX idx_log_identity_c ON public.t_audit_log USING btree (log_identity_c);
ALTER TABLE ONLY public.t_authentication_token
    ADD CONSTRAINT fk_aut_iduser_c FOREIGN KEY (aut_iduser_c) REFERENCES public.t_user(use_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_comment
    ADD CONSTRAINT fk_com_iddoc_c FOREIGN KEY (com_iddoc_c) REFERENCES public.t_document(doc_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_comment
    ADD CONSTRAINT fk_com_iduser_c FOREIGN KEY (com_iduser_c) REFERENCES public.t_user(use_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_document_metadata
    ADD CONSTRAINT fk_dme_iddocument_c FOREIGN KEY (dme_iddocument_c) REFERENCES public.t_document(doc_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_document_metadata
    ADD CONSTRAINT fk_dme_idmetadata_c FOREIGN KEY (dme_idmetadata_c) REFERENCES public.t_metadata(met_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_document
    ADD CONSTRAINT fk_doc_idfile_c FOREIGN KEY (doc_idfile_c) REFERENCES public.t_file(fil_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_document
    ADD CONSTRAINT fk_doc_iduser_c FOREIGN KEY (doc_iduser_c) REFERENCES public.t_user(use_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_document_tag
    ADD CONSTRAINT fk_dot_iddocument_c FOREIGN KEY (dot_iddocument_c) REFERENCES public.t_document(doc_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_document_tag
    ADD CONSTRAINT fk_dot_idtag_c FOREIGN KEY (dot_idtag_c) REFERENCES public.t_tag(tag_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_file
    ADD CONSTRAINT fk_fil_iddoc_c FOREIGN KEY (fil_iddoc_c) REFERENCES public.t_document(doc_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_file
    ADD CONSTRAINT fk_fil_iduser_c FOREIGN KEY (fil_iduser_c) REFERENCES public.t_user(use_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_role_base_function
    ADD CONSTRAINT fk_rbf_idbasefunction_c FOREIGN KEY (rbf_idbasefunction_c) REFERENCES public.t_base_function(baf_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_role_base_function
    ADD CONSTRAINT fk_rbf_idrole_c FOREIGN KEY (rbf_idrole_c) REFERENCES public.t_role(rol_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_route
    ADD CONSTRAINT fk_rte_iddocument_c FOREIGN KEY (rte_iddocument_c) REFERENCES public.t_document(doc_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_route_step
    ADD CONSTRAINT fk_rtp_idroute_c FOREIGN KEY (rtp_idroute_c) REFERENCES public.t_route(rte_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_route_step
    ADD CONSTRAINT fk_rtp_idvalidatoruser_c FOREIGN KEY (rtp_idvalidatoruser_c) REFERENCES public.t_user(use_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_tag
    ADD CONSTRAINT fk_tag_iduser_c FOREIGN KEY (tag_iduser_c) REFERENCES public.t_user(use_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.t_user
    ADD CONSTRAINT fk_use_idrole_c FOREIGN KEY (use_idrole_c) REFERENCES public.t_role(rol_id_c) ON UPDATE RESTRICT ON DELETE RESTRICT;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
psql -d teedy_db -U teedy -f step2.sql -L teedy_db_dump_step2.log

Configure Jetty Service to use database connection instead of H2 local DB

See Teedy with PostgreSQL on how to do this

Restart Jetty

service jetty9 restart && journalctl -f -u jetty9.service

Reindex all files with Tesseract OCR scanning libraries (the OCR data from is H2 is lost)

There is no way to click some button for this. It has to be scripted. The provided API deals with it. Please see this page for re-processing files by API → API Scripts / database queries

Before executing this please move (or remove) the *_thumb and *_web files away because they will be re-processed too. If you don't do this the following will occur: Fix Preview Bug

Keine Kommentare
nach oben