Home | 简体中文 | 繁体中文 | 杂文 | 知乎专栏 | 51CTO学院 | CSDN程序员研修院 | Github | OSChina 博客 | 腾讯云社区 | 阿里云栖社区 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
知乎专栏多维度架构

部分 VI. PostgreSQL

目录

57. PostgreSQL 安装
57.1. Ubuntu 12.04.1 LTS 安装
57.2. YUM 默认源安装
57.3. PostgreSQL 官方 YUM 源安装
57.3.1. 9.2
57.3.2. 9.3
57.3.3. 9.4
57.3.4. 9.6
57.3.5. PostgreSQL 10
57.4. PostgreSQL 配置
57.4.1. postgresql.conf
57.4.2. pg_hba.conf
57.5. 创建dba用户
58. 数据库管理(Database Administration)
58.1. 用户管理(User Account Management)
58.1.1. 创建用户
58.1.2. 删除用户
58.1.3. 修改用户名
58.1.4. 修改密码
58.2. Access Privilege System
58.2.1. SHOW GRANTS
58.2.2. show privileges
58.2.3. Grant privileges
58.2.4. Revoke privileges
58.2.5. Show Privileges
58.2.6. MAX_QUERIES_PER_HOUR/MAX_UPDATES_PER_HOUR
58.2.7. Table Privileges
58.2.8. Column Privileges
58.3. 字符集转换
58.3.1. 转换 latin1 到 UTF-8
58.4. 重新整理AUTO_INCREMENT字段
58.5. 数据库内容替换
58.6. Kill 脚本
58.7. MySQL 时区管理
58.8. SHOW COMMAND
58.8.1. 查看版本
58.8.2. status
58.8.2.1. show status
58.8.2.2. show master status
58.8.2.3. show slave status
58.8.2.4. show plugins
58.8.3. show processlist
58.8.4. 线程的使用情况
58.8.5. DATABASES
58.8.6. 排序统计信息
58.8.7. Key 状态
58.8.8. FUNCTION
58.8.9. PROCEDURE
58.8.10. TRIGGERS
58.8.11. EVENTS
58.8.12. 引擎(ENGINES)
58.8.13. 字符集(Collation)
58.8.14. SHOW GRANTS
58.8.15. validate_password
58.9. Maintenance 数据库维护
58.9.1. CHECK 检查表
58.9.2. ANALYZE 分析表
58.9.3. CHECKSUM
58.9.4. OPTIMIZE 优化表
58.9.5. REPAIR 修复
58.10. INFORMATION_SCHEMA
58.10.1. 查询表字段
58.10.2. 列出所有触发器
58.10.3. 查看表数据尺寸
58.11. Backup and Recovery
58.11.1. Import / Export
58.11.1.1. Export(Backup)
58.11.1.2. Import(Recovery)
58.11.1.3. xml
58.11.1.4. 备份表数据
58.11.1.5. source
58.11.1.6. 使用 mysqlhotcopy 备份 MyISAM 引擎的数据库
58.11.1.7. AutoMySQLBackup
58.11.1.8. xtrabackup - Open source backup tool for InnoDB and XtraDB.
58.11.1.8.1. Percona yum Repository
58.11.1.8.2. Creating an Incremental Backup
58.11.2. Snapshot Backup
58.11.2.1. LVM Snapshot
58.11.2.2. Btrfs Snapshot
59. PostgreSQL 命令
59.1. psql - PostgreSQL interactive terminal
59.1.1. 执行文件
59.2. Backup / Restore
59.2.1. Backup
59.2.2. Restore
59.2.3. import
59.2.4. 导出指定 schema
59.2.5. PostgreSQL for windows
59.2.6. 大型数据库备份
59.2.7. schema之间迁移数据
60. DDL - Data Definition Language
60.1. 数据库管理(Database)
60.1.1. 创建数据库
60.1.2. 删除数据库
60.1.3. 修改数据库
60.1.4. 重命名数据库
60.1.5. 修改字符集
60.1.6. 查看数据库创建语句
60.2. 表管理(Table)
60.2.1. 数据类型
60.2.1.1. SET 集合类型
60.2.2. 基于现有表结构创建新表
60.2.3. 基于已存在表创建新表
60.2.4. 修改表
60.2.5. 临时表
60.2.6. CHARACTER
60.2.7. DEFAULT
60.2.7.1. AUTO_INCREMENT
60.2.7.2. TIMESTAMP NULL DEFAULT NULL ON UPDATE
60.2.7.3. 表存储位置(DATA DIRECTORY)
60.2.8. KEY
60.2.8.1. PRIMARY KEY
60.2.9. AUTO_INCREMENT 定义初始值
60.2.10. COMMENT
60.2.11. Engine 存储引擎
60.2.11.1. 显示当前数据库支持引擎
60.2.11.2. 切换引擎
60.2.11.3. FEDERATED
60.2.11.4. BLACKHOLE
60.2.11.5. ARCHIVE
60.2.11.6. CSV
60.3. Partitioning
60.3.1. RANGE
60.3.2. LIST
60.3.3. HASH
60.3.3.1. LINEAR HASH
60.3.4. KEY分区
60.3.5. Subpartitioning
60.3.6. 分区管理
60.3.6.1. 新增分区
60.3.6.2. 删除分区
60.3.6.3. 重建分区
60.3.6.4. 分区维护
60.3.7. EXPLAIN PARTITIONS
60.3.8. SHOW CREATE TABLE
60.3.9. INFORMATION_SCHEMA.partitions 表
60.3.10. 分区数据操作
60.4. Index
60.4.1. SHOW INDEX
60.4.2. CREATE INDEX
60.4.3. DROP INDEX
60.4.4. rebuild
60.5. 外键(Foreign Key)
60.5.1. FOREIGN KEY (RESTRICT)
60.6. 视图(View)
60.7. 存储过程(PROCEDURE)
60.7.1. 存储程序
60.7.2. EXECUTE 执行 SQL
60.7.3. PREPARE 传递参数
60.7.4. 存储过程返回数据
60.7.5. 结果集转JSON
60.7.6. 例子·过程返回结果
60.8. 函数
60.8.1. TIMESTAMP TO ISO8601
60.9. 触发器(Trigger)
60.9.1. create trigger
60.9.1.1. Update 更新出发
60.9.1.2. Delete 删除出发
60.9.1.3. Insert 插入出发
60.9.2. drop trigger
60.9.3. show triggers
60.9.3.1. SHOW CREATE TRIGGER
60.9.4. EXAMPLE
60.9.4.1. BEFORE/AFTER
60.9.4.2. UUID
60.9.4.3. CALL PROCEDURE
60.10. 事件调度器(EVENT)
60.10.1. 启用 EVENT
60.10.2. 创建 EVENT
60.10.3. 禁用/启用
60.10.4. 查看 events
60.10.5. 删除 EVENT
60.10.6. EVENT 应用案例
60.10.6.1. 实例·每月创建一个表
60.10.6.2. 案例·定时删除数据
60.10.6.3. 指定日期执行
61. DML (Data Manipulation Language)
61.1. INSERT
61.1.1. INSERT INTO ... SELECT
61.1.2. INSERT IGNORE
61.1.3. INSERT...ON DUPLICATE KEY UPDATE
61.2. REPLACE
61.3. DELETE
61.3.1. 删除重复数据
62. SQL Statement Syntax
62.1. DISTINCT
62.2. group by
62.3. HAVING
62.4. REGEXP
62.5. IN / NOT IN
62.6. ALL / Any
62.7. exists, not exists
62.8. UNION
62.8.1. UNION ALL
62.8.2. 两张表字段不对等解决方法
62.9. OUTFILE/LOAD DATA INFILE
62.9.1. Export data to CSV from MySQL
62.9.2. Import data from CSV file.
62.10. CASE Syntax
62.11. 查询结果放入变量
62.12. MySQL 专有命令
62.12.1. SQL_NO_CACHE
62.12.2. SIGNAL Syntax
62.13. SQL 92
63. Functions and Operators
63.1. COUNT
63.2. group_concat() 列传行
63.3. UUID()
63.4. String
63.4.1. LEFT/RIGHT
63.4.2. RPAD/LPAD
63.4.3. CONCAT
63.4.4. CONCAT_WS
63.4.5. 链接所有字段
63.4.6. GROUP_CONCAT
63.4.7. replace
63.4.8. SUBSTRING
63.4.9. SUBSTRING_INDEX
63.4.10. AES_ENCRYPT / AES_DECRYPT
63.5. Date and Time
63.5.1. year/month/day hour:minite:second
63.5.2. Unix time
63.5.3. DATE_FORMAT
63.5.4. DATE_SUB/DATE_ADD
63.5.4.1. DATE_ADD
63.5.5. datediff / timediff
63.6. 数值函数
63.6.1. cast 类型转换
63.6.2. truncate 保留小数位数
63.6.3. MOD 求余
63.7. Control Flow Functions
64. PostgreSQL GUI
64.1. pgAdmin III
64.2. phpPgAdmin
64.3. Monitoring
64.3.1. PgBadger
64.4. pgModeler - PostgreSQL Database Modeler
65. Barman Backup & recovery for PostgreSQL
66. pgbouncer - lightweight connection pooler for PostgreSQL
66.1. 安装 pgbouncer
66.1.1. Ubuntu
66.1.2. CentOS
66.2. 配置 pgbouncer
67. Foreign data wrappers
68. Barman for PostgreSQL
69. Connector
69.1. Pomm
70. Replication
70.1. Bucardo
71. FAQ
71.1. Reset root password 重置MySQL root密码
71.1.1. MySQL 5.7.x
71.1.2. MySQL 8.0
71.2. 查看错误代码
71.2.1. ERROR 1153 (08S01) at line 3168: Got a packet bigger than 'max_allowed_packet' bytes
71.2.2. ERROR 1129 (00000): Host 'XXXXXX' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
71.3. 临时表是否需要建索引
71.4. [Warning] Changed limits: max_open_files: 5000 (requested 20480)
71.5. SQL Error (1038): Out of sort memory, consider increasing server sort buffer size
71.6. this is incompatible with sql_mode=only_full_group_by
71.7. ERROR 1071 (42000) at line 25: Specified key was too long; max key length is 767 bytes
71.8. ERROR 1086 (HY000): File '/var/lib/mysql-files/order.txt' already exists
71.9. Error Code: 1146. Table 'test.CACHE_UPDATE' doesn't exist
71.10. Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
71.11. ERROR 1273 (HY000) at line 3364: Unknown collation: 'utf8mb4_0900_ai_ci'
71.12. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
71.13. ERROR 1364: 1364: Field 'id' doesn't have a default value
71.14. ERROR 1415: Not allowed to return a result set from a trigger
71.15. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
71.16. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
71.17. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
71.18. ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
71.19. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
71.20. ERROR 2013 (HY000): Lost connection to MySQL server during query
71.21. ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
71.22. ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
71.23. Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such fileor directory
71.24. com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Public Key Retrieval is not allowed
71.25. mysqldump: Couldn't execute 'SELECT COLUMN_NAME,
71.26. this is incompatible with sql_mode=only_full_group_by
71.27. mysqldump: [Warning] Using a password on the command line interface can be insecure.
71.28. mysql: [Warning] Using a password on the command line interface can be insecure.

PostgreSQL 实用实例参考