CREATE UNIQUE INDEX idx_mdata_main on mdata (server_ip, server_port, vtype); // 创建唯一的关键字段索引
ALTER TABLE public.mdata OWNER TO apguser; // 安全需要 更改数据到普通用户
实际落地的操作
postgres@pve-debian-server-02:~$ psql -d apgdb
psql (13.3 (Debian 13.3-1.pgdg100+1))
apgdb=# \d
Did not find any relations.
apgdb=# CREATE TABLE mdata (
apgdb(# idx bigserial not null primary key,
apgdb(# server_ip inet not null,
apgdb(# server_port integer not null,
apgdb(# vtype varchar(6) not null,
apgdb(# auth varchar(6) not null default 'NoAuth',
apgdb(# auth_name text,
apgdb(# auth_pass text,
apgdb(# status varchar(1) not null default 'N',
apgdb(# cnt integer not null default 0,
apgdb(# y_cnt integer not null default 0,
apgdb(# n_cnt integer not null default 0,
apgdb(# constncnt integer not null default 0,
apgdb(# rsp_time real default 0.0,
apgdb(# download_speed real default 0.0,
apgdb(# upload_speed real default 0.0,
apgdb(# location varchar(6),
apgdb(# location_more text,
apgdb(# isp text,
apgdb(# anonymity varchar(1) default 'N',
apgdb(# insert_time timestamp without time zone default (now()),
apgdb(# update_time text,
apgdb(# remark text
apgdb(# );
CREATE TABLE
apgdb=# \d
public | mdata | table | postgres
public | mdata_idx_seq | sequence | postgres
apgdb=# CREATE UNIQUE INDEX idx_mdata_main on mdata (server_ip, server_port, vtype);
CREATE INDEX
apgdb=# ALTER TABLE public.mdata OWNER TO apguser;
ALTER TABLE
apgdb=#
apgdb=#
apgdb=# \d
public | mdata | table | apguser
public | mdata_idx_seq | sequence | apguser
apgdb=#
apgdb=# insert into mdata(server_ip, server_port, vtype) values('37.49.127.234',1080,'socks5');
INSERT 0 1
apgdb=# select server_ip, server_port, vtype, status, cnt, y_cnt, n_cnt, constncnt, location, insert_time, update_time from mdata limit 3;
server_ip | server_port | vtype | status | cnt | y_cnt | n_cnt | constncnt | location | insert_time | update_time
---------------+-------------+--------+--------+-----+-------+-------+-----------+----------+----------------------------+-------------
172.21.22.3 | 8080 | socks5 | N | 0 | 0 | 0 | 0 | | 2021-06-22 12:12:59.439876 |
175.1.22.3 | 8080 | http | N | 0 | 0 | 0 | 0 | | 2021-06-22 12:12:59.447857 |
24.249.199.12 | 4145 | socks5 | N | 0 | 0 | 0 | 0 | | 2021-06-22 12:12:59.451406 |
(3 rows)
apgdb=#