Tagged: PostgreSQL

Install PostgreSql with brew

安装brew的文章请查看:https://jalena.bcsytv.com/archives/2298

版本选择

可以在 https://formulae.brew.sh/formula/postgresql#default 这里找到你需要的PostgreSql版本。本文章使用的是postgresql@10

安装及锁定版本

# 搜索需要的程序
brew search postgresql

# 安装程序
brew install postgresql@10 

# 锁定程序版本
brew pin postgresql@10

初始化设置

# 写入环境变量
echo 'export PATH="/usr/local/opt/postgresql@10/bin:$PATH"' >> ~/.bash_profile

# 让变量立即生效
source ~/.bash_profile

# 如果不想每次输入data目录路径,可以设置数据文件环境变量
echo 'export PGDATA="/usr/local/var/postgresql@10"' >> ~/.bash_profile

启动、使用

# 启动数据库
brew services start postgresql@10

# 停止数据库
brew services stop postgresql@10

# 重启数据库
brew services restart postgresql@10

# 查看当前运行的所有服务
brew services run --all

# 进入数据库
# 这种方式默认初始化的数据库账号为当前系统用户
# 默认的数据库为 postgres
psql postgres

设置快捷命令

vim ~/.bash_profile
alias pgst='brew services start postgresql@10'
alias pgsp='brew services stop postgresql@10'
alias pgrs='brew services restart postgresql@10'
source ~/.bash_profile

现在就可以在任何地方使用pgst来启动数据库了。

psql connect docker postgresql

方式一、

docker exec -u 0 -it <CONTAINER ID> bash
su postgres
psql -U <DBUSER> -W -d <DBNAME>

# 查看PGSQL默认服务
pstree

方式二、

docker run -it --rm --network docker_odoonetwork postgres psql -h pgsql -U odoo -d analogcircuit

docker-compose.yml

version: '2'
services:
  web:
    image: odoo:12.0
    container_name: odoo
    depends_on:
      - db
    ports:
      - "8069:8069"
    networks:
      - "odoonetwork"
    volumes:
      - odoo-web-data:/var/lib/odoo
      - ./config:/etc/odoo
      - ./addons:/mnt/extra-addons
  db:
    image: postgres:10
    container_name: pgsql
    networks:
      - "odoonetwork"
    environment:
      - POSTGRES_DB=postgres
      - POSTGRES_PASSWORD=odoo
      - POSTGRES_USER=odoo
      - PGDATA=/var/lib/postgresql/data/pgdata
    volumes:
      - odoo-db-data:/var/lib/postgresql/data/pgdata
networks:
  odoonetwork:
    driver: bridge  
volumes:
  odoo-web-data:
  odoo-db-data:

PostgreSql 常用操作

PostgreSQL Client Applications

  • clusterdb — cluster a PostgreSQL database
  • createdb — create a new PostgreSQL database
  • createuser — define a new PostgreSQL user account
  • dropdb — remove a PostgreSQL database
  • dropuser — remove a PostgreSQL user account
  • ecpg — embedded SQL C preprocessor
  • pg_basebackup — take a base backup of a PostgreSQL cluster
  • pgbench — run a benchmark test on PostgreSQL
  • pg_config — retrieve information about the installed version of PostgreSQL
  • pg_dump — extract a PostgreSQL database into a script file or other archive file
  • pg_dumpall — extract a PostgreSQL database cluster into a script file
  • pg_isready — check the connection status of a PostgreSQL server
  • pg_receivewal — stream write-ahead logs from a PostgreSQL server
  • pg_recvlogical — control PostgreSQL logical decoding streams
  • pg_restore — restore a PostgreSQL database from an archive file created by pg_dump
  • psql — PostgreSQL interactive terminal
  • reindexdb — reindex a PostgreSQL database
  • vacuumdb — garbage-collect and analyze a PostgreSQL database

登陆控制台

psql -U postgres -h localhost -W -d baseName

一、用户和权限管理

# 可以使用 \h CREATE ROLE 查看语法
CREATE ROLE rolename;
CREATE USER username;

# 例子
CREATE USER jalena WITH CREATEDB LOGIN PASSWORD 'jalena';

当然还可以直接使用客户端工具直接创建。 createuser --interactive

CREATE USER和CREATE ROLE的区别在于,CREATE USER指令创建的用户默认是有登录权限的,而CREATE ROLE没有。

# 修改用户权限
ALTER USER jalena WITH NOCREATEDB; # 取消数据库创建权限
ALTER USER jalena WITH PASSWORD 'password'; # 修改密码

定义存取权限

# 授予jalena对demo库所有的权限
GRANT ALL PRIVILEGES ON DATABASE demo TO jalena;

# 只授予某个权限
GRANT UPDATE ON DATABASE demo TO jalena;
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO jalena;

# 特殊符号《ALL》代表所有权限,《PUBLIC》代表所有用户
GRANT ALL ON demo TO jalena; # 赋给用户所有全选
GRANT SELECT ON demo TO PUBLIC; # 将SELECT权限赋予所有用户

二、备份

PostgreSql提供了2个备份命令,分别为pg_dump和pg_dumpall,这种方式可以在数据库正在使用的时候进行完整一致的备份, 并不阻塞其它用户对数据库的访问。

使用说明可以使用pg_dump --help查看。

pg_dump -F c -f <filename.dmp> -C -E UTF-8 -U <DataBaseUser> <DataBaseName>
pg_dumpall -U postgres -F c -c -f all.dmp

三、恢复

CREATE USER jalena WITH CREATEDB PASSWORD 'password';
CREATE DATABASE demo OWNER jalena;
GRANT ALL PRIVILEGES ON DATABASE odam to inspur;

pg_restore -j5 -U <DBUSER> -W -d <DBNAME> -v <FILEPATH>

四、表与文件之间的拷贝

postgresql提供了COPY命令用于表与文件(标准输出、标准输入)之间的相互拷贝,COPY TO由表至文件,COPY FROM由文件至表。

# 将整张表拷贝至标准输出
copy res_users to stdout;

# 将表的部分字段拷贝至标准输出,且输出字段名称,字段间使用','分割
copy res_users(id,login) to stdout delimiter ',' csv header;

# 将查询结果拷贝至标准输出
copy(select id,login from res_users) to stdout delimiter '|' quote '"' csv header;

将标准输入拷贝至表中需要注意几点

  1. 字段间分割符默认使用【Tab】键
  2. 换行使用回车键
  3. 结束使用反斜线加英文标点(\.)
  4. 指定字段顺序,不然会出现错误的赋值
#将标准输入拷贝至表中
copy res_users(id,login) from stdin;
>> 1	login1
>> 2	login2
>> \.

# 从标准输入拷贝至表中,并将标准输入第一行作为字段名,字段分隔符为','
copy res_users(id,login) from stdin delimiter ',' csv header;
>> 1,login1
>> 2,login2
>> \.

以上是表与标准输入、输出之间的相互拷贝,表与文件的拷贝和以上完全相同,只是将标准输入、输出换成文件。需要注意的是

  1. 数据库用户必须有文件所在路径的写权限
  2. 如果表存在中文字符,导出至csv文件时需要设置编码为GBK,否则使用excel打开中文显示乱码
  3. 将文件导入表中时任然需要考虑编码问题
# 将表拷贝至csv文件中
copy res_users to 'D:/res_users.csv' delimiter ',' csv header;

# 以GBK编码拷贝到cvs文件
copy res_users to 'D:/res_users.csv' delimiter ',' csv header encoding  'GBK';
# 将文件导入至表中
copy res_users from 'd:/res_users.csv' delimiter ',' csv header encoding 'GBK';

五、PSQL常用命令

-- 查看所有psql可执行的命令
\?

-- 退出控制台
\q

-- 退出当前执行命令
q

-- 更改当前工作目录
\cd [目录]

-- 从文件中执行命令
\i 文件

-- 列出全部数据库
\l

-- 列出全部序列
\ds

-- 切换当前数据库到指定的数据库
\c [database_name]

-- 显示所有的表
\dt

-- 显示指定的表的结构
\d [table_name]

-- 显示视图
\dv

-- 列出全部用户
\du or \dg

-- 列出当前数据库和连接的信息
\conninfo

-- 修改用户密码
\password uesrname

Postgres 日志分析

最近在项目开发过程中发现客户方总是出现卡顿,数据服务器峰值出现驼峰的情况,所以需要分析数据库日志,且ODOO使用的是 Postgres  的数据库,靠眼睛去看那是分析不出啥的。

pgBadger乃是 Postgres 的一个分析工具

项目地址:https://github.com/darold/pgbadger

yum install -y pgbadger
which pgbadger
pgbadger postgresql-* -f syslog

命令执行完成后,会在当前命令执行目录生成一个out.html的文件。

Postgresql pg_dump: 因为服务器版本不匹配而终止

之前在生产环境部署的Postgresql为9.6版本,但最近在备份数据的时候发现他所使用的客户端居然是9.2的版本。。

# 查看已安装的postgresql程序
yum list installed |grep postgresql

# 查看Yum已安装的源
yum repolist

# 引入postgresql对应版本的源
# http://yum.postgresql.org/repopackages.php
rpm -Uvh https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

# 查找需要的版本
yum search postgresql --enablerepo=pgdg96

# 安装对应版本的客户端
yum install -y postgresql96

# 查看执行程序路径
find / -name pg_dump -type f

# 替换低版本
ln -snf /usr/pgsql-9.6/bin/pg_dump /usr/bin/pg_dump

备份命令

su - postgres -c "pg_dump -U odoo -Fc -d Yunshang -f Yunshang.dump"

恢复命令

createdb -U odoo Yunshang
pg_restore -v -U odoo "F:\python\YunShangZhuangShi\Yunshang.dump"

Centos7 部署 Odoo10.0 生产环境

更新

  • 经验证PostgreSQL 10在Odoo10下会引起一些运行问题,例如序号无法正常工作。2017年10月8日 已修复

准备工作

# EPEL repository
yum install -y epel-release yum-utils yum-fastestmirror python-pip python-wheel
yum upgrade python-setuptools
pip install --upgrade pip

# Update OS
yum -y update

PostgreSQL安装

# 获取安装源地址:https://yum.postgresql.org/repopackages.php#pg96
yum install -y https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
# 清理缓存
yum clean all

# 查找当前最新的版本
yum -y search postgresql96
yum list | grep postgresql96

# 安装
yum -y install postgresql96-server

# 查看安装好的服务名
systemctl -l | grep postgresql
systemctl list-unit-files | grep postgresql

# 初始化数据库
find / -name postgresql*-setup
/usr/pgsql-9.6/bin/postgresql96-setup initdb

# 设置开机启动
systemctl enable postgresql-9.6

# 启动数据库
systemctl start postgresql-9.6

# 查看服务运行情况
systemctl status postgresql-9.6

# 停止服务
systemctl stop postgresql-9.6

# 重置超级管理员密码
su - postgres -c psql
alter user postgres with password 'new password';
# 查看用户
\du
# 查看帮助
\? 
# 退出 
\q
# 开启外部访问
# FAQ:https://www.postgresql.org/docs/9.6/static/auth-pg-hba-conf.html
find / -name pg_hba.conf
echo "host    odoo    all    0.0.0.0/0    md5" >> /var/lib/pgsql/9.6/data/pg_hba.conf

# 开放防火墙postgresql服务
firewall-cmd --permanent --get-services | grep postgresql # 查看防火墙规则文件是否存在
firewall-cmd --add-service=postgresql --permanent 
# 重载防火墙
firewall-cmd --reload
# 查看是否开启
firewall-cmd --query-service postgresql
firewall-cmd --list-services
firewall-cmd --list-all

Odoo 安装

# 添加Odoo 10 源
yum-config-manager --add-repo=https://nightly.odoo.com/10.0/nightly/rpm/odoo.repo

# 安装Odoo 10
yum update && yum -y install odoo

# 生成配置文件
odoo --save --config myodoo.cfg --stop-after-init

# 设置开机启动
systemctl enable odoo

# 修改配置
vi /etc/odoo/odoo.conf

# 启动Odoo
systemctl start odoo

# 重启Odoo
systemctl restart odoo

# 停止Odoo
systemctl stop odoo

# 检查运行情况
systemctl status odoo
ps aux | grep odoo

# 查看运行日志
tail -f /var/log/odoo/odoo-server.log

# 开启防火墙
firewall-cmd --zone=public --add-port=8069/tcp --permanent
# 重载规则
firewall-cmd --reload
# 查看是否生效
firewall-cmd --list-all
Read more

Archives