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 (
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.