PostgreSQL部署手册

安装依赖包

  1. apt-get install -y make gcc g++ 
  2. apt-get install -y libreadline-dev zlib1g-dev uuid uuid-dev libxml2-dev libgdal-dev libossp-uuid-dev

安装PostgreSQL

  1. # 源码安装
  2. wget https://ftp.postgresql.org/pub/source/v11.3/postgresql-11.3.tar.gz
  3. ./configure --prefix=/usr/local/postgresql/11 --with-uuid=ossp
  4. make && make install
  6. # 添加环境变量
  7. vim /etc/profile
  9. PGDATA=/data/pgsql/11/data
  10. PATH=/usr/local/postgresql/11/bin:$PATH
  11. export PGDATA PATH
  13. source /etc/profile

安装中文分词器

  1. # 安装SCWS
  2. wget http://www.xunsearch.com/scws/down/scws-1.2.3.tar.bz2
  3. tar -xf scws-1.2.3.tar.bz2
  4. cd scws-1.2.3 ; ./configure ; make install
  6. # 安装zhparser
  7. git clone https://github.com/amutu/zhparser.git
  8. cd zhparser
  9. make && make install
  11. # 提示pg_config找不到
  12. export PG_CONFIG=/usr/local/postgresql/11/bin/pg_config

安装PG插件

  1. # btree_gist
  2. postgresql-11.3/contrib 
  3. cd btree_gist
  4. make && make install
  6. # pg_trgm
  7. postgresql-11.3/contrib
  8. cd ../pg_trgm
  9. make && make install
  11. # dblink
  12. postgresql-11.3/contrib 
  13. cd ../dblink
  14. make && make install
  16. # uuid-ossp
  17. postgresql-11.3/contrib 
  18. cd ../uuid-ossp
  19. make && make install
  21. # pg_stat_statements(监控需要)
  22. postgresql-11.3/contrib 
  23. cd ../pg_stat_statements
  24. make && make install

安装citus扩展—先不安装

  1. wget https://github.com/citusdata/citus/releases
  3. ./configure
  4. make && make install
  6. # 说明
  7. # 编译postgreql后，设置pg_config变量，但是在编译citus时，需要设置软链
  8. ln -s /usr/local/pgsql/bin/pg_config  /usr/bin/pg_config

安装Postgis

  1. # 安装Proj4
  2. wget http://download.osgeo.org/proj/proj-4.9.3.tar.gz
  3. tar -xf proj-4.9.3.tar.gz
  4. cd proj-4.9.3
  5. ./configure --prefix=/usr/local/postgresql/11/plugin/proj
  6. make && make install
  7. echo "/usr/local/postgresql/11/plugin/proj/lib" > /etc/ld.so.conf.d/proj-4.9.3.conf
  8. ldconfig
  10. # 安装GEOS
  11. wget http://download.osgeo.org/geos/geos-3.6.1.tar.bz2
  12. tar -jxf geos-3.6.1.tar.bz2
  13. cd geos-3.6.1
  14. ./configure --prefix=/usr/local/postgresql/11/plugin/geos
  15. make && make install
  16. echo "/usr/local/postgresql/11/plugin/geos/lib" > /etc/ld.so.conf.d/geos-3.6.1.conf
  17. ldconfig
  19. # 安装GDAL 这个安装太慢了
  20. wget http://download.osgeo.org/gdal/2.1.2/gdal-2.1.2.tar.gz
  21. tar -xf gdal-2.1.2.tar.gz
  22. cd gdal-2.1.2
  23. ./configure --prefix=/usr/local/postgresql/11/plugin/gdal
  24. make && make install
  25. echo "/usr/local/postgresql/11/plugin/gdal/lib" > /etc/ld.so.conf.d/gdal-2.1.2.conf
  26. ldconfig
  28. # 安装PostGIS
  29. wget https://download.osgeo.org/postgis/source/postgis-2.5.2.tar.gz
  30. tar -xvzf postgis-2.5.2.tar.gz
  31. cd postgis-2.5.2
  32. ./configure --prefix=/usr/local/postgresql/11/plugin/postgis \
  33. --with-pgconfig=/usr/local/postgresql/11/bin/pg_config \
  34. --with-geosconfig=/usr/local/postgresql/11/plugin/geos/bin/geos-config \
  35. --with-gdalconfig=/usr/local/postgresql/11/plugin/gdal/bin/gdal-config \
  36. --with-projdir=/usr/local/postgresql/11/plugin/proj
  37. make && make install

PG初始化

  1. # postgres运行用户
  2. useradd -s /bin/bash -m postgres
  4. # 数据目录、流复制归档目录
  5. mkdir -p /data/pgsql/11/data
  6. mkdir -p /data/pgsql/archivedir
  7. mkdir -p /var/log/postgres
  8. chown -R postgres.postgres /data/pgsql /var/log/postgres
  10. # 初始化
  11. su - postgres
  12. /usr/local/postgresql/11/bin/initdb -D /data/pgsql/11/data
  14. # 启动
  15. /usr/local/postgresql/11/bin/pg_ctl -D /data/pgsql/11/data -l /var/log/postgres/postgresql-11-main.log start

使用systemctl管理pg

  1. vi /etc/systemd/system/postgresql@11-main.service
  3. # systemd service template for PostgreSQL clusters. The actual instances will
  4. # be called "postgresql@version-cluster", e.g. "postgresql@9.3-main". The
  5. # variable %i expands to "version-cluster", %I expands to "version/cluster".
  6. # (%I breaks for cluster names containing dashes.)
  8. [Unit]
  9. Description=PostgreSQL Cluster %i
  10. AssertPathExists=/etc/postgresql/%I/postgresql.conf
  11. RequiresMountsFor=/etc/postgresql/%I /var/lib/postgresql/%I
  12. PartOf=postgresql.service
  13. ReloadPropagatedFrom=postgresql.service
  14. Before=postgresql.service
  15. # stop server before networking goes down on shutdown
  16. After=network.target
  18. [Service]
  19. Type=forking
  20. # -: ignore startup failure (recovery might take arbitrarily long)
  21. # the actual pg_ctl timeout is configured in pg_ctl.conf
  22. ExecStart=-/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i start
  23. # 0 is the same as infinity, but "infinity" needs systemd 229
  24. TimeoutStartSec=0
  25. ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast %i stop
  26. TimeoutStopSec=1h
  27. ExecReload=/usr/bin/pg_ctlcluster --skip-systemctl-redirect %i reload
  28. PIDFile=/run/postgresql/%i.pid
  29. SyslogIdentifier=postgresql@%i
  30. # prevent OOM killer from choosing the postmaster (individual backends will
  31. # reset the score to 0)
  32. OOMScoreAdjust=-900
  33. # restarting automatically will prevent "pg_ctlcluster ... stop" from working,
  34. # so we disable it here. Also, the postmaster will restart by itself on most
  35. # problems anyway, so it is questionable if one wants to enable external
  36. # automatic restarts.
  37. #Restart=on-failure
  38. # (This should make pg_ctlcluster stop work, but doesn't:)
  39. #RestartPreventExitStatus=SIGINT SIGTERM
  41. [Install]
  42. WantedBy=multi-user.target
  1. systemctl start postgresql.service
  2. systemctl status postgresql.service
  3. systemctl stop postgresql.service
  4. systemctl disable postgresql.service
  5. systemctl enable postgresql.service