PostgreSQL
【PostgreSQL】
========================================================================================================================================
【基础学习】
https://baike.baidu.com/item/PostgreSQL/530240?fr=aladdin
特点：
对象-关系数据库
多平台操作系统运行
数据类型丰富
适用于复杂逻辑业务需求
【主安装配置】
#安装依赖包
yum -y install readline readline-develbison bison-devel flex flex-develzlib-devel wget yum install -y epel-release yum install -y lrzsz gcc gcc-c++ make man vim unzip wget curl lua-devel lua-static patch libxml2-devel libxslt libxslt-devel gd gd-devel ntp ntpdate screen sysstat tree rsync lsof openssh-clients telnet iftop yum update -y
#安装pg
cd /usr/local/src wget https://ftp.postgresql.org/pub/source/v9.2.2/postgresql-9.2.2.tar.gz tar zxf postgresql-9.2.2.tar.gz cd postgresql-9.2.2 ./configure --prefix=/usr/local/pgsql --without-readline gmake world && gmake install-world groupadd postgres useradd -g postgres postgres mkdir -p /home/{pg_data_5432,pg_archive_5432,pg_log_5432} chown -R postgres:postgres /home/pg_*
#配置环境变量 $加了转义变量
cat >>/home/postgres/.bash_profile <<EOF export PATH export PGHOME=/usr/local/pgsql export LD_LIBRARY_PATH=\$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:\$LD_LIBRARY_PATH export DATE=`date +"%Y%m%d%H%M"` export PATH=\$PGHOME/bin:\$PATH:/usr/local/pgsql/bin: export MANPATH=\$PGHOME/share/man:$MANPATH export PGUSER=postgres export PGDATABASE=postgres alias rm='rm -i' alias ll='ls -lh' EOF
#加载环境变量
su - postgres
source /home/postgres/.bash_profile
##初始化数据库 配置postgres用户密码 ^regex$
initdb -D $PGDATA -E UTF-8 --locale=C -U postgres -W #或者 initdb -D /home/pg_data_5432 -E UTF-8 --locale=C -U postgres -W ^regex$
配置编辑
vim /home/pg_data_5432/postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 1000 # (change requires restart) shared_buffers = 5GB # min 128kB temp_buffers = 128MB # min 800kB work_mem = 12MB # min 64kB maintenance_work_mem = 64MB # min 1MB shared_preload_libraries = 'pg_stat_statements' # (change requires restart) pg_stat_statements.max = 1000 pg_stat_statements.track = all wal_level = hot_standby # minimal, archive, or hot_standby checkpoint_timeout = 20min # range 30s-1h checkpoint_completion_target = 0.8 # checkpoint target duration, 0.0 - 1.0 checkpoint_warning = 0 # 0 disables archive_mode = on # allows archiving to be done archive_command = 'test ! -f /home/pg_archive_5432/%f && cp %p /home/pg_archive_5432/%f' archive_timeout = 0 # force a logfile segment switch after this max_wal_senders = 6 # max number of walsender processes wal_keep_segments = 500 # in logfile segments, 16MB each; 0 disables synchronous_standby_names = '' # standby servers that provide sync rep hot_standby = on # "on" allows queries during recovery max_standby_streaming_delay = 30s # max delay before canceling queries wal_receiver_status_interval = 10s # send replies at least this often effective_cache_size = 5GB log_destination = 'csvlog' # Valid values are combinations of logging_collector = on # Enable capturing of stderr and csvlog log_directory = '/home/pg_log_5432' # directory where log files are written, log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern, log_rotation_age = 1d # Automatic rotation of logfiles will log_rotation_size = 500MB # Automatic rotation of logfiles will log_min_duration_statement = 5s log_lock_waits = on log_timezone = 'America/Manaus' track_activity_query_size = 3096 # (change requires restart) datestyle = 'iso, mdy' timezone = 'America/Manaus' lc_messages = 'C' # locale for system error message lc_monetary = 'C' # locale for monetary formatting lc_numeric = 'C' # locale for number formatting lc_time = 'C' # locale for time formatting default_text_search_config = 'pg_catalog.english' deadlock_timeout = 10s
vim /home/pg_data_5432/pg_hba.conf
host pay pay 192.168.255.11/32 md5 host pay pay 192.168.255.12/32 md5 host pay pay 192.168.255.11/32 md5 host pay pay 192.168.255.12/32 md5 host pay pay 192.168.0.0/32 md5 host GPO GPO 192.168.255.38/32 md5 host GPO GPO 192.168.0.0/32 md5 host all all 5.5.5.5/32 md5 host all all 103.240.65.178/32 md5 host all all 103.240.65.174/32 md5 host all all 43.243.49.250/26 md5 host all all 192.168.0.0/32 md5 host replication postgres 43.243.49.250/26 md5 host replication postgres 103.230.111.154/32 md5 host replication postgres 5.5.5.5/32 md5 host replication postgres 192.168.0.0/32 md5 host replication postgres 5.5.5.5/32 md5 host replication postgres 192.168.255.38/32 md5
权限配置：
chmod 0700 /home/pg_data_5445/
启动数据库：
su - postgres #开启数据库 pg_ctl start -D $PGDATA -l /home/pg_log/pgsql.log #或者 pg_ctl -D /home/pg_data_5432 start su - postgres -c "/usr/local/pgsql/bin/pg_ctl -D $PGDATA start" su - postgres -c "pg_ctl start -D /home/pg_data_kaoping -l /home/pg_log_kaoping/pgsql.log"
【从安装配置】
#安装依赖包
yum -y install readline readline-develbison bison-devel flex flex-develzlib-devel wget yum install -y epel-release yum install -y lrzsz gcc gcc-c++ make man vim unzip wget curl lua-devel lua-static patch libxml2-devel libxslt libxslt-devel gd gd-devel ntp ntpdate screen sysstat tree rsync lsof openssh-clients telnet iftop yum update -y
#下载源码
cd /usr/local/src wget https://ftp.postgresql.org/pub/source/v9.6.8/postgresql-9.6.8.tar.gz tar zxf postgresql-9.6.8.tar.gz cd postgresql-9.6.8 ./configure --prefix=/usr/local/pgsql --without-readline gmake world && gmake install-world groupadd postgres useradd -g postgres postgres
# 创建目录，权限配置
mkdir -p /home/{pg_data_5445,pg_archive_5445,pg_log_5445}
chown -R postgres:postgres /home/pg_*
vim /etc/sysctl.conf
kernel.sem = 50100 128256000 50100 2560
sysctl -p
#配置环境变量 $加了转义变量
cat >>/home/postgres/.bash_profile <<EOF export PATH export PGHOME=/usr/local/pgsql export LD_LIBRARY_PATH=\$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:\$LD_LIBRARY_PATH export DATE=`date +"%Y%m%d%H%M"` export PATH=\$PGHOME/bin:\$PATH:/usr/local/pgsql/bin: export MANPATH=\$PGHOME/share/man:$MANPATH export PGUSER=postgres export PGDATABASE=postgres alias rm='rm -i' alias ll='ls -lh' EOF
#加载环境变量
su - postgres source /home/postgres/.bash_profile
###########################-================主==========================权限开放
su - postgres vim pg_hba.conf host all all 8.8.8.8/32 md5 host replication postgres 8.8.8.8/32 md5 host GPO GPO 8.8.8.8/32 md5 pg_ctl -D /home/pg_data/ reload
#主开放防火墙
-A INPUT -s 8.8.8.8/32 -p tcp -m tcp -j ACCEPT
/etc/init.d/iptables restart
firewall-cmd --zone=public --permanent --add-source=8.8.8.8/29
firewall-cmd --reload
#从库流复制拉取：
screen -S anthony su - postgres pg_basebackup -D /home/pg_data_5445/ -Fp -Xs -v -P -h 10.39.12.11 -p 5432 -U postgres ^regex$ #改端口 改日志目录 改二进制文件目录 vim /home/pg_data_5445/postgresql.conf vim /home/pg_data_5445/recovery.conf # 这个说明这台机器为从库 standby_mode = on # 这个说明这台机器对应主库的信息 primary_conninfo = 'host=10.12.12.10 port=5432 user=postgres password=^regex$ application_name=node_server_5445' # 这个说明这个流复制同步到最新的数据 recovery_target_timeline = 'latest' exit chmod 0700 /home/pg_data_5445/ su - postgres pg_ctl -D /home/pg_data_5445/ start
-----------------------------------------------------------------
查看复制状态
主库中执行：
psql postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 8467 # sender的进程 usesysid | 44673 # 复制的用户id usename | replica # 复制的用户用户名 application_name | walreceiver client_addr | 10.12.12.12 # 复制的客户端地址 client_hostname | client_port | 55804 # 复制的客户端端口 backend_start | 2015-05-12 07:31:16.972157+08 # 这个主从搭建的时间 backend_xmin | state | streaming # 同步状态 startup: 连接中、catchup: 同步中、streaming: 同步 sent_location | 3/CF123560 # Master传送WAL的位置 write_location | 3/CF123560 # Slave接收WAL的位置 flush_location | 3/CF123560 # Slave同步到磁盘的WAL位置 replay_location | 3/CF123560 # Slave同步到数据库的WAL位置 sync_priority | 0 #同步Replication的优先度 0: 异步、1～?: 同步(数字越小优先度越高) sync_state | async # 有三个值，async: 异步、sync: 同步、potential: 虽然现在是异步模式，但是有可能升级到同步模式
------------------------------------------------------------------------------------
连接成功之后所有的命令都是使用”\“+ 字符或者word完成相应的功能。现将常用的几个列车
\l 列出所有数据库
\dt 列出连接数据库中所有表
\di 列出连接数据库中所有index
\dv 列出连接数据库中所有view
\h sql命令帮助
\? \ 所有命令帮助
\q 退出连接
\d tablename 列出指定tablename的表结构
----------------------------------------------------------------------------------------------------------------------------------------------
【postgres问题集锦】
1.故障或者断电起不来postgres，需要修复日志
https://blog.csdn.net/nextaction/article/details/85606530
错误日志：
2019-10-09 21:29:58.982 AMT,,,1709,,5d9e8996.6ad,1,,2019-10-09 21:29:58 AMT,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,""
2019-10-09 21:29:58.983 AMT,,,1711,,5d9e8996.6af,1,,2019-10-09 21:29:58 AMT,,0,LOG,00000,"database system was interrupted; last known up at 2019-10-09 15:32:20 AMT",,,,,,,,,""
2019-10-09 21:29:59.037 AMT,,,1711,,5d9e8996.6af,2,,2019-10-09 21:29:58 AMT,,0,LOG,00000,"invalid record length at 26/5C7E74D8: wanted 24, got 0",,,,,,,,,""
2019-10-09 21:29:59.037 AMT,,,1711,,5d9e8996.6af,3,,2019-10-09 21:29:58 AMT,,0,LOG,00000,"invalid primary checkpoint record",,,,,,,,,""
2019-10-09 21:29:59.037 AMT,,,1711,,5d9e8996.6af,4,,2019-10-09 21:29:58 AMT,,0,LOG,00000,"invalid record length at 26/5C7E73F8: wanted 24, got 0",,,,,,,,,""
2019-10-09 21:29:59.037 AMT,,,1711,,5d9e8996.6af,5,,2019-10-09 21:29:58 AMT,,0,LOG,00000,"invalid secondary checkpoint record",,,,,,,,,""
2019-10-09 21:29:59.037 AMT,,,1711,,5d9e8996.6af,6,,2019-10-09 21:29:58 AMT,,0,PANIC,XX000,"could not locate a valid checkpoint record",,,,,,,,,""
2019-10-09 21:29:59.037 AMT,,,1709,,5d9e8996.6ad,2,,2019-10-09 21:29:58 AMT,,0,LOG,00000,"startup process (PID 1711) was terminated by signal 6: Aborted",,,,,,,,,""
2019-10-09 21:29:59.037 AMT,,,1709,,5d9e8996.6ad,3,,2019-10-09 21:29:58 AMT,,0,LOG,00000,"aborting startup due to startup process failure",,,,,,,,,""
2019-10-09 21:29:59.046 AMT,,,1709,,5d9e8996.6ad,4,,2019-10-09 21:29:58 AMT,,0,LOG,00000,"database system is shut down",,,,,,,,,""
切换到bin目录下：
cd /usr/pgsql-9.3/bin
使用pg_resetxlog命令修复日志：（/var/lib/pgsql/9.3/data为pg_data的目录）
pg_resetxlog -f /var/lib/pgsql/9.3/data
重启数据库：
pg_ctl start
【增删改查】
查：
\l 查所有数据库
\c dbname 进入db库
\dt 查所有表
Select 格式：
SQL语言的固定形式
SELECT + 表字段名 + FROM +数据表名+ WHERE + 筛选条件
查看某个表格某个值
select value_name from gold_config where key_name = 'FTP_PASSWORD';
改：
Update格式：
UPDATE tableSET column1 = value1,column2 = value2 ,...WHERE condition;
更新某个值
UPDATE gold_config SET value_name = 'xuatgpo' WHERE key_name = 'FTP_USERNAME';
update gold_config set value_name= 'esbcallback.idc-oob.site' where key_name = 'esb_callback_domain';
浙公网安备 33010602011771号