Heroku Postgres #

# 创建 Mini 数据库
heroku addons:create heroku-postgresql:mini

# 创建指定计划
heroku addons:create heroku-postgresql:standard-0

# 指定应用名称
heroku addons:create heroku-postgresql:mini --app myapp

# 指定版本
heroku addons:create heroku-postgresql:mini --version=16

# 查看数据库信息
heroku pg:info

# 输出示例
# === DATABASE_URL, HEROKU_POSTGRESQL_IVORY_URL
# Plan:        Mini
# Status:      Available
# Connections: 1/20
# PG Version:  16.2
# Created:     2024-01-15 10:00 UTC
# Data Size:   10.2 MB
# Tables:      5
# Rows:        1000/10000 (Row limit)
# Fork/Follow: Unsupported
# Rollback:    Unsupported
# Add-on:      postgresql-round-12345

# 查看数据库 URL
heroku config:get DATABASE_URL

# 输出格式
# postgres://user:password@host:port/database

# 多个数据库
heroku config | grep HEROKU_POSTGRESQL

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: false
  }
});

// 查询示例
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);
console.log(result.rows);

// 关闭连接池
await pool.end();

import os
import psycopg2
from psycopg2.extras import RealDictCursor

# 连接数据库
conn = psycopg2.connect(
    os.environ['DATABASE_URL'],
    sslmode='require',
    cursor_factory=RealDictCursor
)

# 查询示例
cur = conn.cursor()
cur.execute('SELECT * FROM users WHERE id = %s', (user_id,))
rows = cur.fetchall()

# 关闭连接
cur.close()
conn.close()

require 'pg'

# 连接数据库
conn = PG.connect(ENV['DATABASE_URL'], sslmode: 'require')

# 查询示例
result = conn.exec_params('SELECT * FROM users WHERE id = $1', [user_id])
result.each do |row|
  puts row
end

# 关闭连接
conn.close

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: { rejectUnauthorized: false },
  max: 10,                    // 最大连接数
  idleTimeoutMillis: 30000,   // 空闲超时
  connectionTimeoutMillis: 2000 // 连接超时
});

// 使用连接池
app.get('/users/:id', async (req, res) => {
  try {
    const result = await pool.query(
      'SELECT * FROM users WHERE id = $1',
      [req.params.id]
    );
    res.json(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Database error' });
  }
});

# 进入 psql 命令行
heroku pg:psql

# 指定数据库
heroku pg:psql DATABASE_URL

# 执行 SQL 文件
heroku pg:psql < schema.sql

# 执行单条 SQL
heroku pg:psql -c "SELECT COUNT(*) FROM users"

# 进入 psql 后
\dt          # 列出所有表
\d users     # 查看 users 表结构
\di          # 列出所有索引
\df          # 列出所有函数
\q           # 退出

// 使用 node-pg-migrate
// package.json
{
  "scripts": {
    "migrate": "node-pg-migrate up",
    "migrate:down": "node-pg-migrate down",
    "migrate:create": "node-pg-migrate create"
  }
}

// 创建迁移
// npm run migrate:create add users table

// migrations/20240115000000_add-users-table.js
exports.up = (pgm) => {
  pgm.createTable('users', {
    id: { type: 'serial', primaryKey: true },
    email: { type: 'varchar(255)', notNull: true, unique: true },
    name: { type: 'varchar(255)' },
    created_at: { type: 'timestamp', default: pgm.func('now()') }
  });
};

exports.down = (pgm) => {
  pgm.dropTable('users');
};

# 创建备份
heroku pg:backups:capture

# 查看备份列表
heroku pg:backups

# 输出示例
# === Backups
# ID    Created at               Status                              Size    Database
# b001  2024-01-15 10:00:00 UTC  Completed 2024-01-15 10:01:00 UTC  10.2MB  DATABASE_URL

# 下载备份
heroku pg:backups:download b001

# 恢复备份
heroku pg:backups:restore b001 --app myapp --confirm myapp

# 设置自动备份
heroku pg:backups:schedule DATABASE_URL --at '02:00 America/Los_Angeles'

# 详细信息
heroku pg:info

# 查看连接数
heroku pg:connections

# 查看锁
heroku pg:locks

# 查看等待事件
heroku pg:wait-events

# 查看慢查询
heroku pg:psql -c "SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10"

# 查看表大小
heroku pg:psql -c "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC"

# 查看索引使用情况
heroku pg:psql -c "SELECT indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan"

# Data Clips 是 Heroku 提供的 SQL 查询结果分享功能
# 在 Dashboard 中访问：
# https://data.heroku.com/dataclips

# 查看当前计划
heroku pg:info

# 升级到更高计划
heroku addons:upgrade heroku-postgresql-standard-0

# 降级计划
heroku addons:downgrade heroku-postgresql-basic

# 查看存储使用情况
heroku pg:info

# 升级到更大存储的计划
heroku addons:upgrade heroku-postgresql-standard-2

# 创建只读副本
heroku addons:create heroku-postgresql:standard-0 --follow DATABASE_URL

# 查看复制状态
heroku pg:info

# 输出示例
# === HEROKU_POSTGRESQL_SILVER_URL
# Plan:        Standard 0
# Status:      Available
# Following:   DATABASE_URL (DATABASE_URL)
# Behind By:   0 sec

# 创建数据库分叉（用于测试）
heroku addons:create heroku-postgresql:standard-0 --fork DATABASE_URL

# 指定时间点分叉
heroku addons:create heroku-postgresql:standard-0 --fork DATABASE_URL --at "2024-01-15 10:00:00"

// 强制 SSL 连接
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: false
  }
});

# 查看连接限制
heroku pg:info

# Mini: 20 连接
# Basic: 20 连接
# Standard-0: 120 连接
# Standard-2: 400 连接

# 使用连接池避免连接数耗尽

# 查看凭证
heroku pg:credentials:url

# 轮换凭证
heroku pg:credentials:rotate

# 创建只读用户
heroku pg:credentials:create readonly

# 导出数据
pg_dump $DATABASE_URL > backup.sql

# 导入数据
heroku pg:psql < backup.sql

# 使用 CSV 导入
heroku pg:psql -c "COPY users FROM '/tmp/users.csv' WITH CSV HEADER"

# 导出为 CSV
heroku pg:psql -c "COPY users TO STDOUT WITH CSV HEADER" > users.csv

# 重置数据库（删除所有数据）
heroku pg:reset DATABASE_URL --confirm myapp

# 警告：此操作不可逆！

# 从其他数据库迁移到 Heroku Postgres
heroku pg:push mylocaldb DATABASE_URL --app myapp

# 从 Heroku Postgres 迁移到本地
heroku pg:pull DATABASE_URL mylocaldb --app myapp

-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

-- 查看索引使用情况
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch 
FROM pg_stat_user_indexes;

-- 删除未使用的索引
DROP INDEX idx_unused;

-- 使用 EXPLAIN 分析查询
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- 创建部分索引
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- 使用连接池
-- 使用预处理语句

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: { rejectUnauthorized: false },
  max: 10,                      // 最大连接数
  min: 2,                       // 最小连接数
  idleTimeoutMillis: 30000,     // 空闲超时
  connectionTimeoutMillis: 2000 // 连接超时
});

# 检查数据库状态
heroku pg:info

# 检查连接数
heroku pg:connections

# 查看错误日志
heroku logs --tail | grep postgres

# 查看慢查询
heroku pg:psql -c "SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10"

# 查看锁
heroku pg:locks

# 查看等待事件
heroku pg:wait-events

# 查看存储使用
heroku pg:info

# 清理不需要的数据
heroku pg:psql -c "VACUUM FULL ANALYZE"

# 升级存储计划
heroku addons:upgrade heroku-postgresql-standard-2