分类 PostgreSQL 下的文章

PostgreSQL备份与恢复

postgresql数据库的备份和还原命令pg_dump
单个库纯文本SQL格式的备份:

pg_dump -U postgres -d myDBname -f dump.sql

还原:

psql -U username -f filename dbname 

使用pg_dumpall备份:

pg_dumpall -U postgres > alldbs.pgsql

恢复所有数据库:

psql -f filename

备份和恢复单个表
备份:从一个名为mydb的数据库中备份mytable一个表。

pg_dump -U postgres -d mydb -t mytable > mydb-mytable.pgsql

还原:单表备份到数据库中还原。请确保您的备份文件包含要恢复唯一的单表备份。

psql -U postgres -d mydb 
pg_dump 把一个数据库转储为纯文本文件或者是其它格式.

用法:
  pg_dump [选项]... [数据库名字]

一般选项:
  -f, --file=FILENAME          输出文件或目录名
  -F, --format=c|d|t|p         输出文件格式 (定制, 目录, tar)
                               明文 (默认值))
  -j, --jobs=NUM               执行多个并行任务进行备份转储工作
  -v, --verbose                详细模式
  -V, --version                输出版本信息,然后退出
  -Z, --compress=0-9           被压缩格式的压缩级别
  --lock-wait-timeout=TIMEOUT  在等待表锁超时后操作失败
  -?, --help                   显示此帮助, 然后退出

控制输出内容选项:
  -a, --data-only              只转储数据,不包括模式
  -b, --blobs                  在转储中包括大对象
  -c, --clean                  在重新创建之前,先清除(删除)数据库对象
  -C, --create                 在转储中包括命令,以便创建数据库
  -E, --encoding=ENCODING      转储以ENCODING形式编码的数据
  -n, --schema=SCHEMA          只转储指定名称的模式
  -N, --exclude-schema=SCHEMA  不转储已命名的模式
  -o, --oids                   在转储中包括 OID
  -O, --no-owner               在明文格式中, 忽略恢复对象所属者

  -s, --schema-only            只转储模式, 不包括数据
  -S, --superuser=NAME         在明文格式中使用指定的超级用户名
  -t, --table=TABLE            只转储指定名称的表
  -T, --exclude-table=TABLE    不转储指定名称的表
  -x, --no-privileges          不要转储权限 (grant/revoke)
  --binary-upgrade             只能由升级工具使用
  --column-inserts             以带有列名的INSERT命令形式转储数据
  --disable-dollar-quoting     取消美元 (符号) 引号, 使用 SQL 标准引号
  --disable-triggers           在只恢复数据的过程中禁用触发器
  --enable-row-security        启用行安全性(只转储用户能够访问的内容)
  --exclude-table-data=TABLE   不转储指定名称的表中的数据
  --if-exists              当删除对象时使用IF EXISTS
  --inserts                    以INSERT命令,而不是COPY命令的形式转储数据
  --no-security-labels         不转储安全标签的分配
  --no-synchronized-snapshots  在并行工作集中不使用同步快照
  --no-tablespaces             不转储表空间分配信息
  --no-unlogged-table-data     不转储没有日志的表数据
  --quote-all-identifiers      所有标识符加引号,即使不是关键字
  --section=SECTION            备份命名的节 (数据前, 数据, 及 数据后)
  --serializable-deferrable   等到备份可以无异常运行
  --snapshot=SNAPSHOT          为转储使用给定的快照
  --strict-names               要求每个表和/或schema包括模式以匹配至少一个实体
  --use-set-session-authorization
                               使用 SESSION AUTHORIZATION 命令代替
  ALTER OWNER 命令来设置所有权

联接选项:
  -d, --dbname=DBNAME       对数据库 DBNAME备份
  -h, --host=主机名        数据库服务器的主机名或套接字目录
  -p, --port=端口号        数据库服务器的端口号
  -U, --username=名字      以指定的数据库用户联接
  -w, --no-password        永远不提示输入口令
  -W, --password           强制口令提示 (自动)
  --role=ROLENAME          在转储前运行SET ROLE

如果没有提供数据库名字, 那么使用 PGDATABASE 环境变量的数值.

PostgreSQL常用操作命令整理

使用yum安装PostgreSQL:
安装PostgreSQL客户端

yum install postgresql-client -y

安装PostgreSQL服务端:

yum install postgresql -y

安装完成后,PostgreSQL服务器会自动在本机的5432端口开启。
安装图形管理界面(可选)

yum install pgadmin3 -y

启动服务

service postgresql start

安装参考:https://www.unixso.com/PostgreSQL/centos7-4-install-postgresql10-1.html
进入控制台

psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432

退出

postgres=# \q

创建用户

CREATE USER youusername WITH PASSWORD 'youpassword';

创建数据库并赋予用户

postgres=# CREATE DATABASE youdbname OWNER youusername;
postgres=# GRANT ALL PRIVILEGES ON DATABASE youdbname to youusername;
postgres=# \c youdbname;
postgres=# ALTER SCHEMA public OWNER to dbuser;
postgres=# GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO youusername;
postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO youusername;

查看所有用户

postgres=# \du

更改密码

postgres=# \password youusername

删除用户

postgres=# drop user youusername ;

查看所有库

postgres=# \l

切换数据库

postgres=# \c exampledb

常用控制台命令

\password           设置密码。
\q                  退出。
\h                  查看SQL命令的解释,比如\h select。
\?                  查看psql命令列表。
\l                  列出所有数据库。
\c [database_name]  连接其他数据库。
\d                  列出当前数据库的所有表格。
\d [table_name]     列出某一张表格的结构。
\du                 列出所有用户。
\e                  打开文本编辑器。
\conninfo           列出当前数据库和连接的信息。

基本的 SQL 语句

# 创建新表
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);

# 插入数据
INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');

# 查询记录
SELECT * FROM user_tbl;

# 更新数据
UPDATE user_tbl set name = '李四' WHERE name = '张三';

# 删除记录
DELETE FROM user_tbl WHERE name = '李四' ;

# 添加字段
ALTER TABLE user_tbl ADD email VARCHAR(40);

# 更改字段类型
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;

# 设置字段默认值(注意字符串使用单引号)
ALTER TABLE user_tbl ALTER COLUMN email SET DEFAULT 'example@example.com';

# 去除字段默认值
ALTER TABLE user_tbl ALTER email DROP DEFAULT;

# 重命名字段
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;

# 删除字段
ALTER TABLE user_tbl DROP COLUMN email;

# 表重命名
ALTER TABLE user_tbl RENAME TO backup_tbl;

# 删除表
DROP TABLE IF EXISTS backup_tbl;

# 删除库
\c hello2;
DROP DATABASE IF EXISTS hello;

使用gprecoverseg修复Segment节点

greenplum环境中测试的时候, segment节点sdw2由于硬盘空间不足,显示宕机了,重新启动的时候节点报错,启动不了;
使用gpstate -m查看节点状态显示sdw2节点失败:

[gpadmin@dw01 gpmaster]$ gpstate -m

gpstate:dw01:gpadmin-[INFO]:-Starting gpstate with args: -m
gpstate:dw01:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.12.0 build 1'
gpstate:dw01:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.2.15 (Greenplum Database 4.3.12.0 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Feb 27 2017 20:45:12'
gpstate:dw01:gpadmin-[INFO]:-Obtaining Segment details from master...
gpstate:dw01:gpadmin-[INFO]:--------------------------------------------------------------
gpstate:dw01:gpadmin-[INFO]:--Current GPDB mirror list and status
gpstate:dw01:gpadmin-[INFO]:--Type = Group
gpstate:dw01:gpadmin-[INFO]:--------------------------------------------------------------
gpstate:dw01:gpadmin-[INFO]:-   Mirror   Datadir                        Port    Status    Data Status    
gpstate:dw01:gpadmin-[WARNING]:-sdw2     /data/gpdata/gpdatam1/gpseg0   50000   Failed                   <<<<<<<<
gpstate:dw01:gpadmin-[WARNING]:-sdw2     /data/gpdata/gpdatam1/gpseg1   50001   Failed                   <<<<<<<<
gpstate:dw01:gpadmin-[INFO]:-   sdw1     /data/gpdata/gpdatam1/gpseg2   50000   Passive   Synchronized
gpstate:dw01:gpadmin-[INFO]:-   sdw1     /data/gpdata/gpdatam1/gpseg3   50001   Passive   Synchronized
gpstate:dw01:gpadmin-[INFO]:--------------------------------------------------------------
gpstate:dw01:gpadmin-[WARNING]:-2 segment(s) configured as mirror(s) have failed

gprecoverseg参数选项

-a (不提示)
不要提示用户确认。
-B parallel_processes
并行恢复的Segment数。如果未指定,则实用程序将启动最多四个并行进程,具体取决于需要恢复多少个Segment实例。
-d master_data_directory
可选。Master主机的数据目录。如果未指定,则使用为$MASTER_DATA_DIRECTORY设置的值。
-F (完全恢复)
可选。执行活动Segment实例的完整副本以恢复出现故障的Segment。 默认情况下,仅复制Segment关闭时发生的增量更改。
-i recover_config_file
指定文件的名称以及有关失效Segment要恢复的详细信息。文件中的每一行都是以下格式。SPACE关键字表示所需空间的位置。不要添加额外的空间。
filespaceOrder=[filespace1_fsname[, filespace2_fsname[, ...]]
<failed_host_address>:<port>:<data_directory>SPACE 
<recovery_host_address>:<port>:<replication_port>:<data_directory>
[:<fselocation>:...]

恢复所有失效的Segment实例:

gprecoverseg
恢复后,重新平衡用户的Greenplum数据库系统,将所有Segment重置为其首选角色。 首先检查所有Segment已启动并同步

将任何失效的Segment实例恢复到新配置的空闲Segment主机:

$ gprecoverseg -i recover_config_file

本例使用gprecoverseg修复:

20180420_172.28.95.255038[gpadmin@dw01 pg_log]$ gprecoverseg
20180420_172.28.95.25503820180420:21:50:37:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Starting gprecoverseg with args: 
20180420_172.28.95.25503820180420:21:50:37:002098 gprecoverseg:dw01:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.12.0 build 1'
20180420_172.28.95.25503820180420:21:50:37:002098 gprecoverseg:dw01:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.2.15 (Greenplum Database 4.3.12.0 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Feb 27 2017 20:45:12'
20180420_172.28.95.25503820180420:21:50:37:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Checking if segments are ready to connect
20180420_172.28.95.25503820180420:21:50:37:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Obtaining Segment details from master...
20180420_172.28.95.25503820180420:21:50:37:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Obtaining Segment details from master...
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Greenplum instance recovery parameters
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:----------------------------------------------------------
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Recovery type              = Standard
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:----------------------------------------------------------
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Recovery 1 of 2
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:----------------------------------------------------------
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Synchronization mode                        = Incremental
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Failed instance host                        = dw04
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Failed instance address                     = sdw2
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Failed instance directory                   = /data/gpdata/gpdatam1/gpseg0
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Failed instance port                        = 50000
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Failed instance replication port            = 51000
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Recovery Source instance host               = dw03
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Recovery Source instance address            = sdw1
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Recovery Source instance directory          = /data/gpdata/gpdatap1/gpseg0
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Recovery Source instance port               = 40000
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Recovery Source instance replication port   = 41000
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Recovery Target                             = in-place
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:----------------------------------------------------------
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Recovery 2 of 2
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:----------------------------------------------------------
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Synchronization mode                        = Incremental
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Failed instance host                        = dw04
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Failed instance address                     = sdw2
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Failed instance directory                   = /data/gpdata/gpdatam1/gpseg1
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Failed instance port                        = 50001
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Failed instance replication port            = 51001
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Recovery Source instance host               = dw03
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Recovery Source instance address            = sdw1
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Recovery Source instance directory          = /data/gpdata/gpdatap1/gpseg1
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Recovery Source instance port               = 40001
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Recovery Source instance replication port   = 41001
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:-   Recovery Target                             = in-place
20180420_172.28.95.25503920180420:21:50:38:002098 gprecoverseg:dw01:gpadmin-[INFO]:----------------------------------------------------------
20180420_172.28.95.255039
20180420_172.28.95.255039Continue with segment recovery procedure Yy|Nn (default=N):
20180420_172.28.95.255041> y
20180420_172.28.95.25504120180420:21:50:40:002098 gprecoverseg:dw01:gpadmin-[INFO]:-2 segment(s) to recover
20180420_172.28.95.25504120180420:21:50:40:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Ensuring 2 failed segment(s) are stopped
20180420_172.28.95.255042 
20180420_172.28.95.25504220180420:21:50:41:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Ensuring that shared memory is cleaned up for stopped segments
20180420_172.28.95.255047updating flat files
20180420_172.28.95.25504720180420:21:50:46:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Updating configuration with new mirrors
20180420_172.28.95.25504720180420:21:50:46:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Updating mirrors
20180420_172.28.95.255048. 
20180420_172.28.95.25504820180420:21:50:47:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Starting mirrors
20180420_172.28.95.25504820180420:21:50:48:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Commencing parallel primary and mirror segment instance startup, please wait...
20180420_172.28.95.255052.... 
20180420_172.28.95.25505220180420:21:50:52:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Process results...
20180420_172.28.95.25505220180420:21:50:52:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Updating configuration to mark mirrors up
20180420_172.28.95.25505220180420:21:50:52:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Updating primaries
20180420_172.28.95.25505220180420:21:50:52:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Commencing parallel primary conversion of 2 segments, please wait...
20180420_172.28.95.255054.. 
20180420_172.28.95.25505420180420:21:50:54:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Process results...
20180420_172.28.95.25505420180420:21:50:54:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Done updating primaries
20180420_172.28.95.25505420180420:21:50:54:002098 gprecoverseg:dw01:gpadmin-[INFO]:-******************************************************************
20180420_172.28.95.25505420180420:21:50:54:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Updating segments for resynchronization is completed.
20180420_172.28.95.25505420180420:21:50:54:002098 gprecoverseg:dw01:gpadmin-[INFO]:-For segments updated successfully, resynchronization will continue in the background.
20180420_172.28.95.25505420180420:21:50:54:002098 gprecoverseg:dw01:gpadmin-[INFO]:-
20180420_172.28.95.25505420180420:21:50:54:002098 gprecoverseg:dw01:gpadmin-[INFO]:-Use  gpstate -s  to check the resynchronization progress.
20180420_172.28.95.25505420180420:21:50:54:002098 gprecoverseg:dw01:gpadmin-[INFO]:-******************************************************************

修复完成查看节点状态:

20180420_172.28.95.255110[gpadmin@dw01 pg_log]$ gpstate -m
20180420_172.28.95.25511120180420:21:51:10:002350 gpstate:dw01:gpadmin-[INFO]:-Starting gpstate with args: -m
20180420_172.28.95.25511120180420:21:51:10:002350 gpstate:dw01:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.12.0 build 1'
20180420_172.28.95.25511120180420:21:51:10:002350 gpstate:dw01:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.2.15 (Greenplum Database 4.3.12.0 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Feb 27 2017 20:45:12'
20180420_172.28.95.25511120180420:21:51:10:002350 gpstate:dw01:gpadmin-[INFO]:-Obtaining Segment details from master...
20180420_172.28.95.25511120180420:21:51:10:002350 gpstate:dw01:gpadmin-[INFO]:--------------------------------------------------------------
20180420_172.28.95.25511120180420:21:51:10:002350 gpstate:dw01:gpadmin-[INFO]:--Current GPDB mirror list and status
20180420_172.28.95.25511120180420:21:51:10:002350 gpstate:dw01:gpadmin-[INFO]:--Type = Group
20180420_172.28.95.25511120180420:21:51:10:002350 gpstate:dw01:gpadmin-[INFO]:--------------------------------------------------------------
20180420_172.28.95.25511120180420:21:51:10:002350 gpstate:dw01:gpadmin-[INFO]:-   Mirror   Datadir                        Port    Status    Data Status       
20180420_172.28.95.25511120180420:21:51:10:002350 gpstate:dw01:gpadmin-[INFO]:-   sdw2     /data/gpdata/gpdatam1/gpseg0   50000   Passive   Resynchronizing
20180420_172.28.95.25511120180420:21:51:10:002350 gpstate:dw01:gpadmin-[INFO]:-   sdw2     /data/gpdata/gpdatam1/gpseg1   50001   Passive   Resynchronizing
20180420_172.28.95.25511120180420:21:51:10:002350 gpstate:dw01:gpadmin-[INFO]:-   sdw1     /data/gpdata/gpdatam1/gpseg2   50000   Passive   Synchronized
20180420_172.28.95.25511120180420:21:51:10:002350 gpstate:dw01:gpadmin-[INFO]:-   sdw1     /data/gpdata/gpdatam1/gpseg3   50001   Passive   Synchronized
20180420_172.28.95.25511120180420:21:51:10:002350 gpstate:dw01:gpadmin-[INFO]:--------------------------------------------------------------

节点全部启动,sdw2节点正在重新同步,过一段时间一般几分钟即可,根据数据量大小而定,一般很很快同步完毕;
参考文档:
https://gp-docs-cn.github.io/docs/utility_guide/admin_utilities/gprecoverseg.html
http://mysql.taobao.org/monthly/2016/04/03/

安装greenplum-cc-web-3.2.0监控工具

所有节点都提前部署好了greenplum-db数据库,且数据库及节点能启动正常并可访问,参考安装文档:https://www.unixso.com/PostgreSQL/GreenPlum.html
一、解压安装

unzip greenplum-cc-web-3.2.0-LINUX-x86_64.zip
./greenplum-cc-web-3.2.0-LINUX-x86_64.bin
安装过程中控制台提示一律yes

greenplum-cc-web默认安装在/usr/local/目录下
在/home/gpadmin/.bash_profile文件中增加

source /usr/local/greenplum-cc-web/gpcc_path.sh

赋予gpadmin用户权限:

chown -R gpadmin:gpadmin /usr/local/greenplum-cc-web-3.2.0
chown -R gpadmin:gpadmin /usr/local/greenplum-cc-web

给其他三台机器安装greenplum-cc-web

su - gpadmin
cd /usr/local/greenplum-db/gpconfig/dw234
smdw
sdw1
sdw2
gpccinstall -f /usr/local/greenplum-db/gpconfig/dw234

在/data/gpmaster/gpseg-1/pg_hba.conf中添加用户登录权限(如果不添加可能会导致不能创建gpcc实例)

host  all   all   ::1/128  trust

重启GP

gpstop -r

设置the Command Center Console

[gpadmin@dw01 ~]$ gpcmdr --setup

The instance name identifies the GPDB cluster this Greenplum Command Center web UI monitors and controls.
Instance names can contain letters, digits, and underscores and are not case sensitive.

Please enter the instance name
gpmon_ys

The display name is shown as the "server" in the web interface and does not need to be
a hostname.Display names can contain letters, digits, and underscores and ARE case sensitive.

Please enter the display name for this instance:(Press ENTER to use instance name)
gpmon_db

A GPCC instance can be set to manage and monitor a remote Greenplum Database.
Notice: remote mode will disable these functionalities:
1. Standby host for GPCC.
2. Workload Manager UI.

Is the master host for the Greenplum Database remote? Yy/Nn (default=N)
n

What port does the Greenplum Database use? (default=5432)


Enable kerberos login for this instance? Yy/Nn (default=N)
n

Creating instance schema in GPDB.  Please wait ...

The Greenplum Command Center runs a small web server for the UI and web API.
This web server by default runs on port 28080, but you may specify any available port.

What port would you like the new web server to use for this instance? (default=28080)


Users logging in to the Command Center must provide database user
credentials. In order to protect user names and passwords, it is recommended
that SSL be enabled.

Enable SSL for the Web API Yy/Nn (default=N)
n

Copy the instance to a standby master host Yy/Nn (default=Y)
y

What is the hostname of the standby master host?
smdw
standby is smdw
Done writing webserver configuration to  /usr/local/greenplum-cc-web/instances/gpmon_ys/webserver/conf/app.conf
Copying instance gpmon_ys to host smdw ...
=>Cleanup standby host's instance gpmon_ys if any ...
=>Copying the instance folder to standby host ...

Creating instance at /usr/local/greenplum-cc-web/instances/gpmon_ys

Greenplum Command Center UI configuration is now complete.

To change parameters of this instance, edit the configuration file
at /usr/local/greenplum-cc-web/instances/gpmon_ys/webserver/conf/app.conf

To configure multi-cluster view, edit the configuration file at /usr/local/greenplum-cc-web/instances/gpmon_ys/conf/clusters.conf

The web UI for this instance is located at http://dw01:28080

You can now start the web UI for this instance by running: gpcmdr --start gpmon_ys

启动实例

gpcmdr --start gpmon_ys
Starting instance gpmon_ys ...
Greenplum Command Center UI for instance 'gpmon_ys' - [RUNNING on PORT: 28080, pid 11984]

访问console,打开浏览器,在地址栏输入http://mastert[或者你主机的ip地址]:28080,如果出现以下界面,那么恭喜你,你的greenplum-cc-web全部安装成功!
web1.png
用户名是默认的gpmon
密码是安装Performance Monitor时的密码,登陆进去界面如下:
web2.png
web3.png
官方安装文档:
https://gpcc.docs.pivotal.io/320/gpcc/topics/setup-software.html

ucloud云上部署GreenPlum4.3.12集群

一、机器环境、版本如下:
操作系统:CentOS6.5
数据库版本:
greenplum-db-4.3.12.0-rhel5-x86_64
greenplum-cc-web-3.2.0-LINUX-x86_64

节点信息:

主机名        Ip地址          说明
mdw          172.28.1.11     主库master
smdw         172.28.1.12     备库standby master
sdw1         172.28.1.13     segment库节点一
sdw2         172.28.1.14     segment库节点二

二、系统初始化设置(四台机器都修改)
1、修改系统参数/etc/sysctl.conf

kernel.shmmax = 500000000
kernel.shmmni = 4096
kernel.shmall = 4000000000
kernel.sem = 250 512000 100 2048
kernel.sysrq = 1
kernel.core_uses_pid = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.msgmni = 2048
net.ipv4.tcp_syncookies = 1
net.ipv4.ip_forward = 0
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.arp_filter = 1
net.ipv4.conf.all.arp_filter = 1
net.ipv4.ip_local_port_range = 1025 65535
net.core.netdev_max_backlog = 10000
vm.overcommit_memory = 2

2、文件句柄数修改/etc/security/limits.conf

* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
* soft core unlimited

3、关闭selinux、iptables

修改/etc/selinux/config中SELINUX=disabled
关闭iptables
service iptables stop
chkconfig iptables off

4、调整磁盘IO调度
Linux磁盘I/O调度器对磁盘的访问支持不同的策略,默认的为cfq,GreenPlum建议设置为deadline
查看磁盘的I/O调度策略,看到默认的为[cfq]

查看系统分区情况:
[root@dw01 ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        20G  3.3G   16G  18% /
tmpfs            12G     0   12G   0% /dev/shm
/dev/vdb        493G  311M  467G   1% /data
查看/分区的I/O调度策略
[root@dw01 ~]# cat /sys/block/vda/queue/scheduler  
noop anticipatory [deadline] cfq 
查看/data分区的I/O调度策略
[root@dw01 ~]# cat /sys/block/vdb/queue/scheduler 
noop anticipatory [deadline] cfq 

查看当前系统内核

[root@dw01 ~]# uname -r
2.6.32-696.18.7.1.el6.ucloud.x86_64

修改系统引导文件,在/boot/grub/menu.lst 文件里面关于kernel这一行的末尾添加elevator=deadline

 /boot/grub/menu.lst 
# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You do not have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /, eg.
#          root (hd0,0)
#          kernel /boot/vmlinuz-version ro root=/dev/vda1
#          initrd /boot/initrd-[generic-]version.img
#boot=/dev/vda
default=0
timeout=1
splashimage=(hd0,0)/boot/grub/splash.xpm.gz
hiddenmenu
title CentOS (2.6.32-696.18.7.1.el6.ucloud.x86_64)
        root (hd0,0)
        kernel /boot/vmlinuz-2.6.32-696.18.7.1.el6.ucloud.x86_64 ro root=/dev/vda1 rd_NO_LUKS rd_NO_LVM LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun16 crashkernel=512M-2G:64M,2G-4G:128M,4G-:192M  KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM quiet console=tty1 console=ttyS0,115200n8 elevator=deadline
        initrd /boot/initramfs-2.6.32-696.18.7.1.el6.ucloud.x86_64.img
title CentOS (2.6.32-696.18.7.el6.x86_64.debug)
        root (hd0,0)
        kernel /boot/vmlinuz-2.6.32-696.18.7.el6.x86_64.debug ro root=/dev/vda1 rd_NO_LUKS rd_NO_LVM LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun16 crashkernel=512M-2G:64M,2G-4G:128M,4G-:192M  KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM quiet console=tty1 console=ttyS0,115200n8
        initrd /boot/initramfs-2.6.32-696.18.7.el6.x86_64.debug.img
title CentOS (2.6.32-431.11.29.el6.ucloud.x86_64)
        root (hd0,0)
        kernel /boot/vmlinuz-2.6.32-431.11.29.el6.ucloud.x86_64 ro root=/dev/vda1 rd_NO_LUKS rd_NO_LVM LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun16 crashkernel=512M-2G:64M,2G-4G:128M,4G-:192M  KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM quiet console=tty1 console=ttyS0,115200n8
        initrd /boot/initramfs-2.6.32-431.11.29.el6.ucloud.x86_64.img
title CentOS (2.6.32-431.11.25.el6.ucloud.x86_64)
        root (hd0,0)
        kernel /boot/vmlinuz-2.6.32-431.11.25.el6.ucloud.x86_64 ro root=/dev/vda1 rd_NO_LUKS rd_NO_LVM LANG=en_US.UTF-8 rd_NO_MD SYSFONT=latarcyrheb-sun16 crashkernel=512M-2G:64M,2G-4G:128M,4G-:192M  KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM quiet console=tty1 console=ttyS0,115200n8
        initrd /boot/initramfs-2.6.32-431.11.25.el6.ucloud.x86_64.img

查看硬盘I/O预读扇区值:默认为256

[root@dw01 ~]# blockdev --getra /dev/vda1 
256
[root@dw01 ~]# blockdev --getra /dev/vdb
256

修改为65536

blockdev --setra 65536 /dev/vda1
blockdev --setra 65536 /dev/vdb

须将其写入开机配置文件/etc/rc.d/rc.local 否则重启就会失效。

/etc/rc.d/rc.local 
echo "blockdev --setra 65536 /dev/vda1" >> /etc/rc.d/rc.local 
echo "blockdev --setra 65536 /dev/vdb" >> /etc/rc.d/rc.local 

5、修改hostname

[root@dw01 ~]# vim /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=dw01
NOZEROCONF=yes

6、修改hosts

 /etc/hosts 
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

172.28.1.11   dw01 mdw
172.28.1.12   dw02 smdw
172.28.1.13   dw03 sdw1
172.28.1.14   dw04 sdw2

以上设置四台机器都需设置,重启生效;

7、创建配置文件
a、创建包含Greenplum部署的所有主机名,内容如下

[gpadmin@dw01 gpconfig]$ cat host_file 
mdw
smdw
sdw1
sdw2

b、创建包含备库standby mastersegment库节点一节点二的主机名,内容如下

[gpadmin@dw01 gpconfig]$ cat dw234 
smdw
sdw1
sdw2

c、创建包含segment库节点一节点二的主机名,内容如下

[gpadmin@dw01 gpconfig]$ cat seg_hosts 
sdw1
sdw2

三、下载GreenPlum安装包
根据操作系统版本下载:
https://network.pivotal.io/products/pivotal-gpdb#/releases/4540/file_groups/560
这里选择
Greenplum Database 4.3.12.0 for RedHat Entrerprise Linux 5, 6 and 7
122 MB4.3.12.0
注意 登陆Pivotal Network账号以后,才能下载

md5sum *zip
ee90c7a35c706404840be38ba1be557b  greenplum-cc-web-3.2.0-LINUX-x86_64.zip
edaa67d561653fbf81431c968e5f297f  greenplum-db-4.3.12.0-rhel5-x86_64.zip

四、解压安装

unzip greenplum-db-4.3.12.0-rhel5-x86_64.zip
./greenplum-db-4.3.12.0-rhel5-x86_64.bin

根据系统提示输入yes和回车,默认会按照在/usr/local/greenplum-db-4.3.12.0下,并创建软链接greenplum-db ---/greenplum-db-4.3.12.0
在/home/gpadmin/.bash_profile文件中添加

source /usr/local/greenplum-db/greenplum_path.sh
export GPHOME=/usr/local/greenplum-db
export MASTER_DATA_DIRECTORY=/data/gpmaster/gpseg-1

五、免key登陆
四台机器之间互相做免密码登陆,参考https://www.unixso.com/Linux/ssh-key.html

source  /usr/local/greenplum-db/greenplum_path.sh
gpssh-exkeys -f /usr/local/greenplum-db/gpconfig/host_file     #打通所有服务器       

六、创建配置文件gpinitsystem_config
在master机器上创建

mkdir -p /usr/local/greenplum-db/gpconfig
创建/usr/local/greenplum-db/gpconfig/gpinitsystem_config文件内容如下:
ARRAY_NAME="EMC Greenplum DW"
SEG_PREFIX=gpseg
PORT_BASE=40000
declare -a DATA_DIRECTORY=(/data/gpdata/gpdatap1 /data/gpdata/gpdatap1)
MASTER_HOSTNAME=dw01
MASTER_DIRECTORY=/data/gpmaster
MASTER_PORT=5432
TRUSTED_SHELL=ssh
CHECK_POINT_SEGMENTS=8
ENCODING=UNICODE
MIRROR_PORT_BASE=50000
REPLICATION_PORT_BASE=41000
MIRROR_REPLICATION_PORT_BASE=51000
declare -a MIRROR_DATA_DIRECTORY=(/data/gpdata/gpdatam1 /data/gpdata/gpdatam1)
MACHINE_LIST_FILE=/usr/local/greenplum-db/gpconfig/seg_hosts

六、分发、创建用户、目录、权限
安装包分发:master上操作

cd /usr/local
scp -r greenplum-db-4.3.12.0/ dw02:/usr/local/
scp -r greenplum-db-4.3.12.0/ dw03:/usr/local/
scp -r greenplum-db-4.3.12.0/ dw04:/usr/local/
gpssh -f /usr/local/greenplum-db/gpconfig/host_file
Note: command history unsupported on this machine ...
=>
依次执行以下命令
useradd -g gpadmin gpadmin
useradd -g gpmon gpmon
echo 123456 | passwd gpadmin --stdin
echo 123456 | passwd gpmon --stdin
mkdir -p /data/gpdata/gpdatap1
mkdir -p /data/gpdata/gpdatam1
mkdir -p /data/gpmaster
chown -R gpadmin.gpadmin /data/gpdata/
chown -R gpadmin.gpadmin /data/gpmaster/
chown -R gpadmin.gpadmin /usr/local/greenplum-db-4.3.12.0/

七、系统检查

source /usr/local/greenplum-db/greenplum_path.sh
gpcheck -f /usr/local/greenplum-db/gpconfig/host_file -m mdw -s smdw          
20180406:14:39:12:026168 gpcheck:dw01:root-[INFO]:-dedupe hostnames
20180406:14:39:13:026168 gpcheck:dw01:root-[INFO]:-Detected platform: Generic Linux Cluster
20180406:14:39:13:026168 gpcheck:dw01:root-[INFO]:-generate data on servers
20180406:14:39:13:026168 gpcheck:dw01:root-[INFO]:-copy data files from servers
20180406:14:39:13:026168 gpcheck:dw01:root-[INFO]:-delete remote tmp files
20180406:14:39:13:026168 gpcheck:dw01:root-[INFO]:-Using gpcheck config file: /usr/local/greenplum-db/./etc/gpcheck.cnf
20180406:14:39:13:026168 gpcheck:dw01:root-[INFO]:-GPCHECK_NORMAL
20180406:14:39:13:026168 gpcheck:dw01:root-[INFO]:-gpcheck completing...

如果系统检查有Error提示,需根据提示修改系统参数,然后重新检查
检查网络性能

gpcheckperf -f host_file -r N -d /tmp/ > checknet.out
cat checknet.out
-------------------
--  NETPERF TEST
-------------------

====================
==  RESULT
====================
Netperf bisection bandwidth test
mdw -> smdw = 287.060000
sdw1 -> sdw2 = 279.900000
smdw -> mdw = 299.840000
sdw2 -> sdw1 = 302.450000

Summary:
sum = 1169.25 MB/sec
min = 279.90 MB/sec
max = 302.45 MB/sec
avg = 292.31 MB/sec
median = 299.84 MB/sec

八、初始化数据库
seg_hosts是segment服务器列表,一行存一个hostname,smdw是standby master的hostname名字,在gpadmin账号下运行:

su - gpadmin
cd /usr/local/greenplum-db/gpconfig/
gpinitsystem -c /usr/local/greenplum-db/gpconfig/gpinitsystem_config  -h seg_hosts -s smdw

如有报错参考官方文档:http://gpdb.docs.pivotal.io/43120/install_guide/init_gpdb.html
安装完以后登陆

[gpadmin@dw01]$ psql -d postgres

psql (8.2.15)

Type "help" for help.

 

postgres=# help

You are using psql, the command-line interface to PostgreSQL.

Type:  \copyright for distribution terms

       \h for help with SQL commands

       \? for help with psql commands

       \g or terminate with semicolon to execute query

       \q to quit

postgres=#

查看各机器进程

gpssh -f /usr/local/greenplum-db/gpconfig/host_file 
Note: command history unsupported on this machine ...
=> netstat -nltp | grep postgres
[sdw2] (Not all processes could be identified, non-owned process info
[sdw2]  will not be shown, you would have to be root to see it all.)
[sdw2] tcp        0      0 0.0.0.0:40000               0.0.0.0:*                   LISTEN      10601/postgres      
[sdw2] tcp        0      0 0.0.0.0:40001               0.0.0.0:*                   LISTEN      10602/postgres      
[sdw2] tcp        0      0 172.28.64.190:41000         0.0.0.0:*                   LISTEN      10636/postgres      
[sdw2] tcp        0      0 172.28.64.190:41001         0.0.0.0:*                   LISTEN      10641/postgres      
[sdw2] tcp        0      0 0.0.0.0:50000               0.0.0.0:*                   LISTEN      10603/postgres      
[sdw2] tcp        0      0 0.0.0.0:50001               0.0.0.0:*                   LISTEN      10600/postgres      
[sdw2] tcp        0      0 172.28.64.190:51000         0.0.0.0:*                   LISTEN      10621/postgres      
[sdw2] tcp        0      0 172.28.64.190:51001         0.0.0.0:*                   LISTEN      10620/postgres      
[sdw2] tcp        0      0 :::40000                    :::*                        LISTEN      10601/postgres      
[sdw2] tcp        0      0 :::40001                    :::*                        LISTEN      10602/postgres      
[sdw2] tcp        0      0 :::50000                    :::*                        LISTEN      10603/postgres      
[sdw2] tcp        0      0 :::50001                    :::*                        LISTEN      10600/postgres      
[smdw] (Not all processes could be identified, non-owned process info
[smdw]  will not be shown, you would have to be root to see it all.)
[smdw] tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      8501/postgres       
[smdw] tcp        0      0 :::5432                     :::*                        LISTEN      8501/postgres       
[ mdw] (Not all processes could be identified, non-owned process info
[ mdw]  will not be shown, you would have to be root to see it all.)
[ mdw] tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      17489/postgres      
[ mdw] tcp        0      0 :::5285                     :::*                        LISTEN      17496/postgres      
[ mdw] tcp        0      0 :::5432                     :::*                        LISTEN      17489/postgres      
[sdw1] (Not all processes could be identified, non-owned process info
[sdw1]  will not be shown, you would have to be root to see it all.)
[sdw1] tcp        0      0 0.0.0.0:40000               0.0.0.0:*                   LISTEN      10662/postgres      
[sdw1] tcp        0      0 0.0.0.0:40001               0.0.0.0:*                   LISTEN      10660/postgres      
[sdw1] tcp        0      0 172.28.56.68:41000          0.0.0.0:*                   LISTEN      10685/postgres      
[sdw1] tcp        0      0 172.28.56.68:41001          0.0.0.0:*                   LISTEN      10684/postgres      
[sdw1] tcp        0      0 0.0.0.0:50000               0.0.0.0:*                   LISTEN      10661/postgres      
[sdw1] tcp        0      0 0.0.0.0:50001               0.0.0.0:*                   LISTEN      10663/postgres      
[sdw1] tcp        0      0 172.28.56.68:51000          0.0.0.0:*                   LISTEN      10693/postgres      
[sdw1] tcp        0      0 172.28.56.68:51001          0.0.0.0:*                   LISTEN      10694/postgres      
[sdw1] tcp        0      0 :::40000                    :::*                        LISTEN      10662/postgres      
[sdw1] tcp        0      0 :::40001                    :::*                        LISTEN      10660/postgres      
[sdw1] tcp        0      0 :::50000                    :::*                        LISTEN      10661/postgres      
[sdw1] tcp        0      0 :::50001                    :::*                        LISTEN      10663/postgres      
=> 

九、安装PerformanceMonitor数据收集Agent

source /usr/local/greenplum-db/greenplum_path.sh
gpperfmon_install --enable --password 123456 --port 5432
gpstop -r   #重启GP生效

查看进程

ps -ef |grep gpmmon
gpadmin  17498 17489  0 Apr04 ?        00:01:04 /usr/local/greenplum-db-4.3.12.0/bin/gpmmon -D /data/gpmaster/gpseg-1/gpperfmon/conf/gpperfmon.conf -p 5432

查看端口

netstat -nltp | grep gp
tcp        0      0 0.0.0.0:28080               0.0.0.0:*                   LISTEN      11984/gpmonws             
tcp        0      0 :::8888                     :::*                        LISTEN      17611/gpsmon 

查看监控数据是否写入数据库

psql -d gpperfmon -c 'select * from system_now;'
        ctime        | hostname |  mem_total  |  mem_used  | mem_actual_used | mem_actual_free | swap_total | swap_used | swap_page_in | swap_page_out | c
pu_user | cpu_sys | cpu_idle | load0 | load1 | load2 | quantum | disk_ro_rate | disk_wo_rate | disk_rb_rate | disk_wb_rate | net_rp_rate | net_wp_rate | n
et_rb_rate | net_wb_rate 
---------------------+----------+-------------+------------+-----------------+-----------------+------------+-----------+--------------+---------------+--
--------+---------+----------+-------+-------+-------+---------+--------------+--------------+--------------+--------------+-------------+-------------+--
-----------+-------------
 2018-04-06 14:59:15 | dw01     | 25132879872 | 1774948352 |       391598080 |     24741281792 |  536866816 |         0 |            0 |             0 |  
   0.22 |    0.34 |    99.44 |  0.02 |  0.04 |     0 |      15 |            0 |            3 |            0 |        47850 |          40 |          43 |  
      9267 |       21458
 2018-04-06 14:59:15 | dw02     |  8188444672 |  963731456 |       157970432 |      8030474240 |  536866816 |         0 |            0 |             0 |  
   0.13 |    0.32 |    99.55 |  0.05 |  0.03 |     0 |      15 |            0 |            2 |            0 |         9788 |           3 |           3 |  
       331 |         572
 2018-04-06 14:59:15 | dw03     |  8188444672 | 2338099200 |       239792128 |      7948652544 |  536866816 |         0 |            0 |             0 |  
   0.28 |    0.42 |    99.27 |     0 |     0 |     0 |      15 |            0 |            9 |            0 |       178355 |         169 |          66 |  
    174387 |      154813
 2018-04-06 14:59:15 | dw04     |  8188444672 | 2338926592 |       242188288 |      7946256384 |  536866816 |         0 |            0 |             0 |  
   0.28 |    0.61 |    99.07 |     0 |     0 |     0 |      15 |            0 |            8 |            0 |       175088 |         165 |          65 |  
    167569 |      162326
(4 rows)

至此GreenPlum4.3.12集群安装完毕·

GreenPlum启动、关闭及状态查看命令参数说明

GreenPlum启动:
greenplum.jpg
在Master主机上运行gpstart启动Greenplum数据库:

gpstart

常用的启动参数有以下几个参数:
-a,该模式不需要在启动过程中输入Y进行确认,将直接启动数据库。
-m,只启动Master节点,不启动Segment节点,通常在维护的时候使用。
-y,只启动Master的primary节点,不启动standby节点。

GreenPlum停止或重启:
不要发出kill命令来结束任何Postgres进程,发出kill -9或者kill -11可能会导致数据库损坏并且妨碍对根本原因的分析。
在Master主机上运行gpstop停止Greenplum数据库:

gpstop
常用的参数如下:
-a,不需要输入Y确认是否关闭,将直接关闭数据库。

-m,只关闭Master节点,一般用于维护模式

-r,重启数据库。

-u,加载参数文件,使修改的参数生效。pg_hba.conf配置文件和Master上postgresql.conf、pg_hba.conf文件中运行时参数的更改,活动会话将会在它们重新连接到数据库时使用这些更新。很多服务器配置参数需要完全重启系统(gpstop -r)才能激活

-M,设置关闭数据库的级别,有三种级别,fast、immediate和smart。
Immediate smart 这是默认的关闭级别,所有连接的会话会收到关闭警告,不允许新链接访问数据库。
gpstop –M immediate,强制关闭数据库,这种方式是不一致的关闭模式,不建议使用。
gpstop –M fast 快速模式,停止所有连接将中断并且回滚

GreenPlum状态查看gpstate

常用的参数如下:

-s,详细信息。

-m,Mirror信息。

-f,Master的Standby信息。

-e,Segment的Mirror信息。

-i,版本信息。

参考文档:https://gp-docs-cn.github.io/docs/admin_guide/managing/startstop.html

Postgresql修改存储位置注意事项

1、修改postgresql.conf文件中的data_directory项内容,指定自己要存储数据的文件夹。

data_directory = '/data/pg10data/'

2、修改/usr/lib/systemd/system/postgresql-10.service中Environment项的内容

Environment=PGDATA=/data/pg10data/

3、将修改后的文件夹的属组和属主都改成postgre

chown postgres:postgres /data/pg10data

4、将修改后的文件夹的权限改成0700,即chmod 700 文件夹路径

chmod 700 /data/pg10data

查看PostgreSQL版本号

在Linux系统下,可以通过命令“postgres -V”或者命令“psql -V”查看

su - postgres
psql -V  或者 psql --version
psql (PostgreSQL) 10.1

在数据库里面直接使用SELECT version();查询

postgres=# select version();   #当前客户端Postgresql版本号
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# show server_version;   #查看所连接服务器端Postgresql版本号
 server_version 
----------------
 10.1
(1 row)

修改PostgreSQL数据库默认用户postgres密码

PostgreSQL 安装完成后,会建立一下'postgres'用户,用于执行PostgreSQL,数据库中也会建立一个'postgres'用户,默认密码为自动生成,需要在系统中改一下。

su - postgres    #切换用户,执行后提示符会变为 '-bash-4.2$'
psql -U postgres #登录数据库,执行后提示符变为 'postgres=#'
ALTER USER postgres WITH PASSWORD 'xxxxxx';  设置postgres用户密码 或者直接输入
\q  #退出数据库

也可以用下面这个方式

su - postgres
psql    #登录pg
\password postgres          #给postgres用户设置密码
Enter new password: 
Enter it again: 
\q

CentOS7.4快速安装PostgreSQL10.1

PostgreSQL可根据不同操作系统版本现在相应的安装包,在官方下载https://www.postgresql.org/download/ 根据数据库版本、操作系统版本选择相对应的的版本:
postgresql10.jpg
安装yum更新源

yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat10-10-1.noarch.rpm

安装PostgreSQL客户端和服务端

yum install postgresql10 -y
yum install postgresql10-server -y

修改配置、初始化数据库,启动服务

mkdir /data/pg10data
mkdir /data/pg10data
chmod 700 /data/pg10data
chown postgres:postgres /data/pg10data
vim /usr/lib/systemd/system/postgresql-10.service
把Environment=PGDATA=/var/lib/pgsql/10/data/修改为Environment=PGDATA=/data/pg10data/

/usr/pgsql-10/bin/postgresql-10-setup initdb   #初始化
systemctl enable postgresql-10                 #设置开机启动    
systemctl start postgresql-10                  #启动postgresql服务   

也可以使用postgreSQL自带的初始化命令initdb初始化数据库:

su - postgres
初始化
/usr/pgsql-10/bin/initdb --encoding=UTF-8 --local=zh_CN.UTF8 --pgdata=/data/pg10data/
启动
/usr/pgsql-10/bin/pg_ctl -D /data/pg10data/ -l /data/pg10data/log/pglog start
停止
/usr/pgsql-10/bin/pg_ctl -D /data/pg10data/ -l /data/pg10data/log/pglog stop
#写入系统启动项
echo "su - postgres -c '/usr/pgsql-10/bin/pg_ctl -D /data/pg10data/ -l /data/pg10data/log/pglog start'" >> /etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local   

使用一种方式就可以了。

开启远程访问

vim /data/pg10data/postgresql.conf 
修改#listen_addresses = 'localhost'  为  listen_addresses='*'

当然,'*'也可以改为任何你想开放的服务器IP

信任远程连接

echo "host    all             all             192.168.120.1/24        password" >> /data/pg10data/pg_hba.conf

允许192.168.120段的机器可以使用密码认证的方式访问数据库

修改数据目录位置

vim /data/pg10data/postgresql.conf 
data_directory = '/data/pg10data/'

最后重启postgresql服务生效

systemctl restart postgresql-10

最新

分类

归档

评论

  • Liang: 贴下编译参数和步骤,...
  • shao3911: 您好,为什么我在编译...
  • aliang: 先看是yum安装还是...
  • aliang: 将原来的nginx安...
  • yen: 3、如果要回滚的话,...
  • yen: 刚好需要升级ngin...
  • 文雨: 一些新的method...
  • aliang: 默认不屏蔽估计开发团...
  • 山野愚人居: PHP既然允许直接使...
  • aliang: 最下面有github地址·

其它