Does cstore replicate with postgresql 9.4 stream replication about cstore_fdw HOT 29 CLOSED

@mtuncer, could you advise any progress here?

from cstore_fdw.

Need it too

from cstore_fdw.

cstore_fdw doesn't support streaming replication yet.

from cstore_fdw.

@pykello: I had a couple of people ask how to do backup or replication with cstore_fdw… if they put the data files on a DRBD-backed volume, and if another host had the same table created, could it read from a replica of that volume? Can we investigate whether such a configuration is feasible?

from cstore_fdw.

XtreemFS may work as well.

from cstore_fdw.

For now I've been doing uploads to S3 (and then pulls on the other side), the replica sees the created foreign tables just fine, just never seems to replay the data import into the cstore backed foreign table. And thus I get error hints like "try inserting data into the table" and such.

from cstore_fdw.

Most likely reason is that cstore_fdw OID is different in the replica. cstore_fdw attempts to find data and header files using DatabaseID, and table OID, since those could be different in the replicated database, it could not find them.

You probably have 2 cstore data file set at the replica's data directory.

$DATA/OLD_DATABASE_ID/OLD_CSTORE_TABLE_ID
$DATA/OLD_DATABASE_ID/OLD_CSTORE_TABLE_ID.footer
and
$DATA/NEW_DATABASE_ID/NEW_CSTORE_TABLE_ID
$DATA/NEW_DATABASE_ID/NEW_CSTORE_TABLE_ID.footer

Please override new data and footer file from the content from old data and footer file.

DISCLAIMER : if you have ALTERED the table by adding or removing columns in the old database at any given time simply copying cstore_fdw data files is not enough. In this case your best option will be "copy out" from old database and "copy into" new database

from cstore_fdw.

Actually the cstore data directory ($PGDATA/cstore_fdw for me) was just never created in the replica at all. The footer id the replica was complaining about did match the id from the primary database if I recall correctly..

from cstore_fdw.

are you sure cstore_fdw folder is missing ? this means that
1 - cstore_fdw extension is not created, or extension creation has failed
2 - cstore_fdw data files are not copied

cstore_fdw could not operate even with fixing table ids if those items are missing.

from cstore_fdw.

Yes it was missing, but I just realized I failed to mention that I was using hot standby without streaming, which may change the behavior a bit. But for clarity here are the steps I took to get to that point:

• Set up hot standby replication between a master & slave database (which are both empty for the most part), having built the cstore extension on both machines (but not creating the extension in postgres yet)
• Ran a set of migrations on the master, which includes creating the cstore extension as well as creating a foreign table w/ the cstore server
• And finally bulk importing data into that foreign table using a select query from another existing table, which is also present (& filled with data) on the replica

So the cstore extension was successfully created on the replica, and the replica does see that foreign table, just not the data from the initial bulk import.

from cstore_fdw.

There are 2 ways to bulk import data into cstore table
1 . copy from a file
2 . insert statement

cstore supports inserts in form "insert into cstore_table select * from another_table"
if your bulk insert mechanism inserts row by row, then those inserts will fail.

I did not fully understand how you inserted into cstore. Could you elaborate a bit ?

from cstore_fdw.

Sure, so the bulk insert was done via a statement like:
insert into my_cstore_table select * from source_table

Which I thought would be also ran on the replica, which does also have access to the source table. Perhaps it was an issue with permissions? I'm not sure what kind of access inherently comes with a replication user in Postgres though on creation I did alter the default privileges on tables & sequences in the public schema to allow inserts.

from cstore_fdw.

It could be ordering. insert statement might be executed before the source_table is populated.
Could you verify that statement is executed ?

from cstore_fdw.

Yes that would definitely have been populated on both sides as I had tested queries on the replica before the insert statement.

from cstore_fdw.

Quick update, I had to drop all the content from the master & uninstalled the cstore extensions & servers then recreated them, changing the footer ID. So I can verify that the replica does indeed look for the updated footer ID but it simply isn't copied over and so it isn't found. Additionally it looks like the master deleted the old footer data along with the data sharing the same ID, adding the files w/ the new ID in their place. However on the replica the old ID (which I had copied via S3) is still there and even has the original data so apparently it does not correctly replay a drop foreign table either.

from cstore_fdw.

Streaming replication only replicates the WAL log, which only contains data written to regular tables. Streaming replication does not copy over cstore_fdw files and it does not replay queries, hence it also does not create or remove cstore_fdw files.

from cstore_fdw.

We are about to support streaming replication in cstore_fdw.

from cstore_fdw.

it does now. merged to develop branch

from cstore_fdw.

Does this work with pg9.6? I've just tried building this develop branch and it does not appear that cstore tables are replicated to standby systems automatically with streaming replication.

from cstore_fdw.

It should work. In fact it is developed on pg96 first. Can you confirm regular tables get replicated on your setup ?

from cstore_fdw.

Yup, this is my method of testing:

create table test_inheritence ( id bigserial primary key, type text not null, message text not null );
create table t1_inherit (primary key(id)) inherits(test_inheritence);
create foreign table t2_cstore () inherits(test_inheritence) server cstore_server options(compression 'pglz');

insert into t1_inherit (type, message) values ('test','message');
insert into t1_inherit (type, message) values ('test','hello world');
insert into t1_inherit (type, message) values ('test','goodbye world');

start transaction;
insert into t2_cstore select * from t1_inherit where id < 3;
delete from t1_inherit where id < 3;
commit;

select * from test_inheritence;

On the primary system, the output of the last statement is:

id | type |    message
----+------+---------------
  3 | test | goodbye world
  1 | test | message
  2 | test | hello world
(3 rows)

On a secondary system it is this:

select * from test_inheritence;
ERROR:  could not open file "/var/lib/pgsql/9.6/data/cstore_fdw/16386/16536.footer" for reading: No such file or directory
HINT:  Try copying in data to the table.

A selection from the other inherited table works fine though:

select * from t1_inherit ;
 id | type |    message
----+------+---------------
  3 | test | goodbye world
(1 row)

from cstore_fdw.

looks like that error is coming from cstore_fdw 1.6 version. streaming replication is still on develop branch. what do you see when you \dx ? You should see 2.0. Otherwise you are on wrong cstore_fdw branch.

from cstore_fdw.

From all three systems:

Primary:

tgburrin=> \dx
                          List of installed extensions
    Name    | Version |   Schema   |                 Description
------------+---------+------------+---------------------------------------------
 cstore_fdw | 2.0     | public     | foreign-data wrapper for flat cstore access
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

1st secondary:

tgburrin=> \dx
                          List of installed extensions
    Name    | Version |   Schema   |                 Description
------------+---------+------------+---------------------------------------------
 cstore_fdw | 2.0     | public     | foreign-data wrapper for flat cstore access
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

2nd secondary

tgburrin=> \dx
                          List of installed extensions
    Name    | Version |   Schema   |                 Description
------------+---------+------------+---------------------------------------------
 cstore_fdw | 2.0     | public     | foreign-data wrapper for flat cstore access
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

1.6 was originally installed and was removed by using 'drop extension', removing the rpm, installing the develop branch copy and rebooting all three systems.

I will clear all three systems down and try again to confirm.

from cstore_fdw.

@mtuncer , clearing things down and reloading from scratch worked; I must have missed something. Apologies for the confusion on my part.

Is there any expectation when this will be merged to master?

from cstore_fdw.

I have been getting this question a lot lately. I was originally thinking of finishing up an important part (better transaction support) and releasing this in early summer. Perhaps sometime in june/july. But I can't really provide exact timing. sorry :(

from cstore_fdw.

I would love this also @mtuncer, thanks!

from cstore_fdw.

Hi folks, we are not doing active development on cstore_fdw recently.

If you must, you should be able to use the code in develop branch.

from cstore_fdw.

Is there any chance to get it fixed in near future in the master version?

from cstore_fdw.

@Kireyko cstore_fdw has evolved into as a part of Citus, which has streaming replication for columnar storage too. See https://www.citusdata.com/blog/2021/03/06/citus-10-columnar-compression-for-postgres/

from cstore_fdw.