使用lealone做为postgresql使用，没有默认的数据库

dbgp commented 11 months ago

背景：本地没有按照pq数据库，实验的项目采用了pg数据库。想使用lealone做为替代。

现象：启动lealone之后，使用psql做为postgresql的命令行客户端进行连接，连接后提示输入。提示对应的数据库不存在。

需求：如何创建pg使用的数据库?

所有数据时共享的话，利用root登陆lealone，直接创建对应的db，user这些信息？——不确定这样的理解是否正确。

INFO 16:54:09.421 Lealone version: 6.0.0-SNAPSHOT
INFO 16:54:09.427 Loading config from file:/Users/geb/dblealone/conf/lealone.yaml
INFO 16:54:09.476 Base dir: /Users/geb/dblealone/data
INFO 16:54:09.481 Init storage engines: 2 ms
INFO 16:54:09.514 Init transaction engines: 33 ms
INFO 16:54:09.522 Init sql engines: 7 ms
INFO 16:54:09.531 Init protocol server engines: 8 ms
INFO 16:54:09.627 Init lealone database: 95 ms
INFO 16:54:09.637 TcpServer started, host: 127.0.0.1, port: 9210
INFO 16:54:09.638 MongoServer started, host: 127.0.0.1, port: 27017
INFO 16:54:09.646 MySQLServer started, host: 127.0.0.1, port: 3306
INFO 16:54:09.651 PgServer started, host: 127.0.0.1, port: 5432
INFO 16:54:09.651 Total time: 228 ms (Load config: 42 ms, Init: 162 ms, Start: 24 ms)
INFO 16:54:09.651 Exit with Ctrl+C
ERROR 16:55:09.327
org.lealone.common.exceptions.JdbcSQLException: Database "root" not found [90013-0]
    at org.lealone.common.exceptions.DbException.getJdbcSQLException(DbException.java:342) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.common.exceptions.DbException.get(DbException.java:173) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.common.exceptions.DbException.get(DbException.java:150) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.db.LealoneDatabase.getDatabase(LealoneDatabase.java:144) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.db.session.ServerSessionFactory.createServerSession(ServerSessionFactory.java:46) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.db.session.ServerSessionFactory.createSession(ServerSessionFactory.java:37) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.db.session.SessionFactory.createSession(SessionFactory.java:15) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.db.ConnectionInfo.createSession(ConnectionInfo.java:655) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.plugins.postgresql.server.PgServerConnection.createSession(PgServerConnection.java:87) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.plugins.postgresql.server.handler.AuthPacketHandler.handle(AuthPacketHandler.java:73) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.plugins.postgresql.server.handler.PacketHandler.handle(PacketHandler.java:55) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.plugins.postgresql.server.PgServerConnection.handlePacket(PgServerConnection.java:166) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.plugins.postgresql.server.PgServerConnection.handle(PgServerConnection.java:145) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.net.nio.NioEventLoop.read(NioEventLoop.java:275) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.net.nio.NioEventLoop.handleSelectedKeys(NioEventLoop.java:492) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.net.NetScheduler.runEventLoop(NetScheduler.java:62) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.server.scheduler.GlobalScheduler.run(GlobalScheduler.java:75) [lealone-6.0.0-SNAPSHOT.jar:?]
    at java.lang.Thread.run(Thread.java:829) [?:?]

查看有默认的 POSTGRES数据库，

sql> show databases;
+---------------+
| DATABASE_NAME |
+---------------+
| MYSQL         |
| POSTGRES      |
| MONGO         |
| lealone       |
+---------------+
(5 rows, 19 ms)

使用postgres账户连接时，会提示密码不正确。

 psql -U postgres -h localhost  -p 5432
Password for user postgres:
psql: error: connection to server at "localhost" (::1), port 5432 failed: Connection refused
    Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: ERROR:  Wrong user name or password [28000-0]
DETAIL:  org.lealone.common.exceptions.JdbcSQLException: Wrong user name or password [28000-0]

codefollower commented 11 months ago

lealone 这个 database 的默认用户是 root，密码是空。 POSTGRES 这个 database 的默认用户是 postgres，密码也是 postgres。

在 psql 中连 lealone 这个 database，因为 psql 不支持空密码所以连不上，需要先在 lealone 的客户端执行 alter user root set password 'root';

在 psql 中可以连 POSTGRES 这个 database，如果不指定 -d 这个参数，默认数据库就是 POSTGRES。

codefollower commented 11 months ago

每个 database 都有自己的独立 user，不同 database 里的 user 是不能跨库访问的，连不同 database 只能用它的 user。比如 lealone 这个数据库就没有 postgres 这个 user，所以不能用 postgres 这个用户连。

dbgp commented 11 months ago

使用postgres用户可以连接到postgres数据库。

应该是版本不一致导致的？连接后由提示—— lealone里的pg版本是8.2.23，然后安装的版本是 16.1 （后面执行语法的报错是非是因为版本不一致导致的？）

➜  ~ psql -U postgres -h localhost -p 5432
Password for user postgres:
psql (16.1, server 8.2.23)
WARNING: psql major version 16, server major version 8.2.
         Some psql features might not work.
Type "help" for help.

执行 \l 显示数据库时，server端报错

ERROR 21:37:06.905
org.lealone.common.exceptions.JdbcSQLException: Function "ARRAY_TO_STRING" not found; SQL statement:
SELECT
  d.datname as "Name",
  pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
  pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
  'libc' AS "Locale Provider",
  d.datcollate as "Collate",
  d.datctype as "Ctype",
  NULL as "ICU Locale",
  NULL as "ICU Rules",
  pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1 [90022-0]
    at org.lealone.common.exceptions.DbException.getJdbcSQLException(DbException.java:342) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.common.exceptions.DbException.get(DbException.java:173) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.common.exceptions.DbException.get(DbException.java:150) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.readJavaFunction(SQLParserBase.java:2269) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.readFunction(SQLParserBase.java:2313) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.readTermObjectDot(SQLParserBase.java:2530) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.readTerm(SQLParserBase.java:2646) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.readFactor(SQLParserBase.java:2183) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.readSum(SQLParserBase.java:2170) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.readConcat(SQLParserBase.java:2143) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.readCondition(SQLParserBase.java:2007) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.readAnd(SQLParserBase.java:1988) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.readExpression(SQLParserBase.java:1980) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.parseSelectSimpleSelectPart(SQLParserBase.java:1707) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.parseSelectSimple(SQLParserBase.java:1530) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.parseSelectSub(SQLParserBase.java:1507) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.parseSelectUnion(SQLParserBase.java:1477) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.parseSelect(SQLParserBase.java:1465) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.parseStatement(SQLParserBase.java:455) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.parse(SQLParserBase.java:327) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.parse(SQLParserBase.java:285) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.sql.SQLParserBase.parse(SQLParserBase.java:186) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.db.session.ServerSession.prepareStatement(ServerSession.java:426) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.db.session.ServerSession.prepareStatement(ServerSession.java:413) ~[lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.plugins.postgresql.server.handler.CommandPacketHandler.query(CommandPacketHandler.java:209) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.plugins.postgresql.server.handler.CommandPacketHandler.handle(CommandPacketHandler.java:64) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.plugins.postgresql.server.handler.PacketHandler.handle(PacketHandler.java:55) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.plugins.postgresql.server.PgServerConnection.handlePacket(PgServerConnection.java:166) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.plugins.postgresql.server.PgTask.run(PgTask.java:23) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.server.scheduler.SessionInfo.runTask(SessionInfo.java:104) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.server.scheduler.SessionInfo.submitTask(SessionInfo.java:68) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.plugins.postgresql.server.PgServerConnection.handle(PgServerConnection.java:147) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.net.nio.NioEventLoop.read(NioEventLoop.java:275) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.net.nio.NioEventLoop.handleSelectedKeys(NioEventLoop.java:492) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.net.NetScheduler.runEventLoop(NetScheduler.java:62) [lealone-6.0.0-SNAPSHOT.jar:?]
    at org.lealone.server.scheduler.GlobalScheduler.run(GlobalScheduler.java:75) [lealone-6.0.0-SNAPSHOT.jar:?]
    at java.lang.Thread.run(Thread.java:829) [?:?]
ERROR 21:38:38.517

客户端报错——

postgres-> \l
ERROR:  Function "ARRAY_TO_STRING" not found; SQL statement:
SELECT
  d.datname as "Name",
  pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
  pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
  'libc' AS "Locale Provider",
  d.datcollate as "Collate",
  d.datctype as "Ctype",
  NULL as "ICU Locale",
  NULL as "ICU Rules",
  pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1 [90022-0]
DETAIL:  org.lealone.common.exceptions.JdbcSQLException: Function "ARRAY_TO_STRING" not found; SQL statement:
SELECT
  d.datname as "Name",
  pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
  pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
  'libc' AS "Locale Provider",
  d.datcollate as "Collate",
  d.datctype as "Ctype",
  NULL as "ICU Locale",
  NULL as "ICU Rules",
  pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1 [90022-0]

使用 \i path/to/schema.sql执行 sql文件时也会报错。语法相关的错误。

codefollower commented 11 months ago

目前对 postgresql 的兼容还在不断完善，ARRAY_TO_STRING 这个函数没有实现。

dbgp commented 11 months ago

专用的语法应该都还需要一些兼容工作。\dt中用到的 PG_TABLE_IS_VISIBLE 函数也找不到。

postgres=> \dt
ERROR:  Function "PG_TABLE_IS_VISIBLE" not found; SQL statement:
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2 [90022-0]
DETAIL:  org.lealone.common.exceptions.JdbcSQLException: Function "PG_TABLE_IS_VISIBLE" not found; SQL statement:
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2 [90022-0]

请教下这个创建表的sql报错是因为哪些部分没有兼容？‘ TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()’ 这个语法？

CREATE TABLE IF NOT EXISTS users (
  user_id             VARCHAR(36) NOT NULL PRIMARY KEY,
  full_name           VARCHAR(1024) NOT NULL,
  identity_number     VARCHAR(11) NOT NULL UNIQUE,
  avatar_url          VARCHAR(1024) NOT NULL,
  access_token        VARCHAR(512) NOT NULL DEFAULT '',
  created_at          TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

codefollower commented 11 months ago

TIMESTAMP WITH[*] TIME ZONE NOT NULL DEFAULT NOW()

带有 *[]** 的地方就是语法错误的起始位置。

TIMESTAMP 类型目前还不支持 WITH TIME ZONE。

codefollower commented 11 months ago

目前 lealone 6 内置的 mongodb、mysql、postgresql 插件，当前是优先做 mongodb 插件，兼容度更高，其次是 mysql，postgresql 兼容的优先级低一些，接下来3个月会陆续完善这3个插件。

lealone / Lealone

使用lealone做为postgresql使用，没有默认的数据库 #217