Failing to open DuckDB database

[nestarz]

Hello @alitrack , thanks for the connector ! I can't open my duckdb inside psql (and neither using Postgres connection), it failed to open it.

My duckdb table:

root@server:/# ./duckdb test.db
0.2.4-dev0 436f6455f
Enter ".help" for usage hints.
D CREATE TABLE helloworld (phrase TEXT);
D INSERT INTO helloworld (phrase) VALUES ('Hello, World!');

My Error:

psql (13.1 (Debian 13.1-1.pgdg100+1))
Type "help" for help.

postgres=# CREATE SERVER testsrv FOREIGN DATA WRAPPER duckdb_fdw OPTIONS (database '/test.db');
CREATE SERVER
postgres=# IMPORT FOREIGN SCHEMA public FROM SERVER testsrv INTO public;
ERROR:  failed to open SQLite DB. rc=1 path=/test.db

My Env:

FROM postgres

RUN apt-get update && apt-get install -y \
  git \
  build-essential \
  cmake

RUN git clone https://github.com/cwida/duckdb.git \
  && cd duckdb \
  && make

RUN apt-get install -y postgresql-server-dev-13 postgresql-client-13

RUN git clone https://github.com/alitrack/duckdb_fdw.git  \
   && cd duckdb_fdw \
   && cp /duckdb/build/release/tools/sqlite3_api_wrapper/libsqlite3_api_wrapper.so $(pg_config --libdir)  \
   && cp /duckdb/tools/sqlite3_api_wrapper/include/sqlite3.h .  \
   && make USE_PGXS=1 \
   && make install USE_PGXS=1

ENV POSTGRES_HOST_AUTH_METHOD='trust'

[alitrack]

PostgreSQL run as postgres user, but your test.db belongs root user, so Postgres has no right to access it.

[nestarz]

It seems to not work either with the right permissions:

postgres@0366e9a116c2:/$ sudo ./duckdbcli t.db
[sudo] password for postgres: 
0.2.4-dev0 436f6455f
Enter ".help" for usage hints.
D CREATE TABLE helloworld (phrase TEXT);
D INSERT INTO helloworld (phrase) VALUES ('Hello, World!');

postgres@0366e9a116c2:/$ psql
psql (13.1 (Debian 13.1-1.pgdg100+1))
Type "help" for help.

postgres=# CREATE SERVER testsrv2 FOREIGN DATA WRAPPER duckdb_fdw OPTIONS (database '/t.db'); 
CREATE SERVER
postgres=# IMPORT FOREIGN SCHEMA public FROM SERVER testsrv2 INTO public;
ERROR:  failed to open SQLite DB. rc=1 path=/t.db

[alitrack]

please check your owner info of t.db

ls -lh /t.db

[nestarz]

duckdb db creation:

postgres@0366e9a116c2:/home$ ../duckdbcli t2.db
0.2.4-dev0 436f6455f
Enter ".help" for usage hints.
D CREATE TABLE helloworld (phrase TEXT);
D INSERT INTO helloworld (phrase) VALUES ('Hello, World!');

psql access to duckdb:

postgres@0366e9a116c2:/home$ psql
psql (13.1 (Debian 13.1-1.pgdg100+1))
Type "help" for help.

postgres=# CREATE SERVER testsrv4 FOREIGN DATA WRAPPER duckdb_fdw OPTIONS (database '/home/t2.db'); 
CREATE SERVER
postgres=# IMPORT FOREIGN SCHEMA public FROM SERVER testsrv4 INTO public; 
ERROR:  failed to open SQLite DB. rc=1 path=/home/t2.db

Owner info:

postgres@0366e9a116c2:/home$ ls -lh /home/t2.db
-rw-r--r-- 1 postgres postgres 12K Jan 25 09:53 /home/t2.db
postgres@0366e9a116c2:/home$ 

Thanks for your help,

[alitrack]

postgres can't access /home, postgres home folder is /var/lib/postgresql/

[nestarz]

I'm trying everything to make it work really, I have created a database directly inside /var/lib/postgresql/.

postgres@349a4ad0de0d:~$ ls -lu
total 3057212
drwxrwxrwx 19 postgres postgres       4096 Jan 26 14:59 data
-rwxrwxrwx  1 postgres postgres 3106418688 Jan 26 15:08 database.db

Gave all the rights to my file, also to the directory, but still, i have this error that pops up, how can I make the right permissions please? How did you do it ?

[nestarz]

Also there is a weird thing that when I don't have a database before creating a server, everything works, but then i can't open it with duckdb to populate it.

Example:

1. No database

   postgres@349a4ad0de0d:~$ ls
   data  duckdb

2. Creating a wrapper for a non-existing database, no error:

   postgres@349a4ad0de0d:~$ psql
   psql (13.1 (Debian 13.1-1.pgdg100+1))
   Type "help" for help.
   postgres=# CREATE SERVER t6 FOREIGN DATA WRAPPER duckdb_fdw OPTIONS (database '/var/lib/postgresql/t6.db');
   postgres=# IMPORT FOREIGN SCHEMA public FROM SERVER t6 INTO public;
   IMPORT FOREIGN SCHEMA

3. A database have been created:

postgres@349a4ad0de0d:~$ ls -lu total 3057260 drwxrwxrwx 19 postgres postgres 4096 Jan 26 14:59 data -rwxrwxrwx 1 postgres postgres 24123288 Jan 26 15:10 duckdb -rw------- 1 postgres postgres 12288 Jan 26 15:23 t6.db -rw------- 1 postgres postgres 1 Jan 26 15:22 t6.db.wal

4. But I can't open it

postgres@349a4ad0de0d:~$ ./duckdb t6.db Error: unable to open database "t6.db":

[alitrack]

just use the Dockerfile you developed, and nothing special

postgres@0faa08256e78:~$ ls -lh
total 4.0K
drwx------ 19 postgres postgres 4.0K Jan 26 05:35 data
postgres@0faa08256e78:~$ /duckdb/build/release/duckdb hello.db
0.2.4-dev523 2e5ffb465
Enter ".help" for usage hints.
D CREATE TABLE helloworld (phrase TEXT);
D INSERT INTO helloworld (phrase) VALUES ('Hello, World!');
D .q
postgres@0faa08256e78:~$ psql -Uoptima postgres
psql (13.1 (Debian 13.1-1.pgdg100+1))
Type "help" for help.

postgres=# drop server DuckDB_server;
ERROR:  cannot drop server duckdb_server because other objects depend on it
DETAIL:  foreign table helloworld depends on server duckdb_server
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
postgres=# drop server DuckDB_server CASCADE;
NOTICE:  drop cascades to foreign table helloworld
DROP SERVER
postgres=# CREATE SERVER DuckDB_server FOREIGN DATA WRAPPER duckdb_fdw OPTIONS (database '/var/lib/postgresql/hello.db');
CREATE SERVER
postgres=# IMPORT FOREIGN SCHEMA public FROM SERVER DuckDB_server INTO public;
IMPORT FOREIGN SCHEMA
postgres=# select * from helloworld;
    phrase     
---------------
 Hello, World!
(1 row)

postgres=# \q
postgres@0faa08256e78:~$ ls -lh
total 20K
drwx------ 19 postgres postgres 4.0K Jan 26 05:35 data
-rw-r--r--  1 postgres postgres  12K Jan 26 15:25 hello.db
-rw-r--r--  1 postgres postgres  115 Jan 26 15:26 hello.db.wal
postgres@0faa08256e78:~$ ls -lh /var/lib/postgresql/
data/            .duckdb_history  hello.db         hello.db.wal     .psql_history    
postgres@0faa08256e78:~$ ls -lh /var/lib/|grep postgresql
drwxr-xr-x 1 postgres postgres 4.0K Jan 26 15:26 postgresql
postgres@0faa08256e78:~$ 

[alitrack]

I can open database created by postgres too.

[alitrack]

just copy from my other docker-compose.yml

version: "3.3"
services:
  duckdb_fdw:
     build: .
     ports:
         - "5449:5432"
     volumes:
         - mdata:/var/lib/postgresql/data
     environment:
         - POSTGRES_USER=optima
         - POSTGRES_PASSWORD=123456
         - POSTGRES_DB=postgres
volumes:
  mdata:

you can try this. and even I use postgres user(in fact it has no business postgres db user with linux user postgres)

[nestarz]

Ok thank you so much, I figured it how why it doesn't work, it's because I was using the duckdb binary from their release and not from the build directory. Also, my database was constructed using the release binary. Here the proof:

Can't open my database using the built binary from the docker:

➜  app /duckdb/build/release/duckdb ./data/database.db 
Error: unable to open database "./data/database.db": 
➜  app /duckdb/build/release/duckdb
0.2.4-dev521 fa178a21c
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D 

But I can open it using the binary from the release

➜  app /download/v0.2.3/duckdb_cli-linux-amd64/duckdb ./data/database.db 
0.2.4-dev0 436f6455f
Enter ".help" for usage hints.
D 

And in all my comments here I used the downloaded binary to test duckdb_fdw, not the built one like you did. I'm still trying to understand why, and how I can make it work.

[nestarz]

Made it work by using the same exact version of duckdb to build the FDW and to build the database, also by setting before hand the owner and the group as the same UID as postgres user in the container. Thanks.