0

I trying to create new db, when tables in it, then make them distributed, but can't use create_reference_table() in new db (it's not found). If I try to run create_reference_table('newbie.schema.new_table) I will get error "ERROR: cross-database references are not implemented":

CREATE DATABASE newbie;
SELECT * from master_add_node('citus-worker1', 5432);
SELECT * from master_add_node('citus-worker2', 5432);
SELECT run_command_on_workers('CREATE DATABASE newbie;');

\c newbie

create table new_table
SELECT create_reference_table('schema.new_table');

leads to ERROR: function create_reference_table(unknown) does not exist looks like vicious circle (

40min
  • 117
  • 1
  • 9

1 Answers1

0

You need to run CREATE EXTENSION Citus on all databases separately (If you want to distribute some tables in those databases of course). Citus stores the distributed object metadata inside the relevant database.

These steps should work:

\c newbie
CREATE EXTENSION Citus;
CREATE SCHEMA s;
CREATE TABLE s.new_table(id int);
SELECT create_reference_table('s.new_table');

If you run a CREATE DATABASE ... when Citus extension is enabled, you can see the help messages:

postgres=# create database new_db;
NOTICE:  Citus partially supports CREATE DATABASE for distributed databases
DETAIL:  Citus does not propagate CREATE DATABASE command to workers
HINT:  You can manually create a database and its extensions on workers.

Also do not forget to run master_add_node() to add the worker nodes in the new database as well. The worker metadata is stored in the associated database as well.

Hanefi
  • 114
  • 5
  • Thanks! It works. It's was a bit tricky with docker: I had to create separate Dockerfile's for master and workers to run CREATE DATABASE ... and CREATE EXTENSION Citus; on workers and the same plus creation of tables on master. But it become to work unbelievable slowly -- request to three distributed tables with joins needs 5-7 seconds – 40min Mar 18 '19 at 15:55
  • You should run these `CREATE EXTENSION Citus` and `master_add_node()` calls only once per database, and after that things should be as fast as that in a setup with single database in each node. There is a lot of discussion on pros and cons of having multiple databases in a single PostgreSQL server, and I personally prefer having multiple schemas in a single database in a machine. You can find one such discussion [here](https://www.postgresql.org/message-id/flat/016439c9-8017-e319-bd2f-893b1a8cd091%40hogranch.com#9987436602d187df7a04b1c983e5e983) – Hanefi Mar 19 '19 at 16:55
  • ok, thanks, unfortunately I'm not allowed to make decisions about db architecture on this project. Slow query exec was caused because I did request to not colocated table ( . – 40min Mar 20 '19 at 10:26