DBA Tips: 06.04. Preparing the PostgreSQL database (target)

1. Creating a user for replication
[postgres@db-pgsql01]$> psql

-- ggadmin for postgres
create role ggadmin with password '***';
alter user ggadmin with replication;
alter user ggadmin with login;
-- выдача необходимых грантов
GRANT CONNECT ON DATABASE dbquik TO ggadmin;
GRANT USAGE ON SCHEMA stock TO ggadmin;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA stock TO ggadmin;
ALTER DEFAULT PRIVILEGES FOR USER ggadmin IN SCHEMA stock GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ggadmin;
-- создание схемы для пользователя ggadmin
create schema ggadmin;
alter schema ggadminowner to ggadmin;

2. Disabling all constraints and triggers on the target (PostgreSQL) database.

--- см. скрипт ниже

3. Cleaning tables before upload (БД Postgres)
[postgres@db-pgsql01]$> psql

\c dbquik
delete from stock.bonds;
delete from stock.bonds_setting;
delete from stock.bonds_zajvka;
delete from stock.client_account;
delete from stock.dividents;
delete from stock.job_logs;
delete from stock.portfolio;
delete from stock.quotes;
delete from stock.quotes_analyst_15m;
delete from stock.quotes_analyst_1d;
delete from stock.quotes_analyst_1h;
delete from stock.quotes_volume;
delete from stock.report_benefit;
delete from stock.report_benefit_comparator;
delete from stock.report_edu;
delete from stock.report_edu_comparator;
delete from stock.sdelka;
delete from stock.signal;
delete from stock.trading_edu;
delete from stock.trading_prod;

Supplementary (Script Preparation):
--------------------------------------
-- 1. Preparation of the script for creating secondary keys

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,
'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
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_name like '%'
order by tc.table_name,tc.constraint_name;

-- 2. Cleaning tables before loading

select 'delete from '||schemaname||'.'||tablename||';' from pg_tables where schemaname ='stock' and tablename not like 'sys%' order by 1;

<<< Back Next >>>

Pages

06.04. Preparing the PostgreSQL database (target)