Linux（Redhat 7.6）安装PG（Postgresql 9.6.0）
1. 摘要
需要在Linux上安装postgres数据库，指定安装目录，简单测试各种配置。安装包下载地址：
//源码 https://github.com/postgres/postgres/releases //二进制 https://www.enterprisedb.com/download-postgresql-binaries //官网 https://www.postgresql.org/
本次测试使用的版本安装包是【postgresql-9.6.0.tar.gz】
查看本地yum源
[root@mm2020 ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda3 42G 4.0G 38G 10% / devtmpfs 1.9G 0 1.9G 0% /dev tmpfs 1.9G 4.0K 1.9G 1% /dev/shm tmpfs 1.9G 13M 1.9G 1% /run tmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup /dev/sda1 497M 166M 332M 34% /boot tmpfs 378M 44K 378M 1% /run/user/0 /dev/sr0 4.2G 4.2G 0 100% /run/media/root/RHEL-7.6 Server.x86_64 [root@mm2020 ~]# yum repolist Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager This system is not registered with an entitlement server. You can use subscription-manager to register. repo id repo name status !oel6 OEL6.5 5,152 repolist: 5,152
2. 操作步骤
2.1 安装前配置
2.1.1 永久关闭防火墙
systemctl status firewalld
systemctl stop firewalld
systemctl disable firewalld
[root@mm2020 ~]# systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled) Active: active (running) since Tue 2020-11-03 13:43:21 CST; 16min ago Docs: man:firewalld(1) Main PID: 9407 (firewalld) Tasks: 2 CGroup: /system.slice/firewalld.service └─9407 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid Nov 03 13:43:18 mm2020.us.oracle.com systemd[1]: Starting firewalld - dynamic firewall daemon... Nov 03 13:43:21 mm2020.us.oracle.com systemd[1]: Started firewalld - dynamic firewall daemon. [root@mm2020 ~]# systemctl stop firewalld [root@mm2020 ~]# systemctl disable firewalld Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service. Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
2.1.2 系统安装必要依赖
yum install -y gcc.x86_64 glibc.x86_64 glibc-devel.x86_64 vim-enhanced.x86_64 gcc-java apr apr-devel openssl openssl-devel libgcc.x86_64 java-1.8.0-openjdk.x86_64 java-1.8.0-openjdk-devel.x86_64 perl-Module-Install.noarch readline-devel.x86_64
[root@mm2020 ~]# yum install -y gcc.x86_64 glibc.x86_64 glibc-devel.x86_64 vim-enhanced.x86_64 gcc-java apr apr-devel openssl openssl-devel libgcc.x86_64 java-1.8.0-openjdk.x86_64 java-1.8.0-openjdk-devel.x86_64 perl-Module-Install.noarch readline-devel.x86_64 Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager This system is not registered with an entitlement server. You can use subscription-manager to register. oel6 | 4.3 kB 00:00:00 Package glibc-2.17-260.el7.x86_64 already installed and latest version Package 2:vim-enhanced-7.4.160-5.el7.x86_64 already installed and latest version No package gcc-java available. Package 1:openssl-1.0.2k-16.el7.x86_64 already installed and latest version Package libgcc-4.8.5-36.el7.x86_64 already installed and latest version Package 1:java-1.8.0-openjdk-1.8.0.181-7.b13.el7.x86_64 already installed and latest version No package perl-Module-Install.noarch available. Resolving Dependencies --> Running transaction check ---> Package apr.x86_64 0:1.4.8-3.el7_4.1 will be installed ---> Package apr-devel.x86_64 0:1.4.8-3.el7_4.1 will be installed ---> Package gcc.x86_64 0:4.8.5-36.el7 will be installed --> Processing Dependency: cpp = 4.8.5-36.el7 for package: gcc-4.8.5-36.el7.x86_64 ---> Package glibc-devel.x86_64 0:2.17-260.el7 will be installed --> Processing Dependency: glibc-headers = 2.17-260.el7 for package: glibc-devel-2.17-260.el7.x86_64 --> Processing Dependency: glibc-headers for package: glibc-devel-2.17-260.el7.x86_64 ---> Package java-1.8.0-openjdk-devel.x86_64 1:1.8.0.181-7.b13.el7 will be installed ---> Package openssl-devel.x86_64 1:1.0.2k-16.el7 will be installed --> Processing Dependency: krb5-devel(x86-64) for package: 1:openssl-devel-1.0.2k-16.el7.x86_64 --> Processing Dependency: zlib-devel(x86-64) for package: 1:openssl-devel-1.0.2k-16.el7.x86_64 ---> Package readline-devel.x86_64 0:6.2-10.el7 will be installed --> Processing Dependency: ncurses-devel for package: readline-devel-6.2-10.el7.x86_64 --> Running transaction check ---> Package cpp.x86_64 0:4.8.5-36.el7 will be installed ---> Package glibc-headers.x86_64 0:2.17-260.el7 will be installed --> Processing Dependency: kernel-headers >= 2.2.1 for package: glibc-headers-2.17-260.el7.x86_64 --> Processing Dependency: kernel-headers for package: glibc-headers-2.17-260.el7.x86_64 ---> Package krb5-devel.x86_64 0:1.15.1-34.el7 will be installed --> Processing Dependency: libkadm5(x86-64) = 1.15.1-34.el7 for package: krb5-devel-1.15.1-34.el7.x86_64 --> Processing Dependency: keyutils-libs-devel for package: krb5-devel-1.15.1-34.el7.x86_64 --> Processing Dependency: libcom_err-devel for package: krb5-devel-1.15.1-34.el7.x86_64 --> Processing Dependency: libselinux-devel for package: krb5-devel-1.15.1-34.el7.x86_64 --> Processing Dependency: libverto-devel for package: krb5-devel-1.15.1-34.el7.x86_64 ---> Package ncurses-devel.x86_64 0:5.9-14.20130511.el7_4 will be installed ---> Package zlib-devel.x86_64 0:1.2.7-18.el7 will be installed --> Running transaction check ---> Package kernel-headers.x86_64 0:3.10.0-957.el7 will be installed ---> Package keyutils-libs-devel.x86_64 0:1.5.8-3.el7 will be installed ---> Package libcom_err-devel.x86_64 0:1.42.9-13.el7 will be installed ---> Package libkadm5.x86_64 0:1.15.1-34.el7 will be installed ---> Package libselinux-devel.x86_64 0:2.5-14.1.el7 will be installed --> Processing Dependency: libsepol-devel(x86-64) >= 2.5-10 for package: libselinux-devel-2.5-14.1.el7.x86_64 --> Processing Dependency: pkgconfig(libpcre) for package: libselinux-devel-2.5-14.1.el7.x86_64 --> Processing Dependency: pkgconfig(libsepol) for package: libselinux-devel-2.5-14.1.el7.x86_64 ---> Package libverto-devel.x86_64 0:0.2.5-4.el7 will be installed --> Running transaction check ---> Package libsepol-devel.x86_64 0:2.5-10.el7 will be installed ---> Package pcre-devel.x86_64 0:8.32-17.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================================================== Package Arch Version Repository Size ============================================================================================================================================== Installing: apr x86_64 1.4.8-3.el7_4.1 oel6 103 k apr-devel x86_64 1.4.8-3.el7_4.1 oel6 188 k gcc x86_64 4.8.5-36.el7 oel6 16 M glibc-devel x86_64 2.17-260.el7 oel6 1.1 M java-1.8.0-openjdk-devel x86_64 1:1.8.0.181-7.b13.el7 oel6 9.8 M openssl-devel x86_64 1:1.0.2k-16.el7 oel6 1.5 M readline-devel x86_64 6.2-10.el7 oel6 138 k Installing for dependencies: cpp x86_64 4.8.5-36.el7 oel6 6.0 M glibc-headers x86_64 2.17-260.el7 oel6 683 k kernel-headers x86_64 3.10.0-957.el7 oel6 8.0 M keyutils-libs-devel x86_64 1.5.8-3.el7 oel6 37 k krb5-devel x86_64 1.15.1-34.el7 oel6 271 k libcom_err-devel x86_64 1.42.9-13.el7 oel6 31 k libkadm5 x86_64 1.15.1-34.el7 oel6 177 k libselinux-devel x86_64 2.5-14.1.el7 oel6 187 k libsepol-devel x86_64 2.5-10.el7 oel6 77 k libverto-devel x86_64 0.2.5-4.el7 oel6 12 k ncurses-devel x86_64 5.9-14.20130511.el7_4 oel6 713 k pcre-devel x86_64 8.32-17.el7 oel6 480 k zlib-devel x86_64 1.2.7-18.el7 oel6 50 k Transaction Summary ============================================================================================================================================== Install 7 Packages (+13 Dependent packages) Total download size: 46 M Installed size: 109 M Downloading packages: ---------------------------------------------------------------------------------------------------------------------------------------------- Total 56 MB/s | 46 MB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : libkadm5-1.15.1-34.el7.x86_64 1/20 Installing : apr-1.4.8-3.el7_4.1.x86_64 2/20 Installing : libcom_err-devel-1.42.9-13.el7.x86_64 3/20 Installing : libsepol-devel-2.5-10.el7.x86_64 4/20 Installing : zlib-devel-1.2.7-18.el7.x86_64 5/20 Installing : pcre-devel-8.32-17.el7.x86_64 6/20 Installing : libselinux-devel-2.5-14.1.el7.x86_64 7/20 Installing : keyutils-libs-devel-1.5.8-3.el7.x86_64 8/20 Installing : ncurses-devel-5.9-14.20130511.el7_4.x86_64 9/20 Installing : libverto-devel-0.2.5-4.el7.x86_64 10/20 Installing : krb5-devel-1.15.1-34.el7.x86_64 11/20 Installing : kernel-headers-3.10.0-957.el7.x86_64 12/20 Installing : glibc-headers-2.17-260.el7.x86_64 13/20 Installing : glibc-devel-2.17-260.el7.x86_64 14/20 Installing : cpp-4.8.5-36.el7.x86_64 15/20 Installing : gcc-4.8.5-36.el7.x86_64 16/20 Installing : 1:openssl-devel-1.0.2k-16.el7.x86_64 17/20 Installing : readline-devel-6.2-10.el7.x86_64 18/20 Installing : apr-devel-1.4.8-3.el7_4.1.x86_64 19/20 Installing : 1:java-1.8.0-openjdk-devel-1.8.0.181-7.b13.el7.x86_64 20/20 oel6/productid | 1.6 kB 00:00:00 Verifying : 1:java-1.8.0-openjdk-devel-1.8.0.181-7.b13.el7.x86_64 1/20 Verifying : glibc-devel-2.17-260.el7.x86_64 2/20 Verifying : cpp-4.8.5-36.el7.x86_64 3/20 Verifying : kernel-headers-3.10.0-957.el7.x86_64 4/20 Verifying : apr-devel-1.4.8-3.el7_4.1.x86_64 5/20 Verifying : 1:openssl-devel-1.0.2k-16.el7.x86_64 6/20 Verifying : gcc-4.8.5-36.el7.x86_64 7/20 Verifying : libverto-devel-0.2.5-4.el7.x86_64 8/20 Verifying : ncurses-devel-5.9-14.20130511.el7_4.x86_64 9/20 Verifying : glibc-headers-2.17-260.el7.x86_64 10/20 Verifying : keyutils-libs-devel-1.5.8-3.el7.x86_64 11/20 Verifying : pcre-devel-8.32-17.el7.x86_64 12/20 Verifying : krb5-devel-1.15.1-34.el7.x86_64 13/20 Verifying : zlib-devel-1.2.7-18.el7.x86_64 14/20 Verifying : readline-devel-6.2-10.el7.x86_64 15/20 Verifying : libselinux-devel-2.5-14.1.el7.x86_64 16/20 Verifying : libsepol-devel-2.5-10.el7.x86_64 17/20 Verifying : libcom_err-devel-1.42.9-13.el7.x86_64 18/20 Verifying : apr-1.4.8-3.el7_4.1.x86_64 19/20 Verifying : libkadm5-1.15.1-34.el7.x86_64 20/20 Installed: apr.x86_64 0:1.4.8-3.el7_4.1 apr-devel.x86_64 0:1.4.8-3.el7_4.1 gcc.x86_64 0:4.8.5-36.el7 glibc-devel.x86_64 0:2.17-260.el7 java-1.8.0-openjdk-devel.x86_64 1:1.8.0.181-7.b13.el7 openssl-devel.x86_64 1:1.0.2k-16.el7 readline-devel.x86_64 0:6.2-10.el7 Dependency Installed: cpp.x86_64 0:4.8.5-36.el7 glibc-headers.x86_64 0:2.17-260.el7 kernel-headers.x86_64 0:3.10.0-957.el7 keyutils-libs-devel.x86_64 0:1.5.8-3.el7 krb5-devel.x86_64 0:1.15.1-34.el7 libcom_err-devel.x86_64 0:1.42.9-13.el7 libkadm5.x86_64 0:1.15.1-34.el7 libselinux-devel.x86_64 0:2.5-14.1.el7 libsepol-devel.x86_64 0:2.5-10.el7 libverto-devel.x86_64 0:0.2.5-4.el7 ncurses-devel.x86_64 0:5.9-14.20130511.el7_4 pcre-devel.x86_64 0:8.32-17.el7 zlib-devel.x86_64 0:1.2.7-18.el7 Complete! [root@mm2020 ~]#
2.1.3 创建系统用户
adduser postgres echo postgres | passwd --stdin postgres
[root@mm2020 ~]# adduser postgres [root@mm2020 ~]# [root@mm2020 ~]# echo postgres | passwd --stdin postgres Changing password for user postgres. passwd: all authentication tokens updated successfully.
2.1.4 配置环境变量
//root cat >> /etc/profile << EOF PATH=$PATH:/usr/local/pgsql/bin EOF source /etc/profile //postgres su - postgres cat >> .bashrc << EOF export PGHOME=/usr/local/pgsql export PGDATA=$PGHOME/data export PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin EOF source .bashrc
2.1.5 下载上传并解压安装包
上传并解压安装包到指定目录
[root@mm2020 ~]# ls -ltr /usr/local/src total 24492 -rw-r--r--. 1 root root 25075838 Nov 2 16:58 postgresql-9.6.0.tar.gz [root@mm2020 ~]# tar -zxvf /usr/local/src/postgresql-9.6.0.tar.gz -C /usr/local/ ...... [root@mm2020 ~]# mv /usr/local/postgresql-9.6.0/ /usr/local/pgsql/ [root@mm2020 ~]# ls -ltr /usr/local/pgsql total 652 -rw-r--r--. 1 1107 1107 1209 Sep 27 2016 README -rw-r--r--. 1 1107 1107 1489 Sep 27 2016 Makefile -rw-r--r--. 1 1107 1107 283 Sep 27 2016 HISTORY -rw-r--r--. 1 1107 1107 3638 Sep 27 2016 GNUmakefile.in -rw-r--r--. 1 1107 1107 1192 Sep 27 2016 COPYRIGHT -rw-r--r--. 1 1107 1107 75195 Sep 27 2016 configure.in -rwxr-xr-x. 1 1107 1107 471157 Sep 27 2016 configure -rw-r--r--. 1 1107 1107 384 Sep 27 2016 aclocal.m4 drwxrwxrwx. 55 1107 1107 4096 Sep 27 2016 contrib drwxrwxrwx. 2 1107 1107 4096 Sep 27 2016 config drwxrwxrwx. 3 1107 1107 107 Sep 27 2016 doc -rw-r--r--. 1 1107 1107 75065 Sep 27 2016 INSTALL drwxrwxrwx. 16 1107 1107 4096 Sep 27 2016 src
2.1.6 创建数据文件
//创建数据库数据存放的文件夹，不影响后续操作 mkdir /usr/local/pgsql/data //把安装目录赋权给postgres用户 chown -R postgres:postgres /usr/local/pgsql/
2.2 安装PG
// 编译前的配置
cd /usr/local/pgsql/ ./configure --prefix=/usr/local/pgsql // 正式编译 make
//正式编译花费时间较长，请耐心等候几分钟，直到出现下面结束语句时，执行安装
......
All of PostgreSQL successfully made. Ready to install. // 安装 make install
//直到出现下面结束语句时，安装成功
PostgreSQL installation complete.
2.3 启动PG
初始化数据库、启动postgres service服务需要在系统用户【postgres】下执行。
2.3.1 检查安装成功后目录前后的差异
[root@mm2020 pgsql]# pwd /usr/local/pgsql [root@mm2020 pgsql]# chown -R postgres:postgres /usr/local/pgsql/ [root@mm2020 pgsql]# ls -ltr total 1076 -rw-r--r--. 1 postgres postgres 1209 Sep 27 2016 README -rw-r--r--. 1 postgres postgres 1489 Sep 27 2016 Makefile -rw-r--r--. 1 postgres postgres 283 Sep 27 2016 HISTORY -rw-r--r--. 1 postgres postgres 3638 Sep 27 2016 GNUmakefile.in -rw-r--r--. 1 postgres postgres 1192 Sep 27 2016 COPYRIGHT -rw-r--r--. 1 postgres postgres 75195 Sep 27 2016 configure.in -rwxr-xr-x. 1 postgres postgres 471157 Sep 27 2016 configure -rw-r--r--. 1 postgres postgres 384 Sep 27 2016 aclocal.m4 drwxrwxrwx. 55 postgres postgres 4096 Sep 27 2016 contrib drwxrwxrwx. 2 postgres postgres 4096 Sep 27 2016 config drwxrwxrwx. 3 postgres postgres 107 Sep 27 2016 doc -rw-r--r--. 1 postgres postgres 75065 Sep 27 2016 INSTALL drwxr-xr-x. 2 postgres postgres 6 Nov 3 14:29 data -rwxr-xr-x. 1 postgres postgres 39263 Nov 3 14:34 config.status -rw-r--r--. 1 postgres postgres 3638 Nov 3 14:34 GNUmakefile drwxrwxrwx. 16 postgres postgres 4096 Nov 3 14:34 src -rw-r--r--. 1 postgres postgres 369558 Nov 3 14:34 config.log drwxr-xr-x. 6 postgres postgres 4096 Nov 3 14:38 include drwxr-xr-x. 2 postgres postgres 4096 Nov 3 14:38 bin drwxr-xr-x. 6 postgres postgres 4096 Nov 3 14:38 share drwxr-xr-x. 4 postgres postgres 4096 Nov 3 14:38 lib [root@mm2020 pgsql]# cd bin/ [root@mm2020 bin]# ls -ltr total 10384 -rwxr-xr-x. 1 postgres postgres 6958152 Nov 3 14:38 postgres lrwxrwxrwx. 1 postgres postgres 8 Nov 3 14:38 postmaster -> postgres -rwxr-xr-x. 1 postgres postgres 879976 Nov 3 14:38 ecpg -rwxr-xr-x. 1 postgres postgres 109248 Nov 3 14:38 initdb -rwxr-xr-x. 1 postgres postgres 24160 Nov 3 14:38 pg_archivecleanup -rwxr-xr-x. 1 postgres postgres 78720 Nov 3 14:38 pg_basebackup -rwxr-xr-x. 1 postgres postgres 55360 Nov 3 14:38 pg_receivexlog -rwxr-xr-x. 1 postgres postgres 60120 Nov 3 14:38 pg_recvlogical -rwxr-xr-x. 1 postgres postgres 29584 Nov 3 14:38 pg_config -rwxr-xr-x. 1 postgres postgres 42480 Nov 3 14:38 pg_controldata -rwxr-xr-x. 1 postgres postgres 45064 Nov 3 14:38 pg_ctl -rwxr-xr-x. 1 postgres postgres 399048 Nov 3 14:38 pg_dump -rwxr-xr-x. 1 postgres postgres 160208 Nov 3 14:38 pg_restore -rwxr-xr-x. 1 postgres postgres 92904 Nov 3 14:38 pg_dumpall -rwxr-xr-x. 1 postgres postgres 51744 Nov 3 14:38 pg_resetxlog -rwxr-xr-x. 1 postgres postgres 86480 Nov 3 14:38 pg_rewind -rwxr-xr-x. 1 postgres postgres 24936 Nov 3 14:38 pg_test_fsync -rwxr-xr-x. 1 postgres postgres 19664 Nov 3 14:38 pg_test_timing -rwxr-xr-x. 1 postgres postgres 121376 Nov 3 14:38 pg_upgrade -rwxr-xr-x. 1 postgres postgres 76128 Nov 3 14:38 pg_xlogdump -rwxr-xr-x. 1 postgres postgres 136400 Nov 3 14:38 pgbench -rwxr-xr-x. 1 postgres postgres 515864 Nov 3 14:38 psql -rwxr-xr-x. 1 postgres postgres 58856 Nov 3 14:38 createdb -rwxr-xr-x. 1 postgres postgres 54232 Nov 3 14:38 dropdb -rwxr-xr-x. 1 postgres postgres 71752 Nov 3 14:38 createlang -rwxr-xr-x. 1 postgres postgres 71712 Nov 3 14:38 droplang -rwxr-xr-x. 1 postgres postgres 63664 Nov 3 14:38 createuser -rwxr-xr-x. 1 postgres postgres 54200 Nov 3 14:38 dropuser -rwxr-xr-x. 1 postgres postgres 59144 Nov 3 14:38 clusterdb -rwxr-xr-x. 1 postgres postgres 68016 Nov 3 14:38 vacuumdb -rwxr-xr-x. 1 postgres postgres 63336 Nov 3 14:38 reindexdb -rwxr-xr-x. 1 postgres postgres 35744 Nov 3 14:38 pg_isready
2.3.2 初始化数据库
[root@mm2020 ~]# su - postgres Last login: Tue Nov 3 14:50:30 CST 2020 on pts/0 [postgres@mm2020 ~]$ initdb -D $PGDATA 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 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 /usr/local/pgsql/data -l logfile start
2.3.3 启动postgres service服务
[postgres@mm2020 ~]$ pg_ctl -D $PGDATA -l $PGDATA/logfile start server starting
[postgres@mm2020 ~]$ ps -ef | grep pg postgres 80845 1 0 14:52 pts/0 00:00:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data postgres 80903 80787 0 14:56 pts/0 00:00:00 grep --color=auto pg [postgres@mm2020 ~]$ [postgres@mm2020 ~]$ ps -ef | grep postgres root 80786 12825 0 14:50 pts/0 00:00:00 su - postgres postgres 80787 80786 0 14:50 pts/0 00:00:00 -bash postgres 80845 1 0 14:52 pts/0 00:00:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data postgres 80847 80845 0 14:52 ? 00:00:00 postgres: checkpointer process postgres 80848 80845 0 14:52 ? 00:00:00 postgres: writer process postgres 80849 80845 0 14:52 ? 00:00:00 postgres: wal writer process postgres 80850 80845 0 14:52 ? 00:00:00 postgres: autovacuum launcher process postgres 80851 80845 0 14:52 ? 00:00:00 postgres: stats collector process postgres 80904 80787 0 14:56 pts/0 00:00:00 ps -ef postgres 80905 80787 0 14:56 pts/0 00:00:00 grep --color=auto postgres
2.4 测试PG
设置用户密码
//使用postgres账户进入控制台（现在密码应该是空） //把密码设置成123456可以使用\q命令退出控制台 [postgres@mm2020 ~]$ psql -U postgres psql (9.6.0) Type "help" for help. postgres=# \password Enter new password: <postgres> Enter it again: <postgres> //或=# alter user postgres with password 'postgres'; ALTER ROLE postgres=# \q [postgres@mm2020 ~]$
创建用于记录数据库操作语句的历史文件
touch /usr/local/pgsql/.pgsql_history chown postgres:postgres /usr/local/pgsql/.pgsql_history
//创建测试数据库 [postgres@mm2020 ~]$ createdb test //使用psql工具连接进入到数据库操作命令行 [postgres@mm2020 ~]$ psql test psql (9.6.0) Type "help" for help. //设置sql语句执行的上下文（即操作的Schema） test=# set search_path to public; SET //创建测试表 test=# create table test(id int primary key,name varchar(50) not null); CREATE TABLE test=# insert into test(id,name) values(1,'小明'); INSERT 0 1 test=# insert into test(id,name) values(2,'小红'); INSERT 0 1 //查看表记录 test=# select * from test; id | name ----+------ 1 | 小明 2 | 小红 (2 rows) //退出 test=# \q [postgres@mm2020 ~]$
2.5 配置开机自启服务
将postgresql-9.6.0.tar.gz解压后的安装路径中的文件 /usr/local/pgsql/contrib/start-scripts/linux 复制重命名为 /etc/init.d/postgresql
//切换到root用户下 cp /usr/local/pgsql/contrib/start-scripts/linux /etc/init.d/postgresql chown postgres:postgres /etc/init.d/postgresql chmod +x /etc/init.d/postgresql //修改/etc/init.d/postgresql文件的两个变量 vi /etc/init.d/postgresql prefix=$PGHOME //数据库安装路径 PGDATA=$PGDATA //数据库数据路径 //主要参数设置 [root@mm2020 ~]# ll /etc/init.d/postgresql -rwxr-xr-x. 1 postgres postgres 3642 Nov 3 19:59 /etc/init.d/postgresql [root@mm2020 ~]# cat /etc/init.d/postgresql | grep prefix= prefix=/usr/local/pgsql [root@mm2020 ~]# cat /etc/init.d/postgresql | grep PGDATA= PGDATA="/usr/local/pgsql/data"
测试关闭启动postgresql服务
[root@mm2020 ~]# service postgresql status pg_ctl: server is running (PID: 80845) /usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data" [root@mm2020 ~]# service postgresql stop Stopping PostgreSQL: ok [root@mm2020 ~]# service postgresql start Starting PostgreSQL: ok
设置postgresql服务开机自启动
[root@mm2020 ~]# chkconfig --list Note: This output shows SysV services only and does not include native systemd services. SysV configuration data might be overridden by native systemd configuration. If you want to list systemd services use 'systemctl list-unit-files'. To see services enabled on particular target use 'systemctl list-dependencies [target]'. netconsole 0:off 1:off 2:off 3:off 4:off 5:off 6:off network 0:off 1:off 2:on 3:on 4:on 5:on 6:off rhnsd 0:off 1:off 2:on 3:on 4:on 5:on 6:off vmware-tools 0:off 1:off 2:on 3:on 4:on 5:on 6:off [root@mm2020 ~]# chkconfig --add postgresql [root@mm2020 ~]# chkconfig postgresql on [root@mm2020 ~]# chkconfig --list Note: This output shows SysV services only and does not include native systemd services. SysV configuration data might be overridden by native systemd configuration. If you want to list systemd services use 'systemctl list-unit-files'. To see services enabled on particular target use 'systemctl list-dependencies [target]'. netconsole 0:off 1:off 2:off 3:off 4:off 5:off 6:off network 0:off 1:off 2:on 3:on 4:on 5:on 6:off postgresql 0:off 1:off 2:on 3:on 4:on 5:on 6:off rhnsd 0:off 1:off 2:on 3:on 4:on 5:on 6:off vmware-tools 0:off 1:off 2:on 3:on 4:on 5:on 6:off
2.6 设置监听
Linux修改PostgreSQL外部访问白名单，先关闭防火墙，或者开放默认端口5432
//主要修改两个配置文件 PGDATA=/usr/local/pgsql/data $PGDATA/pg_hba.conf $PGDATA/postgresql.conf //文件 pg_hba.conf 修改IPv4配置 //原设置 # IPv4 local connections: host all all 127.0.0.1/32 trust //可更改配置 //IP全匹配 # IPv4 local connections: host all all 192.168.1.21/32 trust //IP匹配前三段 # IPv4 local connections: host all all 192.168.1.21/24 trust //全部允许 # IPv4 local connections: host all all 0.0.0.0/0 trust //文件 postgresql.conf 修改监听配置 //原设置 # - Connection Settings - #listen_addresses = 'localhost' # 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) //可更改配置 # - Connection Settings - listen_addresses = '*' port = 5432
重启pg服务生效
[root@mm2020 ~]# service postgresql status pg_ctl: server is running (PID: 84601) /usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data" [root@mm2020 ~]# service postgresql restart Restarting PostgreSQL: ok
示例测试网络连接
[postgres@mm2020 ~]$ cat $PGDATA/pg_hba.conf | grep trust host all all 127.0.0.1/32 trust [postgres@mm2020 ~]$ psql -h 192.168.1.21 -d test -U postgres -p 5432 psql: FATAL: no pg_hba.conf entry for host "192.168.1.21", user "postgres", database "test" [root@mm2020 ~]# service postgresql restart Restarting PostgreSQL: ok [postgres@mm2020 ~]$ cat $PGDATA/pg_hba.conf | grep trust host all all 0.0.0.0/0 trust [postgres@mm2020 ~]$ psql -h 192.168.1.21 -d test -U postgres -p 5432 psql (9.6.0) Type "help" for help. test=# \q [postgres@mm2020 ~]$
先测试到这里。
至此，PG单实例测试完成。
如有转载，请标明出处。