PostgreSQL12安装

1
2

yum install readline-devel
yum install zlib-devel

1
2
3
4
5
6
7
8
9

tar zxvf postgresql-12.1.tar.gz
cd postgresql-12.1/
./configure --prefix=/usr/local/postgresql-12.1
将contrib下的扩展一同安装
make world -j4
成功提示：
PostgreSQL, contrib, and documentation successfully made. Ready to install.

make install-world

1
2
3
4
5

useradd postgres
groupadd postgres
mkdir /home/storage
mkdir -p /home/storage/pgsql/pgsql_data
chown -R postgres:postgres /home/storage/pgsql/

1
2
3

su - postgres
vi .bash_profile
export PATH=/usr/local/postgresql-12.1/bin/:$PATH

1
2
3
4
5
6
7
8

su - postgres

初始化默认是trust模式，超级账户的密码为空
initdb -D /home/storage/pgsql/pgsql_data

初始化的时候就设置好密码
initdb -A scram-sha-256 --pwfile /opt/pwd.txt
-A设置校验方式，–pwfile 指定密码文件，注意密码文件第一行写密码

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

配置数据库允许访问的IP，设置监听地址和端口
vi /home/storage/pgsql/pgsql_data/pg_hba.conf
追加一条记录：
host    replication     postgres        127.0.0.1/32            trust
host all all 0.0.0.0/0 md5

在配置一下监听地址和端口
vi /home/storage/pgsql/pgsql_data/postgresql.conf
listen_addresses = '*'
port = 5432

max_wal_senders = 2
wal_level = archive
archive_mode = on
archive_command = ' cp %p /home/storage/pgsql/pgsql_archive/%f'
表示端口5432，监听所有地址
然后就可以启动数据库了

1
2
3
4
5
6
7

export PGDATA=/home/storage/pgsql/pgsql_data

查看当前数据库的wal level :
pg_controldata |grep wal_level

reload配置文件postgresql.conf、pg_hba.conf
pg_ctl reload

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

su - postgres
pg_ctl -D /home/storage/pgsql/pgsql_data -l logfile start

pg_ctl -D /home/storage/pgsql/pgsql_data -l logfile stop
pg_ctl -D /home/storage/pgsql/pgsql_data -l logfile -m fast stop
pg_ctl -D /home/storage/pgsql/pgsql_data -l logfile restart

#简化
pg_ctl start #启动
pg_ctl stop #停止
pg_ctl restart #重启

1
2
3
4
5
6
7

psql

#修改密码
postgres-# ALTER USER postgres WITH PASSWORD 'xxxxxx';

#退出
postgres-# \q

1
2
3
4
5
6
7
8
9

[postgres@db ~]$ psql
psql (12.1)
Type "help" for help.

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

1

psql -h 10.10.0.21 -p 5432 -d db_app -U app

1
2
3
4
5
6
7

pg库提供了客户端的交互方式，执行/bin/psql文件，psql还提供了可以直接执行sql文件，

如果是无密码登录的，可以直接使用
./psql -d postgres -f /opt/initdb.sql

如果是密码登录，则可以使用
PGPASSWORD=密码 psql -d postgres -f /opt/initdb.sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

查看所有用户
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 qdfp      |                                                            | {}

列出现有的表空间
postgres=# \db
                      List of tablespaces
    Name    |  Owner   |               Location                
------------+----------+---------------------------------------
 pg_default | postgres | 
 pg_global  | postgres | 
 ts_app    | app     | /home/storage/pgsql/pgsql_tbs/ts_app
 
 
 查询表空间大小
 postgres=# select pg_size_pretty(pg_tablespace_size('ts_app'));
 pg_size_pretty 
----------------
 902 MB
(1 row)

查看数据库清单及对应的默认表空间
postgres=# select d.datname,p.spcname from pg_database d, pg_tablespace p where  p.oid = d.dattablespace;
  datname  |  spcname   
-----------+------------
 postgres  | pg_default
 template1 | pg_default
 template0 | pg_default
 db_app   | ts_app
 aaa       | pg_default
 db_test1  | ts_app
(6 rows)

postgres=# select d.datname,p.spcname from pg_database d, pg_tablespace p where d.datname='db_test1' and p.oid = d.dattablespace;
 datname  | spcname 
----------+---------
 db_test1 | ts_app
(1 row)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

创建用户
create user "app" WITH PASSWORD 'xxxxxx';
alter user "app" WITH PASSWORD 'xxxxxx';

mkdir -p  /home/storage/pgsql/pgsql_tbs
chown -R postgres:postgres /home/storage/pgsql/pgsql_tbs

创建表空间
mkdir -p  /home/storage/pgsql/pgsql_tbs/ts_app
chown -R postgres:postgres /home/storage/pgsql/pgsql_tbs/ts_app

create tablespace "ts_app" OWNER "app" LOCATION '/home/storage/pgsql/pgsql_tbs/ts_app';

创建数据库
create database "db_app";

为数据库指定默认表空间
alter database "db_app" set tablespace "ts_app";
--alter database "db_app" OWNER TO app;

授权
grant all on database "db_app" to app;


SELECT * FROM pg_database ORDER BY datname;