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 mainfor 16.04:
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg mainfor 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其中，address为0.0.0.0/0, auth-method为password
还要修改配置文件:
sudo vim /etc/postgresql/10/main/postgresql.conf将listen_addresses = 'localhost'修改为listen_addresses = '*'
重启postgresql服务生效:
sudo systemctl restart postgresql测试是否能远程连接:
如： 通过navicat premium创建postgresql连接， 填写主机，商品， 初始数据库， 用户名， 密码， 点击”测试连接”， 即可显示”连接成功”。
2. for centos
未完， 待续…