postgresql 开启大页_Postgresql安装与基本使用

环境：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