CentOS/RHEL 7上PostgreSQL的安裝配置與基本使用

標籤：PostgreSQL   安裝   配置   基本使用   

安裝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 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"。

• 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-USERNAMEomicron?????????????   root?? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ???  postgres

  然後在pg_hba.conf中配置使用map:

  local?? all???????????? all??????????????????????????????????? ? ? ? ?? peer map=omicronhost??? 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;
?
執行sql指令碼
\i test.sql
?
Sequence
查詢sequence(currval(), nextval())
select nextval(‘test_sequence‘);

更新sequence
alter sequence test_sequence restart with 42;
?
退出
\q
?
協助
help
\?
\h
?

備份與恢複

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$;??

PostgreSQL

CentOS/RHEL 7上PostgreSQL的安裝配置與基本使用