Postgre介绍
PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES，4.2版本为基础的对象关系型数据库管理系统。POSTGRES的许多领先概念只是在比较迟的时候才出现在商业网站数据库中。PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。同样，PostgreSQL也可以用许多方法扩展，例如通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言等。另外，因为许可证的灵活，任何人都可以以任何目的免费使用、修改和分发PostgreSQL。
应用场景
b-tree适合所有的数据类型，支持排序，支持大于、小于、等于、大于或等于、小于或等于的搜索。
索引与递归查询结合，还能实现快速的稀疏检索。
源码安装
1、先检查一下服务器有没有安装gcc和c++以及依赖包
[root@Server04 ~]# rpm -qa | grep gcc-c++ [root@Server04 ~]# yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake
也可以在镜像中提取，或者在镜像网站下载。http://mirrors.aliyun.com/centos/7/os/x86_64/Packages/ ，这里直接使用yum安装，如果使用镜像文件下载后使用rpm安装
rpm -Uvh *.rpm --nodeps --force
安装之后可以使用
gcc -v g++ -v
查看版本的详细信息，安装完成
2、下载postgresql文件
在https://www.postgresql.org/ftp/source/ 根据自己的需求下载相应的版本，这里使用的是11版本的
wget https://ftp.postgresql.org/pub/source/v11.4/postgresql-11.4.tar.gz mkdir /usr/local/postgre mkdir /usr/local/pgsql tar -zxf postgresql-11.4.tar.gz -C /usr/local/postgre/
3、编译安装
wget http://www.rpmfind.net/linux/opensuse/tumbleweed/repo/oss/x86_64/readline-devel-8.0-1.6.x86_64.rpm wget http://mirror.centos.org/centos/7/os/x86_64/Packages/ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm ./configure --prefix=/usr/local/pgsql make make install
4、创建用户
因为postgre不能使用root用户启动，所以需要创建一个普通用户启动数据库，普通用户的密码就是进入postgre的密码，创建用户并设置密码：
[root@Server04 postgre]# useradd postgres [root@Server04 postgre]# groupadd postgres groupadd: group 'postgres' already exists [root@Server04 postgre]# passwd postgres Changing password for user postgres. New password: Retype new password: passwd: all authentication tokens updated successfully.
5、设置数据路径
创建数据库目录，并赋予权限
mkdir /usr/local/pgsql/data chown postgres /usr/local/pgsql/data chgrp postgres /usr/local/pgsql/data
6、初始化数据库
[root@Server04 postgre]# su postgres
[postgres@Server04 postgre]$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data #初始化数据库
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... PRC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
[postgres@Server04 postgre]$
7、设置远程连接IP
开启允许远程连接IP的限制
vim /usr/local/pgsql/data/pg_hba.conf ...................... host all all 0.0.0.0/0 trust #最后一行增加
开启端口
vim /usr/local/pgsql/data/postgresql.conf
............................
------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
#localhost改成“*” # comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 #注释 # (change requires restart)
max_connections = 100 # (change requires restart)
8、启动数据库
cd /usr/local/postgre cp contrib/start-scripts/linux /etc/init.d/postgresql vim /etc/init.d/postgresql ................ ## EDIT FROM HERE # Installation prefix prefix=/usr/local/pgsql #安装路径 # Data directory PGDATA="/usr/local/pgsql/data" #数据库数据路径 # Who to run the postmaster as, usually "postgres". (NOT "root") PGUSER=postgres # Where to keep a log file PGLOG="$PGDATA/serverlog" #日志路径 ................ chmod +x /etc/init.d/postgresql #赋予权限 chkconfig --add postgresql #添加开机自启动 service postgresql start #启动数据库
进入数据库
su postgres cd /usr/local/pgsql/bin ./psql psql (11.4) Type "help" for help. postgres=#
源码安装完成。
yum安装
1、查看一下postgresql源码
[root@Server04 ~]# yum list | grep postgresql Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast freeradius-postgresql.x86_64 3.0.13-10.el7_6 updates libreoffice-postgresql.x86_64 1:5.3.6.1-19.el7 base pcp-pmda-postgresql.x86_64 4.1.0-5.el7_6 updates postgresql.i686 9.2.24-1.el7_5 base postgresql.x86_64 9.2.24-1.el7_5 base postgresql-contrib.x86_64 9.2.24-1.el7_5 base postgresql-devel.i686 9.2.24-1.el7_5 base postgresql-devel.x86_64 9.2.24-1.el7_5 base postgresql-docs.x86_64 9.2.24-1.el7_5 base postgresql-jdbc.noarch 9.2.1002-6.el7_5 base postgresql-jdbc-javadoc.noarch 9.2.1002-6.el7_5 base postgresql-libs.i686 9.2.24-1.el7_5 base postgresql-libs.x86_64 9.2.24-1.el7_5 base postgresql-odbc.x86_64 09.03.0100-2.el7 base postgresql-plperl.x86_64 9.2.24-1.el7_5 base postgresql-plpython.x86_64 9.2.24-1.el7_5 base postgresql-pltcl.x86_64 9.2.24-1.el7_5 base postgresql-server.x86_64 9.2.24-1.el7_5 base postgresql-static.i686 9.2.24-1.el7_5 base postgresql-static.x86_64 9.2.24-1.el7_5 base postgresql-test.x86_64 9.2.24-1.el7_5 base postgresql-upgrade.x86_64 9.2.24-1.el7_5 base qt-postgresql.i686 1:4.8.7-2.el7 base qt-postgresql.x86_64 1:4.8.7-2.el7 base qt5-qtbase-postgresql.i686 5.9.2-3.el7 base qt5-qtbase-postgresql.x86_64
系统默认的版本是9.，不过在使用的时候9.版本相对比较低了，所以尝试安装11.*版本
2、安装Postgre之前先安装Postgre的rpm
[root@Server04 ~]# yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm -y Loaded plugins: fastestmirror Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast pgdg-centos10-10-2.noarch.rpm | 5.6 kB 00:00:00 Examining /var/tmp/yum-root-5s8iHU/pgdg-centos10-10-2.noarch.rpm: pgdg-redhat-repo-42.0-4.noarch Marking /var/tmp/yum-root-5s8iHU/pgdg-centos10-10-2.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package pgdg-redhat-repo.noarch 0:42.0-4 will be installed --> Finished Dependency Resolution Dependencies Resolved =============================================================================================================== Package Arch Version Repository Size =============================================================================================================== Installing: pgdg-redhat-repo noarch 42.0-4 /pgdg-centos10-10-2.noarch 6.8 k Transaction Summary =============================================================================================================== Install 1 Package Total size: 6.8 k Installed size: 6.8 k Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : pgdg-redhat-repo-42.0-4.noarch 1/1 Verifying : pgdg-redhat-repo-42.0-4.noarch 1/1 Installed: pgdg-redhat-repo.noarch 0:42.0-4 Complete! [root@Server04 ~]#
3、安装Postgre的rpm之后查看一下系统中Postgre的最新版本
[root@Server04 ~]# yum list | grep postgresql postgresql11.x86_64 11.4-1PGDG.rhel7 @pgdg11 postgresql11-contrib.x86_64 11.4-1PGDG.rhel7 @pgdg11 postgresql11-libs.x86_64 11.4-1PGDG.rhel7 @pgdg11 postgresql11-server.x86_64 11.4-1PGDG.rhel7 @pgdg11 freeradius-postgresql.x86_64 3.0.13-10.el7_6 updates libreoffice-postgresql.x86_64 1:5.3.6.1-19.el7 base pcp-pmda-postgresql.x86_64 4.1.0-5.el7_6 updates postgresql.i686 9.2.24-1.el7_5 base postgresql.x86_64 9.2.24-1.el7_5 base postgresql-contrib.x86_64 9.2.24-1.el7_5 base postgresql-devel.i686 9.2.24-1.el7_5 base postgresql-devel.x86_64 9.2.24-1.el7_5 base postgresql-docs.x86_64 9.2.24-1.el7_5 base postgresql-jdbc.noarch 42.2.6-1.rhel7.1 pgdg10 postgresql-jdbc-javadoc.noarch 42.2.6-1.rhel7.1 pgdg10 postgresql-libs.i686 9.2.24-1.el7_5 base postgresql-libs.x86_64 9.2.24-1.el7_5 base postgresql-odbc.x86_64 09.03.0100-2.el7 base postgresql-plperl.x86_64 9.2.24-1.el7_5 base postgresql-plpython.x86_64 9.2.24-1.el7_5 base postgresql-pltcl.x86_64 9.2.24-1.el7_5 base postgresql-server.x86_64 9.2.24-1.el7_5 base postgresql-static.i686 9.2.24-1.el7_5 base postgresql-static.x86_64 9.2.24-1.el7_5 base postgresql-test.x86_64 9.2.24-1.el7_5 base postgresql-unit10.x86_64 7.1-1.rhel7 pgdg10 postgresql-unit10-debuginfo.x86_64 7.1-1.rhel7 pgdg10 postgresql-unit11.x86_64 7.1-1.rhel7 pgdg11 postgresql-unit11-debuginfo.x86_64 7.1-1.rhel7 pgdg11 postgresql-unit94.x86_64 2.0-1.rhel7 pgdg94 postgresql-unit94-debuginfo.x86_64 2.0-1.rhel7 pgdg94 postgresql-unit95.x86_64 7.1-1.rhel7 pgdg95 postgresql-unit95-debuginfo.x86_64 7.1-1.rhel7 pgdg95 postgresql-unit96.x86_64 7.1-1.rhel7 pgdg96 postgresql-unit96-debuginfo.x86_64 7.1-1.rhel7 pgdg96 postgresql-upgrade.x86_64 9.2.24-1.el7_5 base postgresql10.x86_64 10.9-1PGDG.rhel7 pgdg10 #10.*版本的 postgresql10-contrib.x86_64 10.9-1PGDG.rhel7 pgdg10 postgresql10-debuginfo.x86_64 10.9-1PGDG.rhel7 pgdg10 postgresql10-devel.x86_64 10.9-1PGDG.rhel7 pgdg10 postgresql10-docs.x86_64 10.9-1PGDG.rhel7 pgdg10 postgresql10-libs.x86_64 10.9-1PGDG.rhel7 pgdg10 postgresql10-odbc.x86_64 11.01.0000-1PGDG.rhel7 pgdg10 postgresql10-plperl.x86_64 10.9-1PGDG.rhel7 pgdg10 postgresql10-plpython.x86_64 10.9-1PGDG.rhel7 pgdg10 postgresql10-pltcl.x86_64 10.9-1PGDG.rhel7 pgdg10 postgresql10-server.x86_64 10.9-1PGDG.rhel7 pgdg10 postgresql10-tcl.x86_64 2.4.0-1.rhel7 pgdg10 postgresql10-tcl-debuginfo.x86_64 2.3.1-1.rhel7 pgdg10 postgresql10-test.x86_64 10.9-1PGDG.rhel7 pgdg10 postgresql11-debuginfo.x86_64 11.4-1PGDG.rhel7 pgdg11 #11.*版本的 postgresql11-devel.x86_64 11.4-1PGDG.rhel7 pgdg11 postgresql11-docs.x86_64 11.4-1PGDG.rhel7 pgdg11 postgresql11-llvmjit.x86_64 11.4-1PGDG.rhel7 pgdg11 postgresql11-odbc.x86_64 11.01.0000-1PGDG.rhel7 pgdg11 postgresql11-plperl.x86_64 11.4-1PGDG.rhel7 pgdg11 postgresql11-plpython.x86_64 11.4-1PGDG.rhel7 pgdg11 postgresql11-pltcl.x86_64 11.4-1PGDG.rhel7 pgdg11 postgresql11-tcl.x86_64 2.4.0-2.rhel7.1 pgdg11 postgresql11-test.x86_64 11.4-1PGDG.rhel7 pgdg11 postgresql94.x86_64 9.4.23-1PGDG.rhel7 pgdg94 postgresql94-contrib.x86_64 9.4.23-1PGDG.rhel7 pgdg94 postgresql94-debuginfo.x86_64 9.4.23-1PGDG.rhel7 pgdg94 postgresql94-devel.x86_64 9.4.23-1PGDG.rhel7 pgdg94 postgresql94-docs.x86_64 9.4.23-1PGDG.rhel7 pgdg94 postgresql94-jdbc.noarch 9.4.1207-2.rhel7 pgdg94 postgresql94-jdbc-javadoc.noarch 9.4.1207-2.rhel7 pgdg94 postgresql94-libs.x86_64 9.4.23-1PGDG.rhel7 pgdg94 postgresql94-odbc.x86_64 11.01.0000-1PGDG.rhel7 pgdg94 postgresql94-odbc-debuginfo.x86_64 09.03.0400-1PGDG.rhel7 pgdg94 postgresql94-plperl.x86_64 9.4.23-1PGDG.rhel7 pgdg94 postgresql94-plpython.x86_64 9.4.23-1PGDG.rhel7 pgdg94 postgresql94-pltcl.x86_64 9.4.23-1PGDG.rhel7 pgdg94 postgresql94-server.x86_64 9.4.23-1PGDG.rhel7 pgdg94 postgresql94-tcl.x86_64 2.4.0-1.rhel7 pgdg94 postgresql94-tcl-debuginfo.x86_64 2.3.1-1.rhel7 pgdg94 postgresql94-test.x86_64 9.4.23-1PGDG.rhel7 pgdg94 postgresql95.x86_64 9.5.18-1PGDG.rhel7 pgdg95 postgresql95-contrib.x86_64 9.5.18-1PGDG.rhel7 pgdg95 postgresql95-debuginfo.x86_64 9.5.18-1PGDG.rhel7 pgdg95 postgresql95-devel.x86_64 9.5.18-1PGDG.rhel7 pgdg95 postgresql95-docs.x86_64 9.5.18-1PGDG.rhel7 pgdg95 postgresql95-libs.x86_64 9.5.18-1PGDG.rhel7 pgdg95 postgresql95-odbc.x86_64 11.01.0000-1PGDG.rhel7 pgdg95 postgresql95-odbc-debuginfo.x86_64 09.03.0400-1PGDG.rhel7 pgdg95 postgresql95-plperl.x86_64 9.5.18-1PGDG.rhel7 pgdg95 postgresql95-plpython.x86_64 9.5.18-1PGDG.rhel7 pgdg95 postgresql95-pltcl.x86_64 9.5.18-1PGDG.rhel7 pgdg95 postgresql95-server.x86_64 9.5.18-1PGDG.rhel7 pgdg95 postgresql95-tcl.x86_64 2.4.0-1.rhel7 pgdg95 postgresql95-tcl-debuginfo.x86_64 2.3.1-1.rhel7 pgdg95 postgresql95-test.x86_64 9.5.18-1PGDG.rhel7 pgdg95 postgresql96.x86_64 9.6.14-1PGDG.rhel7 pgdg96 postgresql96-contrib.x86_64 9.6.14-1PGDG.rhel7 pgdg96 postgresql96-debuginfo.x86_64 9.6.14-1PGDG.rhel7 pgdg96 postgresql96-devel.x86_64 9.6.14-1PGDG.rhel7 pgdg96 postgresql96-docs.x86_64 9.6.14-1PGDG.rhel7 pgdg96 postgresql96-libs.x86_64 9.6.14-1PGDG.rhel7 pgdg96 postgresql96-odbc.x86_64 11.01.0000-1PGDG.rhel7 pgdg96 postgresql96-plperl.x86_64 9.6.14-1PGDG.rhel7 pgdg96 postgresql96-plpython.x86_64 9.6.14-1PGDG.rhel7 pgdg96 postgresql96-pltcl.x86_64 9.6.14-1PGDG.rhel7 pgdg96 postgresql96-server.x86_64 9.6.14-1PGDG.rhel7 pgdg96 postgresql96-tcl.x86_64 2.4.0-1.rhel7 pgdg96 postgresql96-tcl-debuginfo.x86_64 2.3.1-1.rhel7 pgdg96 postgresql96-test.x86_64 9.6.14-1PGDG.rhel7 pgdg96 postgresql_anonymizer10.noarch 0.2.1-1.rhel7 pgdg10 postgresql_anonymizer11.noarch 0.2.1-1.rhel7 pgdg11 postgresql_anonymizer95.noarch 0.2.1-1.rhel7 pgdg95 postgresql_anonymizer96.noarch 0.2.1-1.rhel7 pgdg96 qt-postgresql.i686 1:4.8.7-3.el7_6 updates qt-postgresql.x86_64 1:4.8.7-3.el7_6 updates qt5-qtbase-postgresql.i686 5.9.2-3.el7 base qt5-qtbase-postgresql.x86_64 5.9.2-3.el7 base [root@Server04 ~]#
通过查看已经系统已经过更新到11版本，所以就安装11版本的
4、安装Postgre需要安装postgresql11-contrib和postgresql11-server
yum install postgresql11-contrib postgresql11-server -y
安装之后查看一下系统的用户，系统多了一个postgres用户
[root@Server04 ~]# cat /etc/passwd root:x:0:0:root:/root:/bin/bash bin:x:1:1:bin:/bin:/sbin/nologin daemon:x:2:2:daemon:/sbin:/sbin/nologin adm:x:3:4:adm:/var/adm:/sbin/nologin lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin sync:x:5:0:sync:/sbin:/bin/sync shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown halt:x:7:0:halt:/sbin:/sbin/halt mail:x:8:12:mail:/var/spool/mail:/sbin/nologin operator:x:11:0:operator:/root:/sbin/nologin games:x:12:100:games:/usr/games:/sbin/nologin ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin nobody:x:99:99:Nobody:/:/sbin/nologin avahi-autoipd:x:170:170:Avahi IPv4LL Stack:/var/lib/avahi-autoipd:/sbin/nologin dbus:x:81:81:System message bus:/:/sbin/nologin polkitd:x:999:998:User for polkitd:/:/sbin/nologin tss:x:59:59:Account used by the trousers package to sandbox the tcsd daemon:/dev/null:/sbin/nologin postfix:x:89:89::/var/spool/postfix:/sbin/nologin sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin ntp:x:38:38::/etc/ntp:/sbin/nologin systemd-network:x:192:192:systemd Network Management:/:/sbin/nologin postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash #postgres用户
至此postgres已经安装完成。
5、扩展安装PostGIS
先安装几个工具包
yum install wget net-tools epel-release -y #工具包 yum install postgis24_11 postgis24_11-client -y #postGIS yum install ogr_fdw11 -y #扩展工具 yum install pgrouting_11 -y
postgres设置
因为是yum安装，所以postgres的设置都是系统默认的，所以需要修改一些配置，这里先修改数据的默认目录。
1、设置数据目录
postgres默认的数据目录是存在/var/lib/pgsql/11/data路径下，11是postgres的版本号，在11目录下有两个目录一个是备份目录一个数据目录。现在修改数据目录的路径为/home/postgres/data，先创建一下目录。
[root@Server04 ~]# mkdir -p /home/postgre/data [root@Server04 ~]# mkdir -p /home/postgre/back [root@Server04 ~]# chown -R postgres:postgres /home/postgre [root@Server04 ~]# chmod 750 /home/postgre/data
创建目录，并赋予权限。
2、设置环境变量
[root@Server04 ~]# vim /etc/profile ............ export LD_LIBRARY_PATH=/usr/pgsql-11/bin export PATH=$POSTGRES/bin:$PATH export PGDATA=/home/postgre/data [root@Server04 ~]# source /etc/profile
3、初始化postgres
[root@Server04 bin]# su postgres
bash-4.2$ ls
clusterdb oid2name pg_ctl pg_resetwal pg_upgrade postmaster
createdb pg_archivecleanup pg_dump pg_restore pg_verify_checksums psql
createuser pg_basebackup pg_dumpall pg_rewind pg_waldump reindexdb
dropdb pgbench pg_isready pg_standby postgres vacuumdb
dropuser pg_config pg_receivewal pg_test_fsync postgresql-11-check-db-dir vacuumlo
initdb pg_controldata pg_recvlogical pg_test_timing postgresql-11-setup
bash-4.2$ pwd
/usr/pgsql-11/bin
bash-4.2$ ./initdb #初始化postgres
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /home/postgre/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... America/New_York
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
./pg_ctl -D /home/postgre/data -l logfile start
bash-4.2$
初始化之后，在/home/postgres/data目录下会生成postgres数据,默认目录为空。
[root@Server04 bin]# ls /home/postgre/data/ base pg_hba.conf pg_notify pg_stat pg_twophase postgresql.auto.conf global pg_ident.conf pg_replslot pg_stat_tmp PG_VERSION postgresql.conf pg_commit_ts pg_logical pg_serial pg_subtrans pg_wal pg_dynshmem pg_multixact pg_snapshots pg_tblspc pg_xact
4、在postgre的配置中也需要修改相应的路径
[root@Server04 ~]# vim /usr/lib/systemd/system/postgresql-11.service .......... #Environment=PGDATA=/var/lib/pgsql/11/data/ #注释掉，下面指定已经创建的目录 Environment=PGDATA=/home/postgre/data/ .............
配置数据开启启动，并启动数据库
[root@Server04 ~]# systemctl enable postgresql-11.service #开启启动
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-11.service to /usr/lib/systemd/system/postgresql-11.service.
[root@Server04 ~]# service postgresql-11 start #启动数据库
Redirecting to /bin/systemctl start postgresql-11.service
[root@Server04 ~]# service postgresql-11 status #查看启动状态
Redirecting to /bin/systemctl status postgresql-11.service
● postgresql-11.service - PostgreSQL 11 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2019-07-26 21:52:40 CST; 6s ago
Docs: https://www.postgresql.org/docs/11/static/
Process: 611 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 617 (postmaster)
CGroup: /system.slice/postgresql-11.service
├─617 /usr/pgsql-11/bin/postmaster -D /home/postgre/data/ #这里已经指定到修改的路径了
├─619 postgres: logger
├─621 postgres: checkpointer
├─622 postgres: background writer
├─623 postgres: walwriter
├─624 postgres: autovacuum launcher
├─625 postgres: stats collector
└─626 postgres: logical replication launcher
Jul 26 21:52:40 Server04 systemd[1]: Starting PostgreSQL 11 database server...
Jul 26 21:52:40 Server04 postmaster[617]: 2019-07-26 21:52:40.392 CST [617] LOG: listening on IPv6 addr...5432
Jul 26 21:52:40 Server04 postmaster[617]: 2019-07-26 21:52:40.392 CST [617] LOG: listening on IPv4 addr...5432
Jul 26 21:52:40 Server04 postmaster[617]: 2019-07-26 21:52:40.395 CST [617] LOG: listening on Unix sock...432"
Jul 26 21:52:40 Server04 postmaster[617]: 2019-07-26 21:52:40.397 CST [617] LOG: listening on Unix sock...432"
Jul 26 21:52:40 Server04 postmaster[617]: 2019-07-26 21:52:40.403 CST [617] LOG: redirecting log output...cess
Jul 26 21:52:40 Server04 postmaster[617]: 2019-07-26 21:52:40.403 CST [617] HINT: Future log output wil...og".
Jul 26 21:52:40 Server04 systemd[1]: Started PostgreSQL 11 database server.
Hint: Some lines were ellipsized, use -l to show in full.
[root@Server04 ~]#
数据库已经安装配置完成。下面看一下基本的操作。
5、设置远程连接
修改数据库的密码
[root@Server04 ~]# passwd postgres Changing password for user postgres. New password: Retype new password: passwd: all authentication tokens updated successfully. [root@Server04 ~]#
进入数据库
[root@Server04 ~]# su postgres bash-4.2$ psql could not change directory to "/root": Permission denied psql (11.4) Type "help" for help. postgres=#
配置远程连接
默认情况下postgresql是不用密码不支持远程登录的，需要修改配置文件
[root@Server04 ~]# vim /home/postgre/data/pg_hba.conf #这个路径是postgers数据的目录 ............... # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. local replication all trust host replication all 127.0.0.1/32 trust #host replication all ::1/128 trust host all all 0.0.0.0/0 trust
设置远程连接的限制以及端口开启
[root@Server04 ~]# vim /home/postgre/data/postgresql.conf
....................
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
listen_addresses = '*' # what IP address(es) to listen on; #允许所有用户连接
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
port = 5432 # (change requires restart) #远程端口
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp' # comma-separated list of directories
# (change requires restart)
#unix_socket_group = '' # (change requires restart)
重启数据库
[root@Server04 ~]# systemctl restart postgresql-11
使用工具远程连接测试
这里使用的pgadmin，邮件servers右键create--server，需要在General栏中Name必须要填写名称，Connection栏中写入IP，用户名密码和端口。注意防火墙关闭或者设定端口
您可以选择一种方式赞助本站
支付宝扫一扫赞助
微信钱包扫描赞助
赏