Postgresql服务管理

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了Postgresql服务管理，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

服务的启停和创建

启停

切换到bin目录下：

pg_ctl命令启动，重启，关机

[postgres@hserver1 bin]$ ./pg_ctl -D /usr/postgresql/psql/data start

server starting

[postgres@hserver1 bin]$ LOG: database system was shut down at 2017-10-13 02:40:55 CST

LOG: MultiXact member wraparound protections are now enabled

LOG: database system is ready to accept connections

LOG: autovacuum launcher started

postgres进程启动：

[postgres@hserver1 bin]$ ./postgres -D /usr/postgresql/psql/data &

[1] 20187

[postgres@hserver1 bin]$ LOG: database system was shut down at 2017-10-13 02:20:54 CST

LOG: MultiXact member wraparound protections are now enabled

LOG: database system is ready to accept connections

LOG: autovacuum launcher started

[postgres@hserver1 bin]$ ./pg_ctl -D /usr/postgresql/psql/data stop

LOG: received fast shutdown request

LOG: aborting any active transactions

FATAL: terminating connection due to administrator command

LOG: autovacuum launcher shutting down

LOG: shutting down

waiting for server to shut down....LOG: database system is shut down

done

server stopped

[postgres@hserver1 bin]$

-D用于指定数据目录；

停止数据库：

pg_ctl命令停止数据库的3种模式：

--智能关机模式 不常用 要用户主动断开数据库连接，数据库才会停止。

pg_ctl stop -D DATADIR -m smart

--快速关闭模式 常用 如果服务处于在线备份状态，将直接终止备份，导致此次的备份失败

pg_ctl stop -D DATADIR -m fast

--立即关闭模式 只在紧急的时候使用 不会妥善地关闭数据库系统，下次启动时会重放WAL日志进行恢复。

pg_ctl stop -D DATADIR -m immediate

pg_ctl

是一个使用工具，

(1)初始化Postgresql数据库实例

pg_ctl init[db] [-s] [-D datadir] [-o options]

-s:只打印错误和警告信息，不打印提示信息

-D datadir:指定数据库实例的数据库

-o options:直接传递给initdb的命令参数

pg_ctl init -D /usr/postgresql/psql/data

(2)启动、终止或重启Postgresqlsql数据库服务

pg_ctl start [-w] [-t seconds] [-s] [-D datadir] [-l filename] [-o options] [-p path][-c]

start:启动数据库实例

-w:等待启动完成

-t:等待启动完成的秒数，默认60s

-s：只打印错误和警告

-D datadir:指定数据库实例的数据目录

-l:把服务器日志输出附加在“filename”文件上，如果该文件不存在则创建它。

-o options:声明要直接传递给postgres的选项。

-p path:指定postgres可执行文件的位置。默认和pg_ctl目录相同，一般不用该参数

-c:提供服务器的软限制，允许数据库在有异常时产生一个coredump文件，以便问题定位和故障分析。

pg_ctl start -w -D /usr/postgresql/psql/data

pg_ctl stop [-W] [-t seconds] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate]]

-W：不等数据库停下来，命令就返回

-m:指定停止的模式

pg_ctl stop -D /usr/postgresql/psql/data -m f

重启：pg_ctl reload -D /usr/postgresql/psql/data

(3)查看Postgresql数据库服务状态

pg_ctl status -D usr/postgresql/psql/data

[postgres@hserver1 bin]$ ./pg_ctl status -D /usr/postgresql/psql/data

pg_ctl: server is running (PID: 20343)

/usr/postgresql/pgsql/bin/postgres "-D" "/usr/postgresql/psql/data"

(4)让数据库实例重读配置文件

(5)允许给一个指定的进程发送信号

postgres及单用户模式

在启动postgres程序时加上“--single”参数，这时postgres进程不会进入到后台服务模式，而是进入交互式命令模式。

postgres --single -D /usr/postgresql/psql/data

服务配置介绍

postgresql.conf

详细内容可见：

http://blog.csdn.net/zhaowenzhong/article/details/51899405

配置参数

所有的配置参数都在系统视图pg_settings中，

select * from pg_settings where name = 'client_min_messages';

select * from pg_settings where name = 'autovacumm_vacumm_cost_delay';

select * from pg_settings where name = 'client_min_messages';

参数的分类：

internal:只读参数，不能配置在postgresql.conf中，因为它们是由postgres程序写死的。

postmaster:在postgresql.conf中改变后，生效需要重启Postgresql实例。

sighup:在postgresql.conf中改变后，不需要重启数据库，只需要向postmaster进程发送SIGHUP信号，重新装载新的参数就可以了。

backend:

superuser:

user:


连接配置项：

listen_address:监听tcp/ip地址，改变后需要重启生效。

port：

max_connections:

superuser_reserved_connections:

unix_socket_directory:

unix_socket_group:

unix_socket_permissions:

内存配置项：

shared_buffers:属于共享内存，设置数控服务器将使用的共享内存缓冲区数量，每个缓冲区大小为8KB,默认值为4000。通常会将该值设置大些，如果物理内存的25%。

temp_buffers:属于本地内存，设置每个数据库会话使用的临时缓冲区的最大数目。默认值1000，每个会话可以用set命令改变此设置值，但必须在会话第一次使用临时表前设置，一旦使用临时表，再改变是无效的

work_mem:声明内部排序操作和hash表在开始使用临时磁盘文件总前可使用的内存数目。属于本地内存，默认值1024千字节（1MB）.

maintenance_work_mem:声明在维护性操作中使用的最大内存数。数值以千字节为单位，默认16MB.

max_stack_depth:服务器执行堆栈的最大安全深度。默认为2MB。

预写式日志的配置项

wal_level:

fsync:

synchronous_commit:

wal_sync_method:

wal_writer_delay:

commit_delay:

commit_siblings:

错误报告和日志项

访问控制文件配置文件

pg_hba.conf

认证方法介绍

备份和还原

物理备份

使用LVM快照进行热备份

常用的管理命令

查看系统信息的常用命令

（1）查看当前数据库实例版本

testdb2=# select version();

version

----------------------------------------------------------------------------------------------------------

Postgresql 9.6.5 on x86_64-pc-linux-gnu,compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16),64-bit

(1 row)

(2)查看数据库的启动时间

testdb2=# select pg_postmaster_start_time();

pg_postmaster_start_time

-------------------------------

2017-10-13 02:41:26.036051+08

(1 row)

(3)查看最后load配置文件时间

testdb2=# select pg_conf_load_time();

pg_conf_load_time

-------------------------------

2017-10-13 02:41:26.013405+08

(1 row)

(4)使用pg_ctl reload改变配置的装载时间

[postgres@hserver1 ~]$ cd /usr/postgresql/pgsql/bin/

[postgres@hserver1 bin]$ ./pg_ctl reload

server signaled

[postgres@hserver1 bin]$ ./psql

psql.bin (9.6.5)

Type "help" for help.

postgres=# select pg_conf_load_time();

pg_conf_load_time

-------------------------------

2017-10-14 00:47:34.008262+08

(1 row)

postgres=# show timezone;

TimeZone

----------

PRC

(1 row)

(5)查看当前实例中有哪些数据库

[postgres@hserver1 bin]$ ./psql -l

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-----------+-----------+----------+-------------+-------------+-----------------------

postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |

template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

testdb | luxuefeng | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |

testdb2 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |

(5 rows)

testdb2-# \l

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-----------+-----------+----------+-------------+-------------+-----------------------

postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |

template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

testdb | luxuefeng | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |

testdb2 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |

(5 rows)

（6）查看当前用户名

testdb2=# select user;

current_user

--------------

postgres

(1 row)

（7）查看session用户

testdb2=# select session_user;

session_user

--------------

postgres

(1 row)

（8）查询当前连接的数据库名称

testdb2=# select current_catalog,current_database();

current_database | current_database

------------------+------------------

testdb2 | testdb2

(1 row)

(9)查询当前session所在客户端的ip地址和端口

testdb2=# select inet_client_addr(),inet_client_port();

inet_client_addr | inet_client_port

------------------+------------------

|

(1 row)

（10）查询当前数据库服务器的I地址和端口

testdb2=# select inet_server_addr(),inet_server_port();

inet_server_addr | inet_server_port

------------------+------------------

|

(1 row)

（11）查询当前session的后台服务进程的pid

testdb2=# select pg_backend_pid();

pg_backend_pid

----------------

28902

(1 row)

通过操作系统命令查看此后台进程

[postgres@hserver1 bin]$ ps -ef|grep 28902 |grep -v grep

postgres 28902 20343 0 00:41 ? 00:00:00 postgres: postgres testdb2 [local] idle

testdb2=# select pg_backend_pid();

pg_backend_pid

----------------

28902

(1 row)

（12）查看当前的一些参数配置

testdb2=# show shared_buffers;

shared_buffers

----------------

128MB

(1 row)

testdb2=# select current_setting('shared_buffers');

current_setting

-----------------

128MB

(1 row)

(13)查看当前正在写的WAL文件

testdb2=# select pg_xlogfile_name(pg_current_xlog_location());

pg_xlogfile_name

--------------------------

000000010000000000000006

(1 row)

（14）查看当前wal的buffer中还有多少字节的数据没有写到磁盘

testdb2=# select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location());

pg_xlog_location_diff

-----------------------

0

(1 row)

（15）查看数据库实例是否正在做基础备份

testdb2=# select pg_is_in_backup(),pg_backup_start_time();

pg_is_in_backup | pg_backup_start_time

-----------------+----------------------

f |

(1 row)

(16)查看数据库大小 （如果数据库有很多表，将变得很慢）

testdb2=# select pg_database_size('testdb2'),pg_size_pretty(pg_database_size('testdb2'));

pg_database_size | pg_size_pretty

------------------+----------------

61684248 | 59 MB

(1 row)

（17）查看表的大小

testdb2=# select pg_size_pretty(pg_relation_size('test'));

pg_size_pretty

----------------

8192 bytes

(1 row)

testdb2=# select pg_size_pretty(pg_total_relation_size('test')); --包括索引的大小

pg_size_pretty

----------------

32 kB

(1 row)

（17）查看索引的大小

testdb2=# select pg_size_pretty(pg_indexes_size('test'));

pg_size_pretty

----------------

16 kB

(1 row)

（18）查看表空间的大小

testdb2=# select pg_size_pretty(pg_tablespace_size('pg_default'));

pg_size_pretty

----------------

87 MB

(1 row)

testdb2=# select pg_size_pretty(pg_tablespace_size('pg_global'));

pg_size_pretty

----------------

497 kB

(1 row)

（19）查看表对应的数据文件

testdb2=# select pg_relation_filepath('test');

pg_relation_filepath

----------------------

base/16389/16390

(1 row)

系统维护常用命令

1.修改配置文件postgresql.conf后让其生效的方法

（1）在操作系统中使用 pg_ctl reload

（2）在psql中使用 select pg_reload_conf(); --如果需要重启数据库的配置项，该操作不会生效

testdb2=# select pg_reload_conf();

pg_reload_conf

----------------

t

(1 row)

2.切换log日志文件到下一个的命令：select pg_rotate_logfile();

testdb2=# select pg_rotate_logfile();

WARNING: rotation not possible because log collection not active

pg_rotate_logfile

-------------------

f

(1 row)

3.切换wal日志文件

testdb2=# select pg_switch_xlog();

pg_switch_xlog

----------------

0/6119358

(1 row)

4.手工产生一次checkpoint

testdb2=# checkpoint;

CHECKPOINT

testdb2=#

5.取消一个正在长时间执行的sql。

pg_cancel_backend(pid):取消一个正在执行的sql。

pg_terminate_backend(pid):终止一个后台服务进程，同时释放此后台服务进程的资源。

通常是先查询pg_stat_activity,试图找出长时间运行的sql，然后 pg_cancel_backend()取消这个sql,如果取消不了，再使用 pg_terminate_backend()

testdb2=# select pid,usename,query_start,query from pg_stat_activity;

pid | usename | query_start | query

-------+----------+-------------------------------+--------------------------------------------------------------------------

28904 | postgres | 2017-10-14 00:42:04.916995+08 | SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype+

| | | FROM pg_type WHERE oid=25

28902 | postgres | 2017-10-14 04:42:25.394546+08 | select pid,query from pg_stat_activity;

28984 | postgres | 2017-10-14 00:49:37.529914+08 | select now();

猜你在找的Postgre SQL相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1. 标识符和关键词 SQL标识符和关键...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1. 数字类型 数字类型由2、4或8字节的...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1. 表基础 SQL并不保证表中行的顺序。...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4. 从修改的行中返回数据 有时在修改行...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1. 读已提交隔离级别 读已提交是Po...
PostgreSQL安装和使用
青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
PostgreSQL整体架构
 
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
专访唐成：从小工到专家，PostgreSQL的修炼之道
唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问