安装
# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Install PostgreSQL:
sudo yum install -y postgresql15-server
##如果需要安装postgresql15-devel
wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/llvm5.0-devel-5.0.1-7.el7.x86_64.rpm
wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/llvm5.0-5.0.1-7.el7.x86_64.rpm
wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/llvm5.0-libs-5.0.1-7.el7.x86_64.rpm
yum install -y centos-release-scl-rh llvm5*
yum install -y postgresql15-devel
# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15
安装exten
yum install pgvector_15
配置
修改监听
修改监听地址为任意地址
vi /var/lib/pgsql/15/data/postgresql.conf在第60行处增加
54 #------------------------------------------------------------------------------
55 # CONNECTIONS AND AUTHENTICATION
56 #------------------------------------------------------------------------------
57
58 # - Connection Settings -
59
60 listen_addresses = '*'
61 #listen_addresses = 'localhost' # what IP address(es) to listen on;
62 # comma-separated list of addresses;
63 # defaults to 'localhost'; use '*' for all
允许所有IP访问
vi /var/lib/pgsql/15/data/pg_hba.conf在第90行处增加一行
84 # "local" is for Unix domain socket connections only
85 local all all peer
86 # IPv4 local connections:
87 host all all 127.0.0.1/32 scram-sha-256
88 # IPv6 local connections:
89 host all all ::1/128 scram-sha-256
90 # add by dba
91 host all all 0.0.0.0/0 scram-sha-256
保存，重启服务
systemctl restart postgresql-15
修改默认密码
su - postgres
psql
// 修改密码
alter user postgres with password 'dboop.com';
测试安装结果
-bash-4.2$ psql
psql (15.2)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)
postgres=#
postgres=# create database dbtest;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
dbtest | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
postgres=# create user test_1 with password 'dboop.com';
CREATE ROLE
postgres=# grant all privileges on database dbtest to test_1;
GRANT
postgres=# \c dbtest
You are now connected to database "dbtest" as user "postgres".
dbtest=# create table orderuser(id int not null primary key ,username varchar(50));
CREATE TABLE
dbtest=# select * from orderuser;
id | username
----+----------
(0 rows)
dbtest=# insert into orderuser (id,username) values (1,'张三');
INSERT 0 1
dbtest=# select * from orderuser;
id | username
----+----------
1 | 张三
(1 row)
dbtest=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | orderuser | table | postgres
(1 row)
dbtest=# \d orderuser
Table "public.orderuser"
Column | Type | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
id | integer | | not null |
username | character varying(50) | | |
Indexes:
"orderuser_pkey" PRIMARY KEY, btree (id)
dbtest=# drop database dbtest;
ERROR: cannot drop the currently open database
dbtest-# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres-# drop database dbtest
postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
dbtest | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =Tc/postgres +
| | | | | | | postgres=CTc/postgres+
| | | | | | | test_1=CTc/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
以上是centos7上安装postgreSQL的步骤
附：常用命令
1 连接数据库：psql -U postgres （psql -U username -d databse_name -h host -W）
-U 指定用户 -d 指定数据库 -h 要链接的主机 -W 提示输入密码
2、切换数据库：\c dbname
3、列举数据库：\l
4、列举表：\dt
5、查看表结构：\d tblname
6、查看索引：\di
7、创建数据库：create database dbname;
8、删除数据库：drop database dbname;
9、重命名一张表：alter table [表名A] rename to [表名B];
10、删除一张表：drop table
11、在已有的表中添加字段：alter table [表名] add column [字段名][类型];
12、删除表中的字段：alter table [表名] drop column [字段名];
13、重命名一个字段：alter table [表名] rename column [字段名A] to [字段名B];
14、给一个字段设置缺省值：alter table [表名] rename column [字段名] set default [新的默认值];
15、去除缺省值：alter table [表名] alter column [字段名] drop default;
16、在表中插入数据：insert into 表名 ([字段名m], [字段名n], ....) values ([列m的值], [列n的值], ...);
17、修改表中的某行某列的数据：update [表名] set [目标字段名]=[目标值] where [该行特征];
18、修改表中某行数据：delete from [表名] where [该行特征];
19、删除整个表：delete from [表名];
20、创建表：create table tbname ([字段名1] [类型1], [字段名2] [类型2], .... primary key (字段名m, 字段名n,...);
21、显示PG的版本信息：\copyright
22、显示字元编码：\encoding
23、SQL命名帮助（用*显示全部命令）：\h [名称]
24、更改用户密码： \password [username]
25、退出postgresql：\q
26、备份数据库到指定目录： pg_dump [dbname] > /opt/1.bak
27、创建用户名称：createuser username;
指定表数据导出：
参考格式：pg_dump -U postgres(用户名) (-t 表名) 数据库名(缺省时同用户名) > c:\fulldb.sql
示例：pg_dump -U postgres -t t_rtic_gs rtic_jam_analysis > /tmp/t_rtic_gs.sql
指定表数据导入：
参考格式：psql -U postgres(用户名) -d 数据库名(缺省时同用户名) < C:\fulldb.sql
示例：psql -U postgres -d rtic_jam_analysis < /tmp/t_rtic_gs.sql
