CentOS 7.x 和 Ubuntu 18.04

CentOS 7.x 和 Ubuntu 18.04

PostgreSQL 所用版本为：PostgreSQL 11

1.安装存储库

For CentOS7

1
yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm

For Ubuntu 18.04

1
2
3
4
5
echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" >> /etc/apt/sources.list.d/pgdg.list

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

sudo apt-get update

2.安装客户端

For CentOS7

1
yum install postgresql11 -y

For Ubuntu 18.04

1
apt-get install postgresql-client-11 -y

3.安装服务端

For CentOS7

1
yum install postgresql11-server -y

For Ubuntu 18.04

1
apt-get install postgresql-11 -y

4.启用开机自启动

1
2
systemctl enable postgresql
systemctl start postgresql

5.修改用户密码

1
2
3
4
5
6
7
8
9
切换用户
su - postgres

登录数据库
psql -U postgres

设置postgres用户密码
   
ALTER USER postgres WITH PASSWORD 'yourpassword'

6.数据库和表的常见操作

1
2
3
4
5
6
7
8
9
10
11
退出数据库
\q  

列出数据库
\l

切换数据库
\c database_name

列出表
\dt

7.设置远程访问

1
vim /etc/postgresql/11/main/postgresql.conf

修改#listen_addresses = ‘localhost’ 为 listen_addresses=’*’

此项表示绑定本机的网卡，*是代表每个网卡都接受请求

️pg_hba.conf需要着重调试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
vim /etc/postgresql/11/main/pg_hba.conf
修改如下内容，信任本地连接，远程连接需要认证
# "local" is for Unix domain socket connections only
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer

# IPv4 local connections:
host    all         all                 127.0.0.1/32           md5
host    all             all             0.0.0.0/0              md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     all                                     peer
#host    replication     all             127.0.0.1/32            md5
#host    replication     all             ::1/128                 md5
#host     all            all             0.0.0.0/0               md5

8.重启服务

1
systemctl restart postgresql

9.完全删除已安装的Postgres

1
2
3
4
5
6
7
8
9
sudo apt-get remove postgresql
sudo apt-get --purge remove postgresql

sudo rm -rf /var/lib/postgresql/
sudo rm -rf /var/log/postgresql/
sudo rm -rf /etc/postgresql/
用以下命令查看还有哪些包
dpkg -l | grep postgres
sudo apt-get --purge remove package_name

10.postgre配置调优

1
2
3
4
5
6
7
8
postgres.conf
shared_buffers         #建议的设置值为机器总内存大小的25％
max_connections        #最大连接数
effective_cache_size   #按默认值
maintenance_work_mem   #按默认值，一般不超过1G
wal_buffers            # PostgreSQL将其WAL（预写日志）记录写入缓冲区，然后将这些缓冲区刷新到磁盘。由wal_buffers定义的缓冲区的默认大小为16MB，但如果有大量并发连接的话，则设置为一个较高的值可以提供更好的性能。
work_mem               #按默认值
synchronous_commit     #强制提交写入磁盘，如果关闭，断电可能丢失数据