运维类问题

如何通过工具或者代码访问数据库?

以PostgreSQL客户端为例介绍连接实例的方法,其他客户端可参见此方法。

说明: 建议使用9.4.4及以上版本的PostgreSQL客户端psql工具连接数据库,否则版本不匹配会导致部分功能不可用,连接时会出现类似以下的提示:

WARNING: psql major version 9.3, server major version 9.4.4
        Some psql features might not work.

使用 PostgreSQL 客户端运行如下命令,根据提示输入密码,连接数据库。

psql -U username -h hostname -p port dbname
Password for user myuser:
psql.bin (9.4.4, server 9.4.4)
Type "help" for help.
dbname=>

参数说明如下:

  • username:初始帐号用户名
  • hostname:实例地址
  • port:实例端口号
  • dbname:要连接的数据库名称

如:psql -U myuser -h rdsgxxxxxxxx.pg.rds.com -p 3306 dbname

如何查看数据库的参数信息?

连接RDS后执行SQL语句show all可查看数据库的参数信息。

如何查看当前数据库的连接信息?

连接RDS后执行SQL语句select * from pg_stat_activity可查看连接信息,结果集会显示出当前连接的数据库名,用户,IP地址,连接开始时间,查询的语句等。

如何查看某个数据库的空间占用?

连接RDS后执行SQL语句select pg_database_size('dbname')可查看数据库的空间占用情况。

如何查看一个表的空间占用?

连接RDS后执行如下SQL语句可查看表的空间占用情况:

  • 查看表文件大小: select pg_size_pretty(pg_relation_size('tablename'))
  • 查看表索引大小: select pg_size_pretty(pg_index_size('tablename'))
  • 查看表大小(包含索引): select pg_size_pretty(pg_total_relation_size('tablename'))

无法连接RDS for PostgreSQL数据库,如何解决?

问题描述

连接PostgreSQL数据库,报错如下:

FATAL: remaining connection slots are reserved for non-replication superuser connections

原因

这是由于连接数满了导致。

解决办法

  1. 查看当前连接数的限制

    show max_connections;

  2. 查看当前数据库的连接

    select * from pg_stat_activity;

  3. 使用如下SQL终止连接

    SELECT pg_terminate_backend(pid)

    其中pid指进程号,具体是pg_stat_activity视图中pid字段的值。

在RDS PostgreSQL删除数据库时,提示有连接在使用该库,该怎么解决?

问题描述

在RDS PostgreSQL删除数据库提示如下:

ERROR: database "test" is being accessed by other users

DETAIL: There is 1 other session using the database.

原因

这是因为当前有连接在使用该库。

解决办法

在删除数据库之前首先断开所有连接该库的链接。

查看所有连接该库的链接:

SELECT * FROM pg_stat_activity WHERE datname = 'db_name';

确定以上的连接是否可以断开,若是则执行:

SELECT pg_terminate_backend(pid) FROM (select pid from pg_stat_activity where datname = 'db_name' ) t;

断开所有连接后再执行:

drop database db_name;

在PG中运行一个SQL语句,一直不返回结果,运行时间远超预估时间,怎么处理?

分析原因

数据库中更有可能发生死锁。

通过使用以下语句查看系统当前执行语句中是否含有waiting_reason 字段为'lock'的语句:

SELECT * FROM pg_stat_activity;

更加直观地可以使用

SELECT * FROM pg_stat_activity WHERE waiting_reason = ‘lock’;

查询锁状态的执行语句。

解决办法

首先查看数据库是否存在长事务,并且阻塞了其他SQL语句;如果当前存在大量的任务处于锁状态,应该终止相应的长事务进程:

SELECT pg_terminate_backend(pid)

其中pid指进程号,具体是pg_stat_activity视图中pid字段的值。

删除数据后无法释放空间,如何处理?

问题描述

在PG使用delete语句清空表数据或者删除部分数据后,表的大小无变化,所占用的磁盘空间无法释放。

分析原因

在PG数据库中,使用 DELETE 语句并不会真正的删除数据记录,而是简单地将其标记为不可见,并不会释放数据所占用的空间;同样,UPDATE语句也不会真正的修改原数据,而是将原数据标记为不可见,然后将更新后的数据记录插入到表尾。所以频繁进行删除更新操作的表会越来越大。

解决办法

  1. 如果想清空表数据,建议直接使用TRUNCATE语句实现,该语句会直接删除表的数据文件,所以会马上释放空间;

  2. 使用VACUUM table_name;回收不可见的空间,但只是简单地回收空间并且令其可以再次使用,空间并不返回到操作系统(在大多数情况下);

  3. 如果想正真释放不可见的空间,可以使用VACUUM FULL table_name;来实现,但该方法会对表加上排它锁,以及会消耗一部分资源,建议在数据库维护阶段操作。

如何通过工具或者代码访问数据库?

以PostgreSQL客户端为例介绍连接实例的方法,其他客户端可参见此方法。

说明: 建议使用9.4.4及以上版本的PostgreSQL客户端psql工具连接数据库,否则版本不匹配会导致部分功能不可用,连接时会出现类似以下的提示:

WARNING: psql major version 9.3, server major version 9.4.4
     Some psql features might not work.

使用 PostgreSQL 客户端运行如下命令,根据提示输入密码,连接数据库。

psql -U username -h hostname -p port dbname
Password for user myuser:
psql.bin (9.4.4, server 9.4.4)
Type "help" for help. dbname=>

参数说明如下:

  • username:初始帐号用户名
  • hostname:实例地址
  • port:实例端口号
  • dbname:要连接的数据库名称

如:psql -U myuser -h rdsgxxxxxxxx.pg.rds.com -p 3306 dbname

如何查看某个数据库的空间占用?

连接RDS后执行以下SQL语句可查看数据库的空间占用情况。

select pg_database_size('dbname');

select sodddatname, pg_size_pretty(sodddatsize) from gp_toolkit.gp_size_of_database where sodddatname='dbname';

如何查看一个表的空间占用?

连接RDS后执行如下SQL语句可查看表的空间占用情况:

  • 查看表文件大小:

select pg_size_pretty(pg_relation_size('tablename'))

  • 查看表索引大小:

select pg_size_pretty(pg_index_size('tablename'))

  • 查看表大小(包含索引):

select pg_size_pretty(pg_total_relation_size('tablename'))