postgresql 随笔

查看数据库服务启动时间。

#两个方法
SELECT pg_postmaster_start_time();
#格式化代码
SELECT date_trunc('second',current_timestamp - pg_postmaster_start_time()) as "PostgreSQLServiceUpTime";

查看服务已运行时间。

SELECT current_timestamp - pg_postmaster_start_time() as "ServiceStartTime";
#格式化代码
SELECT date_trunc('second',current_timestamp - pg_postmaster_start_time()) as "UpTime";

查看当前数据库存储空间占用情况。

SELECT pg_database_size(current_database());

查看表行数。

#根据实际情况修改表名
SELECT count(*) FROM "public"."CN";

查询数据库扩展模块信息。

SELECT * FROM pg_extension;

本文测试环境输出结果如下。

postgres=# SELECT * FROM pg_extension;
 extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
---------+----------+--------------+----------------+------------+-----------+--------------
 plpgsql |       10 |           11 | f              | 1.0        |           |
(1 row)

postgres=#

使用'x'选项有如下类似结果输出。

postgres=# SELECT * FROM pg_extension;
-[ RECORD 1 ]--+--------
extname        | plpgsql
extowner       | 10
extnamespace   | 11
extrelocatable | f
extversion     | 1.0
extconfig      |
extcondition   |

更改会话参数。例如：

set work_mem = '32MB';

部分代码和结果。

postgres=# set work_mem = '32MB';
SET
postgres=# set local work_mem = '16MB';
WARNING:  SET LOCAL can only be used in transaction blocks
SET
postgres=# reset all;
RESET
postgres=#
postgres=#
postgres=# set work_mem = '32MB';
SET
postgres=#
postgres=# select name,setting,reset_val,source from pg_settings where source ='session';
-[ RECORD 1 ]-------
name      | work_mem
setting   | 32768
reset_val | 4096
source    | session

postgres=#

查看配置文件。

postgres=# show config_file;
               config_file
------------------------------------------
 /etc/postgresql/9.5/main/postgresql.conf
(1 row)

postgres=# x
Expanded display is on.
postgres=# show config_file;
-[ RECORD 1 ]-----------------------------------------
config_file | /etc/postgresql/9.5/main/postgresql.conf

postgres=#

该方法还可以查询hba_file和ident_file等配置文件信息。

列出所有参数。

SELECT name,source,setting FROM pg_settings order by 2,1;

结果太长不予列出。例如：

postgres=# SELECT name,setting FROM pg_settings where source !='default' and source !='ove
rride' order by 2,1;
            name            |        source        |                 setting
----------------------------+----------------------+------------------------------------------
 application_name           | client               | psql
 client_encoding            | client               | UTF8
 DateStyle                  | configuration file   | ISO,MDY
 default_text_search_config | configuration file   | pg_catalog.english
 dynamic_shared_memory_type | configuration file   | posix
 external_pid_file          | configuration file   | /var/run/postgresql/9.5-main.pid
 lc_messages                | configuration file   | en_US.UTF-8
 lc_monetary                | configuration file   | en_US.UTF-8
 lc_numeric                 | configuration file   | en_US.UTF-8
 lc_time                    | configuration file   | en_US.UTF-8
 log_line_prefix            | configuration file   | %t [%p-%l] %q%u@%d
 log_timezone               | configuration file   | PRC
 max_connections            | configuration file   | 100
 port                       | configuration file   | 5432
 shared_buffers             | configuration file   | 16384
 ssl                        | configuration file   | on
 ssl_cert_file              | configuration file   | /etc/ssl/certs/ssl-cert-snakeoil.pem
 ssl_key_file               | configuration file   | /etc/ssl/private/ssl-cert-snakeoil.key
 stats_temp_directory       | configuration file   | /var/run/postgresql/9.5-main.pg_stat_tmp
 TimeZone                   | configuration file   | PRC
 unix_socket_directories    | configuration file   | /var/run/postgresql
 max_stack_depth            | environment variable | 2048
(22 rows)

postgres=#

PostgreSQL扩展或模块可以通过手动下载源代码编译和在PGXN(PostgreSQL扩展网，网址为 http://pgxn.org/ )安装，也可以通过系统自带软件管理器下载安装。

管理员权限账号重新加载配置文件。

postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#

配置用户连接数。本文测试用户是test，请以本机实际情况为准。修改连接数不影响现有连接。

#-1为解除限制，该值可以超过数据库最大连接数，取决于实际需要
ALTER ROLE test CONNECTION LIMIT 1;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!