本文共 3304 字,大约阅读时间需要 11 分钟。
安装PostgreSQL 9.6为例:
Install the repository RPM
yum installInstall the client packages
yum install postgresql96Install the server packages
yum install postgresql96-serverInitialize the database and enable automatic start
/usr/pgsql-9.6/bin/postgresql96-setup initdbsystemctl enable postgresql-9.6systemctl 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 onlylocal all all trust# IPv4 local connections:host all all 127.0.0.1/32 identhost 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"。
# MAPNAME SYSTEM-USERNAME PG-USERNAMEomicron root postgres
然后在pg_hba.conf中配置使用map:
local all all peer map=omicronhost all all 127.0.0.1/32 ident map=omicron
连接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; 创建SCHEMACREATE SCHEMA test;ALTER SCHEMA test OWNER TO test; 查看SCHEMA\dn 设置Search PathSET 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 commandspg_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$;
转载于:https://blog.51cto.com/7308310/2086943