PostgreSQL in Action

http://www.postgresql.org/

Install

- Mac with Homebrew

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$ brew install postgres
# ==> Downloading https://homebrew.bintray.com/bottles/postgresql-9.4.5.el_capitan.bottle.tar.gz
# Already downloaded: /Library/Caches/Homebrew/postgresql-9.4.5.el_capitan.bottle.tar.gz
# ==> Pouring postgresql-9.4.5.el_capitan.bottle.tar.gz
# ==> /usr/local/Cellar/postgresql/9.4.5/bin/initdb /usr/local/var/postgres
# ==> Caveats
# If builds of PostgreSQL 9 are failing and you have version 8.x installed,
# you may need to remove the previous version first. See:
# https://github.com/Homebrew/homebrew/issues/2510
#
# To migrate existing data from a previous major version (pre-9.4) of PostgreSQL, see:
# https://www.postgresql.org/docs/9.4/static/upgrading.html
#
# To reload postgresql after an upgrade:
# launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
# launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
# Or, if you don't want/need launchctl, you can just run:
# postgres -D /usr/local/var/postgres
# ==> Summary
# 🍺 /usr/local/Cellar/postgresql/9.4.5: 3021 files, 40M

过程中,Brew 帮我们默认初始化了数据库目录:/usr/local/var/postgres。

手动建立数据库目录并进行初始化

可以手工初始化 DB 目录,来了解到其中原理:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
$ initdb /usr/local/var/postgres
# The files belonging to this database system will be owned by user "lanvige".
# This user must also own the server process.#
# The database cluster will be initialized with locales
# COLLATE: C
# CTYPE: UTF-8
# MESSAGES: C
# MONETARY: C
# NUMERIC: C
# TIME: C
# The default database encoding has accordingly been set to "UTF8".
# initdb: could not find suitable text search configuration for locale "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
# selecting dynamic shared memory implementation ... posix
# 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:#
# pg_ctl -D /usr/local/var/postgres -l logfile start

过程中,建了 2 个数据库:template1 & postgres,同时创建一个和系统用户同名的用户,密码为空。

- Install with Docker

Docker compose 文件

postgres-compose.yml
1
2
3
4
5
6
7
8
9
10
postgres:
container_name: postgres-9.4.4
image: postgres:latest
ports:
- "5432:5432"
environment:
- POSTGRES_PASSWORD=docker1
- POSTGRES_USER=docker1
volumes:
- /data/docker/postgres:/var/lib/postgresql/data
  • 绑定端口
  • 创建时指定用户名,密码
  • 共享 volume

创建、启动 Container

1
$ docker-compose -f postgres-compose.yml up -d

Basic Operation

1. 连接数据库

pg 连接中,需要指定用户名和数据库名,默认用户为系统用户,默认 DB 为 系统用户同名 DB。

这里 DB 可以连 postgres, user 可以用当前系统用户 whoami。

1
2
3
$ psql db_name your_username
# 示例
$ psql postgres `whoami`

2. 添加用户 & 新建 DB

新建用户
1
pg$ CREATE USER dbauser WITH PASSWORD 'password';
修改密码
1
pg$ \password lanvige
新建数据库并指定所有者
1
pg$ CREATE DATABASE newdb OWNER dbauser;
给用户 dabuser 操作 新DB 的权限
1
GRANT ALL PRIVILEGES ON DATABASE newdb to dbauser;

3. 常用数据库操作

1
2
3
4
5
6
7
8
9
10
\h:查看SQL命令的解释,比如\h select。
\?:查看psql命令列表。
\l:列出所有数据库。
\c [database_name]:连接其他数据库。
\d:列出当前数据库的所有表格。
\d [table_name]:列出某一张表格的结构。
\du:列出所有用户。
\e:打开文本编辑器。
\conninfo:列出当前数据库和连接的信息。
\q: 退出

Example: 列出所有数据库

1
2
3
4
5
6
7
8
9
10
11
pg# \l
# # List of databases
# Name | Owner | Encoding | Collate | Ctype | Access privileges
# -----------+---------+----------+---------+-------------+---------------------
# postgres | lanvige | UTF8 | C | en_US.UTF-8 |
# template0 | lanvige | UTF8 | C | en_US.UTF-8 | =c/lanvige +
# | | | | | lanvige=CTc/lanvige
# template1 | lanvige | UTF8 | C | en_US.UTF-8 | =c/lanvige +
# | | | | | lanvige=CTc/lanvige

Set Up with a Rails App

First, install the pg gem:

1
gem install pg -- --with-pg-config=/usr/local/bin/pg_config

Make sure you include the pg gem in your Gemfile, and run

1
$ bundle install

Now, set up your config/database.yml file to point to your Posgres database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
development:
adapter: postgresql
encoding: unicode
database: myapp_dev
pool: 5
username: your_username_on_mac
password:
test:
adapter: postgresql
encoding: unicode
database: myapp_test
pool: 5
username: your_username_on_mac
password:

Let’s create the development and test databases:

1
rake db:create:all

Now you can run pending migrations, if there are any.

1
rake db:migrate

Migration from MySQL

将旧的数据库从 MySQL 迁移到 Postgres:

导出 MySQL:

1
mysqldump --compatible=postgresql --default-character-set=utf8 -r databasename.mysql -u root -p databasename

转换数据:

1
$ python db_converter.py gitlabhq_prod.mysql gitlabhq_prod.psql

导入 pg:

1
$ psql -f databasename.psql

或者,直接在 pgAdmin 中新建数据库,然后执行 pgScript。

GUI Tools

  • pgAdmin3
  • Postic
  • PSequel

REF::