PostgreSQL安装
一.yum安装
1.安装yum源
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
2.安装客户端工具
yum install postgresql10
3.服务端组件
yum install postgresql10-server
4.初始化数据库和开机启动
/usr/pgsql-10/bin/postgresql-10-setup initdb
systemctl enable postgresql-10
systemctl start postgresql-10
[root@slave01 bin]# /usr/pgsql-10/bin/postgresql-10-setup initdb
Initializing database ... OK
https://www.postgresql.org/docs/10/static/tutorial-start.html
5.查看版本信息
[root@slave01 10]# passwd postgres
[root@slave01 10]# su - postgres
Last login: Tue Mar 20 13:59:31 CST 2018 on pts/2
-bash-4.2$ psql -U postgres
psql (10.3)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)
postgres=#
6.更改密码
postgres=# alter user postgres with password 'postgres'
7.配置监听地址
cd /var/lib/pgsql/10/data
vi postgresql.conf
listen_addresses = '*'
8.配置信任
vi /var/lib/pgsql/10/data/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 172.16.0.0/16 trust
host all all 10.2.0.0/16 trust
9.linux用户下创建数据库
-bash-4.2$ createdb testdb
-bash-4.2$
10.重启pg服务
systemctl stop postgresql-10.service
systemctl start postgresql-10.service
可以通过客户端进行连接管理了
http://blog.csdn.net/mbshqqb/article/details/78622167?locationNum=8&fps=1
11.创建用户和授权
postgres=# CREATE USER <user> WITH PASSWORD '<password>';
# 这里的用户是role的概念，代表了一堆角色集合体，可用于登录，schema属于role之下，可在role下创建表、数据对象，但默认存在public schema下
postgres=# CREATE DATABASE test OWNER <user> ENCODING 'UTF8';
pdb1=> create schema mysql authorization postgres1;
CREATE SCHEMA
# 这里的schema相当于库，比如mysql库名，数据对象表、视图、函数等依赖于此
# 必须是已存在的role，如果不指定role，则在当前库下创建
# role登录后可看到所有的schema
# 给一个库下的所有schema授权
grant all privileges on database pdb1 to role1;
pdb1=> select * from mysql.t1;
ERROR: permission denied for relation t1
https://www.server110.com/postgresql/201403/7619.html
# 1.创建角色
create role mydb_select LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE encrypted password 'mydb_select';
# 2.授权
grant connect on database pdb1 to mydb_select;
grant usage on schema mysql to mydb_select;
grant select on all tables in schema mysql to mydb_select;
# 上面命令给帐号 mydb_select 开通了数据库 mydb 的只读权限，可以访问mydb 库中 mydb schema 下的所有表。
https://www.postgresql.org/docs/9.3/sql-alterrole.html
12.创建表空间
pdb1=> create tablespace tabspace_mysql owner postgres1 location '/tmp';
ERROR: permission denied to create tablespace "tabspace_mysql"
HINT: Must be superuser to create a tablespace.
pdb1=> \c - postgres
You are now connected to database "pdb1" as user "postgres".
pdb1=# create tablespace tabspace_mysql owner postgres1 location '/data/pgsql_data/mysql';
ERROR: directory "/data/pgsql_data/mysql" does not exist
pdb1=# create tablespace tabspace_mysql owner postgres1 location '/data/pgsql_data/';
CREATE TABLESPACE
# location可以是任意
# 创建后会产生一个软连接
[root@mysql56 pg_tblspc]# ls -ltr
total 0
lrwxrwxrwx 1 postgres postgres 26 Nov 22 17:26 16398 -> /data/pgsql_data/cust_data
[root@mysql56 pg_tblspc]# ls -l /data/pgsql_data/cust_data
total 4
drwx------ 2 postgres postgres 4096 Nov 22 17:26 PG_9.4_201409291
13.创建表
pdb1=# create table t1(c1 int) tablespace tb1;
CREATE TABLE
[root@mysql56 16385]# pwd
/data/pgsql_data/pg_tblspc/16398/PG_9.4_201409291/16385
[root@mysql56 16385]# ls -ltr
total 0
-rw------- 1 postgres postgres 0 Nov 22 17:29 16399
pdb1=# select oid,datname from pg_database where datname='pdb1';
oid | datname
-------+---------
16385 | pdb1
(1 row)
pdb1=# select relname,relfilenode from pg_class where relname='t1';
relname | relfilenode
---------+-------------
t1 | 16399
(1 row)
# /data/pgsql_data/pg_tblspc/16398/PG_9.4_201409291/16385/16399
# 文件16399对应表的relfilenode，物理文件
# 目录16385对应的是数据库pdb1的oid，物理文件夹
# 一个物理表对应一个文件
15.目录
- 软件目录：/usr/pgsql-10/bin
- 数据目录：/var/lib/pgsql/10/data
二.源码安装
1.下载解压
wget https://ftp.postgresql.org/pub/source/v9.4.7/postgresql-9.4.7.tar.gz
tar -zxvf postgresql-9.4.7.tar.gz
./configure --prefix=/usr/local/postgresql-9.4.7
make && make install
ln -s /usr/local/postgresql-9.4.7 /usr/local/pgsql
useradd postgres
chown -R postgres:postgres /usr/local/postgresql-9.4.7
2.创建数据库目录
mkdir -p /data/pgsql_data /data/pgsql_log
chown -R postgres:postgres /data/pgsql_data
3.初始化数据库
su - postgres
cd /usr/local/postgresql/bin
./initdb -D /data/pgsql_data
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
./postgres -D /data/pgsql_data
or
./pg_ctl -D /data/pgsql_data -l logfile start
4.配置监听地址
vi /data/pgsql_data/postgresql.conf
listen_addresses = '*'
5.配置信任
vi /data/pgsql_data/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 172.16.0.0/16 trust
host all all 10.2.0.0/16 trust
6.复制启动
cp /root/postgresql-9.4.7/contrib/start-scripts/linux /etc/init.d/pgsqld
chmod 755 /etc/init.d/pgsqld
chown -R postgres /data/pgsql*
service pgsqld start
#m 默认目录是/usr/local/pgsql 需要改启动脚本
chkconfig pgsqld on
7.指定目录启动连接
pg_ctl -D /data/pgsql_data -l /data/pgsql_logfile start
psql
8.更改密码
postgres=# alter user postgres with password 'postgres'
9.停止数据库
pg_ctl stop
10.命令
[postgres@mycat01 ~]$ psql -U postgres -w postgres -h 127.0.0.1 -p 5432
psql (9.4.7)
Type "help" for help.
postgres=#
\help
\password
\l
\q
11.切换数据库、用户
- 连接数据
psql - 切换数据库
[postgres@mysql56 ~]$ psql
psql (9.4.7)
Type "help" for help.
postgres=# \c pdb1
You are now connected to database "pdb1" as user "postgres".
pdb1=# \c - postgres1
You are now connected to database "pdb1" as user "postgres1".
postgres=> \c pdb1 postgres1
You are now connected to database "pdb1" as user "postgres1".
20.客户端工具
https://www.postgresql.org/ftp/pgadmin/pgadmin4/v3.2/windows/
