常用软件在 Mac 上的安装和使用

数据库图形工具

mysql 图形界面管理工具:sequel-pro、mysqlworkbench:

brew cask install sequel-pro mysqlworkbench

通用的数据库图形界面工具:navicat-premium

brew cask install navicat-premium

Getting Started with PostgreSQL on Mac OSX 列出了几个 postgres 的图形界面管理工具:

  • Postico
  • pgAdmin

postgres

PostgreSQL 好像越来越流行了。

推荐:

以前的笔记:

安装 postgres

在 Mac 上用 brew 安装:

$ brew search postgres
[email protected]   [email protected]   [email protected]    [email protected]   [email protected]

$ brew install [email protected]

安装完成后显示操作提示:

To migrate existing data from a previous major version of PostgreSQL run:
  brew postgresql-upgrade-database

[email protected] is keg-only, which means it was not symlinked into /usr/local,
because this is an alternate version of another formula.

If you need to have [email protected] first in your PATH run:
  echo 'export PATH="/usr/local/opt/[email protected]/bin:$PATH"' >> ~/.zshrc

For compilers to find [email protected] you may need to set:
  export LDFLAGS="-L/usr/local/opt/[email protected]/lib"
  export CPPFLAGS="-I/usr/local/opt/[email protected]/include"

For pkg-config to find [email protected] you may need to set:
  export PKG_CONFIG_PATH="/usr/local/opt/[email protected]/lib/pkgconfig"


To have launchd start [email protected] now and restart at login:
  brew services start [email protected]
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/[email protected] start

设置环境变量:

echo 'export PATH="/usr/local/opt/[email protected]/bin:$PATH"' >> ~/.zshrc

验证版本:

$ postgres -V
postgres (PostgreSQL) 11.6

命令行工具的单独安装

如果只是要从本地访问 postgres,可以只安装命令行工具:

$ brew install pgcli
...
If you need to have libpq first in your PATH run:
  echo 'export PATH="/usr/local/opt/libpq/bin:$PATH"' >> ~/.zshrc

For compilers to find libpq you may need to set:
  export LDFLAGS="-L/usr/local/opt/libpq/lib"
  export CPPFLAGS="-I/usr/local/opt/libpq/include"

For pkg-config to find libpq you may need to set:
  export PKG_CONFIG_PATH="/usr/local/opt/libpq/lib/pkgconfig"

启动 postgres

启动 postgres:

$ brew services start [email protected]
==> Successfully started `[email protected]` (label: [email protected])

查看状态:

$ brew services list |grep postgres
[email protected] started lijiao /Users/lijiao/Library/LaunchAgents/[email protected]

默认数据库文件路径:

$ ls /usr/local/var/[email protected]
PG_VERSION           pg_ident.conf        pg_snapshots         pg_wal
base                 pg_logical           pg_stat              pg_xact
global               pg_multixact         pg_stat_tmp          postgresql.auto.conf
pg_commit_ts         pg_notify            pg_subtrans          postgresql.conf
pg_dynshmem          pg_replslot          pg_tblspc            postmaster.opts
pg_hba.conf          pg_serial            pg_twophase          postmaster.pid

第一次登陆

本地登陆 postgres:

$ psql postgres
psql (11.6)
Type "help" for help.

postgres=#

默认创建的 role(用户):

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 lijiao    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

所在的系统的当前用户会被自动创建为 postgres 的超级用户,所以在本地可以直接用 psql postgres 登陆。

创建其它用户

创建一个新用户:

create user postgresdemo with password 'password123';

在本地用新用户登陆(注意指定 -h 127.0.0.1 -p 5432):

$ psql -h 127.0.0.1 -p 5432 -U postgresdemo
Password:
psql (11.6)
Type "help" for help.

postgres=>

本地登陆时,可能无需密码就成功了,远程登陆时可能密码正确也无法登陆,这是 postgres 的认证配置导致的:

$ cat /usr/local/var/[email protected]/pg_hba.conf |grep all
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

默认对本地全部信任(trust),没有配置其它来源访问。

用下面的配置允许 postgresdemo 用户从任何地址访问所有数据库,通过密码认证:

# TYPE  DATABASE   USER          ADDRESS      METHOD
  host  all        postgresdemo  0.0.0.0/0    password

添加配置后需要重启 postgresql,详细说明见: Postgres 新建用户怎样才能用密码登陆?

创建数据库

创建数据库并授权给 postgresdemo:

create database postgresdemo;
grant all on database  postgresdemo to postgresdemo;

如果要限制该数据库的访问方式,可以在 pg_hba.conf 添加类似配置:

# TYPE  DATABASE        USER            ADDRESS      METHOD
  host  postgresdemo    postgresdemo    0.0.0.0/0    password

数据库操作:

\list: lists all the databases in Postgres
\connect: connect to a specific database
\dt: list the tables in the currently connected database

参考

  1. 李佶澳的博客
  2. PostgreSQL
  3. PostgreSQL 使用方法
  4. Getting Started with PostgreSQL on Mac OSX

微信公众号:我的网课 微信公众号:我的资产

results matching ""

    No results matching ""