DBA Tips: 05.08. Preparing PostgreSQL Database 'Passive Node' for Replication

!!! Execute on the master server of the passive cluster !!!

Postgres cluster #2:
***************************

- db-pgsql03.oracak.ru - master

1. Dropping secondary keys.
• Note
Execute the scripts in the application below.
The script for creating secondary keys is not executed yet but attached for reference.

[stock] psql>
\c dbquik2

ALTER TABLE stock.bond drop constraint fk_bonds;

2. Disabling triggers according to the list.
[stock] psql>

No triggers to disable in the current schema !!!.

3. Clearing the contents of tables before the Initial Load
-- Clearing tables
--SELECT 'delete from stock."'||table_name||'";' FROM information_schema.tables WHERE table_schema = 'stock' order by table_name;
[stock] psql>

\c dbquik2
truncate table stock.bonds;
truncate table stock.bonds_arch;
truncate table stock.bonds_setting;
truncate table stock.bonds_setting_arch;
truncate table stock.bonds_zajvka;
truncate table stock.bonds_zajvka_arch;
truncate table stock.client_account;
truncate table stock.client_account_arch;
truncate table stock.dividents;
truncate table stock.job_logs;
truncate table stock.portfolio;
truncate table stock.portfolio_arch;
truncate table stock.quotes;
truncate table stock.quotes_analyst_15m;
truncate table stock.quotes_analyst_1d;
truncate table stock.quotes_analyst_1h;
truncate table stock.quotes_arch;
truncate table stock.quotes_volume;
truncate table stock.quotes_volume_arch;
truncate table stock.report_benefit;
truncate table stock.report_benefit_comparator;
truncate table stock.report_edu;
truncate table stock.report_edu_comparator;
truncate table stock.sdelka;
truncate table stock.sdelka_arch;
truncate table stock.signal;
truncate table stock.signal_arch;
truncate table stock.trading_edu;
truncate table stock.trading_prod;
truncate table stock.trading_prod_arch;

4. Recreating the sequence stock.seq_id
[stock] psql>

-- dbquik2
DROP SEQUENCE stock.seq_id;
CREATE SEQUENCE stock.seq_id
INCREMENT BY 100
MINVALUE 1
MAXVALUE 999999999999999999
START 8000000000058
CACHE 10
NO CYCLE;

5. Checking the placement of indexes in the correct tablespace. If an index is located incorrectly, execute the resulting script to move it to the TS ts_stock_index

In the current schema, there are no indexes to move

supplement (script preparation)
--------------------------------------

-- 1. Preparation of the script for creating secondary keys
[stock] psql>

SELECT
'ALTER TABLE ' || tc.table_schema || '."' || tc.table_name ||
'" ADD CONSTRAINT ' ||
'"'||tc.constraint_name||'" '||
' FOREIGN KEY (' || kcu.column_name || ')' ||
' REFERENCES ' || ccu.table_schema || '."' || ccu.table_name ||
'" (' || ccu.column_name || ') ' ||
CASE WHEN rc.match_option <> 'NONE' THEN E'\n
MATCH ' || rc.match_option ELSE '' END ||
CASE WHEN rc.update_rule <> 'NO ACTION' THEN
' ON UPDATE ' || rc.update_rule || ' ' ELSE '' END ||
CASE WHEN rc.delete_rule <> 'NO ACTION'
THEN 'ON DELETE ' || rc.delete_rule ELSE '' END || ';'
AS add_constraint
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
JOIN information_schema.referential_constraints AS rc
ON tc.constraint_name=rc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
and tc.table_schema='stock' and tc.table_name like 'bond%'
order by tc.table_name,tc.constraint_name;

-- 2. Preparation of the script for deleting secondary keys

[stock] psql>

SELECT
tc.table_schema,
tc.table_name,
--kcu.column_name,
tc.constraint_name,
--ccu.table_schema AS foreign_table_schema,
--ccu.table_name AS foreign_table_name,
--ccu.column_name AS foreign_column_name,
'ALTER TABLE '||tc.table_schema||'."'||tc.table_name||'" drop constraint "'||tc.constraint_name||'";' AS drop_constraint
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.table_schema='stock'
--AND tc.table_name like 'bond%'
order by tc.table_name,tc.constraint_name;

-- 3. Preparing the script for disabling triggers

[stock] psql>

SELECT
trigger_catalog as dbname, trigger_schema as trg_schema,trigger_name trg_name,
(SELECT tgenabled FROM pg_trigger WHERE tgname=trigger_name) tgenabled,
event_manipulation,
event_object_schema||'.'||event_object_table, action_statement,
'ALTER TABLE '||event_object_schema||'."'||event_object_table||'" DISABLE TRIGGER last_name_changes;' sql_disable,
'ALTER TABLE '||event_object_schema||'."'||event_object_table||'" ENABLE TRIGGER last_name_changes;' sql_enable
FROM information_schema.triggers
where trigger_schema='stock'
GROUP by trigger_catalog, trigger_schema,trigger_name, action_order, event_manipulation, event_object_schema, event_object_table, action_statement
ORDER BY event_object_schema ,event_object_table, trigger_name;

--- 4. Checking the placement of indexes in the correct tablespace. If an index is in the wrong location, execute the resulting script to move it to TS ts_stock_index.
[stock] psql>

select 'ALTER INDEX stock."'||obj.relname||'" SET TABLESPACE ts_stock_index;',
sch.nspname, obj.relname index_name,
tab.relname table_name , tbs.spcname
,ind.* from pg_index ind
join pg_class obj on obj."oid" = ind.indexrelid
join pg_class tab on tab."oid" = ind.indrelid
join pg_namespace sch on sch."oid" = obj.relnamespace
left join pg_tablespace tbs on obj.reltablespace = tbs."oid"
where sch.nspname = 'stock'
and tbs.spcname is null;

<<< Back Next >>>

Pages

05.08. Preparing PostgreSQL Database 'Passive Node' for Replication