安装环境基于Ubuntu16.04 系统.
安装PGSQL依赖
# apt-get update -y
# apt-get -y install libreadline6-dev libxslt-dev zlib1g-dev systemtap-sdt-dev libxml2-dev libssl-dev python-dev libperl-dev openssl-* openssl libcrypt-openssl*下载PostgreSQL并编译安装
# mkdir -p /services/{packages,databases}
# cd /services/packages/
# wget --no-cookies --no-check-certificate https://ftp.postgresql.org/pub/source/v10.5/postgresql-10.5.tar.gz
# tar -zxf postgresql-10.5.tar.gz && cd postgresql-10.5
# ./configure --prefix=/services/databases/postgresql --with-pgport=5432 --with-python --with-perl --with-libxml --with-libxslt --without-ldap --enable-thread-safety --enable-dtrace --with-openssl
# make && make install创建数据库管理员账户并设置管理密码
# useradd -d /services/databases/postgresql -s /bin/bash -M postgres
# passwd postgres 输入两次密码.创建数据目录并赋予权限
# mkdir /services/databases/postgresql/{data,pg_stats_tmp,logs}
# chown -R postgres.postgres /services/databases/postgresql初始化数据目录
# su - postgres -c "/services/databases/postgresql/bin/initdb -D /services/databases/postgresql/data" 2>&1 | tee /var/log/postgresql-data.log设置环境变量
注意:后期用pgrman备份需要添加内容
# su - postgres
$ cat >> .profile <<EOF
#!/bin/bash
PGDATA=/services/databases/postgresql/data
PATH=$PATH:/services/databases/postgresql/bin
export LD_LIBRARY_PATH=/services/databases/postgresql/lib:$LD_LIBRARY_PATH
export PGDATA PATH
EOF
$ source .profile //使用户设置的变量生效.设置PostgreSQL的lib库
$ cat >> /etc/ld.so.conf.d/postgres.conf <<EOF
/services/databases/postgresql/lib
EOF
$ ldconfig //使用户添加的动态共享库生效.
$ ldconfig -p | grep postgres //查看与PostgreSQL相关的动态共享库。修改配置文件
$ vim data/postgresql.conf
max_connections = 100
shared_buffers = 128MB
dynamic_shared_memory_type = posix
#archive_mode = on
#archive_command = 'DATE=`date +%Y%m%d`; DIR="/services/databases/postgresql/postgresql-backup/walbackup/"; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f'
log_timezone = 'PRC'
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
data_directory = '/services/databases/postgresql/data'
hba_file = '/services/databases/postgresql/data/pg_hba.conf'
ident_file = '/services/databases/postgresql/data/pg_ident.conf'
external_pid_file = '/services/databases/postgresql/logs/postgresql.pid'
listen_addresses = '*'
port = 5432
unix_socket_directories = '/tmp'
shared_buffers = 128MB
dynamic_shared_memory_type = posix
log_line_prefix = '%t [%p-%l] %q%u@%d'
stats_temp_directory ='/services/databases/postgresql/pg_stats_tmp'修改pg_hba.conf
$ vim data/pg_hba.conf
host all all 0.0.0.0/0 trust
host dbname username 18.18.23.% trust临时启动PostgreSQL服务
$ pg_ctl -D /data/services/postgresql9/data/ -l logfile start将在后台启动服务器并将输出放入指定的日志文件中.
PID文件: 用于防止多个服务器实例在同一数据目录中运行，也可用于关闭服务器。快速关机: #kill -INT `head -n1 /data/services/postgresql9/postgresql.pid`注意:最好不要使用SIGKILL来关闭服务器.
设置为开机自启服务
# cp /services/packages/postgresql-10.5/contrib/start-scripts/linux /etc/init.d/postgresql
# chmod +x /etc/init.d/postgresql
# /etc/init.d/postgresql start
# update-rc.d -f postgresql defaults # 设置为开机自启动服务。基本使用
连接PostgreSQL
自带的命令行工具
# su - postgres
$ psql 或者: $ psql -h IP -U postgres -W //这种需要输入密码.
$ psql -h IP -U postgres -W -d DBNAME #指定登录数据库名.安装命令行工具:
# python3 -m pip install pgcli 或者 pip3 install pgcli连接PostgreSQL数据库:
# pgcli -h localhost -p 5432 -U postgres -W PostgreSQL控制台命令参考:
PostgreSQL系列01
基本使用
查看客户端和服务端字符集
postgres=# show client_encoding;
postgres=# show server_encoding;查看当前数据库
postgres=# select current_database();查看PostgreSQL的版本:
postgres=# SELECT version();查看日期
postgres=# SELECT current_date;每日英语(Daily English Tips)(4)
Excuse me. What's your name?
My name is Danielle. What's your name?
My name is Benny.
Can you spell your name, please? 可以拼一下你的名字吗?
yes, It's B-e-n-n-y.
What? I'm Sorry. Can you spell it again?
yes.It's B-e-n-n-y.
Thank you.
you're welcome.