postgresql学习记录

一、数据库安装

centos系统自带的数据库版本比较低，我们这里参照官网安装postgresql 11版本

1、安装存储库RPM：

$ yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2、安装客户端软件包

yum install postgresql11

3、安装服务器包（可选）

yum install postgresql11-server

4、初始化并且启动数据库

/usr/pgsql-11/bin/postgresql-11-setup initdb
systemctl enable postgresql-11
systemctl start postgresql-11

5、登陆数据库

$ su - postgres

$ -bash-4.2$ psql
psql (11.3)
输入 "help" 来获取帮助信息.

$ postgres=#

6、数据库默认数据存放目录

-bash-4.2$ pwd
/var/lib/pgsql/data

二、数据库的初始设置

1、修改监听地址和端口

$ vim /var/lib/pgsql/11/data/postgresql.conf

listen_addresses = '*'        //默认是localhost，也就是127.0.0.1，无法从其他机器远程登陆数据库

port = 5432                    //默认端口，如果有多个数据库实例，可以修改为不同的端口

2、内存参数调整

shared_buffers =128M（默认）   // 共享内存大小，用于共享数据块，根据自己机器内存大小适当调节，大一些可以缓存更多数据，更高效的处理请求

work_mem = 4M       //单个SQL语句执行、排序、hash join使用内存，会自动释放

3、修改数据库log相关参数（同上面的配置参数）

logging_collector = on

log_directory = 'pg_log'

日志的切换和是否选择覆盖可以使用如下几种方案(系统是默认保存一天的):

a、每天生成一个新的日志文件
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 1d  
log_rotation_size = 0

b、每当日志写满一定的大小（如100M），则切换一个新日志
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 0  
log_rotation_size = 100M

c、只保留7天的日志，进行循环覆盖
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = off
log_rotation_age = 7d  
log_rotation_size = 0

4、重启数据库

$ systemctl restart postgresql-11

三、数据库的基本操作

1、创建一个学生表

postgres=# CREATE TABLE score (student_name varchar(40),chinese_score int, test_date date);

postgres=# create table student(no int primary key,student_name varchar(40),age int);


postgres=# \d                                    // \d查看有哪些表

postgres=# \d+                                 //查看表的数据更详细

postgres=# \l                                     //查看有哪些数据库

                                List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

               关联列表
 架构模式 | 名称  |  类型  |  拥有者
----------+-------+--------+----------
 public   | score | 数据表 | postgres
(1 行记录)
    
postgres=# CREATE DATABASE testdb;      //创建数据库
CREATE DATABASE

postgres=# \c testdb ;                        //切换到指定数据库
You are now connected to database "testdb" as user "postgres".

postgres=# \d score    //查看表的结构

postgres=# DROP TABLE score;           //删除一张表

postgres=# insert into student values (1,'张三',18);     //插入一条数据到学生表

postgres=# select * from student;      //查询表里面所有的记录

postgres=# UPDATE student SET age = 15;    //修改某一列数据的值
                      

四、Psql工具使用

1、远程连接数据库
psql -h <hostname or ip> -p <端口> [数据库名称] [用户名称]

[root@test ~]# vim /var/lib/pgsql/11/data/pg_hba.conf

# IPv4 local connections:
host    all             all             180.76.118.22/24         trust

trust：表示信任，不需要密码既可以连接，远程来说非常的不安全
md5：表示需要密码认证才能连接，可以给用户设置一个密码

2、给postgres数据库设置密码或者修改密码（先连接数据库）

postgres=# ALTER USER postgres WITH PASSWORD 'postgres';

>>>密码postgres要用引号引起来

3、显示SQL语句的执行时间

postgres=# select * from student;
 no | student_name | age 
----+--------------+-----
  1 | 张三         |  15
(1 row)

Time: 0.718 ms

4、postgres=# \dn      //列出所有的schema

5、postgres=# \db     //列出所有的表空间

6、postgres=# \encoding utf8;   //设置默认编码方式

连续两个tab键表示命令补全

五、逻辑结构

1、表、索引，在pg中叫做Relation,其他数据库叫做Table

数据行，pg中叫做Tuple,其他数据库叫做Row

2、postgres=# ALTER database osdbadb CONNECTION LIMIT 10;    //修改数据的最大连接为10

3、postgres=# ALTER DATABASE osdbadb RENAME TO osdbadb01;   //修改数据库名称，不能在登录的数据库修改自己名称，道理和大力士不能举起自己是一样的

4、模式：一个命名空间或目录，不同模式下可以有相同名称的表、函数，只是为了便于管理，与mysql的database概念是相等的，

postgres=# create schema osdba;    //创建一个模式

postgres=# \dn                             //查看有哪些模式