postgresql安装配置

yum安装

  • 尝试查找
    yum list | grep postgresql
    yum list postgresql-server
  • yum 安装
    yum install postgresql-server.x86_64

卸载yum安装

  • 安装包查找
    rpm -qa | grep postgresql

    结果
    postgresql-9.2.23-3.el7_4.x86_64
    postgresql-libs-9.2.23-3.el7_4.x86_64

  • 卸载
    rpm -e postgresql-9.2.23-3.el7_4.x86_64
    rpm -e postgresql-libs-9.2.23-3.el7_4.x86_64
  • 检查
    find / -name postgresql

    结果
    /root/pgsql/share/postgresql
    /root/pgsql/pgAdmin 4/venv/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql
    /root/pgsql/doc/postgresql
    /root/pgsql/include/postgresql
    /root/pgsql/lib/postgresql
    /etc/selinux/targeted/active/modules/100/postgresql

  • 清理
    rm -rf /root/pgsql

下载资源包安装

  • 资源寻找
    https://www.postgresql.org/ftp
  • 下载
    wget https://ftp.postgresql.org/pub/source/v10.4/postgresql-10.4.tar.gz
  • 解压
    tar xf postgresql-10.4.tar.gz -C /usr/local/src/
  • 安装
    cd /usr/local/src/postgresql-10.4
    ./configure –prefix=/usr/local/pgsql
    make && make install
    关于make的命令
  • 创建数据文件夹
    mkdir /usr/local/pgsql/data
  • 切换用户
    useradd postgres
    passwd postgres
    chown postgres:postgres /usr/local/pgsql/data
    chmod 750 /usr/local/pgsql/data
    切换至用户postgres

    否则
    “root” execution of the PostgreSQL server is not permitted.
    The server must be started under an unprivileged user ID to prevent
    possible system security compromise. See the documentation for
    more information on how to properly start the server.

    权限解释
    第一个数字表示文件所有者的权限
    第二个数字表示文件所有者同属一个用户组的其他用户的权限
    第三个数字表示其他用户组的权限。
    权限分为三种:读(r=4),写(w=2),执行(x=1)。结合起来还有可读可执行(rx=5=4+1),可读可写(rw=6=4+2)等。

  • 创建数据库簇
    cd /usr/local/pgsql/bin/
    ./initdb -D /usr/local/pgsql/data
  • 启动
    cd /usr/local/pgsql/bin/
    ./pg_ctl start -D /usr/local/pgsql/data

    如果没有设置PGDATA环境变量需要带参数启动
    postgres does not know where to find the server configuration file.
    You must specify the –config-file or -D invocation option or set the PGDATA environment variable.

  • 创建数据库
    ./createdb mydb
  • 创建用户
    ./createuser -P pub
  • 登录
    最高权限登录
    ./psql mydb
    使用pub登录
    ./psql mydb -U pub

    查看当前用户

    select user;
    

    退出sql命令

    \q
    
  • 默认
    默认数据库postgres
    默认用户,当前系统用户
  • 开启远程访问
    vi postgresql.conf

    # listen_addresses = 'localhost' # what IP address(es) to listen on;
    

    修改为

    listen_addresses = '*' # what IP address(es) to listen on;
    

    vi pg_hba.conf
    添加

    host    all             all             0.0.0.0/0               md5
    
  • 做成服务开机启动
    root权限
    cd /etc/systemd/system
    vi postgresql.service

    [Unit]
    Description=postgresql
    After=network.target
    
    [Service]
    User=postgres
    Group=postgres
    Type=forking
    ExecStart=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start
    ExecReload=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data restart
    ExecStop=/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop
    PrivateTmp=true
    
    [Install]
    WantedBy=muti-user.target
    

    启动
    systemctl start postgresql.service
    状态
    systemctl status postgresql.service
    重启
    systemctl restart postgresql.service
    停止
    systemctl stop postgresql.service

    建立软连接
    ln -s /usr/lib/systemd/system/postgresql.service /etc/systemd/system/multi-user.target.wants/postgresql.service
    开机启动
    systemctl enable postgresql.service
    取消开机启动
    systemctl disable postgresql.service

其它

  • 端口查看
    用nmap命令查看端口
    nmap ip
    nmap ip -p port

    若:-bash: nmap: command not found
    yum install nmap.x86_64

来源:https://www.jianshu.com/p/bbb1b9e0b2c7