openGauss或PostgreSQL生成多个库模式表数据
【代码】openGauss或PostgreSQL生成多个库模式表数据。
·
PostgreSQL或openGauss生成多个数据库、模式、表、行
脚本
#!/bin/bash
db_type=PostgreSQL #输入数据库类型 openGauss/PostgreSQL
db_user=test
db_port=5432
if [ $db_type == "openGauss" ];then
app="gsql"
else
app="psql"
fi
$app -d postgres -p $db_port -c "create user $db_user with password 'test@123';"
read -p '请输入想要创建的数据库的个数:' dbs
read -p '请输入每个数据库下要创建的schema的个数:' schs
read -p '请输入每个schema下要创建的表的个数:' tbs
read -p '请输入每个测试表数据的行数:' rows
for i in `seq 1 $dbs`
do
$app -d postgres -p $db_port -c "create database testdb$i with owner=$db_user;"
for j in `seq 1 $schs`
do
$app -d testdb$i -p $db_port -c "create schema test_schema$j;"
for k in `seq 1 $tbs`
do
$app -d testdb$i -p $db_port -c "create table test_schema$j.test_t$k(id int,col1 varchar(100));insert into test_schema$j.test_t$k select generate_series(1,$rows),substr(md5(random()::text),1,5);"
done
done
done
执行演示
[pg12@pghost1 ~]$ vim 1.sh
[pg12@pghost1 ~]$
[pg12@pghost1 ~]$ sh 1.sh
CREATE ROLE
请输入想要创建的数据库的个数:2
请输入每个数据库下要创建的schema的个数:3
请输入每个schema下要创建的表的个数:4
请输入每个测试表数据的行数:10
CREATE DATABASE
CREATE SCHEMA
INSERT 0 10
INSERT 0 10
INSERT 0 10
INSERT 0 10
CREATE SCHEMA
INSERT 0 10
INSERT 0 10
INSERT 0 10
INSERT 0 10
CREATE SCHEMA
INSERT 0 10
INSERT 0 10
INSERT 0 10
INSERT 0 10
CREATE DATABASE
CREATE SCHEMA
INSERT 0 10
INSERT 0 10
INSERT 0 10
INSERT 0 10
CREATE SCHEMA
INSERT 0 10
INSERT 0 10
INSERT 0 10
INSERT 0 10
CREATE SCHEMA
INSERT 0 10
INSERT 0 10
INSERT 0 10
INSERT 0 10
[pg12@pghost1 ~]$ psql
psql (12.6)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb1 | test | UTF8 | en_US.utf8 | en_US.utf8 |
testdb2 | test | UTF8 | en_US.utf8 | en_US.utf8 |
(5 rows)
postgres=# \c testdb1
You are now connected to database "testdb1" as user "postgres".
testdb1=# \dn
List of schemas
Name | Owner
--------------+----------
public | postgres
test_schema1 | postgres
test_schema2 | postgres
test_schema3 | postgres
(4 rows)
testdb1=# set search_path to test_schema1;
SET
testdb1=#
testdb1=# \dt
List of relations
Schema | Name | Type | Owner
--------------+---------+-------+----------
test_schema1 | test_t1 | table | postgres
test_schema1 | test_t2 | table | postgres
test_schema1 | test_t3 | table | postgres
test_schema1 | test_t4 | table | postgres
(4 rows)
testdb1=# select count(*) from test_t1;
count
-------
10
(1 row)
testdb1=# select * from test_t1;
id | col1
----+-------
1 | 8a375
2 | 627fc
3 | 32355
4 | 0c9e9
5 | be1d5
6 | d93a0
7 | c834e
8 | 544c0
9 | f1357
10 | 3bf4f
(10 rows)
testdb1=# \q
[pg12@pghost1 ~]$
更多推荐
已为社区贡献1条内容
所有评论(0)