Use of plugins in PostgreSQL source code

If you compile the database with Gmake world and Gmake Install-world, all the plugins will be installed, then you will not need to install them again.

Plugin Directory

Contrib

Go to the plug-in directory you want to install, such as

CD Contrib/pg_freespacemap

Add the Pg_config command to the default path

Export Path=/opt/pgsql/bin: $PATH

Gmake Clean

Gmake

Gmake Install-This step copies the generated dynamic library files to the Pgsql Lib directory, while copying the generated extension files to

$PGHOME the/share/extension.

After installation, execute create extension in the database as Superuser

Create extension Pg_freespacemap;

Install according to the installation instructions provided by the third-party plug-in

General installation Methods

1. Copy the source directory of the third-party plug-in into the contrib directory

2. Add Pg_config to Path

3. Gmake clean; Gmake; Gmake Install

4. create extension xxx; For some older versions of plugins, there may not be a change to the installation mode of the extension. Then you need to execute SQL directly into the corresponding library.

Installation and use of third-party plug-ins

can be downloaded to git.postgresql.org third-party plugin source code

The PostgreSQL feature test can be tested with the test script provided in the PostgreSQL source code

Src/test/regress/sql

Auto_explain plug-in usage

The purpose of the Auto_explain is to give the SQL statements executed in the database an execution time threshold that exceeds the threshold value, recording the execution plan of the SQL at that time into the log, so that there is no problem in looking at the SQL execution plan in the future.

Export Paht=/home/pg93/pgsql/bin: $PATH

Which Pg_config

cd/opt/soft_bak/postgresql-9.3.4/contrib/auto_explain/

Gmake Clean

Gmake

Gmake Install

1. Modify the configuration file postgresql.conf:

shared_preload_libraries = ' Auto_explain '

Add the following configuration:

auto_explain.log_min_duration = 0 # for ease of viewing, the time is set to 0, and all SQL is captured by the Auto_explain. When actually used, adjust the size properly. such as 100ms

Auto_explain.log_analyze = True

# The following options are available

Auto_explain.log_verbose = True

Auto_explain.log_buffers = True

Auto_explain.log_nested_statements = True

Restarting the PostgreSQL database

Pg_stat_statements Installation

After the PostgreSQL database installation is complete

Edit postgresql.conf

shared_preload_libraries= ' Pg_stat_statements,auto_explain ';

Pg_stat_statements.max = 1000

Pg_stat_statements.tarck =all

Auto_explain.log_min_duration = ' 10s '

Auto_explain.log_analyze = False

Auto_explain.log_verbose = False

Auto_explain.log_nested_statements = True

(For AUTO_EXPLAIN;CD Conttrib make, make install)

Cd/contrib/pg_stat_statemetns

Make

Make install

Restarting the database

Pg_ctl–d. /data Start

Link Database

./psql

Create extension pg_stat_statements;

Select Pg_stat_statements_reset ();

Then the SQL operation

\d pg_stat_statements

Select Query,total_time,calls,from pg_stat_statements ORDER BY 3 desc limit 2;

That is, the execution time and the number of executions of the SQL statement;

(Auto_explain results can be viewed through the log)

Search

Copy

Use of plugins in PostgreSQL source code