Hi! I'm trying to use multicorn to access a docker postgresql database from a virtual machine (ubuntu 20.04) to my local dockerhost on my macbook.
The virtual machine can reach the docker host using psql without any issue:
# psql postgresql://postgres:postgres@10.6.2.64/fb
psql (13.10 (Ubuntu 13.10-1.pgdg20.04+1), server 10.5 (Debian 10.5-2.pgdg90+1))
Type "help" for help.
fb=#
The database in the source system is called fb, the table is called patient.
More information about the table here:
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'patient';
column_name | data_type
---------------+--------------------------
id | text
txid | bigint
ts | timestamp with time zone
resource_type | text
status | USER-DEFINED
resource | jsonb
(6 rows)
The makefile has issues with my python version, so i changed
pip${python_version} install .
to
pip3 install .
then I install using
make
sudo make install
I checked the installation:
# pip3 show multicorn
Name: multicorn
Version: 2.4
Summary: UNKNOWN
Home-page: UNKNOWN
Author: Lussier
Author-email: UNKNOWN
License: Postgresql
Location: /usr/local/lib/python3.8/dist-packages
Requires:
Required-by:
# ls /usr/local/lib/python3.8/dist-packages/multicorn
__init__.py _utils.cpython-38-x86_64-linux-gnu.so csvfdw.py gcfdw.py googlefdw.py ldapfdw.py rssfdw.py statefdw.py utils.py
__pycache__ compat.py fsfdw gitfdw.py imapfdw.py processfdw.py sqlalchemyfdw.py testfdw.py xmlfdw.py
# ls /usr/share/postgresql/13/extension | grep multicorn
multicorn--2.4.sql
multicorn.control
# cat ls /usr/share/postgresql/13/extension/multicorn.control
cat: ls: No such file or directory
comment = 'Multicorn2 Python3.6+ bindings for Postgres 11++ Foreign Data Wrapper'
default_version = '2.4'
module_pathname = '$libdir/multicorn'
relocatable = true
Then I create the extension inside psql, create the server and add the foreign table as follows:
sudo -i -u postgres
psql
# CREATE EXTENSION multicorn;
CREATE EXTENSION
# SELECT name, default_version, installed_version
# FROM pg_available_extensions
# WHERE name LIKE 'multicorn%';
name | default_version | installed_version
-----------+-----------------+-------------------
multicorn | 2.4 | 2.4
(1 row)
# CREATE SERVER alchemy_srv foreign data wrapper multicorn2 OPTIONS ( wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw' );
CREATE SERVER
# CREATE FOREIGN TABLE patient ("id" text, "txid" bigint, "ts" timestamp with time zone, "resource_type" text, "status" text, "resource" jsonb) SERVER alchemy_srv OPTIONS ( tablename 'patient' , db_url 'postgresql://postgres:postgres@10.6.2.64/fb' );
CREATE FOREIGN TABLE
The problem is that the table stays empty and I can't query any data:
# SELECT id FROM patient LIMIT 100;
ERROR: foreign-data wrapper "multicorn2" has no handler
# \dE[S+]
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+---------+---------------+----------+-------------+---------+-------------
public | patient | foreign table | postgres | permanent | 0 bytes |
(1 row)
Am I doing something wrong? Following the documentation if I only want to access the data in another database by querying inside my own postgresql I don't need to created a 'handler', right?
Hi! I'm trying to use multicorn to access a docker postgresql database from a virtual machine (ubuntu 20.04) to my local dockerhost on my macbook.
The virtual machine can reach the docker host using psql without any issue:
The database in the source system is called fb, the table is called patient.
More information about the table here:
I use python3.8.10:
I installed postgresql13 as follows
I installed multicorn as follows:
The makefile has issues with my python version, so i changed
to
then I install using
I checked the installation:
Then I create the extension inside psql, create the server and add the foreign table as follows:
The problem is that the table stays empty and I can't query any data:
Am I doing something wrong? Following the documentation if I only want to access the data in another database by querying inside my own postgresql I don't need to created a 'handler', right?
Thank you