首页 » PostgreSQL/GaussDB » 聊聊翰高数据库Highgo用户(实例和数据库)、模式兼容性(oracle、postgresql)、与PostgreSQL版本对应
聊聊翰高数据库Highgo用户(实例和数据库)、模式兼容性(oracle、postgresql)、与PostgreSQL版本对应
翰高数据库 Highgo是基于PostgreSQL的数据库，但是版本较多有基于postgresql 9 、12、14多个版本，同时在兼容模式上也并不统一，如支持pg, oracle和正在增加的MySQL兼容模式，如V9.5版本可以实现一份数据，两种解析引擎模式的支持，同时在V9版本登录时还有实例级用户和数据库用户级，简单整理总结以扫盲。
实例和数据库用户
翰高数据库分为实例级用户和数据库级用户。
实例用户： 主要用于数据库创建、删除；实例级参数修改、创建用户等高级别维护操作，在安装时初始化时指定。 如通常是highgo
数据库级用户： 用于日常的指定具体的数据库登录访问，创建库用户等维护操作，日常事务、业务应用使用用户，psql登录时指定，如oracle 兼容模式的system, postgrsql兼容模式的hg
highgo V9.5 psql登录 -u是实例级用户-U是库级用户，不加默认是库级
highgo V9 psql登录 psql 不带用户默认是实例级用户， -U是库级用户
highgo V9.5 psql --help Connection options: -h, --host=HOSTNAME database server host or socket directory (default: "local socket") -p, --port=PORT database server port (default: "5432") -u, --instance-user connect to the database with instance level user -U, --username=USERNAME database user name (default: "highgo") -w, --no-password never prompt for password -W, --password force password prompt (should happen automatically) highgo V9 psql --help Connection options: -h, --host=HOSTNAME database server host or socket directory (default: "local socket") -p, --port=PORT database server port (default: "5866") -U, --username=USERNAME database user name (default: "hg") -w, --no-password never prompt for password -W, --password force password prompt (should happen automatically)
V9.5 登录测试
$ psql highgo -u system
psql: FATAL: instance level role "system" does not exist
$ psql highgo -u highgo
psql (HighGo) 9.0
Type "help" for help.
highgo=# \c
You are now connected to database "highgo" as user "highgo".
highgo=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+--------+----------+-------------+-------------+-----------------------------
highgo | highgo | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
lala | highgo | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | highgo | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | highgo | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/*highgo +
| | | | | *highgo=CTc/*highgo
template1 | highgo | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/*highgo +
| | | | | *highgo=CTc/*highgo
highgo=# \c lala
FATAL: database level role "highgo" does not exist
Previous connection kept
highgo=# create user anbob password 'anbob';
CREATE ROLE
highgo=# \q
[hg95@anbob_com ~]$ psql postgres -U anbob -- DB USER
psql (HighGo) 9.0
Type "help" for help.
postgres=> \q
[hg95@anbob_com ~]$ psql postgres -u anbob
psql: FATAL: instance level role "anbob" does not exist
[hg95@anbob_com ~]$ psql postgres -u highgo --Instance user
psql (HighGo) 9.0
Type "help" for help.
postgres=# \q
$ psql lala anbob
psql: FATAL: database level role "anbob" does not exist
$ psql highgo anbob
psql (HighGo) 9.0
Type "help" for help.
postgres=> select sysdate;
sysdate
-------------------
20241210 22:53:53
(1 row)
Note:
实例用户是不允许\c 切换db的， 基本是实例级用户创建完退出，用对应用的数据库级用户重新登录。 oracle模式也可以建实例级普通用户，但没有必要，用默认的实例级超户已满足；如果想用新建的实例级用户再去创建数据库，也不能owner指定数据库属主，建库后属主还是初始化的实例级超户；
V9 登录测试
Highgo V9 以端口号区分不同的兼容模式， 同一份数据，比如以1521 port连接是 Oracle兼容模式， 以5432以是PostgreSQL兼容模式。 不批端口默认是PostgreSQL模式.
[hg@anbob_com ~]$ netstat -an|head
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 127.0.0.1:1521 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:54321 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:44321 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:4330 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:5866 0.0.0.0:* LISTEN
-- pg模式 登录，修改参数
[hg@anbob_com ~]$ psql
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.
highgo=# show random_page_cost;
random_page_cost
------------------
4
(1 row)
highgo=# alter system set random_page_cost=1.5;
ALTER SYSTEM
highgo=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
[hg@anbob_com ~]$ psql anbob hg
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.
anbob=# alter system set random_page_cost=1.5;
ALTER SYSTEM
anbob=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------------------+-------+----------+-------------+-------------+-------------------------
highgo | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
pgbenchdb | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/hg +
| | | | | hg=CTc/hg
template1 | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/hg +
| | | | | hg=CTc/hg
anbob | ymh | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/ymh +
| | | | | ymh=CTc/ymh +
| | | | | anbob_repl=CTc/ymh +
| | | | | anbob_bk=CTc/ymh +
| | | | | anbob_ha=CTc/ymh +
| | | | | anbob_monitor=CTc/ymh +
| | | | | anbob_platform=CTc/ymh
anbob123 | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(8 rows)
anbob=# \c
IvorySQL-pro-3.2.0
You are now connected to database "anbob" as user "hg".
anbob=# \c anbob123
IvorySQL-pro-3.2.0
You are now connected to database "anbob123" as user "hg".
anbob123=# create user anbob password 'anbob';
CREATE ROLE
anbob123=# \q
[hg@dev-anbob-node3 ~]$ psql anbob anbob
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.
anbob=> \c highgo
IvorySQL-pro-3.2.0
You are now connected to database "highgo" as user "anbob".
highgo=> alter system set random_page_cost=1.5;
ERROR: must be superuser to execute ALTER SYSTEM command
-- oracle模式 DB用户登录 ，默认自带system管理用户
[hg@anbob ~]$ psql postgres system -p 1521
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.
postgres=# \c
IvorySQL-pro-3.2.0
You are now connected to database "postgres" as user "system".
[hg@anbob ~]$ psql anbob -U anbob
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.
anbob=> \c highgo
IvorySQL-pro-3.2.0
You are now connected to database "highgo" as user "anbob".
highgo=> \c anbob123
IvorySQL-pro-3.2.0
You are now connected to database "anbob123" as user "anbob".
[hg@dev-anbob-node3 ~]$ psql anbob system
psql: error: connection to server on socket "/tmp/.s.PGSQL.5866" failed: FATAL: role "system" does not exist
[hg@dev-anbob-node3 ~]$ psql anbob -U system
psql: error: connection to server on socket "/tmp/.s.PGSQL.5866" failed: FATAL: role "system" does not exist
[hg@dev-anbob-node3 ~]$ psql anbob -U system -p 1521
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.
anbob=# select sysdate from dual;
sysdate
-------------------
20241211 09:25:19
(1 row)
anbob=# show random_page_cost;
random_page_cost
------------------
1.5
(1 row)
anbob=# alter system set random_page_cost=2;
ERROR: database-level user cann't execute ALTER SYSTEM command
anbob=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------------------+-------+----------+-------------+-------------+-------------------------
highgo | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
pgbenchdb | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/hg +
| | | | | hg=CTc/hg
template1 | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/hg +
| | | | | hg=CTc/hg
test_pg_stat_statements | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
anbob | ymh | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/ymh +
| | | | | ymh=CTc/ymh +
| | | | | anbob_repl=CTc/ymh +
| | | | | anbob_bk=CTc/ymh +
| | | | | anbob_ha=CTc/ymh +
| | | | | anbob_monitor=CTc/ymh +
| | | | | anbob_platform=CTc/ymh
anbob123 | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(8 rows)
anbob=# \c highgo
IvorySQL-pro-3.2.0
You are now connected to database "highgo" as user "system".
highgo=# \c anbob123
IvorySQL-pro-3.2.0
You are now connected to database "anbob123" as user "system".
[hg@dev-anbob-node3 ~]$ psql anbob -U system -p 1521
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.
anbob=# create user anbob password 'anbob';
CREATE ROLE
anbob=# \q
[hg@dev-anbob-node3 ~]$ psql anbob -U anbob -p 1521
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.
anbob=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------------------+-------+----------+-------------+-------------+-------------------------
highgo | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
pgbenchdb | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/hg +
| | | | | hg=CTc/hg
template1 | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/hg +
| | | | | hg=CTc/hg
test_pg_stat_statements | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
anbob | ymh | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/ymh +
| | | | | ymh=CTc/ymh +
| | | | | anbob_repl=CTc/ymh +
| | | | | anbob_bk=CTc/ymh +
| | | | | anbob_ha=CTc/ymh +
| | | | | anbob_monitor=CTc/ymh +
| | | | | anbob_platform=CTc/ymh
anbob123 | hg | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(8 rows)
anbob=> \c highgo
IvorySQL-pro-3.2.0
You are now connected to database "highgo" as user "anbob".
Note:
highgo V9.5 照顾postgresql用户，把pg和oracle以端口模式分开，不同的端口连接使用不同的SQL 解析器，访问的相同的数据库。以-U 表示是数据库级用户。highgo V9里面 oracle模式需要区分实例用户和数据库用户，pg模式不需要区分;以-u和-U区分database level和instance level用户。
翰高数据库和PostgreSQL的版本对应
Highgh V9.5 专业版也叫IvorySQL-pro 3.2，与开源版IvorySQL对应， 是PostgreSQL 14内核；
Highgh V9 是基于PostgreSQL9.5；
Highgh V4.5安全版是基于PostgreSQL 12；
$ psql
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.
highgo=# select version();
version
-------------------------------------------------------------------------------------
PostgreSQL 14.10 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 10.4.0, 64-bit
(1 row)
$ psql postgres -u highgo
psql (HighGo) 9.0
Type "help" for help.
postgres=# select version();
version
------------------------------------------------------------------------------------
PostgreSQL 9.5.4 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
(1 row)
Note:
不过我个人要吐槽一下，数据库名字起的不够符合国情，拼写和读起来不习惯， 数据库版本管理搞的有点让人摸不到头脑。
模式兼容性
安装时-m oracle 可以指定兼容模式, 后期登录数据库可以使用SQL查询参数当前模式
--Highgo V9.5 版本的
用ivorysql.database_mode查看当前模式, 安装时可以选pg模式当Pg使用，也可以选oracle模式，支持2种模式（如自带的pg和oracle）。
select name,setting from pg_settings where name in ('ivorysql.database_mode','ivorysql.compatible_mode');
[hg@dev-anbob-node3 ~]$ psql anbob -U system -p 1521
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.
anbob=# select name,setting from pg_settings where name in ('ivorysql.database_mode','ivorysql.compatible_mode');
name | setting
--------------------------+---------
ivorysql.compatible_mode | oracle
ivorysql.database_mode | oracle
(2 rows)
anbob=# \q
[hg@dev-anbob-node3 ~]$ psql anbob hg
psql (14.10)
IvorySQL-pro-3.2.0
Type "help" for help.
anbob=# select name,setting from pg_settings where name in ('ivorysql.database_mode','ivorysql.compatible_mode');
name | setting
--------------------------+---------
ivorysql.compatible_mode | pg
ivorysql.database_mode | oracle
(2 rows)
--Highgo V9 版
select name,setting from pg_settings where name in ('rp_compatible_level','rp_database_style');
rp_database_style这个是当前数据库的模式，Oracle模式返回oracle，pg返回pg; 包含compatible参数的就是当前会话的模式. 只能安装时选定模式.
[hg95s@dev-anbob-node1 ~]$ psql
psql (HighGo) 9.0
Type "help" for help.
postgres=# select name,setting from pg_settings where name in ('rp_compatible_level','rp_database_style');
name | setting
---------------------+---------
rp_compatible_level | oracle
rp_database_style | oracle
(2 rows)
[hg95s@dev-anbob-node1 ~]$ psql highgo -u highgo
psql (HighGo) 9.0
Type "help" for help.
highgo=# select name,setting from pg_settings where name in ('rp_compatible_level','rp_database_style');
name | setting
---------------------+---------
rp_compatible_level | oracle
rp_database_style | oracle
(2 rows)
highgo=# \q
[hg95s@dev-anbob-node1 ~]$ psql postgres -U anbob
psql (HighGo) 9.0
Type "help" for help.
postgres=> select name,setting from pg_settings where name in ('rp_compatible_level','rp_database_style');
name | setting
---------------------+---------
rp_compatible_level | oracle
rp_database_style | oracle
(2 rows)
Note:
Highgo V9和v9.5 都有 pg和oracle模式,但是V9里面只能 pg和oracle模式 二选一；V9.5 版本也可以只运行pg模式，初始化成pg模式就一个端口(5866)，当成pg去使用，当初始化成oracle模式时分pg模式端口(5866)和oracle端口(1521)，两个端口下的用户不能混用, 后期兼容mysql模式也是同理两个端口pg模式(5866)，mysql模式(3306)；
微信扫一扫，打赏作者吧～
目前这篇文章还没有评论(Rss)