create table test as select generate_series(1, 1000000) as id, cast(random() as text) as info , now() as crt_time;
mydb=# create table test as select generate_series(1, 1000000) as id, cast(random() as text) as info , now() as crt_time;
SELECT 1000000
mydb=# \d test
Table "public.test"
Column | Type | Modifiers
----------+--------------------------+-----------
id | integer |
info | text |
crt_time | timestamp with time zone |
mydb=> select pg_relation_filepath('test1');
pg_relation_filepath
----------------------------------------------
pg_tblspc/16474/PG_9.5_201510051/16475/16477
(1 row)
mydb=> select pg_size_pretty(pg_relation_size('test1'));
pg_size_pretty
----------------
5888 kB
(1 row)
select oid, * from pg_tablespace where oid in (select dattablespace from pg_database where datname='mydb');
postgres=# create tablespace mydb location '/var/lib/pgsql/mydb_tbspace';
CREATE TABLESPACE
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+-----------------------------
mydb | postgres | /var/lib/pgsql/mydb_tbspace
pg_default | postgres |
pg_global | postgres |
(3 rows)
postgres=# create database mydb encoding='utf-8' tablespace = mydb;
CREATE DATABASE
postgres=# select oid, * from pg_tablespace where oid in (select dattablespace from pg_database where datname='mydb');
oid | spcname | spcowner | spcacl | spcoptions
-------+---------+----------+--------+------------
16474 | mydb | 10 | |
(1 row)
select version();
查看数据库的启动时间：
select pg_postmaster_start_time();
查看最后load配置文件的时间：
select pg_conf_load_time();
显示当前数据库时区：
show timezone;
postgres=# show timezone;
TimeZone
----------
PRC
(1 row)
postgres=# select now();
now
-------------------------------
2015-07-10 16:27:59.692621+08
(1 row)
显示实例中有哪些数据库：
postgres=# \l 或者 [postgre@pg-1 ~]$ psql -l
查看当前用户名：
select user;
或者
select current_user;
查看session用户：
select session_user;
查询当前连接的数据库名称：
postgres=# select current_catalog,current_database();
current_database | current_database
------------------+------------------
postgres | postgres
(1 row)
查询当前session所在客户端的IP地址及端口：
select inet_client_addr(),inet_client_port();
查询到当前数据库服务器的IP地址及端口：
select inet_server_addr(),inet_server_port();
查询当前session的后台服务进程的pid:
postgres=# select pg_backend_pid();
pg_backend_pid
----------------
12504
(1 row)
通过操作系统命令看下此后台进程：
[root@pg-1 postgresql-9.4.4]# ps -ef |grep 12504 |grep -v grep
postgre 12504 1590 0 16:34 ? 00:00:00 postgres: postgre postgres [local] idle
查看当前正在写的WAL文件：
postgres=# select pg_xlogfile_name(pg_current_xlog_location());
pg_xlogfile_name
--------------------------
000000010000000000000001
(1 row)
查看当前WAL的buffer中还有多少字节的数据没有写到磁盘中：
postgres=# select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location());
pg_xlog_location_diff
-----------------------
0
(1 row)
查看数据库实例是否在做基础备份：
postgres=# select pg_is_in_backup(),pg_backup_start_time();
pg_is_in_backup | pg_backup_start_time
-----------------+----------------------
f |
(1 row)
查看当前数据库实例是Hot Standby状态还是正常数据库状态：
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
查看数据库大小：
postgres=# select pg_database_size('pgtest'),pg_size_pretty(pg_database_size('pgtest'));
pg_database_size | pg_size_pretty
------------------+----------------
6998688 | 6835 kB
(1 row)
注：如果数据库中有很多表，使用上述命令将比较慢，也可能对当前系统产生不利的影响。pg_size_pretty()函数会把数字以MB、GB等格式显示出来，这样会更直观。
查看表的大小：
pgtest=# select pg_size_pretty(pg_relation_size('test01'));
pg_size_pretty
----------------
8192 bytes
(1 row)
注：仅计算表的大小，不包括索引的大小；
pgtest=# select pg_size_pretty(pg_total_relation_size('test01'));
pg_size_pretty
----------------
24 kB
(1 row)
注：包括索引大小
查看表上所有索引的大小：
pgtest=# select pg_size_pretty(pg_indexes_size('test01'));
pg_size_pretty
----------------
16 kB
(1 row)
查看表空间的大小：
pgtest=# select pg_size_pretty(pg_tablespace_size('pg_global'));
pg_size_pretty
----------------
437 kB
(1 row)
查看表对应的数据文件：
pgtest=# select pg_relation_filepath('test01');
pg_relation_filepath
----------------------
base/16403/16410
(1 row)
修改postgresql.conf后，让修改生效的方法有两种：
1）在操作系统下使用：pg_ctl reload
2) 在psql中使用：select pg_reload_conf();
pgtest=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
pgtest=# select pg_conf_load_time();
pg_conf_load_time
-------------------------------
2015-07-10 17:44:45.620045+08
(1 row)
注：如果需要重启数据库服务的配置项，修改后使用上面的方法并不能使之生效。
切换log日志到下一个的命令：
postgres=# select pg_rotate_logfile();
pg_rotate_logfile
-------------------
t
(1 row)
切换WAL日志文件：
postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/17AF678
(1 row)
收工生成一次checkpoint:
postgres=# checkpoint;
CHECKPOINT
取消一个正在长时间执行的SQL方法：
有两个函数可以完成这个功能：
1）pg_cancel_backend(pid):取消一个正在执行的SQL；
2）pg_terminate_backend(pid):终止一个后台服务进程，同时释放此后台服务进程的资源。
这两个函数的区别是：pg_cancel_backend()函数实际上是给正在执行的SQL任务配置一个取消标志，正在执行的任务是在合适的时候检测到此标志后主动退出；但如果这个任务没有主动检测到这个标志，则该任务就无法正常退出，这时需要使用pg_terminate_backend()命令在终止SQL的执行。
通常是先查询pg_stat_activity,试图找出长时间运行的SQL：
postgres=# select pid,usename,query_start,query from pg_stat_activity ;
pid | usename | query_start | query
------+---------+-------------------------------+--------------------------------------------------------------
1651 | postgre | 2015-07-11 15:35:39.745907+08 | select pid,usename,query_start,query from pg_stat_activity ;
1722 | postgre | 2015-07-11 15:35:17.114117+08 | select pg_sleep(100);
(2 rows)
然后在使用pg_cancel_backend()取消这个SQL，如果pg_cancel_backend()取消不了，再使用pg_terminate_backend():
postgres=# select pid,usename,query_start,query from pg_stat_activity ;
pid | usename | query_start | query
------+---------+-------------------------------+--------------------------------------------------------------
1651 | postgre | 2015-07-11 15:40:11.085173+08 | select pid,usename,query_start,query from pg_stat_activity ;
1747 | postgre | 2015-07-11 15:40:04.277972+08 | select pg_sleep(100);
(2 rows)
postgres=# select pg_cancel_backend(1747);
pg_cancel_backend
-------------------
t
(1 row)
postgres=# select pid,usename,query_start,query from pg_stat_activity ;
pid | usename | query_start | query
------+---------+-------------------------------+--------------------------------------------------------------
1651 | postgre | 2015-07-11 15:40:26.885772+08 | select pid,usename,query_start,query from pg_stat_activity ;
1747 | postgre | 2015-07-11 15:40:04.277972+08 | select pg_sleep(100);
(2 rows)
postgres=# select pg_terminate_backend(1747);
pg_terminate_backend
----------------------
t
(1 row)
postgres=# select pid,usename,query_start,query from pg_stat_activity ;
pid | usename | query_start | query
------+---------+-------------------------------+--------------------------------------------------------------
1651 | postgre | 2015-07-11 15:41:09.413959+08 | select pid,usename,query_start,query from pg_stat_activity ;
(1 row)
[postgre@pg-1 pg_log]$ pg_basebackup -D backup -Ft -z -P
pg_basebackup: could not connect to server: FATAL: number of requested standby connections exceeds max_wal_senders (currently 0)
由于没有设置max_wal_senders参数，修改postgresql.conf
max_wal_senders = 2
wal_level = hot_standby
