PostgreSQL 使用帮助

Windows 下的安装步骤

  1. 启动安装，修改默认安装目录，例如改为c:/pgsql
  2. 增加一个普通用户，如postgres， 密码123456，设为永不过期。
  3. 系统环境变量的PATH增加c:/pgsql/bin 和 c:/pgsql/lib
  4. 进入c:/pgsql。输入命令 initdb -E UTF-8 -D D:\pgsql\data --locale=Chinese。
  5. 输入命令：pg_ctl register -N postgres -D c:/pgsql/data -U postgres -P 123456
  6. 启动服务 pg_ctl -D "D:/pgsql/data" -l logfile start
  7. 启动控制面板/管理工具/服务，可以看到名为postgres的服务。将其启动类型改 为手动，在属性/登录标签下输入密码和确认密码123456。
  8. 以postgres身份输入命令psql可进入客户端。

其他相关命令

卸载后台服务: 
 pg_ctl unregister -N postgres  卸载服务
 pg_ctl start   [-w] [-D 数据目录] [-s] [-l 文件名] [-o "选项"]
 pg_ctl stop    [-W] [-D 数据目录] [-s] [-m 关闭模式]
 pg_ctl restart [-w] [-D 数据目录] [-s] [-m 关闭模式] [-o "选项"]
 pg_ctl reload  [-D 数据目录] [-s]
 pg_ctl status  [-D 数据目录]
 pg_ctl kill    信号名称 进程号
 pg_ctl register   [-N 服务名称] [-U 用户名] [-P 口令] [-D 数据目录]
                   [-w] [-o "选项"]
 pg_ctl unregister [-N 服务名称]

备份所有数据

导出SQL 
pg_dumpall > db.sql

pg_dump dbname | gzip > filename.gz           '压缩保存

psql -f pg.sql
-W 强制给出口令提示。如果服务器要求口令认证，那么这个应该自动发生。 可以配置pg_hbd.conf 设置本地不使用密码登录，这样更加方便 备份：pg_dump -h 127.0.0.1 -p 5432 -U tradesns -W -F c -b -v -f "/home/tradeworkwangbin/us2010.backup" us2010 恢复：pg_restore -h 127.0.0.1 -p 5432 -U postgres -W -d us2011 -v "/root/us2010.backup"

备份所有一个数据库

$ pg_dump mydb > db.out 

$ pg_dump -Ft -b mydb > db.tar 

$BinDir/pg_dumpall -h 127.0.0.1 -U postgres | gzip> $backDateFile
out 类型文件 pg_restore.exe --host localhost --port 5432 --username postgres --dbname esa --verbose "E:\Dl\esa.backup" SQL 导入数据库 
  psql -f db.sql postgres

  psql -f db.sql

主从复制方式

PostgreSQL 数据库扩展方式主要有三种： 1。主从复制方式 PostgreSQL和Mysql的Master-Slave复制原理一样，一个服务器充当主服务器,而一个或多个其它服务器充当从服务器；主服务器处理读写请求，从服务器只处理读请求。 主从复制

Linux下主从配置

环境配置 修改 /etc/sysconfig/i18n 文件 
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"

改为:

LANG="zh_CN.UTF-8"
SUPPORTED="zh_CN.UTF-8:zh_CN:zh"
SYSFONT="latarcyrheb-sun16"
先安装依赖库 
yum install readline-devel
yum install zlib-devel

一．安装Postgresql9.x

  1. 下载Postgresql9.x下载源码
  2. 在两个数据库服务器上执行以下操作安装Postgresql9.x
设置 /database/pgsql 为安装路径 
useradd postgres   //创建postgres用户

passwd postgres    //设置用户密码

mkdir /database/pgsql   **创建数据文件目录**

tar -zxvf  postgresql-9.x.tar.gz

cd postgresql-9.x

./configure --enable-nls=zh_CN --prefix=/database/pgsql

make

make install

./initdb -E UTF-8 -D /database/pgsql/data
参考 https://www.cnblogs.com/d-homme/p/9357013.html /usr/local/postgresql/bin/initdb -E UTF-8 -D /usr/local/postgresql/data

二．权限分配

chown -R postgres:postgres /database/pgsql
chown postgres:postgres /etc/init.d/postgresql
chmod a+x /etc/init.d/postgresql
chmod 700 /database/pgsql/data

chown -R postgres:postgres /database/pgsql/bin/*.sh

三.设置启动

PostgreSQL的开机自启动脚本位于PostgreSQL源码目录的contrib/start-scripts路径下的linux 
cp linux /etc/init.d/postgresql  

chkconfig --add postgresql

chmod a+x postgresql
启动PostgreSQL服务 
service postgresql start
通过下面的命令可以查看系统中运行的postgres进程 
ps -ef | grep postgres

四.主从配置

先创修改密码，和建用户备份用户 
psql –p 5432 –U postgres –h 127.0.0.1

alter user postgres with password '密码'; 

create user repl superuser password '密码'
修改postgresql.conf文件 
vi /database/pgsql/postgresql.conf

listen_addresses = '*'

wal_level = hot_standby

synchronous_commit = on

max_wal_senders = 2

wal_keep_segments = 32

synchronous_standby_names = '*'
修改pg_hba.conf文件 vi /database/pgsql/pg_hba.conf 
local   all             postgres                                trust
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5
两边都安装好后开始同步data目录 
select pg_start_backup('/database/pgsql/data');

scp -r /database/pgsql/data/* postgres@对方ip地址:/database/pgsql/data/.

select pg_stop_backup();

五.同步后修改目录权限

修改Slave数据库postgresl.conf 
synchronous_commit = off

max_wal_senders = 0

wal_keep_segments = 0

synchronous_standby_names = ''

hot_standby = on
编辑recovery.conf文件 
cp /database/pgsql/share/recovery.conf.sample /database/pgsql/recovery.conf

vi  /data/pgsql9.1/ recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=主数据库ip port=5432 user=repl password=密码'
trigger_file = '/database/pgsql/master.trigger'
启动Slave数据库 如何知道主备是否一致 
主机：
select pg_current_xlog_location();
备机：
select pg_last_xlog_receive_location();
如果这俩相差不大，基本可以判断数据已同步了

6.建立快捷方式

ln -s /database/pgsql/bin/psql /sbin/psql

psql常用命令

  1. 列出所有的数据库 mysql: show databases psql: \l或\list
  2. 切换数据库 mysql: use dbname psql: \c dbname
  3. 列出当前数据库下的数据表 mysql: show tables psql: \d
  4. 列出指定表的所有字段 mysql: show columns from table name psql: \d tablename
  5. 查看指定表的基本情况 mysql: describe tablename psql: \d+ tablename
  6. 退出登录 mysql: quit 或者\q psql:\q

psql密码提示

虽然配置pg_hba.conf 文件后可以无密码的登陆本地pgsql了，但如果要登陆pgbouncer.md 或其他应用还是可能会出现密码提示， 下边是自动填写密码方法,通过在客户端 /home/postgres 目录下创建隐藏文件 .pgpass ，从而避免连接数据库时弹出密码输入提示。 
--创建密码文件 .pgpass ( on 客户端 )                               
vi /home/postgres/.pgpass       

--格式                                                               
hostname:port:database:username:password                             

--范例                                                               
192.168.1.25:6432:Mydb:postgres:mypassword                                   

--权限                                                               
chown -R postgres:postgres /home/postgres/
chmod 600 .pgpass
如果出现启动时目录无权限切换 chmod +r+x /home

开启9.4 slot特性

在主库上创建 slot 
postgres=# select * from pg_create_physical_replication_slot('slot1');
从库配置 recovery.conf primary_slotname = 'slot1' 重启后,主库上验证 postgres=# select * from pgreplicationslots ; active 表示是否使用