Server OGG №1
***************************- ogg-srv01.oracak.com
1. Stop replicat r_quik2 (or ensure it is stopped)
. ~/.profile.postgres.gg21
./ggsci
dblogin sourcedb gg_dbquik2 useridalias ggadmin
stop replicat r_quik2
info all
2. Execute the export of tables with the --snapshot option (reference) in the insert_into_table format
Postgres cluster #2:
***************************
- db-pgsql03.oracak.com - master
4.1. In the first session, open the session, obtain the current LSN, and export the snapshot
Session 1:
[postgres]>$
psql -h db-pgsql01.oracak.com -d dbquik1 -U stock
dbquik1=> BEGIN;
BEGIN
dbquik1=*> SELECT pg_current_wal_lsn(), pg_export_snapshot();
pg_current_wal_lsn | pg_export_snapshot
--------------------+---------------------
18/18A8BC08 | 0000000A-00006388-1
• Note: Remember the LSN and the snapshot name (this data will be used below).
4.2. Perform the export with the --snapshot option
Session 2:
[postgres]>$
pg_dump -d dbquik1 -U stock -h db-pgsql01.oracak.com -f ./pgsql_to_pgsql.sql --data-only \
--snapshot='0000000A-00006388-1' \
--table=stock.BONDS \
--table=stock.BONDS_ARCH \
--table=stock.BONDS_SETTING \
--table=stock.BONDS_SETTING_ARCH \
--table=stock.BONDS_ZAJVKA \
--table=stock.BONDS_ZAJVKA_ARCH \
--table=stock.CLIENT_ACCOUNT \
--table=stock.CLIENT_ACCOUNT_ARCH \
--table=stock.DIVIDENTS \
--table=stock.JOB_LOGS \
--table=stock.PORTFOLIO \
--table=stock.PORTFOLIO_ARCH \
--table=stock.QUOTES \
--table=stock.QUOTES_ANALYST_15M \
--table=stock.QUOTES_ANALYST_1D \
--table=stock.QUOTES_ANALYST_1H \
--table=stock.QUOTES_ARCH \
--table=stock.QUOTES_VOLUME \
--table=stock.QUOTES_VOLUME_ARCH \
--table=stock.REPORT_BENEFIT \
--table=stock.REPORT_BENEFIT_COMPARATOR \
--table=stock.REPORT_EDU \
--table=stock.REPORT_EDU_COMPARATOR \
--table=stock.SDELKA \
--table=stock.SDELKA_ARCH \
--table=stock.SIGNAL \
--table=stock.SIGNAL_ARCH \
--table=stock.TRADING_EDU \
--table=stock.TRADING_PROD \
--table=stock.TRADING_PROD_ARCH \
--table=stock.p202* \
--table=stock.sys*
4.3. Close the first session and complete the snapshot export
Session 1:
dbquik1=*> exit
5. Perform the InitialLoad into the Postgres database on the passive side.
psql -h db-pgsql03.oracak.com -U stock -d dbquik2 -f pgsql_to_pgsql.sql
6. Enable all constraints and triggers on the receiver (Oracle database)
• Note: Refer to step 08.
addition (preparation of scripts):
--------------------------------------
-- script for deleting secondary keys
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 '%'
order by tc.table_name,tc.constraint_name;
-- 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
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 '%'
order by tc.table_name,tc.constraint_name;
<<< Back Next >>>
