linux安装postgresql,服务器CentOS Linux release 7.6.1810 (Core)

1. 建立/postgres文件夹，postgres的家目录是这个。安装rzsz，可以传文件
   Creating mailbox file: File exists问题，删除家目录和
   /var/spool/mail/

[root@VM_0_3_centos /]# useradd -d /postgres -m postgres
[root@VM_0_3_centos /]# chmod 777 postgres
[root@VM_0_3_centos /]# yum install lrzsz

2. 上传postgresql-9.4.4.tar.gz文件至/postgres,解压、编译、安装，注意用户为root的要新开一个窗口操作或切回用户su - root

[postgres@VM_0_3_centos postgres]$ rz
[postgres@VM_0_3_centos postgres]$ tar -xzvf postgresql-9.5.7.tar.gz
[postgres@VM_0_3_centos postgres]$ ll
total 23756
drwxrwxr-x 6 postgres postgres     4096 May  9  2017 postgresql-9.5.7
-rw-r--r-- 1 postgres postgres 24318941 May 11 10:07 postgresql-9.5.7.tar.gz
[postgres@VM_0_3_centos postgres]$ cd postgresql-9.5.7/
[root@VM_0_3_centos /]# yum install perl-ExtUtils-Embed
[root@VM_0_3_centos /]# yum install readline-devel
[root@VM_0_3_centos /]# yum -y install zlib-devel
[root@VM_0_3_centos bin]# yum install gcc-c++

[postgres@VM_0_3_centos postgresql-9.5.7]$ ./configure --prefix=/postgres/pgsql -with-perl
All of PostgreSQL successfully made. Ready to install.
[postgres@VM_0_3_centos postgresql-9.5.7]$ make install

3. 初始化数据库：
   切换到root用户，新建pg数据库的数据文件存放目录pgdata和表空间存放目录tablespace

pg数据库的安装路径为postgres/pgsql
pg数据库的日志路径为data/pglog
pg数据库的安装包直接在postgres/解压
pg数据库的数据文件路径为data/pgdb/pgdata
pg数据库的表空间路径为 data/pgdb/tablespace

[root@VM_0_3_centos /]# mkdir -p /data/pgdb/tablespace

改变pgdb目录及子目录归属postgres用户，并切回postgres用户

[root@VM_0_3_centos pgdb]# cd /data
[root@VM_0_3_centos data]# chown -Rf postgres: postgres pgdb/

添加PGHOME、PGDATA参数,看下有没有生效

[postgres@sitdb1 ~]$ vi ~/.bash_profile

添加如下参数
PGHOME=/postgres/pgsql/bin
PGDATA=/data/pgdb/pgdata
export  PGDATA
修改如下项
PATH=$PGHOME:$PATH:$HOME/bin
生效
[postgres@sitdb1 ~]$ source ~/.bash_profile


[postgres@VM_0_3_centos pgsql]$ vi ~/.bash_profile
[postgres@VM_0_3_centos pgsql]$ echo $PATH
/opt/java/jdk1.8.0_161/bin:/opt/java/jdk1.8.0_161/jre/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/postgres/.local/bin:/home/postgres/bin
[postgres@VM_0_3_centos pgsql]$ source ~/.bash_profile
[postgres@VM_0_3_centos pgsql]$ echo $PATH
/postgres/pgsql/bin:/opt/java/jdk1.8.0_161/bin:/opt/java/jdk1.8.0_161/jre/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/postgres/.local/bin:/home/postgres/bin:/home/postgres/bin

初始化数据库并设置数据库字符集，参数为：数据库安装路径bin目录和数据库数据文件存放路径

[postgres@VM_0_3_centos pgdb]$ cd /data/pgdb
[postgres@VM_0_3_centos pgdb]$ mkdir pgdata
[postgres@VM_0_3_centos pgsql]$ cd ~
[postgres@VM_0_3_centos ~]$ /postgres/pgsql/bin/initdb -E UTF-8 -D /data/pgdb/pgdata --locale=zh_CN.UTF-8

如果报错Error while loading shared libraries: libpq.so.5: cannot open shared object file
未安装依赖yum install postgresql-libs.x86_64

4. 配置数据库

[postgres@VM_0_3_centos ~]$ mkdir /postgres/pglog
[postgres@VM_0_3_centos ~]$ cd /data/pgdb/pgdata
[postgres@VM_0_3_centos pgdata]$ vi postgresql.conf

进入数据库数据文件目录，配置postgresql.conf

listen_addresses = '*'            # what IP address(es) to listen on;
port = 5432                    # (change requires restart)
max_connections = 300         # (change requires restart)
logging_collector = on           # Enable capturing of stderr and csvlog
log_directory = '/postgres/pglog'  # directory where log files are written,             
log_filename = 'postgresql-%u.log'   # log file name pattern,
log_file_mode = 0644                    #0640 or 0644
log_truncate_on_rotation = on
log_line_prefix = '%m%%%u%%%d'

相同路径下，配置配置pg_hba.conf,md5验证密码，trust不验证密码

# IPv4 local connections:
host    all             all            127.0.0.1/32            md5
host    all             all            0.0.0.0/0               md5
# IPv6 local connections:
host    all             all            ::1/128                 md5

5. 启动及关闭数据库

[postgres@VM_0_3_centos pgdata]$ cd /postgres/pgsql/bin/
[postgres@VM_0_3_centos bin]$ pg_ctl start
[postgres@VM_0_3_centos bin]$ pg_ctl stop  -m fast

建库、表空间及用户

1.创建表空间和数据库实例
注：表空间必须不在PGDATA目录中

[postgres@VM_0_3_centos pg_log]$ cd /data/pgdb/tablespace/

创建表空间数据文件

[postgres@VM_0_3_centos tablespace]$ mkdir ts_ibank

2. 使用PG管理员连接默认数据库postgres创建表空间

[postgres@VM_0_3_centos tablespace]$ psql -d postgres -U postgres
psql (9.5.7)
Type "help" for help.
postgres=# create tablespace ts_ibank location '/data/pgdb/tablespace/ts_ibank';
CREATE TABLESPACE
postgres=# select spcname from pg_tablespace;
  spcname   
------------
 pg_default
 pg_global
 ts_ibank
(3 rows)
postgres=# set default_tablespace = ts_ibank;
SET

如果psql报错symbol lookup error:psql:undefined symbol:PQconnectdbParams

是没找到动态链接库,指定下
vim ~/.bashrc
添加
export LD_LIBRARY_PATH=/postgres/pgsql/lib

3. 退出超级用户，创建数据库实例，并指定表空间

postgres=# \q
[postgres@VM_0_3_centos tablespace]$ createdb ibank -D ts_ibank;

4. 以超级用户访问ibank数据库，创建user和对应的schema

[postgres@VM_0_3_centos tablespace]$ psql -d ibank
psql (9.5.7)
Type "help" for help.
ibank=# create role ibank login password 'ibank';
CREATE ROLE
ibank=# create schema ibank authorization ibank;
CREATE SCHEMA
ibank=#

5. 创建只读用户view，授权可以访问ibank用户的表，创建完成

ibank=# create role view login password 'view';
CREATE ROLE
ibank=# grant connect on database ibank to view;
GRANT
ibank=# grant usage on schema ibank to view;
GRANT
ibank=# grant select on all tables in schema ibank to view;
GRANT

6. 用DBeaver连接,操作更方便