环境:centos7.4、postgresql-10
目的:熟悉postgresql的安装与基本使用
大纲:
安装前的预备工作(优化,可无)
postgresql安装与配置
使用postgresql
------- 安装前的预备工作(优化,可无) -------
1 禁止内存管理的透明大页模式
1.1 透明大页原理
memory consist of pages, 1 page 4096 字节,1MB memory 256 page
CPU 有内嵌的内存管理单元,这些units include 页面列表
现在内存愈来愈大,管理大容量内存有两种方法
hardware method:增加处理器硬件内存管理单元中页表数,成本昂贵
software method:增大页面大小,目前红帽子采用超大页面方法
超大页面(标准大页)是 2MB 和 1GB 大小的内存块。其中1GB 页是 TB 内存最佳选择。
透明超大页面(THP),超大页面必须在引导时分配,很难管理,因此引出THP,可自动创建、管理和使用超大页面的大多数方面。
这两者的区别在于大页的分配机制,标准大页管理是预分配的方式,而透明大页管理则是动态分配的方式。目前透明大页与传统大页混合使用会出现一些问题,导致性能问题和系统重启。
从Redhat6(CentOS6)开始,系统默认自动启动透明超大页面。
然而,ORACLE、Postgresql等数据库使用标准大页,开启THP会导致异常节点重启和性能问题。因此一般建议关闭THP。
1.2 查看透明大页是否启用
cat /sys/kernel/mm/transparent_hugepage/enabled
输出结果为[always]表示透明大页启用了;[never]表示透明大页禁用、[madvise]表示只在MADV_HUGEPAGE标志的VMA中启用透明大页
1.3 禁用透明大页功能
1.3.1 方法一:修改grub.conf配置文件(推荐)
GRUB是一个多重操作系统启动管理器,是用来引导不同的系统的。
修改
exec command: grub2-mkconfig -o /boot/grub2/grub.cfg
reboot
确认修改成功:cat /sys/kernel/mm/transparent_hugepage/enabled
1.3.2 方法二:修改启动加载文件/etc/rc.d/rc.local
# vi /etc/rc.local
#禁用透明大页
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
#修改 io scheduler为deadline
echo deadline > /sys/block/sda/queue/scheduler
2 set Linux IO schedule algorithm to Deadline
2.1 why Linux IO sechedule
块设备最悲剧的地方就是磁盘转动,这个过程会很耗时间。
IO scheduler 的作用主要是为了减少磁盘转动的需求。
schedule主要通过2种式实现:(1)合并:每个设备都会自己对应请求队列,所有的请求在被处理之前都会在请求队列上。 在新来一个请求的时候如果发现这个请求和前面的某个请求请求的位置是相邻的,那么就可以合并为一个请求。(2)如果不能找到合并的,就会按照磁盘的转动方向进行排序。
2.2 schedule algorithms(5种)
2.2.1 Linus Elevator(现在内核已不用)
为每个设备维护一个request queue.
当来一个新请求时,(1)能合并就合并;(2)不能合并就排序;(3)如果既不能合并,又没有合适的位置插入,就放到请求队列的最后。
2.2.2 DeadLine
Deadline algorithm是对Elevator algorith的一种改进:(1)避免有些请求太长时间不能被处理(等待时间超过阈值则优先处理);(2)区分对待读操作和写操作。
deadline IO 维护3个队列。第一个队列和Elevator 一样, 尽量按照物理位置排序。 第二个队列和第三个队列都是按照时间排序,不同的是一个是读操作一个是写操作。 (读操作应比写操作有更高的优先级,因为读操作完成后才能继续,而写操作不是)
2.2.3 anticipatory
2.2.4 complete fair queruing IO
2.2.5 noop
2.3 设置Linux IO Schedule算法
# vi /etc/rc.local
#修改 io scheduler为deadline
echo deadline > /sys/block/sda/queue/scheduler
# chmod u+x /etc/rc.d/rc.local
# shutdown -r now
执行完echo命令之后,/sys/block/sda/queue/scheduler文件从noop deadline [cfq]修改为noop [deadline] cfq。
3 关闭selinux和防火墙
vi /etc/selinux/config
SELINUX=disabled
查看防火墙状态:firewall-cmd --state
停止firewall:systemctl stop firewalld.service
禁止firewall开机启动:systemctl disable firewalld.service
4 Linux资源限制
vi /etc/security/limits.conf
root soft nofile 655360 # The maximum number of open file descriptors
root hard nofile 655360
postgres soft nofile 655360
postgres hard nofile 655360
root soft nproc 655360 # The maximum number of processes available to a single user
root hard nproc 655360
postgres soft nproc 655360
postgres hard nproc 655360
* soft memlock unlimited # The maximum size that may be locked into memory
* hard memlock unlimited
* soft core unlimited # The maximum size of core files created
* hard core unlimited
* soft stack unlimited
* hard stack unlimited
5 修改Linux内核参数
vi /etc/sysctl.conf
#kernel.shmmax =
#kernel.shmall =
kernel.shmmni = 4096
#kernel.sem = 250 32000 100 128 #这个设置在很多场合都报错了,有公式计算
kernel.sem = 50100 64128000 50100 1280
fs.aio-max-nr = 1048576
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
# 禁用整个系统所有接口的IPv6
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_intvl =20
net.ipv4.tcp_retries2 = 5
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.overcommit_memory = 2
vm.overcommit_ratio = 90
vm.swappiness = 1
使用 sysctl -p 立即生效
------- postgresql安装与配置 -------
1 安装依赖包
# yum install git tree sysstat wget make cmake gcc gcc-c++ lrzsz flex bison unixODBC
# yum install smartmontools coreutils glib2 perl perl-devel perl-ExtUtils-Embed zlib zlib-devel openldap openldap-devel flex readline readline-devel systemtap systemtap-sdt-devel python python-devel tcl tcl-devel sgml-common docbook* stylesheets openjade sgml-tools libxslt libxslt-devel libxml2 libxml2-devel openssl openssl-devel pam pam-devel libicu libicu-devel uuid uuid-devel (其中pam包有问题,仅装pam-devel即可)
2 安装postgresql yum source
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
参考:https://www.postgresql.org/download/linux/redhat/
3 安装postpgresql
yum install postgresql10*
postgres安装完成后,会自动在操作系统创建一个postgres用户、在postgres数据库中创建一个名为postgres的数据库(用户为postgres)。
# cat /etc/passwd |grep -i postgres
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
# cat /etc/group |grep -i postgres
postgres:x:26:
# id postgres
uid=26(postgres) gid=26(postgres) groups=26(postgres)
4 设置环境变量
export PATH=/usr/pgsql-10/bin:$PATH
export LD_LIBRARY_PATH=/usr/pgsql-9.5/lib:$LD_LIBRARY_PATH
export PGDATA=/home/postgres/postgresql_data
4 初始化pg,并启动postgresql service
[root@tpg-1 ~]# find / -name "postgresql-10.service"
/usr/lib/systemd/system/postgresql-10.service
[root@tpg-1 ~]# /usr/pgsql-10/bin/postgresql-10-setup initdb #初始化postgresql数据库
[root@tpg-1 ~]# systemctl enable postgresql-10
[root@tpg-1 ~]# systemctl start postgresql-10
5 configure postgresql
5.1 增设密码
postgresql默认没有密码,修改为采用密码
vi /var/lib/pgsql/10/data/pg_hba.conf
原始内容
修改后(所有的method改为md5,并增加了最后一行)
5.2 允许远程连接
postgresql默认不允许远程连接,仅允许本机连接。
vi /var/lib/pgsql/10/data/postgresql.conf
原始内容
修改为
在root用户下重启:systemctl restart postgresql-10
5.3 修改postgresql数据库的密码
su - postgres
psql
postgres=# ALTER USER postgres WITH PASSWORD '密码';
postgres=# \q
------- 使用postgresql -------
1 使用pgadmin图形化界面连接使用(基于浏览器)
2 命令行操作
连接数据库:psql -U user -d dbname
切换数据库:\c dbname
查看当前数据库:select current_database();
列举数据库:\l
列出库下所有表\dt
查看表结构\d tablename
\di 查看索引
列出所有用户\du
\copyright 显示 PostgreSQL 的使用和发行条款
\encoding [字元编码名称]
显示或设定用户端字元编码
\h [名称] SQL 命令语法上的说明,用 * 显示全部命令
\prompt [文本] 名称
提示用户设定内部变数
\password [USERNAME]
securely change the password for a user
\q 退出 psql
创建数据库:create database [数据库名];
创建表:CREATE TABLE zptable(f1 INT PRIMARY KEY NOT NULL, f2 CHAR(50) NOT NULL );
*在已有的表里添加字段: alter table [表名] add column [字段名] [类型];
远程连接并登陆数据库:psql -U postgres -d zptest
psql -U username -d dbname -h hostip -p port
删除数据库:drop database [数据库名];
*重命名一个表:alter table [表名A] rename to [表名B];
*删除一个表: drop table [表名];
*删除表中的字段: alter table [表名] drop column [字段名];
*重命名一个字段: 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 [表名];--删空整个表
3 python操作postgresql
3.1数据库驱动选择
PostgreSQL至少有三个python接口程序可以实现访问,包括PsyCopg、PyPgSQL、PyGreSQL,三个接口程序各有利弊。推荐使用PsyCopg,对python开发框架的兼容性很好。
3.2 PsyCopg下载安装
官网下载psycopg2-2.5.1.tar.gz:http://initd.org/psycopg/
本文使用windows系统开发,未使用官网版本,选择psycopg2-2.4.2.win-amd64-py2.7-pg9.0.4-release.exe版,地址:http://vdisk.weibo.com/s/Cd8pPaw56Ozys
直接exe,根据提示安装即可.
3.3 python使用代码
__author__ = 'qiongmiaoer'
import psycopg2
# 数据库连接参数
conn = psycopg2.connect(database="platoon", user="postgres", password="postgres", host="192.168.10.80", port="5432")
cur = conn.cursor()
cur.execute("CREATE TABLE test(id serial PRIMARY KEY, num integer,data varchar);")
# insert one item
cur.execute("INSERT INTO test(num, data)VALUES(%s, %s)", (1, 'aaa'))
cur.execute("INSERT INTO test(num, data)VALUES(%s, %s)", (2, 'bbb'))
cur.execute("INSERT INTO test(num, data)VALUES(%s, %s)", (3, 'ccc'))
cur.execute("SELECT * FROM test;")
rows = cur.fetchall() # all rows in table
print(rows)
for i in rows:
print(i)
conn.commit()
cur.close()
conn.close()
输出结果如下:
"C:\Program Files (x86)\Python275\python.exe" E:/PycharmProjects/psycopgPyCharm/xiaoyu/temp.py
[(1, 1, 'aaa'), (2, 2, 'bbb'), (3, 3, 'ccc')]
(1, 1, 'aaa')
(2, 2, 'bbb')
(3, 3, 'ccc')
Process finished with exit code 0