postgresql官方下载: https://www.postgresql.org/download/

1. for ubuntu

https://www.postgresql.org/download/linux/ubuntu/

创建postgresql仓库文件/etc/apt/sources.list.d/pgdg.list, 以添加以下内容:

for 18.04:

deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

for 16.04:

deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main

for 14.04:

deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

保存退出.

导入仓库签名文件并更新仓库文件列表:

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

安装postgresql:

sudo apt-get install postgresql-10

安装完成后会打印如下信息:

Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log

可知:

  • 数据文件目录: /var/lib/postgresql/10/main
  • 日志文件: /var/log/postgresql/postgresql-10-main.log
  • 拥有者是postgres用户

安装完成后,默认会:

  • 创建名为postgres的Linux用户

  • 创建名为postgres, 不带密码的默认数据库账号作为数据库管理员

  • 创建名为postgres的表

PostgreSQL客户端为psql.

查看版本号:

$ psql --version
psql (PostgreSQL) 10.7 (Ubuntu 10.7-1.pgdg16.04+1)

创建名为op用户, 并可指明是否为超级管理员:

$ sudo -u postgres createuser --interactive
Enter name of role to add: op
Shall the new role be a superuser? (y/n) y
$ 

与下等同:

sudo -u postgres createuser --superuser op

修改用户op的密码:

$ sudo -u postgres psql
psql (10.7 (Ubuntu 10.7-1.pgdg16.04+1))
Type "help" for help.

postgres=# \password op;
Enter new password: 
Enter it again: 
postgres=# \q

创建用户”op”并设置密码123456:

$ sudo -u postgres psql
postgres=# create user op with password '123456';

创建数据库, 名为opplatform:

sudo -u postgres createdb opplatform

创建数据库并指定所有者, 其中数据库名为opplatform, 数据库所有者为op:

sudo -u postgres createdb -O op opplatform;

创建数据库opplatform,所有者为op:

$ sudo -u postgres psql
postgres=# create database opplatform owner op;

连接postgresql的shell:

$ sudo -u postgres psql
psql (10.7 (Ubuntu 10.7-1.pgdg16.04+1))
Type "help" for help.

postgres=#

查看所有数据库及其拥有者:

$ sudo -u postgres psql
psql (10.7 (Ubuntu 10.7-1.pgdg16.04+1))
Type "help" for help.

postgres=# \l

List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 opplatform | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
(4 rows)

修改数据库opplatform的拥有者为op:

postgres=# alter database opplatform owner to op;
ALTER DATABASE
postgres=# 

退出postgresql的shell:

postgres=# \q

奖数据库opplatform的所有权授予给用户op:

postgres=# grant all privileges on database opplatform to op;
GRANT
postgres=# 

用户op连接数据库opplatform, 报错:

$ psql -d opplatform -U op -W
Password for user op: 
psql: FATAL:  Peer authentication failed for user "op"

解决方法:

$ sudo vim /etc/postgresql/10/main/pg_hba.conf

将以下内容:

# "local" is for Unix domain socket connections only
local   all             all                                     peer

修改为:

# "local" is for Unix domain socket connections only
local   all             all                                     trust

详见: https://www.postgresql.org/docs/11/auth-pg-hba-conf.html

$ sudo /etc/init.d/postgresql reload
[ ok ] Reloading postgresql configuration (via systemctl): postgresql.service.
$ sudo systemctl enable postgresql
$ sudo systemctl start postgresql
$ sudo systemctl stop postgresql
$ sudo systemctl restart postgresql

重新执行, 如下, 成功连接:

$ psql -d opplatform -U op -W
Password for user op: 
psql (10.7 (Ubuntu 10.7-1.pgdg16.04+1))
Type "help" for help.

opplatform=# 

postgresql远程授权:

sudo vim /etc/postgresql/10/main/pg_hba.conf

追加以下内容:

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

其中,address0.0.0.0/0, auth-methodpassword

还要修改配置文件:

sudo vim /etc/postgresql/10/main/postgresql.conf

listen_addresses = 'localhost'修改为listen_addresses = '*'

重启postgresql服务生效:

sudo systemctl restart postgresql

测试是否能远程连接:
如: 通过navicat premium创建postgresql连接, 填写主机,商品, 初始数据库, 用户名, 密码, 点击”测试连接”, 即可显示”连接成功”。

2. for centos

未完, 待续…