PG数据库常用操作

记录一下,在开发过程中接触到的一些PG数据库常用操作,以备不时之需。

全量迁移

  • 备份数据
1
$ pg_dump -h 172.19.235.145 -U <username> -d <database> > 20180704_dbpe.sql
  • 正式迁移

首先要修改备份文件*.sql的owner,防止权限出现错误。

1
$ psql -h <ip> -U <username> -d <database> -f 20180704_dbpe.sql

【注意点】该迁移操作会覆盖原来的数据库,所以最好创建一个新库。

列出所有表名和数据库名

1
select tablename from pg_tables where schemaname ='public';

PostgreSQL 中 有时候想删除数据库(drop database swiftliveqaapi;),发现提示“ERROR: database “xxxxxx” is being accessed by other users DETAIL: There are 30 other sessions using the database.”

1
2
3
用psql 登录进入, 执行语句:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='数据库名' AND pid<>pg_backend_pid();
然后就可以删除数据库了

修改表的序列为id最大值

1
SELECT setval('表名_id_seq', (SELECT MAX(id) FROM 表名));

查询表结构

1
2
3
4
5
6
7
8
9
10
11
SELECT 
COLUMN_NAME AS 列名,
DATA_TYPE AS 字段类型,
CHARACTER_MAXIMUM_LENGTH AS 长度,
IS_NULLABLE AS 是否为空,
COLUMN_DEFAULT AS 默认值
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
table_schema = 'public'
AND TABLE_NAME = '表名';

PG 数据库状态,启动,停止

1
2
3
$ pg_ctlcluster 9.5 main status
$ pg_ctlcluster 9.5 main start
$ pg_ctlcluster 9.5 main stop