docker run -d --name pgsql \
  -e POSTGRES_USER=admin \
  -e POSTGRES_PASSWORD=admin \
  -e POSTGRES_DB=testdb \
  -p 15432:5432 \
  postgres:16.2

docker exec -it pgsql psql -U admin -d testdb

CREATE TABLE "user" (
    id bigserial PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
);
CREATE TABLE "host" (
    id bigserial PRIMARY KEY,
    hostname VARCHAR(255) NOT NULL
);
CREATE TABLE "vps" (
    id bigserial PRIMARY KEY,
    user_id bigint NOT NULL,
    host_id bigint NOT NULL,
    name VARCHAR(255) NOT NULL,
    sys_disk jsonb NOT NULL DEFAULT '{}'
);

testdb=# \z
                                 Access privileges
 Schema |    Name     |   Type   | Access privileges | Column privileges | Policies
--------+-------------+----------+-------------------+-------------------+----------
 public | host        | table    |                   |                   |
 public | host_id_seq | sequence |                   |                   |
 public | user        | table    |                   |                   |
 public | user_id_seq | sequence |                   |                   |
 public | vps         | table    |                   |                   |
 public | vps_id_seq  | sequence |                   |                   |
(6 rows)

testdb=# \d user
                                     Table "public.user"
  Column  |          Type          | Collation | Nullable |             Default
----------+------------------------+-----------+----------+----------------------------------
 id       | bigint                 |           | not null | nextval('user_id_seq'::regclass)
 username | character varying(255) |           | not null |
 password | character varying(255) |           | not null |
Indexes:
    "user_pkey" PRIMARY KEY, btree (id)

testdb=# \d host
                                     Table "public.host"
  Column  |          Type          | Collation | Nullable |             Default
----------+------------------------+-----------+----------+----------------------------------
 id       | bigint                 |           | not null | nextval('host_id_seq'::regclass)
 hostname | character varying(255) |           | not null |
Indexes:
    "host_pkey" PRIMARY KEY, btree (id)

testdb=# \d vps
                                     Table "public.vps"
  Column  |          Type          | Collation | Nullable |             Default
----------+------------------------+-----------+----------+---------------------------------
 id       | bigint                 |           | not null | nextval('vps_id_seq'::regclass)
 user_id  | bigint                 |           | not null |
 host_id  | bigint                 |           | not null |
 name     | character varying(255) |           | not null |
 sys_disk | jsonb                  |           | not null | '{}'::jsonb
Indexes:
    "vps_pkey" PRIMARY KEY, btree (id)

-- 插入用户数据
INSERT INTO "user" (username, password) VALUES
    ('user1', 'password1'),
    ('user2', 'password2');

-- 插入主机数据
INSERT INTO "host" (hostname) VALUES
    ('host1'),
    ('host2');

-- user1 在 host1 上创建一个 VPS
INSERT INTO "vps" (user_id, host_id, name, sys_disk) VALUES
    ((SELECT id FROM "user" WHERE username = 'user1'), (SELECT id FROM host WHERE hostname = 'host1'), 'vps_user1_host1', '{"disk_size": 50}');

-- user2 在 host1 上创建一个 VPS
INSERT INTO "vps" (user_id, host_id, name, sys_disk) VALUES
    ((SELECT id FROM "user" WHERE username = 'user2'), (SELECT id FROM host WHERE hostname = 'host1'), 'vps_user2_host1', '{"disk_size": 60}');

-- user1 在 host2 上创建一个 VPS
INSERT INTO "vps" (user_id, host_id, name, sys_disk) VALUES
    ((SELECT id FROM "user" WHERE username = 'user1'), (SELECT id FROM host WHERE hostname = 'host2'), 'vps_user1_host2', '{"disk_size": 70}');

-- user2 在 host2 上创建一个 VPS
INSERT INTO "vps" (user_id, host_id, name, sys_disk) VALUES
    ((SELECT id FROM "user" WHERE username = 'user2'), (SELECT id FROM host WHERE hostname = 'host2'), 'vps_user2_host2', '{"disk_size": 80}');