!!! Must be executed on each master server of the cluster !!!
Postgres cluster #1:***************************
- db-pgsql01.oracak.com - master
Postgres cluster #2:
***************************
- db-pgsql03.oracak.com - master
1. Creating the 'heartbeat' tables
[postgres] psql>
\dbquik1 -- dbquik2 for "db-pgsql03"
-- switch role
set role ggadmin;
-- ggadmin.HB
CREATE TABLE ggadmin.HB (ID serial primary key, INSTANCE_NAME VARCHAR(50) NOT NULL, SRC_TIMESTAMP TIMESTAMP NOT NULL);
-- ggadmin.HB_TARGET
CREATE TABLE ggadmin.HB_TARGET (ID serial primary key, INSTANCE_NAME VARCHAR(50) NOT NULL, SRC_TIMESTAMP TIMESTAMP NOT NULL, DEST_TIMESTAMP TIMESTAMP DEFAULT now());
-- ggadmin.HB_VIEW
create or replace view ggadmin.HB_VIEW ("INSTANCE_NAME", "LAG_SOURCE", "LAG_SYSTEM") AS
SELECT instance_name, TRUNC (( CAST (dest_timestamp AS DATE)- CAST (src_timestamp AS DATE))* 24* 60,1) lag_source, TRUNC ((CURRENT_DATE - CAST (dest_timestamp AS DATE)) * 24 * 60, 1)
FROM ggadmin.HB_TARGET WHERE (id, instance_name) IN ( SELECT MAX (id), instance_name FROM ggadmin.HB_TARGET GROUP BY instance_name) ORDER BY 1;
2. Adding permissions for cron_tech.
[postgres] psql>
\c dbquik1 -- dbquik2 for "db-pgsql03"
GRANT USAGE ON SCHEMA ggadmin TO cron_tech;
GRANT SELECT,insert,update,delete ON TABLE ggadmin.hb TO cron_tech;
GRANT USAGE, SELECT ON SEQUENCE ggadmin.hb_id_seq TO cron_tech;
3. Granting permissions on schemas and tables for replication:
-- Get the list of tables for replication.
-- SELECT 'grant select, update, delete, insert on zalogs."'||table_name||'" to ggadmin;' FROM information_schema.tables WHERE table_schema = 'stock' order by table_name;
[postgres] psql>
\dbquik1 -- dbquik2 for "db-pgsql03"
grant select, update,delete, insert on ggadmin.hb to ggadmin;
grant select, update,delete, insert on ggadmin.hb_target to ggadmin;
grant select, SELECT ON SEQUENCE ggadmin.hb_id_seq TO ggadmin;
grant select, SELECT ON SEQUENCE ggadmin.hb_target_id_seq TO ggadmin;
-- Schema permissions
grant USAGE on SCHEMA stock TO ggadmin;
-- Table permissions
grant select ,update,delete, insert on stock.bonds to ggadmin;
grant select ,update,delete, insert on stock.bonds_arch to ggadmin;
grant select ,update,delete, insert on stock.bonds_setting to ggadmin;
grant select ,update,delete, insert on stock.bonds_setting_arch to ggadmin;
grant select ,update,delete, insert on stock.bonds_zajvka to ggadmin;
grant select ,update,delete, insert on stock.bonds_zajvka_arch to ggadmin;
grant select ,update,delete, insert on stock.client_account to ggadmin;
grant select ,update,delete, insert on stock.client_account_arch to ggadmin;
grant select ,update,delete, insert on stock.dividents to ggadmin;
grant select ,update,delete, insert on stock.job_logs to ggadmin;
grant select ,update,delete, insert on stock.portfolio to ggadmin;
grant select ,update,delete, insert on stock.portfolio_arch to ggadmin;
grant select ,update,delete, insert on stock.quotes to ggadmin;
grant select ,update,delete, insert on stock.quotes_analyst_15m to ggadmin;
grant select ,update,delete, insert on stock.quotes_analyst_1d to ggadmin;
grant select ,update,delete, insert on stock.quotes_analyst_1h to ggadmin;
grant select ,update,delete, insert on stock.quotes_arch to ggadmin;
grant select ,update,delete, insert on stock.quotes_volume to ggadmin;
grant select ,update,delete, insert on stock.quotes_volume_arch to ggadmin;
grant select ,update,delete, insert on stock.report_benefit to ggadmin;
grant select ,update,delete, insert on stock.report_benefit_comparator to ggadmin;
grant select ,update,delete, insert on stock.report_edu to ggadmin;
grant select ,update,delete, insert on stock.report_edu_comparator to ggadmin;
grant select ,update,delete, insert on stock.sdelka to ggadmin;
grant select ,update,delete, insert on stock.sdelka_arch to ggadmin;
grant select ,update,delete, insert on stock.signal to ggadmin;
grant select ,update,delete, insert on stock.signal_arch to ggadmin;
grant select ,update,delete, insert on stock.trading_edu to ggadmin;
grant select ,update,delete, insert on stock.trading_prod to ggadmin;
grant select ,update,delete, insert on stock.trading_prod_arch to ggadmin;
