PostgreSQL学习笔记1--PostgreSQL安装

目录

环境准备

1
2
[fan@MiWiFi-R1CM-srv ~]$ cat /etc/redhat-release 
CentOS Linux release 7.3.1611 (Core)

下载安装包

https://www.postgresql.org/ftp/source/

1
wget https://ftp.postgresql.org/pub/source/v9.6.2/postgresql-9.6.2.tar.gz

安装PostgreSQL

安装依赖包

1
yum -y install coreutils glib2 lrzsz mpstat dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make smartmontools flex bison perl-devel perl-ExtUtils* openldap-devel jadetex  openjade bzip2 

编译安装PostgreSQL

1
2
3
4
5
tar xf postgresql-9.6.2.tar.gz 
cd postgresql-9.6.2/
./configure --prefix=/home/fan/pgsql9.6
make world -j 8 
make install-world 

执行world -j 8的时候看到如下内容,则表示成功:

1
2
3
make[2]: Leaving directory `/home/fan/postgresql-9.6.2/contrib/pgcrypto'
make[1]: Leaving directory `/home/fan/postgresql-9.6.2/contrib'
PostgreSQL, contrib, and documentation successfully made. Ready to install.

执行make install-wold的时候看到如下内容,表示成功:

1
2
3
4
5
6
7
8
9
/usr/bin/install -c -m 644 ./unaccent.rules '/home/fan/pgsql9.6/share/tsearch_data/'
make[2]: Leaving directory `/home/fan/postgresql-9.6.2/contrib/unaccent'
make -C vacuumlo install
make[2]: Entering directory `/home/fan/postgresql-9.6.2/contrib/vacuumlo'
/usr/bin/mkdir -p '/home/fan/pgsql9.6/bin'
/usr/bin/install -c  vacuumlo '/home/fan/pgsql9.6/bin'
make[2]: Leaving directory `/home/fan/postgresql-9.6.2/contrib/vacuumlo'
make[1]: Leaving directory `/home/fan/postgresql-9.6.2/contrib'
PostgreSQL, contrib, and documentation installation complete

配置linux用户环境变量

将如下内容追加到~/.bash_profile

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
export PS1="$USER@`/bin/hostname -s`-> "  
export PGPORT=1921  
export PGDATA=/home/fan/pgdata  
export LANG=en_US.utf8  
export PGHOME=/home/digoal/pgsql9.6  
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH  
export PATH=$PGHOME/bin:$PATH:.  
export DATE=`date +"%Y%m%d%H%M"`  
export MANPATH=$PGHOME/share/man:$MANPATH  
export PGHOST=$PGDATA  
export PGUSER=postgres  
export PGDATABASE=postgres  
alias rm='rm -i'  
alias ll='ls -lh'  
unalias vi  

执行source ~/.bash_profile使配置生效

初始化数据库集群

命令: initdb -D $PGDATA -E UTF8 --locale=C -U postgres

 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
fan@MiWiFi-R1CM-srv-> initdb -D $PGDATA -E UTF8 --locale=C -U postgres 
The files belonging to this database system will be owned by user "fan".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /home/fan/pgdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

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:

    pg_ctl -D /home/fan/pgdata -l logfile start

fan@MiWiFi-R1CM-srv-> 

配置数据库

配置文件在$PGDATA目录中

配置postgresql.conf

将如下内容追加到postgresql.conf中

 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
43
44
45
46
47
48
49
50
51
52
listen_addresses = '0.0.0.0'  
port = 1921  
max_connections = 200  
unix_socket_directories = '.'  
tcp_keepalives_idle = 60  
tcp_keepalives_interval = 10  
tcp_keepalives_count = 10  
shared_buffers = 512MB  
dynamic_shared_memory_type = posix  
vacuum_cost_delay = 0  
bgwriter_delay = 10ms  
bgwriter_lru_maxpages = 1000  
bgwriter_lru_multiplier = 10.0  
bgwriter_flush_after = 0   
old_snapshot_threshold = -1  
backend_flush_after = 0   
wal_level = replica  
synchronous_commit = off  
full_page_writes = on  
wal_buffers = 16MB  
wal_writer_delay = 10ms  
wal_writer_flush_after = 0   
checkpoint_timeout = 30min   
max_wal_size = 2GB  
min_wal_size = 128MB  
checkpoint_completion_target = 0.05    
checkpoint_flush_after = 0    
random_page_cost = 1.3   
log_destination = 'csvlog'  
logging_collector = on  
log_truncate_on_rotation = on  
log_checkpoints = on  
log_connections = on  
log_disconnections = on  
log_error_verbosity = verbose  
autovacuum = on  
log_autovacuum_min_duration = 0  
autovacuum_naptime = 20s  
autovacuum_vacuum_scale_factor = 0.05  
autovacuum_freeze_max_age = 1500000000  
autovacuum_multixact_freeze_max_age = 1600000000  
autovacuum_vacuum_cost_delay = 0  
vacuum_freeze_table_age = 1400000000  
vacuum_multixact_freeze_table_age = 1500000000  
datestyle = 'iso, mdy'  
timezone = 'PRC'  
lc_messages = 'C'  
lc_monetary = 'C'  
lc_numeric = 'C'  
lc_time = 'C'  
default_text_search_config = 'pg_catalog.english'  
shared_preload_libraries='pg_stat_statements'  

配置pg_hba.conf

将如下内容追加到pg_hgb.conf中

1
host all all 0.0.0.0/0 md5  

启动数据库集群

命令:pg_ctl start

1
2
3
4
5
6
7
8
fan@MiWiFi-R1CM-srv-> pg_ctl start 
server starting
fan@MiWiFi-R1CM-srv-> LOG:  00000: redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".
LOCATION:  SysLogger_Start, syslogger.c:622

fan@MiWiFi-R1CM-srv-> 

连接数据库

1
2
3
4
5
fan@MiWiFi-R1CM-srv-> psql
psql (9.6.2)
Type "help" for help.

postgres=#

简单使用

查看psql帮助

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
postgres=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \errverbose            show most recent error message at maximum verbosity
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \q                     quit psql
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \watch [SEC]           execute query every SEC seconds
......

使用psql 命令行连接数据库

psql -h IP地址 -p端口 -U 用户名 数据库名

1
2
3
4
5
fan@MiWiFi-R1CM-srv-> psql -h 127.0.0.1 -p 1921 -U postgres postgres
psql (9.6.2)
Type "help" for help.

postgres=# 

新增用户

新建用户属于数据库操作,先使用psql和超级用户postgres连接到数据库。

新增一个普通用户

1
2
postgres=# create role fan login encrypted password '123456';
CREATE ROLE

新增一个超级用户

1
2
postgres=# create role dba_fan login superuser encrypted password '123456';
CREATE ROLE

新增一个流复制用户

1
2
postgres=# create role fan_rep replication login encrypted password '123456';
CREATE ROLE

将一个用户在不同角色之间切换

例如将fan设置为超级用户

1
2
ostgres=# alter role fan superuser;
ALTER ROLE

查看已有用户

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description 
-----------+------------------------------------------------------------+-----------+-------------
 dba_fan   | Superuser                                                  | {}        | 
 fan       | Superuser                                                  | {}        | 
 fan_rep   | Replication                                                | {}        | 
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 

postgres=# 

查看当前配置

show 参数名

1
2
3
4
5
6
postgres=# show client_encoding;
 client_encoding 
-----------------
 UTF8
(1 row)

查看pg_settings

1
postgres=# select * from pg_settings;