====== PostgreSQL ======

PostgreSQL是自由的对象-关系型数据库服务器（数据库管理系统），在灵活的BSD-风格许可证下发行。

===== Install on Ubuntu =====

==== Install PostgreSQL 11  ====

可查看[[http://www.postgresql.org/download/linux/debian/|官网]]教程添加源安装。 

下面以''Debian'' 10 ''buster''为例，安装PostgreSQL 12

添加源：

<code>
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
</code>

导入密钥：

<code>
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-12
</code>

添加新用户：

<code>
sudo -i -u postgres
createuser --interactive
</code>

==== Upgrade PostgreSQL from 9.6 to 10  ====

由于 Debian9 官方源内的PostgreSQL是9.6的，如果已经有数据，升级到10时要做以下步骤：

Thanks: [[https://gist.github.com/dideler/60c9ce184198666e5ab4|Upgrading PostgreSQL from 9.6 to 10 when upgrading Ubuntu 14.04 to 14.10]]

要求：同时安装9.6和10的PostgreSQL

0. 先**备份数据**

<code>
$ pg_dumpall > backup.sql
</code>

1. 运行''pg_lsclusters''，保证9.6和10都启动了。

<code>
$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
10 main    5433 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
</code>

2. 暂停10

<code>
sudo pg_dropcluster 10 main --stop
</code>

3. 迁移9.6数据到最新版本

<code>
sudo pg_upgradecluster 9.6 main
</code>

4. 此时9.6数据库应该停止''stop''了

<code>
$ pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5433 down   postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
10 main    5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
</code>

5. 移除9.6

<code>
sudo pg_dropcluster 9.6 main
sudo aptitude remove postgresql-9.6 postgresql-client-9.6
</code>

===== Install on Centos =====

Visit [[https://yum.postgresql.org/|PostgreSQL Yum Repository]] and select PostgreSQL version, download repo rpm like this:

  wget https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
  yum install postgresql10 postgresql10-devel postgresql10-libs postgresql10-server

===== Usage =====

Change ''user'' password:

<code bash>
sudo -u postgres psql

# psql shell
\password <user>
</code>

Set ''user'' as supersuer:

<code bash>
sudo -u postgres psql -c "ALTER USER <user> with superuser"
</code>

===== Maintain =====

Show size of every PostgreSQL database.[(https://www.peterbe.com/plog/show-size-of-every-postgresql-database)]

First, connect to ''postgres'' database:

<code bash>
psql postgres
</code>

Then run SQL:

<code sql>
SELECT pg_database.datname, 
  pg_database_size(pg_database.datname), 
  pg_size_pretty(pg_database_size(pg_database.datname)) 
FROM pg_database ORDER by 2 DESC;
</code>

===== 性能优化 =====

复用连接可提高数据库的性能，详见[[pgbouncer|PgBouncer]]

==== 优化配置 ====

1. 使用 [[https://pgtune.leopard.in.ua/#/|PGTune]] 生成机器最佳的配置，并放到 ''/etc/postgresql/11/main/conf.d'' 中

2. 开启慢查询日志

<code ini /etc/postgresql/11/main/conf.d/log.conf>
logging_collector = on
log_statement = 'ddl'
log_filename = 'postgresql-%Y-%m-%d.log'
log_min_duration_statement = 300
</code>

===== Awesome List =====

  * [[https://pgroonga.github.io/|PGroonga]] - Make PostgreSQL fast full text search platform for all languages!