CentOS 编译安装 PostgreSQL (实测 笔记 Centos 7.6 + postgresql 11.3)

1. 更新安装 openssl (依赖zlib库)

cd /usr/local/src/
wget http://zlib.net/zlib-1.2.11.tar.gz
tar zvxf zlib-1.2.11.tar.gz && cd zlib-1.2.11
./configure && make && make install

cd /usr/local/src/
wget https://www.openssl.org/source/openssl-1.1.1d.tar.gz
tar zvxf openssl-1.1.1d.tar.gz && cd openssl-1.1.1d
./config shared zlib --prefix=/usr && make && make install

2. 安装依赖包

yum install bison flex -y

yum install readline-devel zlib-devel openssl-devel systemd-devel libxml2-devel libxslt-devel libicu-devel pam-devel openldap-devel -y

******************************************************************************************************

 

yum install perl-ExtUtils-Embed tcl-devel python-devel clang -y

yum install epel-release -y

yum install llvm5.0 llvm5.0-devel -y

******************************************************************************************************
3. 创建用户及组
groupadd postgres
useradd -g postgres postgres
passwd postgres

4.创建目录

mkdir -p /data/pg/pgdata/pgsql  /data/pg/pgdata/pgtbs  /data/pg/archivelog mkdir -p /data/pg/backups 
chmod -R 700 /data/pg 
chown -R postgres:postgres /data/pg

5. 编译安装
wget https://ftp.postgresql.org/pub/source/v12.0/postgresql-12.0.tar.gz
tar -zxvf postgresql-12.0.tar.gz && cd postgresql-12.0
mkdir -p build_dir && cd build_dir

**************************************************************************************************************

如果提示有错，可修改编译文件
vim ../src/Makefile.global.in

修改以下行：

COMPILE.c.bc = $(CLANG) -Wno-ignored-attributes $(BITCODE_CFLAGS) $(CPPFLAGS) -flto=thin -emit-llvm -c

修改为：

COMPILE.c.bc = $(CLANG) -Wno-ignored-attributes $(BITCODE_CFLAGS) $(CPPFLAGS) -emit-llvm -c

**************************************************************************************************************

../configure \
--prefix=/opt/pgsql \
--enable-thread-safety \
--enable-nls \
--with-systemd \
--with-openssl \
--with-libxml \
--with-libxslt \
--with-gssapi \
--with-pam \
--with-icu \

make world && make install -world

***************************************************************************************************
./configure编译参数详解:
--prefix 指定安装目录。
--bindir 指定可执行程序的目录
--sysconfdir 默认设置各种配置文件的目录。
--libdir 设置安装库和动态可加载模块的位置。
--includedir 设置安装C和C ++头文件的目录
--datarootdir 设置各种类型的只读数据文件的根目录。share目录。
--datadir 设置已安装程序使用的只读数据文件的目录。默认值为DATAROOTDIR。
--localedir 设置用于安装区域设置数据的目录。默认值为DATAROOTDIR/locale。
--mandir PostgreSQL附带的手册页将安装在这个目录下. man目录。
--docdir 设置安装文档文件的根目录。
--htmldir PostgreSQL的HTML格式文档将安装在此目录下。默认值为DATAROOTDIR。

--with-extra-version = STRING 将STRING附加到PostgreSQL版本号
--with-libraries 用于搜索库。
--enable-nls 启用本机语言支持（NLS），即以英语以外的语言显示程序消息的功能。
--with-pgport 设置为服务器和客户端的默认端口号。默认值为5432。
--with-perl 支持perl语言。
--with-python 支持python语言。
--with-TCL 支持PL/Tcl语言。
--with-tclconfig 包含构建与Tcl接口的模块所需的配置信息。
--with-GSSAPI 构建支持GSSAPI身份验证。
--with-openssl 构建支持SSL(加密)连接
--with-pam 使用PAM（可插入身份验证模块）支持构建.
--with-ldap 使用LDAP支持构建身份验证和连接参数查找.
--without-readline 禁用psql中的命令行编辑和历史记录 .
--with-libedit-preferred 有利于使用BSD许可的libedit库.默认使用Readline。
--with-bonjour 使用Bonjour支持构建
--with-uuid=LIBRARY 使用指定的UUID库构建uuid-ossp模块.
--with-libxml 使用libxml构建(启用SQL/XML支持).
--with-libxslt 在构建xml2 模块时使用libxslt 。xml2依赖于此库来执行XML的XSL转换。
--disable-integer-datetimes 禁用对时间戳和间隔的64位整数存储的支持，并将日期时间值存储为浮点数。

--disable-float4-byval 禁用“按值”传递float4值，导致它们“通过引用”传递。此选项会降低性能.
--disable-float8-byval 禁用“按值”传递float8值，导致它们“通过引用”传递。此选项会降低性能.

--with-segsize=SEGSIZE 设置段大小，以千兆字节为单位。大表分为多个操作系统文件，每个文件的大小等于段大小

--with-blocksize=BLOCKSIZE 设置块大小，以千字节为单位。这是表中的存储单元和I / O. 默认值为8千字节

--with-wal-segsize=SEGSIZE 设置WAL段大小，以兆字节为单位。这是WAL日志中每个文件的大小。调整此大小以控制WAL日志传送的粒度可能很有用。默认大小为16兆字节。该值必须是1到64（兆字节）之间的2的幂.

--with-wal-blocksize=BLOCKSIZE 设置WAL块大小，以千字节为单位。这是WAL日志中的存储和I / O单位。默认值为8千字节.该值必须是1到64（兆字节）之间的2的幂.

--disable-spinlocks 禁用环形锁。
--disable-thread-safety 禁用线程安全,禁用客户端库的线程安全性。

--with-system-tzdata=DIRECTORY PostgreSQL包含自己的时区数据库，它需要日期和时间操作.默认与linux时区兼容。

--without-zlib 防止使用Zlib 库。

--enable-debug 使用调试符号编译所有程序和库。

--enable-depend 启用自动依赖关系跟踪。
--enable-dtrace 编译PostgreSQL，支持动态跟踪工具DTrace。
--enable-tap-tests 使用Perl TAP工具启用测试。

***************************************************************************************************

6 .配置服务，设置启动配置

vim /usr/lib/systemd/system/postgresql.service

添加内容：

[Unit]
Description=PostgreSQL database server
After=syslog.target
After=network.target

[Service]
Type=notify
User=postgres
Group=postgres
Environment=PGDATA=/data/pg/pgdata/pgsql/
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0
ExecStart=/opt/pgsql/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target

7. 设置环境变量
vim /home/postgres/.bash_profile

添加内容：

export PGPORT=8432
export PGHOME=/opt/pgsql
export PGDATA=/data/pg/pgdata/pgsql
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.UTF-8
export DATE='date +"%Y%m%d%H%M"'
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres

 

保存

source ~/.bash_profile

8. 初始化数据库
su - postgres

initdb -D $PGDATA -U postgres --locale=en_US.UTF8 -E UTF8

#切换回root用户

exit

9. 修改数据库参数

vim /data/pg/pgdata/pgsql/postgresql.conf

修改内容:

listen_addresses = '*'
unix_socket_directories = '.'
port = 8432

保存

vim /data/pg/pgdata/pgsql/pg_hba.conf

添加内容：

host all all 0.0.0.0/0 md5 # 其他用户登陆

保存
***************************************************************************************
TYPE：pg的连接方式，local：本地unix套接字，host：tcp/ip连接

DATABASE：指定数据库

USER：指定数据库用户

ADDRESS：ip地址，可以定义某台主机或某个网段。
（32代表检查整个ip地址，相当于固定的ip，24代表只检查前三位，最后一位是0~255之间的任何一个）

METHOD：认证方式，常用的有ident，md5，password，trust，reject。

md5是常用的密码认证方式。

password是以明文密码传送给数据库，建议不要在生产环境中使用。

trust是只要知道数据库用户名就能登录，建议不要在生产环境中使用。

reject是拒绝认证。
*************************************************************************************

10. 设置开机自启动

systemctl enable postgresql.service

systemctl start postgresql.service

11. 设置防火墙规则

firewall-cmd --zone=public --add-port=8432/tcp --permanent

firewall-cmd --reload && iptables -L --line-numbers|grep ACCEPT

12. 设置postgres密码

su - postgres

psql

postgres=# ALTER USER postgres WITH encrypted PASSWORD 'postgres';

postgres=# \q

exit

 

13. 重启系统

shutdown -r now

 14. 测试连接

连接数据库可以使用如下命令
cd /opt/pgsql/bin
./psql -h 192.168.1.116 -p 8432 -U postgres 

输入密码后登录

postgres=# \copyright

postgres=# \encoding 

postgres=# \l

************************************************************
postgresql 常用命令

PostgreSQL对表名、字段名都是区分大小写的
PostgreSQL在SQL语句中对大小写是不敏感的

连接数据库, 默认的用户和数据库是postgres
psql -U user -d dbname

\q 退出 psql
\c dbname 切换数据库,相当于mysql的use dbname
\l 列举数据库，相当于mysql的show databases
\dt 列举表，相当于mysql的show tables
\d tblname 查看表结构，相当于desc tblname,show columns from tbname
\di 查看索引
\copyright 显示 PostgreSQL 的使用和发行条款
\encoding [字元编码名称] 显示或设定用户端字元编码
\h [名称] SQL 命令语法上的说明，用 * 显示全部命令
\prompt [文本] 名称 提示用户设定内部变数
\password [USERNAME] securely change the password for a user

 

 

导出数据库

/opt/pgsql/bin/pg_dump -h 172.16.15.187 -p 24567 -U postgres test_db > /root/test_db.sql

导入数据库
/opt/pgsql/bin/psql -h 172.16.193.172 -p 24567 -U postgres -d test_db </root/cfmall_db.sql

 

 

创建数据库：
create database [数据库名];

删除数据库：
drop database [数据库名];

*重命名一个表：
alter table [表名A] rename to [表名B];

*删除一个表：
drop table [表名];

*在已有的表里添加字段：
alter table [表名] add column [字段名] [类型];

删除表中的字段：
alter table [表名] drop column [字段名];

修改数据库列属性
alter table 表名 alter 列名 type 类型名(350)

重命名一个字段：
alter table [表名] rename column [字段名A] to [字段名B];

*给一个字段设置缺省值：
alter table [表名] alter column [字段名] set default [新的默认值];

*去除缺省值：
alter table [表名] alter column [字段名] drop default;

在表中插入数据：
insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......);

修改表中的某行某列的数据：
update [表名] set [目标字段名]=[目标值] where [该行特征];

删除表中某行数据：
delete from [表名] where [该行特征];
delete from [表名];--删空整个表

创建表：
create table ([字段名1] [类型1] ;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;);

 

*****************************************************************************

 

https://blog.csdn.net/germany15914326114/article/details/81196778
https://www.cnblogs.com/yhq1314/p/10119556.html
https://www.cnblogs.com/huangcun200816/articles/6767070.html

1.完成单机安装

192.168.0.80 主
192.168.0.81 从 192.168.0.82 从

修改内核参数
vim /etc/sysctl.conf

#Kernel paramaters required by PostgreSql
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144

sysctl -p

vim /etc/security/limits.conf

postgres soft nproc 16384
postgres hard nproc 16384
postgres soft nofile 65536
postgres hard nofile 65536

2.主库设置

ps -ef|grep postgres
postgres 3668 1 0 20:25 ? 00:00:00 /opt/pgsql/bin/postmaster -D /data/pgdata/pgsql/
postgres 3697 3668 0 20:25 ? 00:00:00 postgres: checkpointer
postgres 3698 3668 0 20:25 ? 00:00:00 postgres: background writer
postgres 3699 3668 0 20:25 ? 00:00:00 postgres: walwriter
postgres 3700 3668 0 20:25 ? 00:00:00 postgres: autovacuum launcher
postgres 3702 3668 0 20:25 ? 00:00:00 postgres: archiver last was 000000010000000000000004
postgres 3703 3668 0 20:25 ? 00:00:00 postgres: stats collector
postgres 3705 3668 0 20:25 ? 00:00:00 postgres: logical replication launcher
postgres 3930 3668 0 20:25 ? 00:00:00 postgres: walsender repuser 192.168.0.81(56122) streaming 0/5005CF8
root 3969 3949 0 20:31 pts/0 00:00:00 grep --color=auto postgres

 

/data/pg/pgdata/pgsql
cp postgresql.conf postgresql.conf.bk
chown postgres:postgres postgresql.conf.bk

vim postgresql.conf

listen_addresses = '*'
max_connections = 1000 #从库的max_connections必须要大于主库的
wal_level = hot_standby # 热备模式,主为wal的主机
synchronous_commit = on #实时同步复制.异步改为off
checkpoint_timeout = 5min
archive_mode = on #允许归档
archive_command = '/bin/date'
max_wal_senders = 2 #流复制连接，一般与从库个数一致
wal_keep_segments = 16 #设置流复制保留的最多的xlog数目
hot_standby = on
max_standby_archive_delay = 300s
max_standby_streaming_delay = 30s
wal_sender_timeout = 60s ＃ 设置流复制主机发送数据的超时时间
wal_receiver_timeout = 60s
wal_receiver_status_interval = 1s
hot_standby_feedback = on

vim pg_hba.conf
host replication repuser 192.168.0.80/32 md5

su - postgres
psql
create role repuser login replication encrypted password '123456';

/opt/pgsql/bin/pg_ctl stop
/opt/pgsql/bin/pg_ctl start

3.从库设置
cd /data/pgdata/pgsql
rm -rf ./*
/opt/pgsql/bin/pg_basebackup -D /data/pg/pgdata/pgsql -F p -X stream -R -v -P -h 192.168.0.80 -p 8432 -U repuser

事务日志起始于时间点: 0/9000028, 基于时间表1
pg_basebackup: 启动后台 WAL 接收进程
73859/73859 kB (100%), 2/2 表空间
transaction log end point: 0/90000F8
pg_basebackup: 等待后台进程结束流操作...
pg_basebackup: base backup completed

vim postgresql.conf

#wal_level，
#max_wal_senders
#wal_keep_segments等参数

hot_standby = on #机器为从库,在备份的同时允许查询
max_standby_streaming_delay = 30s #可选，流复制最大延迟
wal_receiver_status_interval = 10s #可选，从向主报告状态的最大间隔时间
hot_standby_feedback = on #可选，查询冲突时向主反馈
max_connections = 1000 #默认参数，非主从配置相关参数，表示到数据库的连接数，一般从库做主要的读服务时，设置值需要高于主库

 

chown -R postgres:postgres /data/pg/pgdata/pgsql

vim recovery.conf

备库的 recovery.conf 配置以下参数

recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.0.80 port=8432 user=repuser sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any''

建议把密码设置在 ~/.pgpass中：也可直接设置 primary_conninfo = ‘host=192.168.0.80 port=8432 user=repuser password=abc123', 但这样会有安全风险

su - postgres
$ cd ~
$ touch .pgpass
$ chmod 0600 .pgpass
$ ll -a
$ vim .pgpass

填写以下内容：

192.168.0.80:8432:replication:repuser:abc123

*****************************************************************************************************************************

standby_mode = on # 这个说明这台机器为从库
recovery_target_timeline = 'latest' #同步到最新数据

#trigger_file = '/postgres/data/trigger_activestandby' 
#指定触发文件，文件存在时将触发从库提升为主库，前提是必须设置”standby_mode = on”；如果不设置此参数，也可采用”pg_ctl promote“触发从库切换成主库

***************************************************************************************

su - postgres

/opt/pgsql/bin/pg_ctl stop
/opt/pgsql/bin/pg_ctl start

4.检查
主库
ps -ef|grep postgres

包含以下内容
postgres 4087 26671 0 09:44 ? 00:00:00 postgres: wal sender process repuser 192.168.19.227(55336) streaming 0/D000140

select sync_state from pg_stat_replication;
async

select pg_is_in_recovery();

cd /opt/pgsql/bin/
./pg_controldata /data/pg/pgdata/pgsql/

从库
ps -ef|grep postgres

包含以下内容
postgres 32565 32563 0 09:44 ? 00:00:00 postgres: startup process recovering 00000001000000000000000D

cd /opt/pgsql/bin/
./pg_controldata /data/pg/pgdata/pgsql/

 

5.主从切换

主库:
su - postgres
cd /opt/pgsql/bin/
pg_ctl stop
pg_controldata /data/pg/pgdata/pgsql/

 

从库：
su - postgres
cd /opt/pgsql/bin/
pg_ctl promote
pg_controldata /data/pg/pgdata/pgsql/

从库的文件 变成recovery.conf
-rw-r--r-- 1 postgres postgres 195 12月 14 09:41 recovery.done

主库：
vim recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=repuser password=123456 host=192.168.0.81 port=8432 sslmode=prefer sslcompression=1 krbsrvname=postgres'
recovery_target_timeline = 'latest'

vim postgresql.conf

#max_wal_senders
#wal_keep_segments

su - postgres
/opt/pgsql/bin/pg_ctl start

 

从库：
/opt/pgsql/bin/pg_controldata /data/pg/pgdata/pgsql/

vim pg_hba.conf
host replication repuser 192.168.0.80/32 md5

su - postgres
/opt/pgsql/bin/pg_ctl reload

主从切换完成

 

6、逻辑备份及恢复

全库

pg_dumpall -h 192.168.159.145 -U postgres -p 5432 >"/backup/full_2018072505.dmp"

单库

/data/pg/bin/pg_dump -h 192.168.159.145 -U test  testdb > /backup/test.bak

 

恢复

 

psql < /backup/full_2018072505.dmp

psql < /backup/test.bak

7.生产库优化参数配置

listen_addresses = '*' # what IP address(es) to listen on;
max_connections = 950 # (change requires restart)
tcp_keepalives_idle = 3600 # TCP_KEEPIDLE, in seconds; 远程连接超时
tcp_keepalives_interval = 10 # TCP_KEEPINTVL, in seconds; 10秒内无响应则超时
tcp_keepalives_count = 10 # TCP_KEEPCNT;发送10个数据包
shared_buffers = 4GB # min 128kB
dynamic_shared_memory_type = posix # the default is the first option
wal_level = hot_standby # minimal, replica, or logical
fsync = on # flush data to disk for crash safety
wal_buffers = 16MB # min 32kB, -1 sets based on shared_buffers
max_wal_size = 2GB
min_wal_size = 1GB
checkpoint_completion_target = 0.7 # checkpoint target duration, 0.0 - 1.0
archive_mode = on # enables archiving; off, on, or always
archive_command = 'cp %p /data/pg/archivedir/%f' # command to use to archive a logfile segment
max_wal_senders = 10 # max number of walsender processes
wal_keep_segments = 10240 # in logfile segments, 16MB each; 0 disables
wal_sender_timeout = 60s # in milliseconds; 0 disables
hot_standby = on # "off" disallows queries during recovery
max_standby_streaming_delay = 30s # max delay before canceling queries
wal_receiver_status_interval = 10s # send replies at least this often
hot_standby_feedback = on # send info from standby to prevent
effective_cache_size = 8GB
log_timezone = 'PRC'
statement_timeout = 7200000 #设置单条SQL的执行超时,防雪崩,单位毫秒ms
datestyle = 'iso, ymd'
timezone = 'PRC' #时区PRC=Asia/Shanghai
lc_messages = 'zh_CN.utf8' # locale for system error message
lc_monetary = 'zh_CN.utf8' # locale for monetary formatting
lc_numeric = 'zh_CN.utf8' # locale for number formatting
lc_time = 'zh_CN.utf8' # locale for time formatting
default_text_search_config = 'pg_catalog.simple'

8.编译相关参数参考

configure 編译选项：
--prefix=PREFIX 指定安装目录，默认为 /usr/local/pgsql
--bindir= 可执行文件目录，默认 /PREFIX/bin
--sysconfdir= 配置文件目录，默认 /PREFIX/etc
--libdir= 库文件目录，默认 /PREFIX/lib
--includedir= 头文件目录，默认 /PREFIX/include
--datarootdir=DATAROOTDIR 设置只读共享文件目录，默认为 /PREFIX/share
--mandir= man手册目录，默认 /DATAROOTDIR/man
--with-extra-version=STRING 在版本号后面追加 STRING 字符串，用作自定义版本标识
--with-pgport=NUMBER 指定服务器端与客户端的默认端口号，缺省为 5432
--with-openssl 編译 ssl 支持，需要事先安装 OpenSSL 包
--with-pam 編译 PAM 支持
--with-ldap 編译 LDAP 支持，需要事先安装 OpenLDAP 包
--with-segsize= 设定 segment size ，以 gigabytes(GB) 为单位，默认为 1GB ，大型的表会按 segsize 被分割成多个文件
--with-blocksize= 指定块大小，是表内存储和 I/O 的基本单位，默认 8Kbytes ，通常无需变更，取值范围 1-32KB
--with-wal-segsize= 设置 WAL(Write-Ahead Logging) 的 segment size ，以 megabytes(MB) 为单位，默认 16MB ，取值范围 1-64MB
--with-wal-blocksize= 指定 WAL 的块大小，这是 WAL 預写式日志存储和 I/O 的基本单位，以 Kbytes 为单位，默认 8KB ，取值范围 1-64KB
--enable-debug (Compiles all programs and libraries with debugging symbols. This means that you can run the programs in a debugger to analyze problems)，使用 GCC 編译器时可用于生产环境，其它編译器会影响性能；多用于开发
--enable-profiling GCC編译器下，所有程序和库将被編译成可进行性能分析，后端退出时生成的 gmon.out 用于性能分析；多用于开发
--enable-coverage 代码覆盖率测试，仅用于开发
--enable-cassert 开启服务器的 assertion（断言） 检查，仅用于开发
--enable-depend (Enables automatic dependency tracking. With this option, the makefiles are set up so that all affected object files will be rebuilt when any header file is changed)，仅用于开发
--enable-dtrace 編译动态追踪工具 Dtrace 支持，当前尚不能用于 Linux 平台，可用于 FreeBSD、Solaris
--with-systemd 开启 systemd 支持，需9.6及以上版本

9.基本命令
\d 数据库 —— 得到所有表的名字
\d 表名 —— 得到表结构

查看字符集
show server_encoding;

修改表结构
ALTER TABLE your_table_name alter COLUMN your_column_name type character varying(3000);

得到当前db中所有表的信息（这里pg_tables是系统视图）
select * from pg_tables

查用户拥有的权限
select * from information_schema.table_privileges

select tablename from pg_tables where schemaname='public'
得到所有用户自定义表的名字（这里"tablename"字段是表的名字，"schemaname"是schema的名字。用户自定义的表，如果未经特殊处理，默认都是放在名为public的schema下）

select pg_size_pretty(pg_relation_size('t_inventory_product')); // 查看空间大小

select A.relname,B.reltuples from pg_stat_user_tables A,pg_class B where A.relname=B.relname order by B.reltuples DESC-- 查询用户所建的表及其表中记录数（非精确值，取决于vacuum的频率，但用于分析足够精度了）

SQL方式查看表结构
SELECT a.attnum,
a.attname AS field,
t.typname AS type,
a.attlen AS length,
a.atttypmod AS lengthvar,
a.attnotnull AS notnull,
b.description AS comment
FROM pg_class c,
pg_attribute a
LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
pg_type t
WHERE c.relname = 'udoc_saldiscount'
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY a.attnum;

网络登陆方式：
psql -h 192.168.159.128 -U dbuser -p password -d exampledb -p 5432
本地登陆
psql -d exampledb

创建数据库用户：
CREATE USER dbuser WITH PASSWORD 'password';

创建数据库
CREATE DATABASE exampledb OWNER dbuser;

赋予权限
GRANT ALL PRIVILEGES ON DATABASE exampledb TO dbuser;

建库参考：
mkdir /data/pg/mmmm_tbs/
CREATE ROLE mmmm LOGIN PASSWORD '123456';
create tablespace data_mmmmowner mmmm location '/data/pg/mmmm_tbs/';
create database mmmmdb with owner=mmmm template=template0 encoding='UTF8' lc_collate='zh_CN.UTF8' lc_ctype='zh_CN.UTF8' tablespace=data_mmmm;

 

*************************************************
在安装完POSTGRESQL后，数据库运行一段时间，发现/data/pg/data/目录下的 gprof目录中，有非常多的目录及文件，且占用空间越来越大

经查这是因为在./configure的时候，直接按照网上的大坑增加了参数--enable-profiling，简直了！！！上官网查这个参数是干嘛用的，原来这个参数是用来给PG开发人员作为性能分析的，根本不是给用户使用的。

--enable-profiling GCC編译器下，所有程序和库将被編译成可进行性能分析，后端退出时生成的 gmon.out 用于性能分析；多用于开发

*************************************************

 

安装psql调试器

cd /opt/pgsql/share

mkdir contrib && cd contrib

yum install git -y

git clone git://git.postgresql.org/git/pldebugger.git

cd pldebugger

export PATH=/opt/pgsql/bin/:$PATH

USE_PGXS=1 make clean

USE_PGXS=1 make

USE_PGXS=1 make install

cd /data/pg/pgdata/pgsql/

vim postgresql.conf

shared_preload_libraries = '$libdir/plugin_debugger'

systemctl restart postgresql

cd /opt/pgsql/bin

./psql -h 192.168.1.116 -p 8432 -U postgres -d postgres

postgres=#create extension pldbgapi ;

postgres=#\q

 

 

自动备份脚本

#
#！ /bin/bash
#

# Database info
DB_USER="postgres"
DB_HOST="127.0.0.1"
DB_NAME="xl_db"
DB_PORT=28221

# Backup vars
BIN_DIR="/opt/pgsql/bin"
BAK_DIR="/data/bak"
NOW_TIME="`date +%Y%m%d_%H%M%S`"

# Remove vars
CLEAN_TIME=`date +%Y%m%d -d "7 day ago"`
GREP_DIR='xl_db.sql.gz.'

# Backup db
$BIN_DIR/pg_dump -h$DB_HOST -p$DB_PORT -U$DB_USER $DB_NAME | gzip > $BAK_DIR/${DB_NAME}.sql.${NOW_TIME}.gz

# Remove expire files
for TARGET_DIR in `ls $BAK_DIR|grep $GREP_DIR`; do
DIR_DATETIME=${TARGET_DIR##*.}
DIR_DATE=${DIR_DATETIME:0:8}
if [ "${DIR_DATE}" \< "${CLEAN_TIME}" ]; then
rm -rf $BAK_DIR/$TARGET_DIR > /dev/null
echo "Match and remove $BAK_DIR/$TARGET_DIR"
#else
#echo "Not match $BAK_DIR/$TARGET_DIR"
fi
done