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.
Pre migracije podataka svi korisnici baze moraju izaći iz programa (Balans, kasa…) jer će se preneti neažurirana tabela UniBroj pa će njene vrednosti biti početne za unibroj sekvence na novom serveru što će dovesti do grešaka tipa “Duplicate key value…”
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;