Mac上使用homebrew安装PostgreSql

1.安装

brew install postgresql

2. 查看版本

pg_ctl -V 

3. 初始化postgresql数据库

initdb /usr/local/var/postgres

$ initdb /usr/local/var/postgres
The files belonging to this database system will be owned by user "june".
This user must also own the server process.

The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

creating directory /usr/local/var/postgres ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in /usr/local/var/postgres/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... 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:

    postgres -D /usr/local/var/postgres
or
    pg_ctl -D /usr/local/var/postgres -l logfile start

4. 启动数据库

#启动方式一 
brew services start postgresql

#启动方式二
 pg_ctl -D /usr/local/var/postgres start

#启动方式三
postgres -D /usr/local/var/postgres

#启动方式四（打印日志）
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

5. 设置启动

$ mkdir -p ~/Library/LaunchAgents

#此次的postgres的版本号，更新为你安装的版本号
$ cp /usr/local/Cellar/postgresql/9.3.5_1/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/

#设置启动加载
$ launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

6. 查看数据库状态

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log status

7. Stop数据库服务器

$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop -s -m fast

8. 创建用户和数据库

# createuser will prompt you for a password, enter it twice.
$ createuser  -P test
$ createdb -Otest -Eutf8 test_db
$ psql
postgres=# GRANT ALL PRIVILEGES ON test TO test;
postgres=# \q

9. 登录数据库

$ psql -U test test_db -h localhost -W

如果出现 FATAL: Ident authentication failed for user，是因为：

This is because by default PostgreSQL uses ‘ident’ authentication i.e it checks if the username exists on the system. You need to change authentication mode to ‘trust’ as we do not want to add a system user. Modify the settings in “pg_hba.conf” to use ‘trust’ authentication.

请修改 /usr/local/var/postgres/pg_hba.conf 为：

host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

10. 卸载postgres数据库

# 通过brew卸载postgres
$ brew uninstall postgres

# 如果配置了开机启动postgres，需要更新或删除配置
$ launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ rm -rf ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

补充：

#在 mac 下，可以利用 homebrew 直接安装 PostgreSQL：
$ brew install postgresql

 

#稍等片刻，PostgreSQL 就安装完成。接下来就是初始数据库，在终端执行一下命令，初始配置 #PostgreSQL：

$ initdb /usr/local/var/postgres -E utf8

 
#上面指定 "/usr/local/var/postgres" 为 PostgreSQL 的配置数据存放目录，并且设置数据库数据编#码是 utf8，更多配置信息可以 "initdb --help" 查看。

#设成开机启动 PostgreSQL：
$ ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
 
#启动 PostgreSQL：
$ pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

 
#关闭 PostgreSQL：
$ pg_ctl -D /usr/local/var/postgres stop -s -m fast

 
#创建一个 PostgreSQL 用户
$ createuser username -P
#Enter password for new role:
#Enter it again:
 
#上面的 username 是用户名，回车输入 2 次用户密码后即用户创建完成。更多用户创建信息可以 "createuser --help" 查看。

#创建数据库
$ createdb dbname -O username -E UTF8 -e

 
#上面创建了一个名为 dbname 的数据库，并指定 username 为改数据库的拥有者（owner），数据库的编码（encoding）是 UTF8，参数 "-e" 是指把数据库执行操作的命令显示出来。

#更多数据库创建信息可以 "createdb --help" 查看。

#连接数据库
$ psql -U username -d dbname -h 127.0.0.1


#PostgreSQL 数据库操作

#显示已创建的数据库：
$ \l

#在不连接进 PostgreSQL 数据库的情况下，也可以在终端上查看显示已创建的列表：
$ psql -l

#连接数据库
$ \c dbname

#显示数据库表
$ \d  

#创建表 和sql的建表语句一样
$ create table tablename (xxx ... ) 

#插入记录
$ INSERT INTO tablename ( ... )

#查询数据
$ SELECT * FROM table [where .... ] [join ... ] ... 

#更新记录
$ UPDATE tablename SET fieldName = 'value' [WHERE ... ];
 
#删除指定的记录
$ DELETE FROM tablename [WHERE id = 1];

#删除表
$ DROP TABLE tablename;

#删除数据库
$ DROP DATABASE dbname;

#或者利用 dropdb 指令，在终端上删除数据库
$ dropdb -U user dbname

参考资料：https://yq.aliyun.com/articles/25638#comment

参考资料：http://www.cnblogs.com/qinyan20/p/3769693.html