Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
kb:pg:migracija_iz_93_u_96 [2019/01/24 14:48] – nessa | kb:pg:migracija_iz_93_u_96 [2019/04/16 11:56] (current) – milica | ||
---|---|---|---|
Line 1: | Line 1: | ||
====== Migracija podataka iz PostgreSQL 9.3 u 9.6 bazu ====== | ====== 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. | + | 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 " | ||
- | 1. **Tekuća godina** - npr. baza b7 | + | 1. **Tekuća godina** - npr. PGS server 9.3 na računaru MOC, PGS server 9.6 na računaru 192.168.0.15, |
- | Izvesti podatke | + | **Izvoz **podataka |
- | pg_dump | + | < |
+ | pg_dump | ||
+ | </ | ||
- | '' | + | Otvoriti fajl nekim editorom teksta npr. Notepad++.\\ |
+ | Promeniti | ||
\\ | \\ | ||
- | //Pre prve INSERT komande dodati:\\ | + | Pre prve INSERT komande dodati: |
- | a) SET session_replication_role = replica; – isključuje strane ključeve\\ | + | |
- | b) 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 = replica; – isključuje strane ključeve |
- | SET session_replication_role = DEFAULT; – uključuje strane ključeve //'' | + | |
+ | |||
+ | Posle poslednje INSERT komande dodati: | ||
+ | |||
+ | * SET session_replication_role = DEFAULT; – uključuje strane ključeve | ||
+ | |||
+ | Uvoz podataka komandom: | ||
+ | |||
+ | < | ||
+ | psql --dbname=postgresql: | ||
+ | </ | ||
+ | |||
+ | 2. **Prethodna godina** | ||
+ | |||
+ | **Izvoz **podataka komandom | ||
+ | |||
+ | < | ||
+ | pg_dump --no-owner --no-privileges --data-only --column-inserts --dbname=postgresql:// | ||
+ | </ | ||
+ | |||
+ | Na PostgreSQL serveru 9.6 u bazi b7 odraditi kopiranje scheme tekuće godine u praznu schemu a2016 komandom: | ||
+ | |||
+ | < | ||
+ | select _blnscloneschema('btek',' | ||
+ | </ | ||
+ | |||
+ | Dodati komande SET search_path, | ||
+ | |||
+ | Uvoz podataka komandom: | ||
+ | |||
+ | < | ||
+ | psql --dbname=postgresql:// | ||
+ | </ | ||
+ | |||
+ | Komande za čišćenje tabela koje u nultoj bazi imaju podatke: | ||
+ | |||
+ | < | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | DELETE FROM " | ||
+ | </ | ||