PG进入单用户模式

进入单用户模式

• 停止PG服务器

如果不停PG，则无法通过单用户模式进入到PG

-bash-4.1$ /home/postgres/pgsql/bin/postgres --single -D /mnt/syncdata/pgsql/data/ postgres
<2020-04-26 19:06:40.446 CST 9177 >FATAL:  lock file "postmaster.pid" already exists
<2020-04-26 19:06:40.446 CST 9177 >HINT:  Is another postmaster (PID 9065) running in data directory "/mnt/syncdata/pgsql/data"?
-bash-4.1$
-bash-4.1$ ps -ef |grep postm
postgres  9065     1  0 19:06 ?        00:00:00 /home/postgres/pgsql/bin/postmaster -p 5432 -D /mnt/syncdata/pgsql/data
postgres  9192  9086  0 19:06 pts/0    00:00:00 grep postm
-bash-4.1$
-bash-4.1$
-bash-4.1$ /home/postgres/pgsql/bin/pg_ctl stop -D /mnt/syncdata/pgsql/data/
waiting for server to shut down.... done
server stopped
-bash-4.1$
-bash-4.1$ ps -ef |grep postm
postgres  9209  9086  0 19:07 pts/0    00:00:00 grep postm
-bash-4.1$
-bash-4.1$ /home/postgres/pgsql/bin/postgres --single -D /mnt/syncdata/pgsql/data/ postgres

PostgreSQL stand-alone backend 9.4.20
backend>
<2020-04-26 19:07:11.462 CST 9211 >LOG:  statement:

一般进入单用户模式我们会对具体的某个数据库进行整理

backend> vacuum freeze;
<2020-04-26 19:07:18.175 CST 9211 >LOG:  statement: vacuum freeze;

backend> -bash-4.1$ /home/postgres/pgsql/bin/postgres --single -D /mnt/syncdata/pgsql/data/ postgres

PostgreSQL stand-alone backend 9.4.20
backend> -bash-4.1$ /home/postgres/pgsql/bin/postgres --single -D /mnt/syncdata/pgsql/data/ imos

PostgreSQL stand-alone backend 9.4.20
backend> vacuum freeze;
<2020-04-26 19:07:30.427 CST 9240 >LOG:  statement: vacuum freeze;

backend> vacuum full;
<2020-04-26 19:07:37.494 CST 9240 >LOG:  statement: vacuum full;

<2020-04-26 19:08:54.763 CST 9240 >LOG:  duration: 77269.638 ms
backend> <2020-04-26 19:08:54.763 CST 9240 >LOG:  statement:

backend> <2020-04-26 19:08:54.763 CST 9240 >LOG:  statement:

• 可以看到 使用 vacuum full; 清理比 vacuum freeze; 用时要多得多；
• vacuum freeze主要是为了解决事务ID回卷问题
• vacuum 则包含了vacuum freeze的功能，并且做了额外的清理工作