Shulou(Shulou.com)06/01 Report--

1. Initialize the database cluster

As with other RDBMS, you need to initialize a database on disk, which is called a database cluster, before you start using a PostgreSQL database. A database cluster is a collection of databases managed by running database service instances. After initialization, the cluster contains a database named postgres as the default database. Another database called template1 is also created, which is used as a template for subsequent database creation.

At the file system level, a database cluster is a data directory where all data is stored. It depends on where you choose to store your data. The default directory is / usr/local/pgsql/data or / var/lib/pgsql/data.

Initialize the database cluster with the iniddb command and specify the path to the database with the-D parameter. Examples are as follows:

Initdb-D / usr/local/pgsql/data

[postgres@rhel7 ~] $initdb-D / usr/local/pgsql/dataThe 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" default database encoding has accordingly been set to "UTF8" default text search configuration will be set to "english" .data page checksums are disabled.fixing permissions on existing directory / usr/local/pgsql/data. Okcreating subdirectories... Okselecting default max_connections... 100selecting default shared_buffers... 128MBselecting dynamic shared memory implementation... Posixcreating configuration files... Okrunning bootstrap script... Okperforming post-bootstrap initialization... Oksyncing data to disk... OkWARNING: enabling "trust" authentication for local connectionsYou 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 / usr/local/pgsql/data-l logfile start

You can specify that the environment variable PGDATA points to the directory of PostgreSQL.

You can also call the pg_ctl command to initialize the database cluster:

Pg_ctl-D / usr/local/pgsql/data initdb

The specified directory must be empty, otherwise it cannot be initialized. After initialization, the permissions of the entire directory change to 700 (drwx-).

[postgres@rhel7 pgsql] $ls-ltotal 4drwx-19 postgres postgres 4096 Mar 23 16:31 data

2. Start the database service

The database service program is called postgres. You must specify a data directory when you start the database. A simple example:

Postgres-D / usr/local/pgsql/data

[postgres@rhel7 data] $postgres-D / usr/local/pgsql/data/LOG: database system was shut down at 2017-03-23 16:31:28 CSTLOG: MultiXact member wraparound protections are now enabledLOG: database system is ready to accept connectionsLOG: autovacuum launcher started

If you do not specify the-D parameter, the command goes to the PGDATA environment variable, and if there are none of them, start an error report.

The above command starts the database service in the foreground, but it is best to start it in the background. Example of background startup:

Postgres-D / usr/local/pgsql/data > logfile 2 > & 1 &

Another encapsulated command, pg_ctl, can also provide the corresponding functionality. Such as the following example:

Pg_ctl start-l logfile

[postgres@rhel7 data] $pg_ctl-D / usr/local/pgsql/data start-l logfileserver starting [postgres@rhel7 data] $cat logfile LOG: database system was shut down at 2017-03-23 16:34:12 CSTLOG: MultiXact member wraparound protections are now enabledLOG: database system is ready to accept connectionsLOG: autovacuum launcher started

The above command enables the database service in the background and writes the output to the log file. The-D parameter is also used to specify the data directory. Pg_ctl can also be used to stop database services.

After the service starts, the corresponding PID is recorded in the postmaster.pid file of the data directory. To prevent multiple starts, can also be used to shut down the service.

3. Shut down the database service

There are several modes of shutting down the PostgreSQL database. The main categories are as follows:

SIGTERM

Smart Shutdown mode. After the database is connected to the SIGTERM, the server does not allow new connections, but the connected session continues to work until the session is complete. Do not shut down the database until all sessions are complete. If the database is in a hot standby state, it will wait for the backup to complete. If you are in the recovery state, wait for all processes to terminate.

SIGINT

Fast Shutdown mode. The server does not allow new connections and sends SIGTERM to all existing service processes, causing them to terminate the current transaction and exit immediately. The database shuts down after all service processes exit.

SIGQUIT

Immediate Shutdown mode. The server sends SIGQUIT to all child processes and waits for them to terminate. If they are not terminated after 5 seconds, these processes are SIGKILL. After all child processes are stopped, the main service process exits. It does not do the normal shutdown process and will start the recovery the next time it starts. It is recommended to use it only in case of emergency.

Pg_ctl provides a way to shut down the database:

Pg_ctl stop # default fast mode is off

[postgres@rhel7 data] $> logfile [postgres@rhel7 data] $pg_ctl stop-D / usr/local/pgsql/data/waiting for server to shut down.... Doneserver stopped [postgres@rhel7 data] $cat logfile LOG: received fast shutdown requestLOG: aborting any active transactionsLOG: autovacuum launcher shutting downLOG: shutting downLOG: database system is shutdown

Specify to close in some way:

Pg_ctl stop-m smart/fast/immediate

You can also directly kill the process number, which is in the postmaster.pid file of the data directory

Kill-INT 'head-1 / usr/local/pgsql/data/postmaster.pid'

4. Server configuration

Postgresql.conf of parameter configuration files in the data directory

View current configuration

Use the show command

Show all/paramter_name

Use function

Select current_setting ('paramter_name')

4.1. Modify parameters

4.1.1 modify parameters using SQL statements

ALTER SYSTEM # modifying the system level is equivalent to modifying psotgresql.conf, and the modified parameters are recorded in the postgresql.auto.conf file.

ALTER DATABASE # modify database level

ALTER ROLE # modify ROLE level

Directly modify the postgresql.conf file, need pg_ctl reload or execute select pg_reload_conf (); read the configuration back into the system

In addition, there is a system view pg_settings that can be used to view and modify parameters at the session level.

SET configuration_parameter TO DEFAULT

UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter'

The above two statements are equivalent.

4.1.2 specify parameters on the command line

When starting the database, use the postgres command to add the specified parameters using-c

Postgres-c log_connections=yes-c log_destination='syslog'

The parameters specified in this way cannot be modified by the ALTER SYSTEM command unless the database is restarted. The specified parameter information is recorded in the postmaster.opts file.

When you start session, you can set the value of the parameter in session by setting the environment variable PGOPTIONS

Env PGOPTIONS= "- c geqo=off-c statement_timeout=5min" psql

4.1.3 referencing other parameter files

The parameter file postgresql.conf can refer to other parameter files and can nest references. Can be specified by the following parameter parameters:

Include='special.conf' # specifies the file directly. You need to specify an absolute path that is not in the same directory as postgresql.conf. If the file does not exist, start the error report.

Include_if_exists='exists.conf' # is used like include, but this parameter is ignored if the file does not exist

Include_dir='conf_dir' # references all files in the specified directory with the suffix .conf.

Reference: https://www.postgresql.org/docs/9.6/static/server-start.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.