WalMgr kasutamine - Redigeerimiste ajalugu

Imre: /* Londiste kasutamine */

2010-04-13T18:16:26Z

Londiste kasutamine

←Vanem redaktsioon		Redaktsioon: 13. aprill 2010, kell 18:16
163. rida:		163. rida:

	Skytools paigaldamise ajal ei pea olema süsteemis veel Pythoni PostgreSQL teeke.		Skytools paigaldamise ajal ei pea olema süsteemis veel Pythoni PostgreSQL teeke.

	~~===Londiste kasutamine===~~

	Londiste http://wiki.postgresql.org/wiki/Londiste_Tutorial on asünkroonne master/slave-tüüpi andmete replitseerimislahendus. Londiste tarkvara sisaldub Skytools komplektis ühe rakendusena. Omakorda, Londiste koosneb sellistest komponentidest

	* ticker.ini
	* p-to-s.ini

	~~Süsteemi peab olema paigaldatud pakett python-psycopg2~~

	~~# apt-get install python-psycopg2~~

	~~Londiste töötamise võiks kujutada sellise skeemiga~~

	~~MASTER SLAVE~~

	~~-----\|---------------------------------------\|-----~~
	~~_\|_ _\|_~~
	~~\| \| \| \|~~
	~~\|___\| \|___\|~~

	~~seadistusfail: ticker.ini p-to-s.ini~~
	~~protsess: python pgqadm.py ticker.ini ticker -d python londiste.py p-to-s.ini replay -d~~
	~~skeemid: pgq, londiste londiste~~

	~~====ticker.ini====~~

	~~Londiste kasutamiseks tuleb tekitada seadistusfail ticker.ini~~

	~~$ cat /home/postgres/londiste/ticker.ini~~
	~~[pgqadm]~~
	~~job_name = myticker_name~~
	~~db = dbname=baasinimi host=10.0.13.170 user=postgres password=parool port=5432~~
	~~# how often to run maintenance [seconds]~~
	~~maint_delay = 600~~

	~~# how often to check for activity [seconds]~~
	~~loop_delay = 0.1~~
	~~logfile = ~/londiste/log/%(job_name)s.log~~
	~~pidfile = ~/londiste/pid/%(job_name)s.pid~~

	~~kusjuures ühendussõnet saab testida selliselt~~

	~~$ python~~
	~~...~~
	~~>>> import psycopg2~~
	~~>>> con = psycopg2.connect('dbname=baasinimi host=10.0.13.170 \~~
	~~user=postgres password=parool port=5432')~~
	~~>>> Ctrl-D~~

	~~PGQ baasi osa paigaldamiseks tuleb seejärel öelda~~

	~~$ mkdir /home/postgres/londiste/log~~
	~~$ mkdir /home/postgres/londiste/pid~~
	~~$ export PYTHONPATH=/usr/local/skytools/lib/python2.4/site-packages~~
	~~$ /usr/local/skytools/bin/pgqadm.py /home/postgres/londiste/ticker.ini install~~
	~~2009-11-03 13:28:41,793 28491 INFO plpgsql is installed~~
	~~2009-11-03 13:28:41,795 28491 INFO txid_current_snapshot is installed~~
	~~2009-11-03 13:28:41,796 28491 INFO Installing pgq~~
	~~2009-11-03 13:28:41,796 28491 INFO Reading from \~~
	~~/usr/local/skytools/share/skytools/pgq.sql~~

	~~Tulemusena tekib andmebaasi baasinimi skeem pgq.~~

	~~Tickeri käivitamiseks tuleb öelda~~

	~~$ /usr/local/skytools/bin/pgqadm.py /home/postgres/londiste/ticker.ini ticker -d~~

	~~ja jääb käima vastav protsess.~~

	~~====p-to-s.ini seadistamine====~~

	~~$ cat p-to-s.ini~~
	~~[londiste]~~
	~~job_name = test_to_subcriber~~

	~~provider_db = dbname=portaal host=10.0.13.170 user=postgres \~~
	~~password=parool port=5433~~
	~~subscriber_db = dbname=portaal host=10.0.13.177 user=postgres \~~
	~~password=parool port=5432~~

	~~# it will be used as sql ident so no dots/spaces~~
	~~pgq_queue_name = londiste.replica~~

	~~logfile = /home/postgres/londiste/log/%(job_name)s.log~~
	~~pidfile = /home/postgres/londiste/pid/%(job_name)s.pid~~

	~~Logiserverist öelda~~

	~~$ londiste.py p-to-s.ini provider install~~
	~~$ londiste.py p-to-s.ini subscriber install~~

	~~ning käivitada deemon~~

	~~$ londiste.py p-to-s.ini replay -d~~

	~~Andmete replitseerimiseks tuleb logiserverisse moodustada vastavad tableid ning seejärel öelda logiserverist~~

	~~$ londiste.py p-to-s.ini provider add public.T1 public.T2~~
	~~$ londiste.py p-to-s.ini subscriber add public.T1 public.T2~~

	~~Subscriberi eemaldamiseks sobib öelda~~

	~~$ londiste.py p-to-s.ini subscriber remove skeeminimi.tabelinimi~~

	~~Staatust saab küsida~~

	~~$ londiste.py p-to-s.ini subscriber tables~~
	~~Table State~~
	~~skeeminimi.tabelinimi in-copy~~

Imre: /* PL/Proxy */

2010-04-12T17:31:23Z

PL/Proxy

←Vanem redaktsioon		Redaktsioon: 12. aprill 2010, kell 17:31
273. rida:		273. rida:
	Table State		Table State
	skeeminimi.tabelinimi in-copy		skeeminimi.tabelinimi in-copy

	~~===PL/Proxy===~~

	~~PL/Proxy https://developer.skype.com/SkypeGarage/DbProjects/PlProxy on PL/Proxy.~~

	~~====Tarkvara paigaldamine====~~

	~~$ wget http://pgfoundry.org/frs/download.php/2447/plproxy-2.0.9.tar.gz~~
	~~$ tar ...~~
	~~$ make~~
	~~$ make install~~
	~~/bin/sh /usr/local/postgresql/lib/pgxs/src/makefiles/../../config/install-sh -c \~~
	~~-m 644 plproxy.sql '/usr/local/postgresql/share/contrib'~~
	~~/bin/sh /usr/local/postgresql/lib/pgxs/src/makefiles/../../config/install-sh -c \~~
	~~-m 755 libplproxy.so.0.0 '/usr/local/postgresql/lib/plproxy.so'~~

	~~ja tulemusena lisanduvad süsteemi kaks faili~~

	~~/usr/local/postgresql/share/contrib/plproxy.sql~~
	~~/usr/local/postgresql/lib/plproxy.so~~

	~~Seejärel tuleb baasi keele lisamiseks täita plproxy.sql skript, nt öeldes~~

	~~$ psql -U postgres -h localhost portaal < /usr/local/postgresql/share/contrib/plproxy.sql~~

	~~Iseenesest see skript sisaldab lauseid~~

	~~-- handler function~~
	~~CREATE FUNCTION plproxy_call_handler ()~~
	~~RETURNS language_handler AS '$libdir/plproxy' LANGUAGE C;~~

	~~-- language~~
	~~CREATE LANGUAGE plproxy HANDLER plproxy_call_handler;~~

	~~====Kasutamine====~~

	~~Olgu eesmärgiks andmebaasist küsida päringutele vastuseid logibaasist~~

	~~Logibaasis sobib kasutada nt~~

	~~CREATE OR REPLACE FUNCTION pwa.logiserver(i_tingimus text)~~
	~~RETURNS SETOF pwa.logi AS~~
	~~$BODY$~~
	~~BEGIN~~
	~~RETURN QUERY EXECUTE ('select * from pwa.logi where '\|\|COALESCE(i_tingimus, 'true limit 1'));~~
	~~RETURN;~~
	~~END;~~
	~~$BODY$~~
	~~LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER~~
	~~COST 100~~
	~~ROWS 1000;~~
	~~ALTER FUNCTION pwa.logiserver(text) OWNER TO logi;~~

	~~ja andmebaasis kasutada nt~~

	~~CREATE OR REPLACE FUNCTION pwa.logiserver_live(i_tingimus text)~~
	~~RETURNS SETOF pwa.logi AS~~
	~~$BODY$~~
	~~CONNECT 'dbname=portaal host=10.0.6.177 user=logi password=parool';~~
	~~SELECT * from pwa.logiserver(i_tingimus);~~
	~~$BODY$~~
	~~LANGUAGE 'plproxy' VOLATILE~~
	~~COST 100~~
	~~ROWS 1000;~~
	~~ALTER FUNCTION pwa.logiserver_live(text) OWNER TO sysadm;~~

Imre: /* Kasutamine */

2009-12-21T12:26:33Z

Kasutamine

←Vanem redaktsioon		Redaktsioon: 21. detsember 2009, kell 12:26
309. rida:		309. rida:
	====Kasutamine====		====Kasutamine====

	~~TODO~~		Olgu eesmärgiks andmebaasist küsida päringutele vastuseid logibaasist

			Logibaasis sobib kasutada nt

			CREATE OR REPLACE FUNCTION pwa.logiserver(i_tingimus text)
			RETURNS SETOF pwa.logi AS
			$BODY$
			BEGIN
			RETURN QUERY EXECUTE ('select * from pwa.logi where '\|\|COALESCE(i_tingimus, 'true limit 1'));
			RETURN;
			END;
			$BODY$
			LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
			COST 100
			ROWS 1000;
			ALTER FUNCTION pwa.logiserver(text) OWNER TO logi;

			ja andmebaasis kasutada nt

			CREATE OR REPLACE FUNCTION pwa.logiserver_live(i_tingimus text)
			RETURNS SETOF pwa.logi AS
			$BODY$
			CONNECT 'dbname=portaal host=10.0.6.177 user=logi password=parool';
			SELECT * from pwa.logiserver(i_tingimus);
			$BODY$
			LANGUAGE 'plproxy' VOLATILE
			COST 100
			ROWS 1000;
			ALTER FUNCTION pwa.logiserver_live(text) OWNER TO sysadm;

Imre: /* Tarkvara paigaldamine */

2009-12-18T13:47:35Z

Tarkvara paigaldamine

←Vanem redaktsioon		Redaktsioon: 18. detsember 2009, kell 13:47
284. rida:		284. rida:
	$ make		$ make
	$ make install		$ make install
			/bin/sh /usr/local/postgresql/lib/pgxs/src/makefiles/../../config/install-sh -c \
			-m 644 plproxy.sql '/usr/local/postgresql/share/contrib'
			/bin/sh /usr/local/postgresql/lib/pgxs/src/makefiles/../../config/install-sh -c \
			-m 755 libplproxy.so.0.0 '/usr/local/postgresql/lib/plproxy.so'

	ja tulemusena lisanduvad süsteemi kaks faili		ja tulemusena lisanduvad süsteemi kaks faili

Imre: /* p-to-s.ini seadistamine */

2009-12-17T13:54:00Z

p-to-s.ini seadistamine

←Vanem redaktsioon		Redaktsioon: 17. detsember 2009, kell 13:54
272. rida:		272. rida:
	$ londiste.py p-to-s.ini subscriber tables		$ londiste.py p-to-s.ini subscriber tables
	Table State		Table State
	~~pwa~~.~~logi~~ in-copy		skeeminimi.tabelinimi in-copy

	===PL/Proxy===		===PL/Proxy===

Imre: /* p-to-s.ini seadistamine */

2009-12-17T13:53:36Z

p-to-s.ini seadistamine

←Vanem redaktsioon		Redaktsioon: 17. detsember 2009, kell 13:53
267. rida:		267. rida:

	$ londiste.py p-to-s.ini subscriber remove skeeminimi.tabelinimi		$ londiste.py p-to-s.ini subscriber remove skeeminimi.tabelinimi

			Staatust saab küsida

			$ londiste.py p-to-s.ini subscriber tables
			Table State
			pwa.logi in-copy

	===PL/Proxy===		===PL/Proxy===

Imre: /* p-to-s.ini seadistamine */

2009-12-17T13:34:29Z

p-to-s.ini seadistamine

←Vanem redaktsioon		Redaktsioon: 17. detsember 2009, kell 13:34
263. rida:		263. rida:
	$ londiste.py p-to-s.ini provider add public.T1 public.T2		$ londiste.py p-to-s.ini provider add public.T1 public.T2
	$ londiste.py p-to-s.ini subscriber add public.T1 public.T2		$ londiste.py p-to-s.ini subscriber add public.T1 public.T2

			Subscriberi eemaldamiseks sobib öelda

			$ londiste.py p-to-s.ini subscriber remove skeeminimi.tabelinimi

	===PL/Proxy===		===PL/Proxy===

Imre: /* Skytools paigaldamine lähtetekstist */

2009-12-16T17:08:47Z

Skytools paigaldamine lähtetekstist

←Vanem redaktsioon		Redaktsioon: 16. detsember 2009, kell 17:08
161. rida:		161. rida:
	/usr/local/postgresql/lib/logtriga.so		/usr/local/postgresql/lib/logtriga.so
	/usr/local/postgresql/lib/pgq_lowlevel.so		/usr/local/postgresql/lib/pgq_lowlevel.so

			Skytools paigaldamise ajal ei pea olema süsteemis veel Pythoni PostgreSQL teeke.

	===Londiste kasutamine===		===Londiste kasutamine===

Imre: /* Londiste kasutamine */

2009-12-16T10:04:10Z

Londiste kasutamine

←Vanem redaktsioon		Redaktsioon: 16. detsember 2009, kell 10:04
184. rida:		184. rida:
	seadistusfail: ticker.ini p-to-s.ini		seadistusfail: ticker.ini p-to-s.ini
	protsess: python pgqadm.py ticker.ini ticker -d python londiste.py p-to-s.ini replay -d		protsess: python pgqadm.py ticker.ini ticker -d python londiste.py p-to-s.ini replay -d
			skeemid: pgq, londiste londiste

	====ticker.ini====		====ticker.ini====

Imre: /* ticker.ini */

2009-12-16T09:44:10Z

ticker.ini

←Vanem redaktsioon		Redaktsioon: 16. detsember 2009, kell 09:44
221. rida:		221. rida:
	2009-11-03 13:28:41,796 28491 INFO Reading from \		2009-11-03 13:28:41,796 28491 INFO Reading from \
	/usr/local/skytools/share/skytools/pgq.sql		/usr/local/skytools/share/skytools/pgq.sql

			Tulemusena tekib andmebaasi baasinimi skeem pgq.

	Tickeri käivitamiseks tuleb öelda		Tickeri käivitamiseks tuleb öelda