# PostgreSQL

## 安装

### Docker安装

1. 我们的项目都会涉及到中文字符，会对中文字符按拼音排序，`Ubuntu`默认安装的`PostgreSQL`是`en_US.UTF-8`字符集排序，要支持拼音培训，需要改为`zh_CN.UTF-8`。
2. Mac下用`brew`安装的PostgreSQL字符集排序是`zn_CN.UTF-8`，但是很奇怪的是他并不能实现拼音排序，经研究之后确实找不到解决办法。\
   因为以上两点，所以我用这个镜像方便我们开发人员快速使用数据库

#### 如何启动

```bash
mkdir -p ~/postgresql/data
docker run -v $(realpath ~/postgresql/data):/var/lib/postgresql/data -p 5432:5432 --name postgresql-10 -d tanmer/postgresql:10
```

#### 进入psql CLI

```
docker exec -it postgresql-10 psql -U postgres
```

#### 测试中文排序是否正确

```
postgres=# select * from (values ('刘少奇'),('刘德华')) as a(c1) order by c1;
  c1
--------
刘德华
刘少奇
(2 rows)

postgres=#
```

#### 镜像 tanmer/postgresql:10 的 Dockerfile内容

```
FROM postgres:10
MAINTAINER Xiaohui <xiaohui@tanmer.com>

RUN sed -i 's!deb.debian.org!mirrors.163.com!' /etc/apt/sources.list \
    && apt update \
    && apt install --reinstall locales \
    && echo zh_CN UTF-8 > /etc/locale.gen \
    && echo zh_CN.UTF-8 UTF-8 >> /etc/locale.gen \
    && echo en_US UTF-8 >> /etc/locale.gen \
    && echo en_US.UTF-8 UTF-8 >> /etc/locale.gen \
    && locale-gen

ENV LC_COLLATE zh_CN.UTF-8

```

### Ubuntu安装

```bash
echo deb http://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/apt/ xenial-pgdg main > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update
apt-get install postgresql-10
```

更改默认配置

```bash
# 改变数据目录
echo data_directory = \'/data/postgresql/10/main\' > /etc/postgresql/10/main/conf.d/tanmer.conf
# 配置最大连接数
echo max_connections = 1000 >> /etc/postgresql/10/main/conf.d/tanmer.conf
# 允许外部主机连接
echo listen_addresses = \'0.0.0.0\' >> /etc/postgresql/10/main/conf.d/tanmer.conf
# 允许外部用户用密码登录
echo host all all 10.103.0.0/24 md5 >> /etc/postgresql/10/main/pg_hba.conf
# 停止服务
systemctl stop postgresql
# 移动数据目录
mv /var/lib/postgresql /data
# 启动服务
systemctl start postgresql
# 查看服务状态
systemctl status postgresql
```

## 使用

### 创建用户

```sql
create role xiaohui with login password 'this-is-a-password';
```

### 创建数据库

```sql
create database project;
```

### 设置数据库拥有者

```sql
alter database project owner to xiaohui; 
grant all privileges on database project to xiaohui;
```

### 更改数据表的拥有者

```sql
SELECT 'ALTER TABLE '|| schemaname || '.' || tablename ||' OWNER TO new-owner;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

SELECT 'ALTER SEQUENCE '|| sequence_schema || '.' || sequence_name ||' OWNER TO new-owner;'
FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
ORDER BY sequence_schema, sequence_name;
```

### 清空数据库

执行下面查询，生成对所有表`drop`的SQL，然后复制、粘贴、执行。

```sql
select 'drop table "' || tablename || '" cascade;' as drop_table from pg_tables where schemaname = 'public';
```

## &#x20;查询

### 获取某时区的时间

```sql
// 先获取所有的时区定义
select * from pg_timezone_names order by utc_offset
```

```
               name               | abbrev | utc_offset | is_dst
----------------------------------+--------+------------+--------
 Africa/Abidjan                   | GMT    | 00:00:00   | f
 Africa/Accra                     | GMT    | 00:00:00   | f
 Africa/Addis_Ababa               | EAT    | 03:00:00   | f
 Africa/Algiers                   | CET    | 01:00:00   | f
 Africa/Asmara                    | EAT    | 03:00:00   | f
 Africa/Asmera                    | EAT    | 03:00:00   | f
 Africa/Bamako                    | GMT    | 00:00:00   | f
```

```sql
// 把字段created_at的UTC时间转换为用户设定的本地时间
select created_at, timezone, created_at at time zone 'UTC' at time zone timezone as localtime from users;
```

```
         created_at         |   timezone    |         localtime
----------------------------+---------------+----------------------------
 2018-10-10 03:36:33.686459 | Asia/Shanghai | 2018-10-10 11:36:33.686459
 2018-10-10 04:11:23.707863 | PST           | 2018-10-09 20:11:23.707863
(2 rows)
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://doc.tanmer.cn/shu-ju-ku/postgresql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.