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 ~]$