Linux上部署postgreSQL(个人实测)

港控/mmm° 2022-12-05 12:11 393阅读 0赞

*  一:上传到 /tools目录中
 *  二:进行解压
 *  三:执行安装
 *  1:安装gcc
 *  2:安装readline
 *  3:安装zlib-devel
 *  4:配置成功
 *  四:编译
 *  1:编译
 *  2:编译安装
 *  五:相关设置
 *  1:创建用户名和密码
 *  2:创建文件存放目录
 *  3:对数据进行初始化
 *  4:修改postgresql.conf 配置文件
 *  5:修改pg\_hba.conf 配置文件
 *  6:设置PostgreSQL开机自启动(环境变量配置)
 *  六: 启动和连接数据库
 *  1:启动数据库
 *  2:查看进程
 *  3:设置postgresql服务开机自启动
 *  七:数据库备份和导入导出(整库)
 *  1:创建测试表
 *  2:数据库的备份
 *  3:数据库的还原

所有安装包下载地址:

链接：https://pan.baidu.com/s/1pCsHXlHGS8I1h1A9jdsGPg   
提取码：ilos

## 一:上传到 /tools目录中 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70][]

## 二:进行解压 ##

tar -zxvf postgresql-9.3.1.tar.gz -C /training

## 三:执行安装 ##

1:进入解压后的postgresql-9.3.1目录

cd /training/postgresql-9.3.1/

2:编译源码

\[root@server2 tools\]\# cd /training/postgresql-9.3.1/

\[root@server2 postgresql-9.3.1\]\# ./configure -prefix=/training/postgresql-9.3.1

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 1][]

1:安装gcc

cd /tools/gcc

\[root@server2 gcc\]\# rpm -ivh \*.rpm --nodeps --force

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 2][]

再次配置

\[root@server2 postgresql-9.3.1\]\# ./configure -prefix=/training/postgresql-9.3.1

2:安装readline

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 3][]

下载地址:

[http://rpmfind.net/linux/RPM/centos/6.10/x86\_64/Packages/readline-devel-6.0-4.el6.x86\_64.html][http_rpmfind.net_linux_RPM_centos_6.10_x86_64_Packages_readline-devel-6.0-4.el6.x86_64.html]

![20200910112153618.png][]

rpm -ivh readline-devel-6.0-4.el6.x86\_64.rpm --nodeps --force

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 4][]

3:安装zlib-devel

1:下载地址

rpm -ivh zlib-devel-1.2.3-29.el6.x86\_64.rpm --nodeps --force

![20200910112226207.png][]

[http://rpmfind.net/linux/rpm2html/search.php?query=zlib-devel][http_rpmfind.net_linux_rpm2html_search.php_query_zlib-devel]

2:再次配置

cd /training/postgresql-9.3.1

\[root@server2 postgresql-9.3.1\]\# ./configure -prefix=/training/postgresql-9.3.1

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 5][]

会提示:checking for inflate in -lz... no

configure: error: zlib library not found

If you have zlib already installed, see config.log for details on the

failure. It is possible the compiler isn't looking in the proper directory.

3:安装zlib-devel

rpm -ivh zlib-devel-1.2.3-29.el6.x86\_64.rpm --nodeps --force

4:配置成功

\[root@server2 tools\]\# cd /training/postgresql-9.3.1/

\[root@server2 postgresql-9.3.1\]\# ./configure -prefix=/training/postgresql-9.3.1

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 6][]

## 四:编译 ##

1:编译

make

2:编译安装

make install

安装完成

![20200910112324721.png][]

## 五:相关设置 ##

1:创建用户名和密码

1) 创建postgres用户

\#useradd postgres

修改postgres密码

\#passwd postgres

![2020091011250162.png][]

2:创建文件存放目录

\[root@server2 postgresql-9.3.1\]\# mkdir -p /mnt/pgsql/data

\[root@server2 postgresql-9.3.1\]\# chown -R postgres:postgres /mnt/pgsql

\[root@server2 postgresql-9.3.1\]\# ll /mnt

![20200910112512932.png][]

\[root@server2 postgresql-9.3.1\]\# chown -R postgres:postgres /mnt/pgsql/data

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 7][]

3:对数据进行初始化

进入安装目录

cd /training/postgresql-9.3.1

/training/postgresql-9.3.1/bin/initdb -D /mnt/pgsql/data -EUTF-8

![20200910112532725.png][]

su postgres

必须切换成root用户才可以完成初始化

/training/postgresql-9.3.1/bin/initdb -D /mnt/pgsql/data -EUTF-8

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 8][]

4:修改postgresql.conf 配置文件

配置任意客户端即可连接,开放端口:5432

vim /mnt/pgsql/data/postgresql.conf

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 9][]

5:修改pg\_hba.conf 配置文件

修改后,其他远程客户端也可以进行连接,当然这里可以做一些配置,只允许指定IP地址访问也是可以的

vim /mnt/pgsql/data/pg\_hba.conf

host  all  all  0.0.0.0/0  trust

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 10][]

6:设置PostgreSQL开机自启动(环境变量配置)

PostgreSQL的开机自启动脚本位于PostgreSQL源码目录的contrib/start-scripts路径下

1:进入目录

cd /training/postgresql-9.3.1/contrib/start-scripts

2:授予权限

chmod a+x linux

3:复制环境目录

复制linux文件到/etc/init.d目录下，更名为postgresql

cp linux /etc/init.d/postgresql

![20200910112625975.png][]

4:配置环境变量

修改/etc/init.d/postgresql文件的两个变量

vim /etc/init.d/postgresql

1:prefix设置为postgresql的安装路径：

prefix=/training/postgresql-9.3.1

2:PGDATA设置为postgresql的数据目录路径：

PGDATA="/mnt/pgsql/data"

3:日志文件路径

PGLOG="/mnt/pgsql/data/pgsql.log"

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 11][]

## 六: 启动和连接数据库 ##

1:启动数据库

1:进入 postgres用户

su postgres

2:启动

/training/postgresql-9.3.1/bin/pg\_ctl -D /mnt/pgsql/data/ -l /mnt/pgsql/data/pgsql.log start

2:查看进程

ps -ef | grep postgres

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 12][]

3:设置postgresql服务开机自启动

su postgres

chkconfig --add postgresql

七:数据库备份和导入导出(整库)

1:创建测试表

为了便于测试,在数据库中创建了一张表

CREATE TABLE test\_0824(

ID INT PRIMARY KEY NOT NULL,

NAME TEXT NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR(50),

SALARY REAL

);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 13][]

2:数据库的备份

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 14][]

cd /training/postgresql-9.3.1/bin/

![20200910112814679.png][]

进行备份

./pg\_dump -h server2 -U postgres postgres> /data/dump.sql

备份完成后,进入到 /data目录,会找到dump.sql文件

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 15][]

3:数据库的还原

现在删除测试用表test\_0824

1:进入postgresql的bin目录

cd /training/postgresql-9.3.1/bin

2:执行命令

./psql -h server2 -p 5432 postgres -W postgres </data/dump.sql

\-h server2 服务器名称

\-p 5432 postgresql端口号

postgres 默认

\-W postgres 数据库名称

现在再看,表已经恢复

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 16][]

最后一步:进行授权

/\* 修改用户权限 \*/

ALTER ROLE username WITH privileges;

/\* 赋给用户表的所有权限 \*/

GRANT ALL ON tablename TO user;

/\* 赋给用户数据库的所有权限 \*/

GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70]: /images/20221123/f143a8b931194cc2ab39d6b3fb94fd07.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 1]: /images/20221123/59d1e88702f844b78bbef2f64409b1dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 2]: /images/20221123/8faaf34843e14e9bb55f53dcaecd6f54.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 3]: /images/20221123/1723b339b2044a5ab80bee12f13f5658.png
[http_rpmfind.net_linux_RPM_centos_6.10_x86_64_Packages_readline-devel-6.0-4.el6.x86_64.html]: http://rpmfind.net/linux/RPM/centos/6.10/x86_64/Packages/readline-devel-6.0-4.el6.x86_64.html
[20200910112153618.png]: /images/20221123/34ff06e74b56483c9e5671a536af464c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 4]: /images/20221123/370ae4548cb1433f99c4751e5209efdd.png
[20200910112226207.png]: /images/20221123/6581c7317b8649ec8f69cac333c35b8c.png
[http_rpmfind.net_linux_rpm2html_search.php_query_zlib-devel]: http://rpmfind.net/linux/rpm2html/search.php?query=zlib-devel
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 5]: /images/20221123/a2d504a9db1848939bb0e2c0b8845b72.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 6]: /images/20221123/ecaecb90c95e466294f8a763b69a1dee.png
[20200910112324721.png]: /images/20221123/ce5d6342e36149f69423f610d15c618f.png
[2020091011250162.png]: /images/20221123/f8b1a3d828e346c590f99000748d1a81.png
[20200910112512932.png]: /images/20221123/f16adfada3fb47cf95f5fc0cf1ce20d7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 7]: /images/20221123/3feaeb32172642239244206f7ba0643e.png
[20200910112532725.png]: /images/20221123/ac25fbc8586546dc894c7a1c43abbd21.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 8]: /images/20221123/3fec71768d2441848d208926afda9572.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 9]: /images/20221123/45c688af6ea4421e9c72243f84ecf0e6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 10]: /images/20221123/91b495ecace54b26aaf57a7d5ba17f57.png
[20200910112625975.png]: /images/20221123/3bdedcbe07f144cfad835495fcefee88.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 11]: /images/20221123/52d2fd288b7644b2b1894b0329d38d4e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 12]: /images/20221123/e5f265773f0f450d9c77bd71c6a9988b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 13]: /images/20221123/288e193a3df6499f821b89323f342abd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 14]: /images/20221123/ec088b10aea64660ab61330592326ee8.png
[20200910112814679.png]: /images/20221123/a8392c8091454218b1a1bf011b620323.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 15]: /images/20221123/90ab4f6dd8224211a895e81cc645c9a0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTYzNDMz_size_16_color_FFFFFF_t_70 16]: /images/20221123/d2fa1ab773a24375b5b7d9275082b703.png