StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The first and obvoius solution is to use <a href="http://www.postgresql.org/docs/9.1/static/dblink.html" rel="nofollow">dblink</a> assuming both servers are postgres.</p> <p>However you can write your own <em>partial replication daemon</em>. Below there is a simple example of replication of table <em>users</em> into SQlite database.</p> <p>We have table <em>users</em> in postgres <em>usersdb</em> database:</p> <pre><code>create table users ( id serial primary key, name text); </code></pre> <p>Create log table for <em>users</em>:</p> <pre><code>create table users_log ( id serial primary key, action text); </code></pre> <p>Create trigger on table <em>users</em>:</p> <pre><code>create or replace function users_trigger () returns trigger language plpgsql as $$ declare newaction text; begin if (TG_OP = 'UPDATE') then newaction:= 'update users set name = '''||new.name||''' where id = '||old.id::text; elsif (TG_OP = 'INSERT') then newaction:= 'insert into users values ('||new.id::text||', '''||new.name||''')'; elsif (TG_OP = 'DELETE') then newaction:= 'delete from users where id = '||old.id::text; end if; insert into users_log (action) values (newaction); return null; end; $$; create trigger users_trigger after insert or update or delete on users for each row execute procedure users_trigger(); </code></pre> <p>Create SQlite3 database <em>users.db</em> and table <em>users</em> in it:</p> <pre><code>create table users ( id integer primary key, name text); </code></pre> <p>Daemon script checks users_log every 2 seconds. If there are new actions then script makes them one by one in SQlite database and deletes from users_log:</p> <pre><code><?php $postgres = new PDO('pgsql:host=localhost port=5432 dbname=usersdb'); $sqlite = new PDO('sqlite:users.db'); $sql = 'select * from users_log order by id limit 1'; while (true) { sleep(2); while ($row = $postgres->query($sql)->fetch()) { $sqlite->query($row[1]); echo $row[1]. "\n"; $postgres->query("delete from users_log where id = $row[0]"); } } ?> </code></pre> <p>For simplicity the script is absolutely minimalist and should be fitted out with necessary exception handling. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload