omniti-labs / mimeo Goto Github PK

View Code? Open in Web Editor NEW

76.0 76.0 13.0 1.25 MB

Extension for specialized, per-table replication between PostgreSQL instances

Makefile 0.02% Python 0.09% PLpgSQL 99.89%

logical-replication plpgsql postgresql postgresql-extension table-replication

mimeo's People

Stargazers

Watchers

Forkers

payals denishpatel palmerj jsoler thorpulse gavioto sifoxdevteam syaroslavtsev keithf4 zhuomingliang rekgrpth rivthor

mimeo's Issues

Snapshot replicated tables/views owner question

Could you please clarify how the owner of the replicated tables is determined?

Currently all my replicated tables/views are owned by user postgres, although i'm executing the replication while logged in under a different user. Is there a way to change this (e.g. have all the tables owned by the user that runs the replication function)?

pg_jobmon and mimeo Cannot Coexist

With the pg_jobmon extension already installed:

sudo psql -U postgres -d reporting -c 'CREATE EXTENSION "mimeo";'
ERROR:  relation "dblink_mapping" already exists

These are pretty closely related projects, no? Should they be able to coexist in a single schema?

Source table in Read-Only Streaming Replication Slave server

I'm having problems with a snapshot when source table is from Pg 9.2 slave server using streaming replication. Always says "NOTICE: Remote table has not had any writes. Skipping data pull", and it's a table with lots of insert mostly. I dunno if the slave cannot see the insertions in master server.

I also have some snapshots using an pg 9.4 Slave (with replication slot) and I don't have this issue.

(Question) Many sources for one destination for logdel replication

Am I understanding correct, that:
It is not possible to configure many sources tables to replicate in one destination table for logdel replication?
Should I use things like PGSQL INHERITS features for such configuration?

Handle mixed-case, special characters and reserved words in object names

Currently if a table name uses non-standard characters, mimeo fails to work properly.

1.3.0 error when replicating view

When we try replicate view with command

SELECT mimeo.snapshot_maker('public.view_name',1)

there's error

ERROR: Source table public.view_name not found for dblink id 1

which seems to be raised in manage_dest_table.sql line 73

Works OK in 1.2.2

Incremental replication via serial id

Currently incremental replication only works with timestamp fields. Add ability to use integer based columns as well.

Snapshot replication with index recreation broken?

When trying to replicate a table using snapshot replication i'm getting the error listed below (with default parameter values and debug enabled). Looks like it's related to the p_index parameter, as replication works when it's disabled.

destination=# select mimeo.snapshot_maker('public.test_repl1',1, null, true, null, null, true, true);
NOTICE:  attempting first snapshot
NOTICE:  v_refresh_snap: public.test_repl1_snap1
CONTEXT:  SQL statement "SELECT gdb(p_debug,'v_refresh_snap: '||v_refresh_snap::text)"
PL/pgSQL function mimeo.refresh_snap(text,boolean,boolean,boolean,boolean) line 141 at PERFORM
SQL statement "SELECT mimeo.refresh_snap('public.test_repl1', p_index := true, p_pulldata := true, p_debug := true)"
PL/pgSQL function mimeo.snapshot_maker(text,integer,text,boolean,text[],text,boolean,boolean) line 50 at EXECUTE statement
NOTICE:  Getting table columns and creating destination table if it doesn't exist
CONTEXT:  SQL statement "SELECT gdb(p_debug, 'Getting table columns and creating destination table if it doesn''t exist')"
PL/pgSQL function mimeo.refresh_snap(text,boolean,boolean,boolean,boolean) line 144 at PERFORM
SQL statement "SELECT mimeo.refresh_snap('public.test_repl1', p_index := true, p_pulldata := true, p_debug := true)"
PL/pgSQL function mimeo.snapshot_maker(text,integer,text,boolean,text[],text,boolean,boolean) line 50 at EXECUTE statement
NOTICE:  v_remote_sql: SELECT cols, cols_n_types FROM dblink('manage_dest_table_dblink_public.test_repl1', 'SELECT array_agg(attname) as cols, array_agg(attname||'' ''||format_type(atttypid, atttypmod)::text) as cols_n_types FROM pg_attribute WHERE attrelid = ''public.test_repl1''::regclass AND attnum > 0 AND attisdropped is false') t (cols text[], cols_n_types text[])
CONTEXT:  SQL statement "SELECT gdb(p_debug,'v_remote_sql: '||v_remote_sql)"
PL/pgSQL function manage_dest_table(text,text,boolean) line 57 at PERFORM
SQL statement "SELECT p_table_exists, p_cols, p_cols_n_types FROM manage_dest_table(v_dest_table, v_snap, p_debug)"
PL/pgSQL function mimeo.refresh_snap(text,boolean,boolean,boolean,boolean) line 145 at SQL statement
SQL statement "SELECT mimeo.refresh_snap('public.test_repl1', p_index := true, p_pulldata := true, p_debug := true)"
PL/pgSQL function mimeo.snapshot_maker(text,integer,text,boolean,text[],text,boolean,boolean) line 50 at EXECUTE statement
NOTICE:  p_cols: {id,name}
CONTEXT:  SQL statement "SELECT gdb(p_debug,'p_cols: {'|| array_to_string(p_cols, ',') ||'}')"
PL/pgSQL function manage_dest_table(text,text,boolean) line 59 at PERFORM
SQL statement "SELECT p_table_exists, p_cols, p_cols_n_types FROM manage_dest_table(v_dest_table, v_snap, p_debug)"
PL/pgSQL function mimeo.refresh_snap(text,boolean,boolean,boolean,boolean) line 145 at SQL statement
SQL statement "SELECT mimeo.refresh_snap('public.test_repl1', p_index := true, p_pulldata := true, p_debug := true)"
PL/pgSQL function mimeo.snapshot_maker(text,integer,text,boolean,text[],text,boolean,boolean) line 50 at EXECUTE statement
NOTICE:  p_cols_n_types: {id integer,name character varying}
CONTEXT:  SQL statement "SELECT gdb(p_debug,'p_cols_n_types: {'|| array_to_string(p_cols_n_types, ',') ||'}')"
PL/pgSQL function manage_dest_table(text,text,boolean) line 60 at PERFORM
SQL statement "SELECT p_table_exists, p_cols, p_cols_n_types FROM manage_dest_table(v_dest_table, v_snap, p_debug)"
PL/pgSQL function mimeo.refresh_snap(text,boolean,boolean,boolean,boolean) line 145 at SQL statement
SQL statement "SELECT mimeo.refresh_snap('public.test_repl1', p_index := true, p_pulldata := true, p_debug := true)"
PL/pgSQL function mimeo.snapshot_maker(text,integer,text,boolean,text[],text,boolean,boolean) line 50 at EXECUTE statement
NOTICE:  v_create_sql: CREATE TABLE public.test_repl1_snap1 (id integer,name character varying)
CONTEXT:  SQL statement "SELECT gdb(p_debug,'v_create_sql: '||v_create_sql::text)"
PL/pgSQL function manage_dest_table(text,text,boolean) line 70 at PERFORM
SQL statement "SELECT p_table_exists, p_cols, p_cols_n_types FROM manage_dest_table(v_dest_table, v_snap, p_debug)"
PL/pgSQL function mimeo.refresh_snap(text,boolean,boolean,boolean,boolean) line 145 at SQL statement
SQL statement "SELECT mimeo.refresh_snap('public.test_repl1', p_index := true, p_pulldata := true, p_debug := true)"
PL/pgSQL function mimeo.snapshot_maker(text,integer,text,boolean,text[],text,boolean,boolean) line 50 at EXECUTE statement
NOTICE:  Fetching rows in batches: 2 done so far.
CONTEXT:  SQL statement "SELECT gdb(p_debug,'Fetching rows in batches: '||v_total||' done so far.')"
PL/pgSQL function mimeo.refresh_snap(text,boolean,boolean,boolean,boolean) line 242 at PERFORM
SQL statement "SELECT mimeo.refresh_snap('public.test_repl1', p_index := true, p_pulldata := true, p_debug := true)"
PL/pgSQL function mimeo.snapshot_maker(text,integer,text,boolean,text[],text,boolean,boolean) line 50 at EXECUTE statement
NOTICE:  Creating indexes
CONTEXT:  SQL statement "SELECT gdb(p_debug, 'Creating indexes')"
PL/pgSQL function mimeo.refresh_snap(text,boolean,boolean,boolean,boolean) line 255 at PERFORM
SQL statement "SELECT mimeo.refresh_snap('public.test_repl1', p_index := true, p_pulldata := true, p_debug := true)"
PL/pgSQL function mimeo.snapshot_maker(text,integer,text,boolean,text[],text,boolean,boolean) line 50 at EXECUTE statement
ERROR:  function dblink.dblink(text, unknown) does not exist
CONTEXT:  SQL statement "SELECT mimeo.refresh_snap('public.test_repl1', p_index := true, p_pulldata := true, p_debug := true)"
PL/pgSQL function mimeo.snapshot_maker(text,integer,text,boolean,text[],text,boolean,boolean) line 50 at EXECUTE statement

With disabled index recreation everything works:

destination=# select mimeo.snapshot_maker('public.test_repl1',1, null, false);
NOTICE:  attempting first snapshot
NOTICE:  attempting second snapshot
NOTICE:  Done
 snapshot_maker 
----------------

(1 row)

I've noticed that this behavior doesn't depend on presence of indexes on the source table i.e. doesn't work with/without indexes.

Snapshot UNLOGGED source tables

Hello. I have a question.

Source postgresql have a table created by: CREATE UNLOGGED TABLE stat;
Because, want to disable WAL and no want replication of this table on source postgresql cluster.

I want use snapshot mimeo replication for periodic copy this tables to destination postgresql cluster, but I want on destination postgresql have standard table, NOT UNLOGGED. Because I need to replicate this table on standby postgresql servers in destination postgresql cluster.

It`s possible? Thanks.

(Question) ERROR: »foo« is not a view

I'm trying to setup a snapshot replication but when running SELECT mimeo.snapshot_maker('public.foo', 1); the error ERROR: »foo« is not a view is thrown.

I'm using Debian with PostgreSQL 9.1.15. The table to replicate looks like this:

CREATE TABLE foo (
    f_id serial NOT NULL,
    ...
    CONSTRAINT foo_pkey PRIMARY KEY (f_id)
);

The following commands were executed (as user postgres):

destinationdb=# CREATE EXTENSION dblink;
destinationdb=# CREATE SCHEMA mimeo;
destinationdb=# CREATE EXTENSION pg_jobmon SCHEMA mimeo;
destinationdb=# CREATE EXTENSION mimeo SCHEMA mimeo;
destinationdb=# GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA mimeo TO postgres;
destinationdb=# INSERT INTO mimeo.dblink_mapping_mimeo (data_source, username, pwd) VALUES ('host=localhost port=5432 dbname=sourcedb', 'postgres', 'password');
destinationdb=# SELECT mimeo.snapshot_maker('public.foo', 1);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "foo_snap1_f_id_pk" for table "foo_snap1"
CONTEXT:  SQL statement "ALTER TABLE public.foo_snap1 ADD CONSTRAINT foo_snap1_f_id_pk PRIMARY KEY ("f_id")"
PL/pgSQL function "create_index" line 82 at EXECUTE statement
SQL statement "SELECT create_index(v_dest_table, v_src_schema_name, v_src_table_name, v_snap, p_debug)"
PL/pgSQL function "refresh_snap" line 338 at PERFORM
SQL statement "SELECT mimeo.refresh_snap('public.foo', p_index := true, p_pulldata := true, p_debug := false)"
PL/pgSQL function "snapshot_maker" line 74 at EXECUTE statement
ERROR:  "foo" is not a view

What did I do wrong? Can Mimeo only replicate views?

was v1.3.4 retagged?

My records show https://github.com/omniti-labs/mimeo/archive/v1.3.4.zip with sha1 9063ba35dea4b9121850392bf181f978d745fc12 on March 5.

But now this was apparently repackaged around April 14 with different contents.

Could you clarify that, and make a new tag possibly?

Incremental replication on serial column not retrieving last record

Source
Version string PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 32-bit

Destination
Version string PostgreSQL 9.1.11 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (Gentoo 4.7.3-r1 p1.4, pie-0.5.5) 4.7.3, 64-bit

SELECT mimeo.refresh_inserter('schema.table') or SELECT mimeo.refresh_inserter('schema.table', null, true) it fails to retrieve the last record. In this case, the max(id) in the destination table is 332000, the table mimeo.refresh_config_inserter_serial has as the last_value==332000 but the source table has a max(id)==332100. My sequence is incrementing by 100 (this is because there are numerous sources and to avoid id collision, this is my work around).

FUNCTION mimeo.create_index

In this function is direct name of dblink schema:

SELECT set_config INTO v_conf FROM dblink.dblink(v_dblink_name, 'SELECT set_config(''search_path'', '''', false)::text') t (set_config text);

Recommend Projects

React

A declarative, efficient, and flexible JavaScript library for building user interfaces.
Vue.js

🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript

TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
TensorFlow

An Open Source Machine Learning Framework for Everyone
Django

The Web framework for perfectionists with deadlines.
Laravel

A PHP framework for web artisans
D3

Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

javascript

JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
web

Some thing interesting about web. New door for the world.
server

A server is a program made to process requests and deliver data to clients.
Machine learning

Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Visualization

Some thing interesting about visualization, use data art
Game

Some thing interesting about game, make everyone happy.

Recommend Org

Facebook

We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft

Open source projects and samples from Microsoft.
Google

Google ❤️ Open Source for everyone.
Alibaba

Alibaba Open Source for everyone
D3

Data-Driven Documents codes.
Tencent

China tencent open source team.