79,539
社区成员
发帖
与我相关
我的任务
分享openGauss建立用户及导入sql文本
转载自：https://www.modb.pro/db/87949
openGauss建立用户及导入sql文本
建立用户同时赋予用户创建database权限：
create user deity with createdb identified by 'Deityle---';
[omm@node1 tmp]$ gsql -p 15400 -d postgres -r
gsql ((openGauss 2.0.1 build d97c0e8a) compiled at 2021-06-02 19:37:17 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
postgres=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valbegin | valuntil | respool | parent | spacelimit | useconfig | nodegroup | tempspacelimit | spillspacelimit | usemonitora
dmin | useoperatoradmin | usepolicyadmin
---------+----------+-------------+----------+-----------+---------+----------+----------+----------+--------------+--------+------------+-----------+-----------+----------------+-----------------+------------
-----+------------------+----------------
omm | 10 | t | t | t | t | ******** | | | default_pool | 0 | | | | | | t
| t | t
(1 row)
postgres=# create user deity with createdb identified by 'Deity----';
CREATE ROLE
postgres=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valbegin | valuntil | respool | parent | spacelimit | useconfig | nodegroup | tempspacelimit | spillspacelimit | usemonitora
dmin | useoperatoradmin | usepolicyadmin
---------+----------+-------------+----------+-----------+---------+----------+----------+----------+--------------+--------+------------+-----------+-----------+----------------+-----------------+------------
-----+------------------+----------------
omm | 10 | t | t | t | t | ******** | | | default_pool | 0 | | | | | | t
| t | t
deity | 24941 | t | f | f | f | ******** | | | default_pool | 0 | | | | | | f
| f | f
(2 rows)
使用新建用户创建数据库及schema：
create database chnbs encoding 'UTF8';
create schema jack;
create schema salene;
[omm@node1 tmp]$ gsql -p 15400 -d postgres -U deity -r
Password for user deity:
gsql ((openGauss 2.0.1 build d97c0e8a) compiled at 2021-06-02 19:37:17 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
postgres=> \dn+
List of schemas
Name | Owner | Access privileges | Description
-------+-------+-------------------+-------------
deity | deity | |
(1 row)
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
mydb | | GBK | C | C |
postgres | | UTF8 | C | C |
template0 | | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
(4 rows)
postgres=> create database chnbs encoding 'UTF8';
CREATE DATABASE
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
chnbs | deity | UTF8 | C | C |
mydb | | GBK | C | C |
postgres | | UTF8 | C | C |
template0 | | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | | UTF8 | C | C | =c/omm +
| | | | | omm=CTc/omm
(5 rows)
postgres=> \c chnbs
Password for user deity:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "chnbs" as user "deity".
chnbs=> \dn
List of schemas
Name | Owner
------+-------
(0 rows)
chnbs=> create schema jack;
CREATE SCHEMA
chnbs=> \dn
List of schemas
Name | Owner
------+-------
jack | deity
(1 row)
chnbs=> \dn+
List of schemas
Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
jack | deity | |
(1 row)
chnbs=> create schema salene;
CREATE SCHEMA
chnbs=> \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+-------+-------------------+-------------
jack | deity | |
salene | deity | |
(2 rows)
gsql下执行SQL文件，导入表及数据
set search_path=jack; ##设置当前schema
\i home/omm/mydb.sql ##gsql下使用\i 导入sql文本
一般导入前需要对sql文本做响应的修改，其他数据库导出的文本，schema、用户名称都需要替换。
chnbs=> show search_path;
search_path
----------------
"$user",public
(1 row)
chnbs=> set search_path=jack;
SET
chnbs=> show search_path;
search_path
-------------
jack
(1 row)
chnbs=> \i home/omm/mydb.sql
SET
SET
SET
SET
SET
SET
SET
CREATE PROCEDURE
ALTER FUNCTION
SET
SET
CREATE TABLE
ALTER TABLE
......
......
......
COMMENT
COMMENT
setval
--------
1
(1 row)
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
REVOKE
REVOKE
GRANT
GRANT
chnbs=> \dn
List of schemas
Name | Owner
--------+-------
jack | deity
salene | deity
(2 rows)
chnbs=> \d
List of relations
Schema | Name | Type | Owner | Storage
--------+--------------------------------+----------+-------+----------------------------------
jack | bank_balance_loan | table | deity | {orientation=row,compression=no}
jack | bi_authentication | table | deity | {orientation=row,compression=no}
jack | bi_bank_product | table | deity | {orientation=row,compression=no}
jack | bi_bank_rate | table | deity | {orientation=row,compression=no}
jack | bi_compatible | table | deity | {orientation=row,compression=no}
jack | bi_credit_feedback | table | deity | {orientation=row,compression=no}
jack | bi_customer | table | deity | {orientation=row,compression=no}
jack | bi_disburse_detail | table | deity | {orientation=row,compression=no}
jack | bi_finance_transaction | table | deity | {orientation=row,compression=no}
jack | bi_flow | table | deity | {orientation=row,compression=no}
jack | bi_flow_define | table | deity | {orientation=row,compression=no}
jack | bi_flow_node | table | deity | {orientation=row,compression=no}
jack | bi_monitor_history_record | table | deity | {orientation=row,compression=no}
jack | bi_monitor_record | table | deity | {orientation=row,compression=no}
jack | bi_mutual_excls | table | deity | {orientation=row,compression=no}
jack | bi_order | table | deity | {orientation=row,compression=no}
jack | bi_order_audit | table | deity | {orientation=row,compression=no}
jack | bi_order_collateral | table | deity | {orientation=row,compression=no}
jack | bi_order_collateral_owner | table | deity | {orientation=row,compression=no}
jack | bi_order_push | table | deity | {orientation=row,compression=no}
jack | bi_order_veritify | table | deity | {orientation=row,compression=no}
jack | bi_orglist | table | deity | {orientation=row,compression=no}
jack | bi_pay_repay | table | deity | {orientation=row,compression=no}
jack | bi_pre_credit | table | deity | {orientation=row,compression=no}
jack | bi_reason_rule | table | deity | {orientation=row,compression=no}
jack | bi_reconl_record | table | deity | {orientation=row,compression=no}
jack | bi_schedule_job | table | deity | {orientation=row,compression=no}
jack | bi_schedule_record | table | deity | {orientation=row,compression=no}
jack | bi_tax_organization | table | deity | {orientation=row,compression=no}
jack | bi_transaction_flow | table | deity | {orientation=row,compression=no}
jack | bi_transaction_node_detail | table | deity | {orientation=row,compression=no}
jack | bi_warm_result | table | deity | {orientation=row,compression=no}
jack | bi_whitelist | table | deity | {orientation=row,compression=no}
jack | credit_parse_record | table | deity | {orientation=row,compression=no}
jack | data_anti_fraud | table | deity | {orientation=row,compression=no}
jack | data_blacklist | table | deity | {orientation=row,compression=no}
jack | data_msg_record | table | deity | {orientation=row,compression=no}
jack | data_nosuitable_loan | table | deity | {orientation=row,compression=no}
jack | data_record | table | deity | {orientation=row,compression=no}
jack | data_rule_decisions | table | deity | {orientation=row,compression=no}
jack | data_sample_credit | table | deity | {orientation=row,compression=no}
jack | dm_hydl | table | deity | {orientation=row,compression=no}
jack | dm_hyml | table | deity | {orientation=row,compression=no}
jack | dm_hymx | table | deity | {orientation=row,compression=no}
jack | dm_hyzl | table | deity | {orientation=row,compression=no}
jack | dm_xzqh_wen | table | deity | {orientation=row,compression=no}
jack | er_basic | table | deity | {orientation=row,compression=no}
chnbs=> select count(*) from bi_order;
count
-------
407
(1 row)
...全文
请发表友善的回复…
发表回复
weixin_62697023 2023-03-18
- 打赏
- 举报
chnbs=> \i home/omm/mydb.sql 应该写成
chnbs=> \i /home/omm/mydb.sql 或者
chnbs=> \i mydb.sql
