PostgreSQL安装、配置及简单使用方法

    一、PostgreSQL简介

    1、什么是PostgreSQL

    PostgreSQL数据库是目前功能最强大的开源数据库,支持丰富的数据类型(如JSON何JSONB类型,数组类型)和自定义类型。而且它提供了丰富的接口,可以很容易地扩展它的功能,如可以在GiST框架下实现自己的索引类型等,它还支持使用C语言写自定义函数、触发器,也支持使用流行的语言写自定义函数,比如其中的PL/Perl提供了使用Perl语言写自定义函数的功能,当然还有PL/Python、PL/Tcl,等等。

    2、PostgreSQL数据库的优势

    PostgreSQL数据库是目前功能最强大的开源数据库,它是最接近工业标准SQL92的查询语言,并且正在实现新的功能已兼容最新的SQL标准:SQL2003.

    稳定可靠:PostgreSQL是唯一能做到数据零丢失的开源数据库。有报道称国外的部分银行也在使用PostgreSQL。

    开源省钱:PostgreSQL数据库是开源的、免费的,而且是BSD协议,在使用和二次开发上基本没有限制。

    支持广泛:PostgreSQL数据库支持大量的主流开发语言,包括C、C++、Perl、Python、Java、Tcl,和PHP等。

    PostgreSQL社区活跃:PostgreSQL基本上每三个月推出一个补丁版本,这意味着已知的BUG很快会被修复,有应用场景的需求也会及时得到响应。

    二、PostgreSQL安装与配置

    #安装前准备:

    1、系统版本

    
    [root@node1 ~]# cat /etc/redhat-release
    CentOS Linux release 7.2.1511 (Core)

    2、yum安装(在官网上找到对应版本的yum源,之后安装到本地。

    
    [root@node1 ~]# yum -y install pgdg-centos96-9.6-3.noarch.rpm #yum源安装
    [root@node1 ~]# yum -y install postgresql-server #安装postgreesql
    #安装生成的文件
    [root@node1 ~]# rpm -ql postgresql-server
    /etc/pam.d/postgresql
    /usr/bin/initdb
    /usr/bin/pg_basebackup
    /usr/bin/pg_controldata
    /usr/bin/pg_ctl
    /usr/bin/pg_receivexlog
    /usr/bin/pg_resetxlog
    /usr/bin/postgres
    /usr/bin/postgresql-check-db-dir
    /usr/bin/postgresql-setup
    /usr/bin/postmaster
    /usr/lib/systemd/system/postgresql.service
    /usr/lib/tmpfiles.d/postgresql.conf
    /var/lib/pgsql
    /var/lib/pgsql/.bash_profile
    /var/lib/pgsql/backups
    /var/lib/pgsql/data
    /var/run/postgresql、
    #启动postgresql
    #直接启动会报错:
    [root@node1 ~]# systemctl start postgresql.service
    Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details.
    #上面是提示数据库还没有初始化,所以我们先初始化一下
    postgresql-setup initdb
    Initializing database ... OK #提示初始化成功
    #重新启动Postgresql
    [root@node1 ~]# systemctl start postgresql.service
    [root@node1 ~]# netstat -tnlp
    Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name 
    tcp  0  0 127.0.0.1:5432  0.0.0.0:*  LISTEN  1512/postgres  
    tcp6  0  0 ::1:5432    :::*  LISTEN  1512/postgres 
    #查看运行状态
    [root@node1 ~]# systemctl status postgresql.service
    ● postgresql.service - PostgreSQL database server
     Active: active (running) since Sat 2016-11-26 22:49:07 CST; 1min 33s ago
    #切换到操作系统下的“postgres”用户,登录数据库
    [root@node1 ~]# su - postgres
    -bash-4.2$ psql
    psql (9.2.15)
    Type "help" for help.
    postgres=# help
    You are using psql, the command-line interface to PostgreSQL.
    Type: \copyright for distribution terms
      \h for help with SQL commands
      \? for help with psql commands
      \g or terminate with semicolon to execute query
      \q to quit
    #到此为止,基本安装已经完成。

    3、源码安装

    #首先到官方网站下载源代码(https://www.postgresql.org/ftp/source/

    
    #开始编译安装
    [root@node1 soft]# tar xf postgresql-9.6.1.tar.bz2
    [root@node1 soft]# cd postgresql-9.6.1
    # yum -y groupinstall "Development tools" #开发包组
    # yum -y install perl-ExtUtils-Embed readline-devel zlib-devel python-devel #依赖包
    # ./configure --prefix=/usr/local/postgresql-9.6.1 --with-perl --with-python --with-blocksize=32 --with-wal-blocksize=64 --with-wal-segsize=64
    # make && make install
    #安装后的配置
    [root@node1 postgresql-9.6.1]# cat /etc/profile.d/postgresql.sh
    export PATH=$PATH:/usr/local/pgsql/bin
    export PGDATA=/data/pgdata
    [root@node1 postgresql-9.6.1]# source /etc/profile.d/postgresql.sh
    [root@node1 postgresql-9.6.1]# echo "/usr/local/pgsql/lib" > /etc/ld.so.conf.d/pgsql.conf
    [root@node1 postgresql-9.6.1]# ldconfig
    #创建数据库目录并初始化数据库
    [root@node1 postgresql-9.6.1]# mkdir /data/pgdata/
    [root@node1 postgresql-9.6.1]# chown -R postgres.postgres /data/pgdata/
    [root@node1 postgresql-9.6.1]# su - postgres
    -bash-4.2$ initdb
    The database cluster will be initialized with locale "en_US.UTF-8".
    The default database encoding has accordingly been set to "UTF8".
    The default text search configuration will be set to "english".
    fixing permissions on existing directory /data/pgdata ... ok
    creating subdirectories ... ok
    selecting default max_connections ... 100
    selecting default shared_buffers ... 128MB
    selecting dynamic shared memory implementation ... posix
    creating configuration files ... ok
    running bootstrap script ... ok
    performing post-bootstrap initialization ... ok
    syncing data to disk ... ok
    Success. You can now start the database server using:
     pg_ctl -D /data/pgdata -l logfile start
    #安装contrib目录下的工具
    # cd postgresql-9.6.1/contrib/
    # make
    # make install
    #启动和停止数据库
    # pg_ctl start -D $PGDATA #PGDATA是pgsql的数据目录
    # pg_ctl stop -D $PGDATA [-m SHUTDOWN-MODE]
    其中-m是制定数据库的停止方法,有以下三种
    smart:等所有的连接中止后,关闭数据库。如果客户端不中止,则无法关闭数据库。
    fast:快速关闭数据库,断开客户端的连接,让已有的事务回滚,然后正常关闭数据库。
    immediate:立即关闭数据库,相当于数据库进程立即停止,直接退出,下次启动数据库需要进行修复。

    4、PostgreSQL的简单配置

    
    在数据目录下编辑postgresql.conf文件,找到如下内容:
    #listen_addresses = 'localhost'   # what IP address(es) to listen on;
    #port = 5432       # (change requires restart)
    listen_addresses表示要监听的地址,要让网络上的主机登录这台数据库,需要把这个地址改成"*",或者0.0.0.0。
    port表示监听的端口,可以不更改,修改这两个参数后,需要重启才能生效。
    #与数据库Log相关的参数
    logging_collector = on #日志的收集,on表示打开
    log_directory = 'pg_log' #定义日志的收集目录
    日志的切换和是否选择覆盖则可以使用如下几种方案
    方案一:每天生产一个新的日志文件
    log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log'
    log_truncate_on_rotation = off
    log_rotation_age = 1d
    log_rotation_size = 0
    方案二:每当日志写满一定的大小(如10MB空间),则切换一个日志
    log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log'
    log_truncate_on_rotation = off
    log_rotation_age = 0
    log_rotation_size = 10M
    方案三:只保留7天的日志,进行循环覆盖 
    log_filename = ‘postgresql-%a.log'
    log_truncate_on_rotation = off
    log_rotation_age = 1d
    log_rotation_size = 0

    5、内存参数的设置

    shared_buffers:共享内存的大小,主要用于共享数据块。

    #shared_buffers默认值为32MB,如果有足够的内存,可以把这个参数改得大一些,这样数据库就可以缓存更多的数据库,当读取数据时,就可以从共享内存中读,而不需要再从文件上去读取。

    work_mem:单个SQL执行时,排序、hash join所使用的内存,SQL运行完后,内存就释放了,把这个值设大一些,会让排序操作快一些。

    三、SQL语法入门

    1、SQL语句语法简介

    (1)、语句的分类(SQL命令一般分为DDL、DML、DQL几类)

    DDL:Data Definition Language的缩写,即数据定义语言,主要用于创建、删除,以及修改表、索引等数据库对象语言。

    DML:Data Manipulation Language的简称,即数据操纵语言,主要用于插入、更新、删除数据,所以也分为INSERT、UPDATE、DELETE三种语句。

    DQL:数据库查询语句,基本及时SELECT查询命令,用于数据查询。