.27t_lBZ4oZgg_
0%

PostgreSQL安装.md

发表于 更新于 分类于

PostgreSQL官网

安装依赖：

1
2
3
4
5
# centos
$ yum -y install readline-devel zlib-devel gcc gcc-c++ zlib readline
# debian/ubuntu
$ apt-get install -y gcc make
$ apt-get install -y libreadline-dev zlib1g zlib1g.dev

安装PostgreSQL：

postgresql-10.3(maybe too
old)

1
2
3
4
5
6
7
8
9
10
11
$ wget https://sandbox-experiment-resource-north-4.obs.cn-north-4.myhuaweicloud.com/postgresql-arm/postgresql-10.3.tar.gz
$ tar -zxvf postgresql-10.3.tar.gz -C /opt/
$ cd /opt/postgresql-*/
$ ./configure --build=arm-linux --host=arm-linux --prefix=/usr/local/pgsql
# canuse：./configure --prefix=/usr/local/pgsql --with-blocksize=8 \
# --enable-dtrace --enable-debug --enable-thread-safety
# --with-libxml --with-python --with-openssl
# $ make world
# $ make check-world (by normal user)
# $ make install-world
$ make && make install && make clean

授权用户：

1
2
3
$ adduser postgres
$ chown -R postgres:postgres /usr/local/pgsql
$ ls -ld /usr/local/pgsql

配置环境变量：

1
2
$ su postgres
$ vim ~/.bash_profile

配置文件中加入：

1
2
3
$ export PGHOME=/usr/local/pgsql
$ export PGDATA=/usr/local/pgsql/data
$ export PATH=$PATH:$PGHOME/bin

执行修改：

1
$ source  ~/.bash_profile && psql -V

初始化数据库：

1
$ initdb -D $PGDATA -E UTF8 --locale=C -U postgres -W

启动日志服务：

1
2
$ mkdir -p /usr/local/pgsql/log
$ chmod 755 /usr/local/pgsql/log

启动数据库：

1
$ pg_ctl -D $PGDATA  -l  $PGHOME/log/pg_server.log start

检查结果：

1
2
$ ps -ef | grep "postgres"
$ netstat -nlp | grep "postgres"

修改数据库密码：

1
$ psql中`\password postgres`可修改

配置为远程访问：

将”listen_addresses=’localhost’”取消注释并修改为任意地址’*’

1
$ vim /usr/local/pgsql/data/postgresql.conf

修改访问地址控制设置：

1
$ vim /usr/local/pgsql/data/pg_hba.conf

在”IPv4 local connections”下构建规则：

1
host    all             all             0.0.0.0/0               md5

重启生效：

1
$ pg_ctl restart

在debian中

基本安装

创建文件/etc/apt/sources.list.d/pgdg.list，里面添加：

1
2
# 其中stretch-pgdg具体可以在https://www.postgresql.org/download/linux/debian/中选择具体版本得到
deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main

Import the repository signing key, and update the package lists:

1
2
3
4
5
6
7
8
9
$ apt-get install -y gnupg gnupg1 gnupg2
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ apt-get update
$ apt-get install -y postgresql-10

$ useradd -m -s /bin/bash postgres
$ mkdir /usr/local/pgsql
$ chown postgres:postgres /usr/local/pgsql
$ sudo -u postgres /usr/lib/postgressql

数据库初始化：

1
$ /usr/lib/postgresql/11/bin/initdb -D $PGDATA -E UTF8 --locale=C -U postgres -W

vim ~/.bash_profile配置：

1
2
3
4
export PGHOME=/usr/local/pgsql
export PGDATA=/usr/local/pgsql/data
export PATH=$PATH:/usr/lib/postgresql/11/bin
export LANG=en_US.utf8

数据库日志：

1
2
3
# 大可不必
$ mkdir -p /usr/local/pgsql/log
$ chmod 755 /usr/local/pgsql/log
  • can start data server 
    1
    2
    3
    # 一般启动
    $ /usr/lib/postgresql/11/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
    $ pg_ctl -D $PGDATA -l $PGHOME/log/pg_server.log start

    通过配置文件

    启动，vim /etc/sysctl.conf中配置内核相关参数：

    1
    2
    3
    4
    5
    6
    7
    8
    kernel.shmmni = 4096
    kernel.sem = 50100 64128000 50100 1280
    fs.file-max = 7672460
    net.ipv4.ip_local_port_range = 9000 65000
    net.core.rmem_default = 1048576
    net.core.rmem_max = 4194304
    net.core.wmem_default = 262144
    net.core.wmen_max = 1028576

    vim /etc/sysconfig/iptables进行防火墙设置：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -A RH-Firewall-1-INPUT -i lo -j ACCEPT
    # 允许源IP
    -A RH-Firewall-1-INPUT -s 192.168.0.0/16 -j ACCEPT
    # 允许源IP访问目标端口
    -A RH-Firewall-1-INPUT -s 192.168.1.0/24 -m state --state 
    NEW -m tcp -p tcp --dport [PORT] -j ACCEPT
    # 允许任意IP访问目标端口
    -A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m
    tcp --dport [PORT] -j ACCEPT
    # 不是很懂
    # 使用iptables -L -v -n查看出入规则
  • 在/etc/security/limits.conf配置。。。

    vim /usr/local/pgsql/data/pg_hba.conf中的IPv4 local
    connections修改访问地址控制规则设置：

    1
    host    all             all             0.0.0.0/0               md5

    vim /usr/local/pgsql/data/postgresql.conf中

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    将listen_addresses='localhost'取消注释并修改为任意地址'*0.0.0.0'
    superuser_reserved_connections超级用户最多连接改为13
    unix_socket_directories连接改为'.'根目录下
    unix_socket_permissions权限为0700
    # 心跳检测
    tcp_keepalives_idle = 60
    tcp_keepalives_interval = 10
    tcp_keepalives_count = 10
    # 延迟
    vacuum_cost_delay = 10
    bgwritter_delay = 10ms
    wal_writer_delay = 200ms
    # 归档
    hot_standby = on
    # 日志
    log_destination = "csvlog"
    logging_collector = on
    log_directory = 'pg_log'
    log_file_mode = 0600
    log_truncate_on_rotation = on等
    # 还有checkpoint
    log_checkpoints = on
    log_connection = on
    log_disconnections = on
    log_error_verbosity = verbose

    启动psql

    1
    2
    $ pg_ctl -D $PGDATA -l $PGHOME/log/pg_server.log start
    $ psql -h localhost -U postgres