PostgreSQL12.8 创建数据库实例，并启动停止服务的三种方法

在PostgreSQL中一个数据库实例和一组使用相同配置文件和监听端口的数据库集关联，它由数据目录组成，数据目录中包含了所有的数据文件和配置文件。一台数据库服务器可以管理多个数据库实例，PostgreSQL通过数据目录的位置和这个数据集合实例的端口号引用它。

1.创建操作系统用户

在创建数据库实例之前首先要创建一个独立的操作系统账号，目的是防止应用软件的BUG被攻击，对系统造成破坏。它拥有该数据库实例管理的所有数据，是这个数据库实例的超级用户。可以使用任何您喜欢的名字。但通常使用postgres作为操作系统超级用户名，这个用户被用来对数据库实例进行start/stop/restart操作。

yum 安PostgresQL时会自动创建用户postgres，仍建议预先手动创建postgres用户。

创建系统用户组和用户的命令如下所示：

groupadd -g 1000 postgres

useradd -g 1000 -u 1000 postgres

如果是集群账号，建议用户uid和gid一致。修改的命令是：

groupmod -g 1000 postgres

usermod -u 1000 -g 1000 postgres

2.创建数据目录

数据目录，顾名思义就是存放数据文件与配置文件的位置。

mkdir -p /pgdata/12.8/{data,backup,scripts,archive_wals}

将目录属组改为创建的操作系统用户，修改目录权限0700这一步其实并不需要，因为initdb会回收PostgreSQL之外所有用户的访问权限。

chown -R postgres.postgres /pgdata/12.8

chmod 0700 /pgdata/12.8/data

[root@pg02 ~]# groupmod -g 1000 postgres

groupmod: group 'postgres' does not exist

[root@pg02 ~]# groupadd -g 1000 postgres

[root@pg02 ~]# useradd -g 1000 -u 1000 postgres

[root@pg02 ~]# chown -R postgres.postgres /pgdata/12.8

chmod 0700 /pgdata/12.8/data[root@pg02 ~]# chmod 0700 /pgdata/12.8/data

[root@pg02 ~]#

[root@pg02 ~]# tree -L 3 /pgdata

/pgdata

└── 12.8

    ├── archive_wals

    ├── backup

    ├── data

    └── scripts

5 directories, 0 files

3.初始化数据目录

实例化数据目录使用initdb工具。initdb工具将创建一个数据库目录，创建template1和postgres数据库，初始化该数据库实例的默认区域和字符编码。initdb命令的语法如下所示：

template1是生成其它数据库的模板，postgres数据库是一个默认数据库，用于给用户、工具或第三方应用提供默认数据库。

[postgres@pg02 ~]$ initdb --help
initdb initializes a PostgreSQL database cluster.

Usage:
  initdb [OPTION]... [DATADIR]

Options:
  -A, --auth=METHOD         default authentication method for local connections
      --auth-host=METHOD    default authentication method for local TCP/IP connections
      --auth-local=METHOD   default authentication method for local-socket connections
[-D, --pgdata=]DATADIR     location for this database cluster
  -E, --encoding=ENCODING   set default encoding for new databases
  -g, --allow-group-access  allow group read/execute on data directory
      --locale=LOCALE       set default locale for new databases
      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE
      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE
                            set default locale in the respective category for
                            new databases (default taken from environment)
      --no-locale           equivalent to --locale=C
      --pwfile=FILE         read password for the new superuser from file
  -T, --text-search-config=CFG
                            default text search configuration
  -U, --username=NAME       database superuser name
  -W, --pwprompt            prompt for a password for the new superuser
  -X, --waldir=WALDIR       location for the write-ahead log directory
      --wal-segsize=SIZE    size of WAL segments, in megabytes

Less commonly used options:
  -d, --debug               generate lots of debugging output
  -k, --data-checksums      use data page checksums
  -L DIRECTORY              where to find the input files
  -n, --no-clean            do not clean up after errors
  -N, --no-sync             do not wait for changes to be written safely to disk
  -s, --show                show internal settings
  -S, --sync-only           only sync data directory

Other options:
  -V, --version             output version information, then exit
  -?, --help                show this help, then exit

If the data directory is not specified, the environment variable PGDATA
is used.

Report bugs to <pgsql-bugs@lists.postgresql.org>.

知道选项意义后，开始初始化上一步建好的数据目录，如下所示：

[postgres@pg02 ~]$ initdb -D /pgdata/12.8/data -W
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.


The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".


Data page checksums are disabled.


Enter new superuser password:
Enter it again:


fixing permissions on existing directory /pgdata/12.8/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Los_Angeles
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.


Success. You can now start the database server using:


    pg_ctl -D /pgdata/12.8/data -l logfile start

还可以使用pg_ctl工具进行数据库目录的初始化，用法如下：

pg_ctl -D /pgdata/12.8/data -o "-W"

4.启动和停止数据库服务器

手动安装，需要设置启动服务，将源码包contrib目录中服务脚本中配置项设置成postgresql实际安装：

# Installation prefix

prefix=/opt/pgsql

# Data directory

PGDATA="/pgdata/12.8/data"

将脚本拷贝到/etc/init.d目录中，并重新命名：

cp linux /etc/init.d/postgresql-12

chmod +x /etc/init.d/postgresql-12

设置开机启动：

chkconfig postgresql-12 on
chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

postgresql-12   0:off   1:off   2:on    3:on    4:on    5:on    6:off

[root@pg02 start-scripts]# systemctl list-dependencies|grep post
● ├─postgresql-12.service

服务起停

[root@pg02 start-scripts]# service postgresql-12 stop
Stopping PostgreSQL: ok
[root@pg02 start-scripts]# service postgresql-12 start
Starting PostgreSQL: ok
[root@pg02 start-scripts]# systemctl stop  postgresql-12
[root@pg02 start-scripts]# systemctl start postgresql-12
[root@pg02 start-scripts]# systemctl status postgresql-12
● postgresql-12.service - SYSV: PostgreSQL RDBMS
   Loaded: loaded (/etc/rc.d/init.d/postgresql-12; generated)
   Active: active (exited) since Sat 2021-10-09 20:50:30 PDT; 4s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 70779 ExecStart=/etc/rc.d/init.d/postgresql-12 start (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 11221)
   Memory: 0B
   CGroup: /system.slice/postgresql-12.service


Oct 09 20:50:30 pg02 systemd[1]: Starting SYSV: PostgreSQL RDBMS...
Oct 09 20:50:30 pg02 su[70780]: (to postgres) root on none
Oct 09 20:50:30 pg02 su[70780]: pam_unix(su-l:session): session opened for user postgres by (uid=0)
Oct 09 20:50:30 pg02 su[70780]: pam_unix(su-l:session): session closed for user postgres
Oct 09 20:50:30 pg02 postgresql-12[70779]: Starting PostgreSQL: ok
Oct 09 20:50:30 pg02 systemd[1]: Started SYSV: PostgreSQL RDBMS.

第二种起停服务方法：pg_ctl

1.启动数据库

 [root@pg02 start-scripts]# su - postgres

[postgres@pg02 ~]$ /opt/pgsql/bin/pg_ctl -D /pgdata/12.8/data start
waiting for server to start....2021-10-09 20:55:33.855 PDT [70942] LOG:  starting PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit
2021-10-09 20:55:33.855 PDT [70942] LOG:  listening on IPv6 address "::1", port 1921
2021-10-09 20:55:33.855 PDT [70942] LOG:  listening on IPv4 address "127.0.0.1", port 1921
2021-10-09 20:55:33.857 PDT [70942] LOG:  listening on Unix socket "/tmp/.s.PGSQL.1921"
2021-10-09 20:55:33.878 PDT [70943] LOG:  database system was shut down at 2021-10-09 20:55:29 PDT
2021-10-09 20:55:33.879 PDT [70942] LOG:  database system is ready to accept connections
done
server started

2.查看数据库运行状态

[postgres@pg02 ~]$ /opt/pgsql/bin/pg_ctl -D /pgdata/12.8/data status
pg_ctl: server is running (PID: 70942)
/opt/pg12/bin/postgres "-D" "/pgdata/12.8/data"
[postgres@pg02 ~]$
[postgres@pg02 ~]$
[postgres@pg02 ~]$ /opt/pgsql/bin/pg_isready -p 1921
/tmp:1921 - accepting connections
[postgres@pg02 ~]$

3.停止数据库

参数-m控制数据库停止模式 smart fast immediate；

smart 模式会等待活动的事务提交结束，并等待客户端主动断开连接之后关闭数据库

fast 模式则会回滚所有活动的事务，并强制断客户端的连接之后关闭数据库

immediate 模式立即终止所有服务器进程，当下一次数据库启动时它会首先进入恢复状态，一般不推荐使用。

-s参数控制屏幕上的消息输出；-t 参数设置超时时间，超时设置时间自动退出。

[postgres@pg02 ~]$ /opt/pgsql/bin/pg_ctl -D /pgdata/12.8/data -mf top
pg_ctl: unrecognized operation mode "top"
Try "pg_ctl --help" for more information.
[postgres@pg02 ~]$ /opt/pgsql/bin/pg_ctl -D /pgdata/12.8/data -mf stop
waiting for server to shut down....2021-10-09 21:04:08.029 PDT [70942] LOG:  received fast shutdown request
2021-10-09 21:04:08.029 PDT [70942] LOG:  aborting any active transactions
2021-10-09 21:04:08.030 PDT [70942] LOG:  background worker "logical replication launcher" (PID 70949) exited with exit code 1
2021-10-09 21:04:08.030 PDT [70944] LOG:  shutting down
2021-10-09 21:04:08.035 PDT [70942] LOG:  database system is shut down
done
server stopped

第三种启停服务方法：

使用postgmaster或postgres程序启动数据库：&符号让它在后台运行。

启动服务：

[postgres@pg02 ~]$ /opt/pgsql/bin/postgres -D /pgdata/12.8/data/
2021-10-09 21:06:30.893 PDT [71076] LOG:  starting PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit
2021-10-09 21:06:30.893 PDT [71076] LOG:  listening on IPv6 address "::1", port 1921
2021-10-09 21:06:30.893 PDT [71076] LOG:  listening on IPv4 address "127.0.0.1", port 1921
2021-10-09 21:06:30.894 PDT [71076] LOG:  listening on Unix socket "/tmp/.s.PGSQL.1921"
2021-10-09 21:06:30.902 PDT [71077] LOG:  database system was shut down at 2021-10-09 21:04:08 PDT
2021-10-09 21:06:30.903 PDT [71076] LOG:  database system is ready to accept connections
^C2021-10-09 21:06:34.591 PDT [71076] LOG:  received fast shutdown request
2021-10-09 21:06:34.592 PDT [71076] LOG:  aborting any active transactions
2021-10-09 21:06:34.592 PDT [71076] LOG:  background worker "logical replication launcher" (PID 71083) exited with exit code 1
2021-10-09 21:06:34.593 PDT [71078] LOG:  shutting down
2021-10-09 21:06:34.597 PDT [71076] LOG:  database system is shut down
[postgres@pg02 ~]$
[postgres@pg02 ~]$
[postgres@pg02 ~]$ /opt/pgsql/bin/postgres -D /pgdata/12.8/data/ &
[1] 71084
[postgres@pg02 ~]$ 2021-10-09 21:06:38.625 PDT [71084] LOG:  starting PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit
2021-10-09 21:06:38.625 PDT [71084] LOG:  listening on IPv6 address "::1", port 1921
2021-10-09 21:06:38.625 PDT [71084] LOG:  listening on IPv4 address "127.0.0.1", port 1921
2021-10-09 21:06:38.638 PDT [71084] LOG:  listening on Unix socket "/tmp/.s.PGSQL.1921"
2021-10-09 21:06:38.646 PDT [71085] LOG:  database system was shut down at 2021-10-09 21:06:34 PDT
2021-10-09 21:06:38.648 PDT [71084] LOG:  database system is ready to accept connections

停止服务：

对SIGINT/SIGTERM/SIGQUIT处理方式分别对应Postgresql的三种关闭方式smart，fast，immediate。

[postgres@pg02 data]$ kill -sigterm `head -1 /pgdata/12.8/data/postmaster.pid`
[postgres@pg02 data]$ 2021-10-09 21:14:10.797 PDT [71084] LOG:  received smart shutdown request
2021-10-09 21:14:10.799 PDT [71084] LOG:  background worker "logical replication launcher" (PID 71091) exited with exit code 1
2021-10-09 21:14:10.799 PDT [71086] LOG:  shutting down
2021-10-09 21:14:10.817 PDT [71084] LOG:  database system is shut down


[1]+  Done                    /opt/pgsql/bin/postgres -D /pgdata/12.8/data/  (wd: ~)
(wd now: /pgdata/12.8/data)

至此，Postgresql实例创建与服务管理结束。