安装前环境准备
- make—4.2.1(至少3.8)
- gmake—4.2.1(至少3.8)
- gcc—8.3.1
- tar—1.30
- readline
- zlib
创建postgres用户组和用户
[root@CentOS /]# groupadd postgres
[root@CentOS /]# useradd -g postgres postgres
[root@CentOS /]# passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
创建安装路径并授权给postgres
-P表示递归创建。
[root@CentOS ~]# mkdir -p /data/postgres/10.14/
[root@CentOS ~]# chown -R postgres:postgres /data/
[root@CentOS ~]# ll /data/
total 0
drwxr-xr-x 3 postgres postgres 18 May 9 11:41 postgres
获取官方源码
官方路径:https://www.postgresql.org/ftp/source/v10.0/
在对应gz上右键复制链接地址。例如PostgreSQL10.14的链接地址为:https://ftp.postgresql.org/pub/source/v10.14/postgresql-10.14.tar.gz
[root@CentOS ~]# su - postgres
[postgres@CentOS ~]$ wget https://ftp.postgresql.org/pub/source/v10.14/postgresql-10.14.tar.gz
--2021-05-09 13:54:19-- https://ftp.postgresql.org/pub/source/v10.14/postgresql-10.14.tar.gz
Resolving ftp.postgresql.org (ftp.postgresql.org)... 147.75.85.69, 87.238.57.227, 72.32.157.246, ...
Connecting to ftp.postgresql.org (ftp.postgresql.org)|147.75.85.69|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 25007394 (24M) [application/octet-stream]
Saving to: ‘postgresql-10.14.tar.gz’
postgresql-10.14.tar.gz 100%[===============================================================>] 23.85M 7.66MB/s in 3.1s
2021-05-09 13:54:23 (7.66 MB/s) - ‘postgresql-10.14.tar.gz’ saved [25007394/25007394]
解压源码
[postgres@CentOS ~]$ tar -zxvf postgresql-10.14.tar.gz
...
...
[postgres@CentOS ~]$ ll
total 24424
drwxrwxr-x 6 postgres postgres 273 Aug 11 2020 postgresql-10.14
-rw-rw-r-- 1 postgres postgres 25007394 Aug 11 2020 postgresql-10.14.tar.gz
[postgres@CentOS ~]$ cd postgresql-10.14/
[postgres@CentOS postgresql-10.14]$ ll
total 684
-rw-r--r-- 1 postgres postgres 493 Aug 11 2020 aclocal.m4
drwxrwxr-x 2 postgres postgres 4096 Aug 11 2020 config
-rwxr-xr-x 1 postgres postgres 510855 Aug 11 2020 configure
-rw-r--r-- 1 postgres postgres 80199 Aug 11 2020 configure.in
drwxrwxr-x 55 postgres postgres 4096 Aug 11 2020 contrib
-rw-r--r-- 1 postgres postgres 1192 Aug 11 2020 COPYRIGHT
drwxrwxr-x 3 postgres postgres 107 Aug 11 2020 doc
-rw-r--r-- 1 postgres postgres 3911 Aug 11 2020 GNUmakefile.in
-rw-r--r-- 1 postgres postgres 284 Aug 11 2020 HISTORY
-rw-r--r-- 1 postgres postgres 73032 Aug 11 2020 INSTALL
-rw-r--r-- 1 postgres postgres 1665 Aug 11 2020 Makefile
-rw-r--r-- 1 postgres postgres 1212 Aug 11 2020 README
drwxrwxr-x 16 postgres postgres 328 Aug 11 2020 src
configure配置安装路径
[postgres@CentOS postgresql-10.14]$ ./configure --prefix=/data/postgres/10.14/
执行./configure命令,要先进入到源码解压出来的路径下,cd postgresql-10.14/
gmake编译安装
[postgres@CentOS postgresql-10.14]$ gmake world
...
...
PostgreSQL, contrib, and documentation successfully made. Ready to install.
[postgres@CentOS postgresql-10.14]$ gmake install-world
...
...
PostgreSQL, contrib, and documentation installation complete.
初始化数据库
[postgres@CentOS postgresql-10.14]$ /data/postgres/10.14/bin/initdb -D /data/postgres/10.14/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.
creating directory /data/postgres/10.14/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Asia/Shanghai
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:
/data/postgres/10.14/bin/pg_ctl -D /data/postgres/10.14/data -l logfile start
启动数据库
[postgres@CentOS postgresql-10.14]$ /data/postgres/10.14/bin/pg_ctl -D /data/postgres/10.14/data -l logfile start
waiting for server to start.... done
server started
修改环境变量
CentOS8之后的环境变量在~/.bashrc
文件中(8以下在~/.bash_profile
中)。
修改后结果如下:
[postgres@CentOS postgresql-10.14]$ cat ~/.bashrc
# .bashrc
# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
# User specific environment
if ! [[ "$PATH" =~ "$HOME/.local/bin:$HOME/bin:" ]]
then
PATH="$HOME/.local/bin:$HOME/bin:$PATH:/data/postgres/10.14/bin"
PGDATA="/data/postgres/10.14/data"
fi
export PATH PGDATA
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
# User specific aliases and functions
使用如下命令重新加载环境变量。
[postgres@CentOS postgresql-10.14]$ source ~/.bashrc
修改数据库参数
修改参数前请使用 pg_ctl stop -m fast
停止数据库,修改后再使用pg_ctl start
启动数据库
- 修改pg_hba.conf文件(文件在/data/postgres/10.14/data下)补充一行。
# IPv4 local connections:
host all all 0.0.0.0/0 md5
- 修改postgresql.conf 文件(文件在/data/postgres/10.14/data下)找到
#listen_addresses = 'localhost'
,修改如下,删除前边的#注释。
listen_addresses = '*'
测试连接
本地:
[root@CentOS /]# su - postgres
Last login: Sun May 9 13:54:00 CST 2021 on pts/0
[postgres@CentOS ~]$ psql
psql (10.14)
Type "help" for help.
postgres=#
远程
C:\Users\zhangjiqiang>psql -h 121.199.166.95 -p 5432 -d postgres -U postgres
用户 postgres 的口令:
psql (13.2, 服务器 10.14)
输入 "help" 来获取帮助信息.
postgres=#
常用数据库服务器维护命令
- 查看进程
[postgres@CentOS ~]$ ps -ef|grep postgres
postgres 46394 1 0 May09 ? 00:00:05 /data/postgres/10.14/bin/postgres
postgres 46396 46394 0 May09 ? 00:00:00 postgres: checkpointer process
postgres 46397 46394 0 May09 ? 00:00:02 postgres: writer process
postgres 46398 46394 0 May09 ? 00:00:02 postgres: wal writer process
postgres 46399 46394 0 May09 ? 00:00:01 postgres: autovacuum launcher process
postgres 46400 46394 0 May09 ? 00:00:02 postgres: stats collector process
postgres 46401 46394 0 May09 ? 00:00:00 postgres: bgworker: logical replication launcher
root 82975 82941 0 13:39 pts/1 00:00:00 su - postgres
postgres 82976 82975 0 13:39 pts/1 00:00:00 -bash
postgres 83008 82976 0 13:41 pts/1 00:00:00 ps -ef
postgres 83009 82976 0 13:41 pts/1 00:00:00 grep --color=auto postgres
- 查看端口
[postgres@CentOS ~]$ nmap localhost
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000087s latency).
Other addresses for localhost (not scanned): ::1
Not shown: 998 closed ports
PORT STATE SERVICE
22/tcp open ssh
5432/tcp open postgresql
- 查看服务状态
[postgres@CentOS ~]$ pg_ctl status
pg_ctl: server is running (PID: 46394)
/data/postgres/10.14/bin/postgres
- 停止服务
[postgres@CentOS ~]$ pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped
- 启动服务
[postgres@CentOS ~]$ pg_ctl start
waiting for server to start....2021-05-13 13:44:31.718 CST [83020] LOG: listening on IPv4 address "0.0.0.0", port 5432
2021-05-13 13:44:31.718 CST [83020] LOG: listening on IPv6 address "::", port 5432
2021-05-13 13:44:31.723 CST [83020] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-05-13 13:44:31.744 CST [83021] LOG: database system was shut down at 2021-05-13 13:44:06 CST
2021-05-13 13:44:31.749 CST [83020] LOG: database system is ready to accept connections
done
server started
卸载PostgreSQL数据库
- 源码路径gmake uninstall
[postgres@CentOS postgresql-10.14]$ pwd
/home/postgres/postgresql-10.14
[postgres@CentOS postgresql-10.14]$ gmake uninstall
...
rm -f '/data/postgres/10.14/lib/pgxs/config/install-sh'
rm -f '/data/postgres/10.14/lib/pgxs/config/missing'
gmake[1]: Leaving directory '/home/postgres/postgresql-10.14/config'
- 删除安装路径
[root@CentOS ~]# cd /
[root@CentOS /]# rm -rf /data
[root@CentOS /]# ll
total 28
lrwxrwxrwx. 1 root root 7 Nov 3 2020 bin -> usr/bin
dr-xr-xr-x. 5 root root 4096 Apr 20 12:09 boot
drwxr-xr-x 19 root root 2880 May 9 00:07 dev
drwxr-xr-x. 96 root root 8192 May 9 19:25 etc
drwxr-xr-x. 3 root root 22 May 9 19:25 home
lrwxrwxrwx. 1 root root 7 Nov 3 2020 lib -> usr/lib
lrwxrwxrwx. 1 root root 9 Nov 3 2020 lib64 -> usr/lib64
drwxr-xr-x. 2 root root 6 Nov 3 2020 media
drwxr-xr-x. 2 root root 6 Nov 3 2020 mnt
drwxr-xr-x. 3 root root 24 May 8 16:28 opt
dr-xr-xr-x 105 root root 0 May 9 00:06 proc
dr-xr-x---. 5 root root 4096 May 10 09:35 root
drwxr-xr-x 30 root root 880 May 9 00:07 run
lrwxrwxrwx. 1 root root 8 Nov 3 2020 sbin -> usr/sbin
drwxr-xr-x. 2 root root 6 Nov 3 2020 srv
dr-xr-xr-x 13 root root 0 May 9 08:06 sys
drwxrwxrwt. 9 postgres postgres 4096 May 13 13:49 tmp
drwxr-xr-x. 12 root root 144 May 8 23:40 usr
drwxr-xr-x. 21 root root 4096 May 9 00:06 var
- 删除postgres用户
[root@CentOS /]# userdel -r postgres
[root@CentOS /]# ll /home
total 0