pgsql安装和基础文档

一.pgsql安装

1.下载

wget https://ftp.postgresql.org/pub/source/v14.5/postgresql-14.5.tar.gz

2.创建用户和文件夹

groupadd postgres
useradd -g postgres postgres
passwd postgres

设定数据库软件安装在/data/postgres/路径下
数据库的数据存放在/data/postgres/data路径下

这里，先把/data/postgres/路径创建出来即可，/data/postgres/data路径不需提前创建，届时初始化数据库的时候，会自动创建。

mkdir -p /data/postgres/13.3/
chown -R postgres:postgres /data/

3.解压源码

tar xzvf postgresql-14.5.tar.gz

4.执行configure
执行configure的命令行选项，–prefix参数，表示把PostgreSQL安装在哪个路径下。这里，我们就把它安装在前面第2步骤中配置的/data/postgres/路径下。默认情况下，不带该参数时，则会安装在/usr/local/pgsql路径下。

cd postgresql-14.5
./configure --prefix=/data/postgres

如果报错readline library not found

yum -y install -y readline-devel

如果报错 zlib library not found

yum install -y zlib

5.make
这里，也可以简单执行make就可以build安装文件的，但是，make world的意思是指把PostgreSQL相关的文档，HTML，以及其它的一些模块都会一起编译进去。比如，在有的环境下遇到无法使用uuid或者使用不了gin index的原因，就是在编译的时候，没有包含这些对应的模块。处理这种的问题的方法也不复杂，只需要进到当时安装时的那个源文件路径下，重新执行make world，然后make install-world。

建议，在初始安装的时候，就直接用make world或等价的gmake world命令。

make world

6.make install

make install-world

7.初始化数据库

su postgres
/data/postgres/bin/initdb -D /data/postgres/data

8.启动数据库

/data/postgres/bin/pg_ctl -D /data/postgres/data -l logfile start

9.修改参数

--在/data/postgres/data/pg_hba.conf文件中，添加下面的一行
#IPv4 local connections:
host  all  all  0.0.0.0/0  trust

表示当前数据库服务器，对使用IPV4地址的客户端机器，允许其通过任意IP地址，通过任意数据库用户发起对该数据库服务器上的任意数据库的访问，但是必须得提供数据库用户密码。

一句话简介：对于使用IPV4地址的客户端，数据库服务器不做限制，可以通过任意用户访问所有数据库

在/data/postgres/data/postgresql.conf 文件中，修改#listen_addresses = ‘localhost’为listen_addresses = ‘*’

如果不修改的话，则客户端只能通过数据库服务器的环路地址127.0.0.1来访问数据库，不能使用服务器的对外IP地址来访问。也就是说，这个数据库只对本机提供服务，其它任何机器都访问不了这个数据库。重启生效

/data/postgres/bin/pg_ctl -D /data/postgres/data restart

二.php扩展安装

1.先安装pgsql客户端

yum install postgresql-devel

2.安装pgsql

 cd /usr/local/src/php-7.2.13/ext/pgsql/
 /usr/local/php/bin/phpize
 ./configure --with-php-config=/usr/local/php/bin/php-config
 make
 make install

3.安装pdo_pgsql

cd /usr/local/src/php-7.2.13/ext/pdo_pgsql/
/usr/local/php/bin/phpize
./configure --with-php-config=/usr/local/php/bin/php-config
make
make install

4.修改php.ini

echo "extension=pgsql.so" >> /usr/local/php/etc/php.ini
echo "extension=pdo_pgsql.so" >> /usr/local/php/etc/php.ini

三.基础用法

1.连接到pgsql客户端

/data/postgres/bin/psql -U postgres

2.创建数据库

CREATE DATABASE testdb;

查看所有库

\l

3.使用超级用户登录数据库 -> 然后执行 ->创建用户及密码 ->设置备注

CREATE USER testuser WITH PASSWORD '123456';
comment on role testuser is '测试'; 

创建完成后，使用下面命令查看所有用户

\du

4.创建schema被赋予权限 ->在数据库testdb session下创建schema

create schema testschema;

PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。

一个模式可以包含视图、索引、数据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。

使用模式的优势：

允许多个用户使用一个数据库并且不会互相干扰。

将数据库对象组织成逻辑组以便更容易管理。

第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突。

模式类似于操作系统层的目录，但是模式不能嵌套。

赋予用户对应的schema权限和表权限

ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON TABLES TO testuser ;
ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT EXECUTE ON FUNCTIONS TO testuser ;
ALTER DEFAULT PRIVILEGES IN SCHEMA testschema GRANT ALL ON SEQUENCES TO testuser ;
ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO testuser ;

GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA testschema  TO testuser ;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA testschema TO testuser ;
GRANT ALL ON ALL SEQUENCES IN SCHEMA testschema TO testuser ;
GRANT USAGE,CREATE ON SCHEMA testschema TO testuser ;

GRANT TEMPORARY ON DATABASE testdb TO testuser ;

5.切换数据库

\c testdb;

6.查看当前库所有表

\d

7.创建表

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

查看表结构

\d COMPANY

8.创建序列

CREATE SEQUENCE t_sts_all_book_id_seq

主要用于设置自增id

ALTER TABLE IF EXISTS public.t_sts_all_book
    ADD COLUMN id integer NOT NULL DEFAULT nextval('t_sts_all_book_id_seq'::regclass);

也可以基于datagrip或者PHPstorm的数据库工具自动生成语句。
9.创建索引

• gin索引
  支持任意组合查询都走索引贼牛逼！

  CREATE INDEX myindex ON company USING gin (name,age);
  

  如果报data type text has no default operator class for access method "gin"
  执行

  create extension btree_gin;

• btree索引

  CREATE INDEX myindex2 ON company USING btree (name);

  10.表空间
  什么是PG中的表空间（tablespace）？

pg中的表空间实际上就是文件系统中的一个目录, 是pg中数据库对象（包括表、索引等）的容器。

Pg使用操作系统的文件系统进行对象存储，每个数据库对象都有自己的数据文件，如果对象很大，它将会被分为多个文件，单个文件大小的上限是1GB。表空间就是包含这些数据文件的目录。

简而言之，表空间是告诉PG服务器将数据库对象的物理文件放在哪里。

11.推荐工具
datagrip 和pgadmin4(更叼一点)

12.结合tp框架使用需要pgsql先创建一下函数

CREATE OR REPLACE FUNCTION pgsql_type(a_type varchar) RETURNS varchar AS
$BODY$
DECLARE
    v_type varchar;
BEGIN
    IF a_type='int8' THEN
        v_type:='bigint';
    ELSIF a_type='int4' THEN
        v_type:='integer';
    ELSIF a_type='int2' THEN
        v_type:='smallint';
    ELSIF a_type='bpchar' THEN
        v_type:='char';
    ELSE
        v_type:=a_type;
    END IF;
    RETURN v_type;
END;
$BODY$
    LANGUAGE PLPGSQL;

CREATE TYPE "public"."tablestruct" AS (
                                          "fields_key_name" varchar(100),
                                          "fields_name" VARCHAR(200),
                                          "fields_type" VARCHAR(20),
                                          "fields_length" BIGINT,
                                          "fields_not_null" VARCHAR(10),
                                          "fields_default" VARCHAR(500),
                                          "fields_comment" VARCHAR(1000)
                                      );

CREATE OR REPLACE FUNCTION "public"."table_msg" (a_schema_name varchar, a_table_name varchar) RETURNS SETOF "public"."tablestruct" AS
$body$
DECLARE
    v_ret tablestruct;
    v_oid oid;
    v_sql varchar;
    v_rec RECORD;
    v_key varchar;
BEGIN
    SELECT
        pg_class.oid  INTO v_oid
    FROM
        pg_class
            INNER JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid AND lower(pg_namespace.nspname) = a_schema_name)
    WHERE
            pg_class.relname=a_table_name;
    IF NOT FOUND THEN
        RETURN;
    END IF;

    v_sql='
     SELECT
           pg_attribute.attname AS fields_name,
           pg_attribute.attnum AS fields_index,
           pgsql_type(pg_type.typname::varchar) AS fields_type,
           pg_attribute.atttypmod-4 as fields_length,
           CASE WHEN pg_attribute.attnotnull  THEN ''not null''
           ELSE ''''
           END AS fields_not_null,
           pg_get_expr(pg_attrdef.adbin, pg_attrdef.adrelid) AS fields_default,
           pg_description.description AS fields_comment
     FROM
           pg_attribute
           INNER JOIN pg_class  ON pg_attribute.attrelid = pg_class.oid
           INNER JOIN pg_type   ON pg_attribute.atttypid = pg_type.oid
           LEFT OUTER JOIN pg_attrdef ON pg_attrdef.adrelid = pg_class.oid AND pg_attrdef.adnum = pg_attribute.attnum
           LEFT OUTER JOIN pg_description ON pg_description.objoid = pg_class.oid AND pg_description.objsubid = pg_attribute.attnum
     WHERE
           pg_attribute.attnum > 0
           AND attisdropped <> ''t''
           AND pg_class.oid = ' || v_oid || '
     ORDER BY pg_attribute.attnum' ;

    FOR v_rec IN EXECUTE v_sql LOOP
            v_ret.fields_name=v_rec.fields_name;
            v_ret.fields_type=v_rec.fields_type;
            IF v_rec.fields_length > 0 THEN
                v_ret.fields_length:=v_rec.fields_length;
            ELSE
                v_ret.fields_length:=NULL;
            END IF;
            v_ret.fields_not_null=v_rec.fields_not_null;
            v_ret.fields_default=v_rec.fields_default;
            v_ret.fields_comment=v_rec.fields_comment;
            SELECT constraint_name INTO v_key FROM information_schema.key_column_usage WHERE table_schema=a_schema_name AND table_name=a_table_name AND column_name=v_rec.fields_name;
            IF FOUND THEN
                v_ret.fields_key_name=v_key;
            ELSE
                v_ret.fields_key_name='';
            END IF;
            RETURN NEXT v_ret;
        END LOOP;
    RETURN ;
END;
$body$
    LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

COMMENT ON FUNCTION "public"."table_msg"(a_schema_name varchar, a_table_name varchar)
    IS '获得表信息';

---重载一个函数
CREATE OR REPLACE FUNCTION "public"."table_msg" (a_table_name varchar) RETURNS SETOF "public"."tablestruct" AS
$body$
DECLARE
    v_ret tablestruct;
BEGIN
    FOR v_ret IN SELECT * FROM table_msg('public',a_table_name) LOOP
            RETURN NEXT v_ret;
        END LOOP;
    RETURN;
END;
$body$
    LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

COMMENT ON FUNCTION "public"."table_msg"(a_table_name varchar)
    IS '获得表信息';

还有tp批量插入有问题