Migracija podataka iz PostgreSQL 9.3 u 9.6 bazu

Koristeći Pg_dump i psql moguće je preneti podatke iz baze servera verzije 9.3 u bazu servera 9.6.
Obe baze moraju biti iste verzije npr. 10462.10536.20044, a baza na serveru 9.6 mora biti raščlanjena.

1. Tekuća godina - npr. PGS server 9.3 na računaru MOC, PGS server 9.6 na računaru 192.168.0.15, baza b7

Izvoz podataka komandom

pg_dump --no-owner --no-privileges --data-only --column-inserts --dbname=postgresql://korisničko_ime:lozinka@MOC:5432/b7 --schema=public --file="F:\b7tekuca.dump"

Otvoriti fajl nekim editorom teksta npr. Notepad++.
Promeniti liniju SET search_path = public, pg_catalog; u SET search_path = btek, bpub, public, pg_catalog;

Pre prve INSERT komande dodati:

• SET session_replication_role = replica; – isključuje strane ključeve
• Komande za brisanje podataka. Ako baza na serveru 9.6 ima podatke dodati DELETE FROM komande za svaku tabelu, inače samo za tabele koje u nultoj bazi imaju podatke.

Posle poslednje INSERT komande dodati:

• SET session_replication_role = DEFAULT; – uključuje strane ključeve

Uvoz podataka komandom:

psql --dbname=postgresql://korisničko_ime:lozinka@192.168.0.15:5432/b7 --file="F:\b7tekuca.dump"

2. Prethodna godina - npr. PGS server 9.3 na računaru MOC, PGS server 9.6 na računaru 192.168.0.15, baza b7, arhiva 2016

Izvoz podataka komandom

pg_dump --no-owner --no-privileges --data-only --column-inserts --dbname=postgresql://korisničko_ime:lozinka@MOC:5432/b7 --schema=2016 --file="F:\b7_2016.dump"

Na PostgreSQL serveru 9.6 u bazi b7 odraditi kopiranje scheme tekuće godine u praznu schemu a2016 komandom:

select _blnscloneschema('btek','a2016',false);

Dodati komande SET search_path, SET session_replication_role i DELETE FROM “2016”.naziv_tabele kao u primeru izvoza tekuće godine.

Uvoz podataka komandom:

psql --dbname=postgresql://korisničko_ime:lozinka@192.168.0.15:5432/b7 --file="F:\b7_2016.dump"

Komande za čišćenje tabela koje u nultoj bazi imaju podatke:

DELETE FROM "schemaname"._PGSTranslations WHERE True;
DELETE FROM "schemaname".Banke WHERE True;
DELETE FROM "schemaname".Brojac WHERE True;
DELETE FROM "schemaname".defCustData WHERE True;
DELETE FROM "schemaname".defDocTip WHERE True;
DELETE FROM "schemaname".defDocTipDes WHERE True;
DELETE FROM "schemaname".defDocTipPGP WHERE True;
DELETE FROM "schemaname".defDocTipRep WHERE True;
DELETE FROM "schemaname".defNalog WHERE True;
DELETE FROM "schemaname".defPars WHERE True;
DELETE FROM "schemaname".defSklTip WHERE True;
DELETE FROM "schemaname".DopOpis WHERE True;
DELETE FROM "schemaname".Dozvole WHERE True;
DELETE FROM "schemaname".EDIDocTipHook WHERE True;
DELETE FROM "schemaname".EDIDocTipSubs WHERE True;
DELETE FROM "schemaname".EDIDocTipTrig WHERE True;
DELETE FROM "schemaname".ElemAkcijaVar WHERE True;
DELETE FROM "schemaname".ElementiUrC WHERE True;
DELETE FROM "schemaname".FinReportSas WHERE True;
DELETE FROM "schemaname".Firme WHERE True;
DELETE FROM "schemaname".FirmeNfo WHERE True;
DELETE FROM "schemaname".GarantPlata WHERE True;
DELETE FROM "schemaname".JedOdnos WHERE True;
DELETE FROM "schemaname".KontneGrupe WHERE True;
DELETE FROM "schemaname".KontneGrupeSas WHERE True;
DELETE FROM "schemaname".KontneGrupeVals WHERE True;
DELETE FROM "schemaname".Korisnici WHERE True;
DELETE FROM "schemaname".Kupci WHERE True;
DELETE FROM "schemaname".KupciHistory WHERE True;
DELETE FROM "schemaname".Kurs WHERE True;
DELETE FROM "schemaname".LDDefPrihod WHERE True;
DELETE FROM "schemaname".LDDefPrihodMap WHERE True;
DELETE FROM "schemaname".LDDodaci WHERE True;
DELETE FROM "schemaname".LDDodaciDop WHERE True;
DELETE FROM "schemaname".LDDodaciHI WHERE True;
DELETE FROM "schemaname".LDDodaciRpt WHERE True;
DELETE FROM "schemaname".LDDodaciRT WHERE True;
DELETE FROM "schemaname".LDFilijale WHERE True;
DELETE FROM "schemaname".Lokacije WHERE True;
DELETE FROM "schemaname".LongDesc WHERE True;
DELETE FROM "schemaname".Nazivi WHERE True;
DELETE FROM "schemaname".NomAmort WHERE True;
DELETE FROM "schemaname".NomGrupe WHERE True;
DELETE FROM "schemaname".OrderQuotes WHERE True;
DELETE FROM "schemaname".OsnSred WHERE True;
DELETE FROM "schemaname".Podkatalozi WHERE True;
DELETE FROM "schemaname".Porezi WHERE True;
DELETE FROM "schemaname".Prezent WHERE True;
DELETE FROM "schemaname".Profili WHERE True;
DELETE FROM "schemaname".ProfiliSas WHERE True;
DELETE FROM "schemaname".RacTip WHERE True;
DELETE FROM "schemaname".RadVreme WHERE True;
DELETE FROM "schemaname".RJ WHERE True;
DELETE FROM "schemaname".SasRacTip WHERE True;
DELETE FROM "schemaname".ShortDesc WHERE True;
DELETE FROM "schemaname".Sifarnik WHERE True;
DELETE FROM "schemaname".SifarnikAlt WHERE True;
DELETE FROM "schemaname".SifPlacOblik WHERE True;
DELETE FROM "schemaname".SifPlacOsnov WHERE True;
DELETE FROM "schemaname".STOblikUvoza WHERE True;
DELETE FROM "schemaname".Struka WHERE True;
DELETE FROM "schemaname".SynT1 WHERE True;
DELETE FROM "schemaname".TarifneGrupe WHERE True;
DELETE FROM "schemaname".TipKor WHERE True;
DELETE FROM "schemaname".TmpDates WHERE True;
DELETE FROM "schemaname".UniBroj WHERE True;
DELETE FROM "schemaname".Valuta WHERE True;
DELETE FROM "schemaname".Ver WHERE True;
DELETE FROM "schemaname".VrstaPlac WHERE True;