安装PostgreSQL 9.6为例:
安装
Install the repository RPM yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
Install the client packages yum install postgresql96
Install the server packages yum install postgresql96-server
Initialize the database and enable automatic start /usr/pgsql-9.6/bin/postgresql96-setup initdb systemctl enable postgresql-9.6 systemctl start postgresql-9.6
配置
编辑/var/lib/pgsql/9.6/data/postgresql.conf,修改listen_addresses,监听所有地址:
listen_addresses = '*'
编辑/var/lib/pgsql/9.6/data/pg_hba.conf,修改认证方式:
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 ident
host all all 0.0.0.0/0 md5
重启PostgreSQL systemctl restart postgresql-9.6
认证方式
认证方式支持"trust", "reject", "md5", "password", "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" , "cert"。
- trust 任何人都可以访问数据库,需要指定数据库用户名。如上,本地可以使用psql -U postgres连接数据库(当未指定数据库用户名时,默认为root)。
- password 密码认证,发送明文密码
- md5 密码认证,发送经MD5加密的密码,假如数据库服务器IP是10.188.13.29,则可以这样访问:psql -h 10.188.13.29 -U postgres,回车后会提示输入密码。
- ident 从ident server获取客户端操作系统的用户名,当与数据库用户名匹配时则可访问。当ident配置在local连接时,将使用peer替代。存在安全隐患,仅适用于封闭网络,不建议使用。
- peer 从kernel获取客户端操作系统的用户名,当与数据库用户名匹配时则可访问,仅用于local连接。如local配置为peer时,可以这样访问psql -U postgres 当操作系统用户名与数据库用户名不一致时可以在文件pg_ident.conf中配置map关系,如下:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
omicron root postgres
然后在pg_hba.conf中配置使用map:
local all all peer map=omicron
host all all 127.0.0.1/32 ident map=omicron
PSQL
连接PostgreSQL psql -U postgres
更多参数可以查看帮助psql --help 刷新配置 修改配置文件后,可执行以下命令刷新配置: select pg_reload_conf(); 更改密码 ALTER USER postgres WITH PASSWORD 'postgres'; 查看用户 select * from pg_shadow; 查看data文件夹所在目录 show data_directory; 创建用户 CREATE USER test WITH PASSWORD 'test'; ALTER USER test WITH SUPERUSER; 创建SCHEMA CREATE SCHEMA test; ALTER SCHEMA test OWNER TO test; 查看SCHEMA \dn 设置Search Path SET search_path TO test;
查看Table \dt
查看Sequence \ds
查看View \dv
查看Table Sequence View \d 执行sql脚本 \i test.sql Sequence 查询sequence(currval(), nextval()) select nextval('test_sequence');
更新sequence alter sequence test_sequence restart with 42; 退出 \q 帮助 help ? for help with psql commands \h for help with SQL commands
备份与恢复
pg_dump -h host1 -U postgres [-n schema] dbname > outfile psql -U postgres dbname < infile 也可直接备份data目录 tar -cf backup.tar /usr/local/pgsql/data
存储过程
清空所有表数据的一个小存储过程(schema名称为test):
-- FUNCTION: test.truncatealltable()
-- DROP FUNCTION test.truncatealltable();
CREATE OR REPLACE FUNCTION test.truncatealltable()
RETURNS text
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
cur_all_tables CURSOR FOR
select relname from pg_class
where relnamespace = (select oid from pg_namespace where nspname = 'test')
and relkind = 'r' order by relname;
truncate_sql CHARACTER VARYING(100);
BEGIN
FOR record IN cur_all_tables
LOOP
truncate_sql := concat('truncate table test.', record.relname, ' cascade');
EXECUTE truncate_sql;
END LOOP;
return 'success';
END
$BODY$;