postgreSQL的安装和初始化(centos7)

安装

# 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

配置

修改监听

修改监听地址为任意地址

 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访问

 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

>> Home

51ak

2023/04/26

Categories: postgreSQL Tags: 整理

《数据库工作笔记》公众号
扫描上面的二维码,关注我的《数据库工作笔记》公众号