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

可查看官网教程添加源安装。

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

添加源：

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

导入密钥：

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

添加新用户：

sudo -i -u postgres
createuser --interactive

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

Thanks: Upgrading PostgreSQL from 9.6 to 10 when upgrading Ubuntu 14.04 to 14.10

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

0. 先备份数据

$ pg_dumpall > backup.sql

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

$ 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

2. 暂停10

sudo pg_dropcluster 10 main --stop

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

sudo pg_upgradecluster 9.6 main

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

$ 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

5. 移除9.6

sudo pg_dropcluster 9.6 main
sudo aptitude remove postgresql-9.6 postgresql-client-9.6

Visit 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

Change user password:

sudo -u postgres psql
 
# psql shell
\password <user>

Set user as supersuer:

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

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

First, connect to postgres database:

psql postgres

Then run 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;

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

1. 使用 PGTune 生成机器最佳的配置，并放到 /etc/postgresql/11/main/conf.d 中

2. 开启慢查询日志

/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

• PGroonga - Make PostgreSQL fast full text search platform for all languages!