1 环境描述
-
操作系统:Centos7.6
-
postgresql:13.6
安装方式：源码安装
2 创建用户
# groupadd -g 2000 pgsql
# useradd -u 2000 -g pgsql pgsql
3 目录规划
# mkdir -p /postgresql/{pgdata,archive,scripts,backup,pg13,soft,pg_log}
# chown -R pgsql:pgsql /postgresql
# chmod -R 755 /postgresql
4 安装
# cd /usr/local/src
# wget http://172.25.24.80/postgresql/postgresql-13.6.tar.gz
# tar zxvf postgresql-13.6.tar.gz
# cd postgresql-13.6
# yum install -y cmake make gcc zlib gcc-c++ perl readline readline-devel zlib zlib-devel perl python36 tcl openssl ncurses-devel openldap pam rlwrap
# yum -y install perl-devel perl-ExtUtils-Embed
# cd /usr/local/src/postgresql-13.6
# ./configure --prefix=/postgresql/pg13 --without-readline --with-perl --with-python
# make
# make install
# cd contrib/pg_trgm/
# make
# make install
# cd ../btree_gist extension
# make
# make install
5 配置环境变量
# vim /etc/profile
export PGPORT=5432
export PGDATA=/postgresql/pgdata
export PGHOME=/postgresql/pg13
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
alias psql='rlwrap psql'
# source /etc/profile
6 初始化数据库
# su - pgsql
$ /postgresql/pg13/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /postgresql/pgdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: 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:
/postgresql/pg13/bin/pg_ctl -D /postgresql/pgdata -l logfile start
7 配置远程登陆
# cat /postgresql/pgdata/postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 1000
unix_socket_directories = '/postgresql/pgdata' # comma-separated list of directories
# - Authentication -
password_encryption = md5
# - Memory -
shared_buffers = 1024MB ## 1/4 of the memory
dynamic_shared_memory_type = posix
# WRITE-AHEAD LOG
wal_level = replica
max_wal_size = 4GB
min_wal_size = 1024MB
# - Archiving -
archive_mode = on
archive_command = 'cp %p /postgresql/archive/%f'
# REPLICATION
max_wal_senders = 10
wal_keep_size = 1000
wal_sender_timeout = 120s
max_slot_wal_keep_size = 10
log_directory = '/postgresql/pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
log_timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.utf8'
lc_monetary = 'en_US.utf8'
lc_numeric = 'en_US.utf8'
lc_time = 'en_US.utf8'
default_text_search_config = 'pg_catalog.english'
# cat /postgresql/pgdata/pg_hba.conf
local all all trust
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
host all all ::1/128 trust
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host replication all 0.0.0.0/32 md5
8 启动/关闭服务
# su - gpsql
$ pg_ctl start -l /postgresql/pg_log/pg.log
$ pg_ctl stop
$ pg_ctl status
或者
$ nohup /postgresql/pg13/bin/postgres -D /postgresql/pgdata > /postgresql/pg13/pglog.out 2>&1 &
说明: postgresql支持三种停止数据库的模式，这个类似Oracle。
- smart： 等待活动事务提交结束，并等待客户端主动断开连接之后关闭数据库
- fast： 回滚所有活动的事务，并强制断开客户端的链接之后关闭数据库，默认为fast
- immediate： 立即终止所有服务器进程，当下一次数据库启动的时候首先进入恢复默认。
推荐使用fast默认关闭数据库。
9 开机自启动
方式一： 配置/etc/rc.local
# vim /etc/rc.local
su - pgsql -c "pg_ctl start"
方式二： 配置systemctl服务
# vim /etc/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network.target
[Service]
Type=forking
User=pgsql
Group=pgsql
Environment=PGPORT=5432
Environment=PGDATA=/postgresql/pgdata
Environment=PGLOG=/postgresql/pg_log/pg.log
OOMScoreAdjust=-1000
ExecStart=/postgresql/pg13/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -l ${PGLOG} -w -t 300
ExecStop=/postgresql/pg13/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/postgresql/pg13/bin/pg_ctl reload -D ${PGDATA} -l ${PGLOG} -s
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=300
[Install]
WantedBy=multi-user.target
# systemctl enable postgresql.service
10. 登陆及常用命令
$ psql
常用的操作命令
(1) 列出所有数据库
\l 或者\l+
（2）连接到数据库
\c 或者 \connect , 如\c dbname
（3）切换新的用户连接当前的数据库
\c - username
关于表的操作
（4）列出当前数据库的表
\dt 或者 \dt+ 视图为\dv
(5) 显示表结构
\d tablename
(6) 显示可用的模式
\dn
（7）设置模式搜索路径
set search_path to schema_name;
(8) 查看数据库有哪些用户
\du