postgresql 数据库的单用户模式(single mode)

最新推荐文章于 2025-03-10 14:09:19 发布

数据库人生

最新推荐文章于 2025-03-10 14:09:19 发布

阅读量2.9k

点赞数

分类专栏： # postgresql basic 文章标签： postgres pg_ctl singel mode vacuum freeze

本文链接：https://blog.csdn.net/ctypyb2002/article/details/104948585

版权

postgresql basic 专栏收录该内容

58 篇文章

订阅专栏

os: centos 7.4
db: postgresql 10.10

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# su - postgres
Last login: Sat Oct 26 22:55:25 CST 2019 on pts/0
$
$ psql -c "select version();"
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 10.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

single mode

关闭 postgresql

$ /usr/pgsql-10/bin/pg_ctl stop -m fast -D /var/lib/pgsql/10/data/

单用户模式启动 postgresql，在启动的时候指定具体的数据库 postgres

$ /usr/pgsql-10/bin/postgres --single -D /var/lib/pgsql/10/data/ postgres

PostgreSQL stand-alone backend 10.11
backend> select current_database();
2020-03-18 15:27:06.204 CST [19938] LOG:  statement: select current_database();
	
	 1: current_database	(typeid = 19, len = 64, typmod = -1, byval = f)
	----
	 1: current_database = "postgres"	(typeid = 19, len = 64, typmod = -1, byval = f)
	----
backend> vacuum full verbose;

在启动的时候指定具体的数据库 peiybdb

$ /usr/pgsql-10/bin/postgres --single -D /var/lib/pgsql/10/data/ peiybdb

backend> select current_database();
2021-08-11 23:37:25.988 CST [1911] LOG:  statement: select current_database();
	
	 1: current_database	(typeid = 19, len = 64, typmod = -1, byval = f)
	----
	 1: current_database = "peiybdb"	(typeid = 19, len = 64, typmod = -1, byval = f)
	----

backend> select txid_current();
2021-08-11 23:37:38.204 CST [1911] LOG:  statement: select txid_current();
	
	 1: txid_current	(typeid = 20, len = 8, typmod = -1, byval = t)
	----
	 1: txid_current = "2020208"	(typeid = 20, len = 8, typmod = -1, byval = t)
	----

最重要就是执行 vacuum freeze

backend> vacuum freeze;
2021-08-11 23:37:55.325 CST [1911] LOG:  statement: vacuum freeze;
	
backend> select datname,datfrozenxid from pg_database;
2021-08-11 23:38:09.472 CST [1911] LOG:  statement: select datname,datfrozenxid from pg_database;
	
	 1: datname	(typeid = 19, len = 64, typmod = -1, byval = f)
	 2: datfrozenxid	(typeid = 28, len = 4, typmod = -1, byval = t)
	----
	 1: datname = "postgres"	(typeid = 19, len = 64, typmod = -1, byval = f)
	 2: datfrozenxid = "797"	(typeid = 28, len = 4, typmod = -1, byval = t)
	----
	 1: datname = "template1"	(typeid = 19, len = 64, typmod = -1, byval = f)
	 2: datfrozenxid = "548"	(typeid = 28, len = 4, typmod = -1, byval = t)
	----
	 1: datname = "template0"	(typeid = 19, len = 64, typmod = -1, byval = f)
	 2: datfrozenxid = "548"	(typeid = 28, len = 4, typmod = -1, byval = t)
	----
	 1: datname = "pgbenchdb"	(typeid = 19, len = 64, typmod = -1, byval = f)
	 2: datfrozenxid = "699981"	(typeid = 28, len = 4, typmod = -1, byval = t)
	----
	 1: datname = "peiybdb"	(typeid = 19, len = 64, typmod = -1, byval = f)
	 2: datfrozenxid = "2020209"	(typeid = 28, len = 4, typmod = -1, byval = t)
	----
	 1: datname = "contrib_regression"	(typeid = 19, len = 64, typmod = -1, byval = f)
	 2: datfrozenxid = "548"	(typeid = 28, len = 4, typmod = -1, byval = t)
	----
	 1: datname = "yewudb"	(typeid = 19, len = 64, typmod = -1, byval = f)
	 2: datfrozenxid = "548"	(typeid = 28, len = 4, typmod = -1, byval = t)
	----

使用 Ctrl + D退出单用户模式

参考：