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;