经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 数据库/运维 » PostgreSQL » 查看文章
postgresql数据库部署
来源：cnblogs　　作者：骚涛　　时间：2019/5/24 10:04:12　　对本文有异议

运维开发技术交流群欢迎大家加入一起学习（QQ:722381733）

一、postgresql数据库部署

　　1、前往postgresql安装包的目录（这里我部署的是10.5的版本）

  1. [root@web1 ~]# cd /package/
  2. [root@web1 package]# ls
  3. apache-tomcat-8.5.39.tar.gz  jdk-8u131-linux-x64.tar.gz  postgresql-10.5.tar.gz  redis-3.2.0.tar.gz
  4. [root@web1 package]#

　　2、解压缩包

  1. [root@web1 package]# tar xf postgresql-10.5.tar.gz 
  2. [root@web1 package]# ls
  3. apache-tomcat-8.5.39.tar.gz  jdk-8u131-linux-x64.tar.gz  postgresql-10.5  postgresql-10.5.tar.gz  redis-3.2.0.tar.gz
  4. [root@web1 package]#

　　3、进入解压后生成的目录文件进行编译

  1. [root@web1 postgresql-10.5]# ./configure --prefix=/usr/local/pgsql-10.5
  2. checking build system type... x86_64-pc-linux-gnu
  3. checking host system type... x86_64-pc-linux-gnu
  4. checking which template to use... linux
  5. checking whether NLS is wanted... no
  6. checking for default port number... 5432
  7. checking for block size... 8kB
  8. checking for segment size... 1GB
  9. checking for WAL block size... 8kB
  10. checking for WAL segment size... 16MB

　　4、提示如下信息是缺少包文件

  1. checking for library containing readline... no
  2. configure: error: readline library not found
  3. If you have readline already installed, see config.log for details on the
  4. failure.  It is possible the compiler isn't looking in the proper directory.
  5. Use --without-readline to disable readline support.

　　5、安装相应的包文件文件

  1. [root@web1 postgresql-10.5]#yum install ncurses* readline* zlib* -y
  2. ######################################
  3. [root@web1 postgresql-10.5]# rpm -aq ncurses* readline* zlib*
  4. readline-devel-6.2-10.el7.x86_64
  5. zlib-static-1.2.7-18.el7.x86_64
  6. ncurses-libs-5.9-14.20130511.el7_4.x86_64
  7. ncurses-devel-5.9-14.20130511.el7_4.x86_64
  8. readline-static-6.2-10.el7.x86_64
  9. zlib-devel-1.2.7-18.el7.x86_64
  10. readline-6.2-10.el7.x86_64
  11. ncurses-base-5.9-14.20130511.el7_4.noarch
  12. ncurses-5.9-14.20130511.el7_4.x86_64
  13. zlib-1.2.7-18.el7.x86_64
  14. [root@web1 postgresql-10.5]#

　　6、安装完成后重新编译下

  1. [root@web1 postgresql-10.5]# ./configure --prefix=/usr/local/pgsql-10.5
  2. checking build system type... x86_64-pc-linux-gnu
  3. checking host system type... x86_64-pc-linux-gnu
  4. checking which template to use... linux
  5. checking whether NLS is wanted... no
  6. checking for default port number... 5432
  7. checking for block size... 8kB
  8. checking for segment size... 1GB
  9. checking for WAL block size... 8kB
  10. checking for WAL segment size... 16MB
  11. checking for gcc... gcc

　　7、解析文件（在postgresql-10.5目录执行：make install），最后输出结果没提示异常【error】便可开始安装了

  1. rm -f pg_regress.o && ln -s ../../../src/test/regress/pg_regress.o .
  2. gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql-10.5/lib',--enable-new-dtags  -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -lcrypt -ldl
  3. make[2]: Leaving directory `/package/postgresql-10.5/src/test/isolation'
  4. make -C test/perl all
  5. make[2]: Entering directory `/package/postgresql-10.5/src/test/perl'
  6. make[2]: Nothing to be done for `all'.
  7. make[2]: Leaving directory `/package/postgresql-10.5/src/test/perl'
  8. make[1]: Leaving directory `/package/postgresql-10.5/src'
  9. make -C config all
  10. make[1]: Entering directory `/package/postgresql-10.5/config'
  11. make[1]: Nothing to be done for `all'.
  12. make[1]: Leaving directory `/package/postgresql-10.5/config'
  13. All of PostgreSQL successfully made. Ready to install.

 

　　8、开始安装（在postgresql-10.5目录执行：make install），最后输出结果没提示异常【error】便表示安装成功

  1. /bin/install -c -m 644 Makefile.global '/usr/local/pgsql-10.5/lib/pgxs/src/Makefile.global'
  2. /bin/install -c -m 644 Makefile.port '/usr/local/pgsql-10.5/lib/pgxs/src/Makefile.port'
  3. /bin/install -c -m 644 ./Makefile.shlib '/usr/local/pgsql-10.5/lib/pgxs/src/Makefile.shlib'
  4. /bin/install -c -m 644 ./nls-global.mk '/usr/local/pgsql-10.5/lib/pgxs/src/nls-global.mk'
  5. make[1]: Leaving directory `/package/postgresql-10.5/src'
  6. make -C config install
  7. make[1]: Entering directory `/package/postgresql-10.5/config'
  8. /bin/mkdir -p '/usr/local/pgsql-10.5/lib/pgxs/config'
  9. /bin/install -c -m 755 ./install-sh '/usr/local/pgsql-10.5/lib/pgxs/config/install-sh'
  10. /bin/install -c -m 755 ./missing '/usr/local/pgsql-10.5/lib/pgxs/config/missing'
  11. make[1]: Leaving directory `/package/postgresql-10.5/config'
  12. PostgreSQL installation complete.

　　9、安装完成后可以看到在/usr/local目录上成了pgsql-10.5目录

  1. [root@web1 postgresql-10.5]# ll /usr/local/ |grep pgsql-10.5
  2. drwxr-xr-x  6 root root   56 May 23 18:06 pgsql-10.5
  3. [root@web1 postgresql-10.5]#

　　10、创建用户及设置/usr/local/pgsql-10.5目录文件属主组

 

  1. [root@web1 postgresql-10.5]# useradd postgres
  2. [root@web1 postgresql-10.5]# chown -R postgres.postgres /usr/local/pgsql-10.5/
  3. [root@web1 postgresql-10.5]# ll /usr/local/ |grep pgsql-10.5
  4. drwxr-xr-x  6 postgres postgres   56 May 23 18:06 pgsql-10.5
  5. [root@web1 postgresql-10.5]#

 

　　11、给/usr/local/pgsql-10.5目录设置软连接（方便查看和管理吧....）

  1. [root@web1 postgresql-10.5]# ln -s /usr/local/pgsql-10.5/ /usr/local/pgsql
  2. [root@web1 postgresql-10.5]# ll /usr/local/pgsql
  3. lrwxrwxrwx 1 root root 22 May 23 18:15 /usr/local/pgsql -> /usr/local/pgsql-10.5/
  4. [root@web1 postgresql-10.5]#

　　12、进入刚创建的postgres用户

  1. [root@web1 postgresql-10.5]# su postgres
  2. [postgres@web1 postgresql-10.5]$

　　13、设置环境变量（注意路径）

  1. [postgres@web1 ~]$ vim ~/.bash_profile
  3. # .bash_profile
  5. # Get the aliases and functions
  6. if [ -f ~/.bashrc ]; then
  7.         . ~/.bashrc
  8. fi
  10. # User specific environment and startup programs
  12. PATH=$PATH:$HOME/.local/bin:$HOME/bin
  13. export PATH=$PATH:/usr/local/pgsql-10.5/bin
  14. PGDATA=/usr/local/pgsql-10.5/data
  15. export PGDATA
  16. export PATH

　　14、当前生效

  1. [postgres@web1 ~]$ source ~/.bash_profile 
  2. [postgres@web1 ~]$

　　15、测试

  1. [postgres@web1 ~]$ which psql
  2. /usr/local/pgsql-10.5/bin/psql
  3. [postgres@web1 ~]$ psql -V
  4. psql (PostgreSQL) 10.5

　　16、初始化数据库（initdb --help查看讲情），指定库文件路径

  1. [postgres@web1 ~]$ initdb /usr/local/pgsql-10.5/data
  2. The files belonging to this database system will be owned by user "postgres".
  3. This user must also own the server process.
  5. The database cluster will be initialized with locale "en_US.UTF-8".
  6. The default database encoding has accordingly been set to "UTF8".
  7. The default text search configuration will be set to "english".
  9. Data page checksums are disabled.
  11. creating directory /usr/local/pgsql-10.5/data ... ok
  12. creating subdirectories ... ok
  13. selecting default max_connections ... 100
  14. selecting default shared_buffers ... 128MB
  15. selecting dynamic shared memory implementation ... posix
  16. creating configuration files ... ok
  17. running bootstrap script ... ok
  18. performing post-bootstrap initialization ... ok
  19. syncing data to disk ... ok
  21. WARNING: enabling "trust" authentication for local connections
  22. You can change this by editing pg_hba.conf or using the option -A, or
  23. --auth-local and --auth-host, the next time you run initdb.
  25. Success. You can now start the database server using:
  27.     pg_ctl -D /usr/local/pgsql-10.5/data -l logfile start
  29. [postgres@web1 ~]$

***在此就已经部署成功了，现在要做的就是修改下配置文件，优化下***

　　17、前往文件安装所在地，修改pg_hba.conf配置文件

  1. [postgres@web1 data]$ cp pg_hba.conf pg_hba.conf.`date +%F`
  2. [postgres@web1 data]$ vim pg_hba.conf
  3. #######################################
  4. # TYPE  DATABASE        USER            ADDRESS                 METHOD
  6. # "local" is for Unix domain socket connections only
  7. local   all             all                                     trust
  8. # IPv4 local connections:
  9. #host    all             all             127.0.0.1/32            trust
  10. host    all             all             0.0.0.0/0               md5     #所有ip都可以通过密码连接
  11. # IPv6 local connections:
  12. host    all             all             ::1/128                 trust
  13. # Allow replication connections from localhost, by a user with the
  14. # replication privilege.
  15. local   replication     all                                     trust
  16. host    replication     all             127.0.0.1/32            trust
    ######################################

　　18、前往文件安装所在地，修改postgresql.conf配置文件

  1. [postgres@web1 data]$ cd /usr/local/pgsql/data/
  2. [postgres@web1 data]$ cp postgresql.conf postgresql.conf.`date +%F`
  3. [postgres@web1 data]$ vim postgresql.conf
  4. #########################################
  5. # - Connection Settings -
  7. listen_addresses = '*'  #为了方便，监听所有# what IP address(es) to listen on;
  8.                                         # comma-separated list of addresses;
  9.                                         # defaults to 'localhost'; use '*' for all
  10.                                         # (change requires restart)
  11. #port = 5432                            # (change requires restart)
  12. max_connections = 100                   # (change requires restart)
  13. #superuser_reserved_connections = 3     # (change requires restart)
  14. #unix_socket_directories = '/tmp'       # comma-separated list of directories
  15.                                         # (change requires restart)
  16. #unix_socket_group = ''                 # (change requires restart)
  17. #unix_socket_permissions = 0777         # begin with 0 to use octal notation
  18. #########################################

 

 

　　19、创建log目录用于存放开机日志，及启动数据库

  1. [postgres@web1 data]$ mkdir /usr/local/pgsql/log
  2. [postgres@web1 data]$ pg_ctl start -l /usr/local/pgsql-10.5/log/pg_server.log
  3. waiting for server to start.... done
  4. server started
  5. [postgres@web1 data]$

　　20、检查是否启动，可以看日志

  1. [postgres@web1 data]$ lsof -i:5432
  2. COMMAND    PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
  3. postgres 25479 postgres    3u  IPv4  50990      0t0  TCP *:postgres (LISTEN)
  4. postgres 25479 postgres    4u  IPv6  50991      0t0  TCP *:postgres (LISTEN)
  5. [postgres@web1 data]$ netstat -lnutp|grep postgres
  6. (Not all processes could be identified, non-owned process info
  7.  will not be shown, you would have to be root to see it all.)
  8. tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      25479/postgres      
  9. tcp6       0      0 :::5432                 :::*                    LISTEN      25479/postgres      
  10. [postgres@web1 data]$ ps -ef|grep postgres
  11. root     24060  1297  0 18:17 pts/0    00:00:00 su postgres
  12. postgres 24061 24060  0 18:17 pts/0    00:00:00 bash
  13. postgres 25479     1  0 18:45 pts/0    00:00:00 /usr/local/pgsql-10.5/bin/postgres
  14. postgres 25481 25479  0 18:45 ?        00:00:00 postgres: checkpointer process   
  15. postgres 25482 25479  0 18:45 ?        00:00:00 postgres: writer process   
  16. postgres 25483 25479  0 18:45 ?        00:00:00 postgres: wal writer process   
  17. postgres 25484 25479  0 18:45 ?        00:00:00 postgres: autovacuum launcher process  
  18. postgres 25485 25479  0 18:45 ?        00:00:00 postgres: stats collector process  
  19. postgres 25486 25479  0 18:45 ?        00:00:00 postgres: bgworker: logical replication launcher  
  20. postgres 25543 24061  0 18:46 pts/0    00:00:00 ps -ef
  21. postgres 25544 24061  0 18:46 pts/0    00:00:00 grep --color=auto postgres
  22. [postgres@web1 data]$

　　21、进入库的一些小操作

  1. [postgres@web1 data]$ psql    #进入库
  2. psql (10.5)
  3. Type "help" for help.
  5. postgres=# \password    #设置密码
  6. Enter new password: 
  7. Enter it again: 
  8. postgres=# CREATE DATABASE name;       #创建库
  9. CREATE DATABASE
  10. postgres=# \du    #查看用户
  11.                                    List of roles
  12.  Role name |                         Attributes                         | Member of 
  13. -----------+------------------------------------------------------------+-----------
  14.  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
  16. postgres=# 
  17. postgres=# \l    #查看数据库列表
  18.                                   List of databases
  19.    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
  20. -----------+----------+----------+-------------+-------------+-----------------------
  21.  name      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
  22.  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
  23.  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
  24.            |          |          |             |             | postgres=CTc/postgres
  25.  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
  26.            |          |          |             |             | postgres=CTc/postgres
  27. (4 rows)
  29. postgres=# 
  30. postgres=# create user test superuser password '123456';    #创建用户及密码
  31. CREATE ROLE
  32. postgres=#

*****到此就大致部署完了，如有什么问题以及不足之处，欢迎读客在线批评****

 

原文链接：http://www.cnblogs.com/wangguangtao/p/10910648.html

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

PostgreSQL热门文章

  • PostgreSQL报错 解决操作符不存在的问题
  • PostgreSQL 自定义自动类型转换操作(CAST)
  • postgresql如何兼容MySQL if函数
  • Postgresql在mybatis中报错:操作符不存在:character varying == unknown的问题
  • PostgreSQL数据库事务插入删除及更新操作示例
  • PostgreSQL 中的单引号与双引号用法说明
  • PostgreSQL将日期转为当前年、月、日的函数date_trunc
  • PostgreSQL是世界最先进的开源数据库系统吗？
  • postgresql 实现启动、状态查看、关闭
  • PostgreSQL聚合函数的分组排序使用示例

    • PostgreSQL推荐文章

  • EF Core连接PostgreSQL数据库
  • PostgreSQL、KingBase 数据库 ORDER BY LIMIT 查询缓慢案例
  • ClickHouse(20)ClickHouse集成PostgreSQL表引擎详细解析
  • 分享一次公司晋级考试的SQL题目，非常有趣的案例（postgresql 标量子查询 where like 谓词过滤条件）
  • 纯分享：将MySql的建表DDL转为PostgreSql的DDL
  • 群晖DS218+部署PostgreSQL(docker)
  • rockyLinux 初体验（教程）PostgreSQL15
  • postgresql兼容MySQL on update current_timestamp问题
  • PostgreSQL聚合函数的分组排序使用示例
  • PostgreSQL数据库视图及子查询使用操作

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号