Tips for PostgreSQL

I have been using Oracle and PostgreSQL for several years and have been trying to use many advanced features in Oracle in PostgreSQL. Therefore, I have been paying attention to this issue. Below I will first sort out three points, it will be improved in the future.

(1) dblink functions similar to Oracle

Anyone who has used Oracle knows that Oracle has a very advanced function called dblink, which can operate another remote database in one database, such as a data database in Beijing, China, another database is located in Shanghai, China. I can create a dblink to the database in Shanghai on this database in Beijing, and then query, update, or delete databases in Shanghai on this database in Beijing. This advanced feature is available in the original PostgreSQLCodeOf: contrib \ dblink already exists. You can compile and install it into our database like this.

 
# Cd contrib/Dblink
# Make
# Make install
 

Suppose our PostgreSQL is installed in:/home/pgsql.

After make install, there will be a dblink. So file in/home/pgsql/lib. This is the function file required to use dblink. In addition, there is a dblink. SQL file in/home/pgsql/share/contrib, which is the SQL statement required to install the dblink. So function.

You can install all the functions of dblink as follows:

# Cat dblink. SQL|Psql
[Pgsql @ Webtrends contrib] $ cat dblink. SQL|Psql
Create Function
[Pgsql @ Webtrends contrib] $
 

This indicates that our function is successfully installed.

All the advanced functions of dblink can be used below.

Let's take a look at some function declarations in dblink. SQL to better understand their functions.

Enter Psql below:

Pgsql=# Select dblink_connect ('Host = localhost user = pgsql Password =');
Dblink_connect
----------------
OK
(1Row)
 

This function is used to establish a connection to a remote database.

We can insert a record in a remote database as follows:

Pgsql=# Select dblink_exec ('Insert into student values (\ 'linux _ prog \ ', \ '2017 \')');
Dblink_exec
-------------------
Insert22516276 1
(1Row)
 

Now let's retrieve the insert record: How is it? The insert record is already in.

Dblink is very powerful. I listed it as its simplest application. You can refer to the source code of postgresql below:

Contrib/dblink/SQL/dblink. SQL take a closer look.

(2) identify and optimize SQL statements with poor system performance

What we often do when doing ORACLE System Management is:

First, let's take a look at which SQL statements in the system have the worst performance. Run the Linux Command: top-C to find the PID of the first several Oracle processes, and then go to the relevant Oracle View

Find out these SQL statements, then look at the Execute Plan of these SQL statements, and then perform related optimization.

PostgreSQL also provides such advanced functions.

First, open stats_command_string = true in PostgreSQL. conf to enable PostgreSQL's stats collector process to monitor the SQL statements of each session.

Compile related scripts:

 Viewsql. sh:
#  ! /  Bin  /  Sh
######################################## ##############
# Viewsql. Sh #
# Author: linux_prog #
# Use to show all active sessions  ' S SQL in PostgreSQL .#  
  
######################################## ##############
  If  Test  -  Z $  1  ; Then
Echo  "  Usage: $0 PID  "  
Exit  10  
Fi
Echo  " Select * from (select pg_stat_get_backend_pid (S. backendid) 
 Procpid, pg_stat_get_backend_activity (S. backendid) as current_query from 
 (Select pg_stat_get_backend_idset () as backendid) as s) as querystring 
 Where procpid = $1;  "   |  Psql 

This script displays the SQL statement currently being executed by the Session of the specified PID.

For example:

I use top-C and the result is:

3665 pgsql 15 0 124 m 124 m 122 M r 30.0 0: 04 s: pgsql [local] Insert

As you can see, the PID 3665 is displayed in the first one, indicating that its SQL efficiency may be relatively low.

[Pgsql @ Webtrends bin] $./Viewsql. Sh3665
Procpid|Current_query
--------- + ---------------------------------------------------
3665 |Insert into access_log select*From access_log;
(1Row)
 

We can see the SQL statements that are being executed, and then we can optimize the performance of these SQL statements.

If the execution speed of a SELECT statement is too slow, we can use explain to look at his execute plan, check whether an appropriate index exists or whether a table has not been analyzed for a long time.

In addition, you can provide a script to kill a session. For example, if a session occupies too many resources and is not killed, the system may be down.

 Killsession. sh:
#  ! /  Bin  /  Sh
######################################## ########
# Author: linux_prog #
# Use to kill one session .#
######################################## ########
  If  Test  -  Z $  1 ; Then
Echo  "  Usage: $0 PID  "  
Exit  10  
Fi
Sid  =  $  1  
Echo  "  Select pg_cancel_backend ($ Sid );  "  |  Psql
For example, execute:
[Pgsql @ Webtrends bin] $. /  Killsession. Sh  3665  
Pg_cancel_backend
  -------------------  
  1  
(  1  Row) 

The current SQL operation of the session that occupies resources was canceled by cancel.

Psql 3665 displays:

Pgsql = # insert into access_log select * From access_log;

Error: canceling query due to user request

(3) clearly know the size of each table or index

Every DBA should know that the bottleneck of Io is the bottleneck of all databases. Therefore, when designing the table structure, We must minimize the size of each field. Only in this way can the table size be as small as possible.

Also, when we make SQL adjustments, the first thing we do is definitely performance turnning for large tables.

Therefore, it is necessary to know the disk size occupied by each table or index. In Oracle, you can directly access the dba_segments view to know the size of each table or index.

PostgreSQL contrib/dbsize also has such a module.

You can install dbsize. So as you have installed dblink above.

View the table: access_log size as follows:

Pgsql = # select relation_size ('Access _ log')/1024/1024 | 'M' as dbsize;

Dbsize

--------

332 m

(1 row)

View the size of index: test_idx as follows:

Pgsql = # select relation_size ('test _ idx')/1024/1024 | 'M' as dbsize;

Dbsize

--------

0 m

(1 row)

First, we can write a script to perform this operation (for everyone to do it ).

the above content is quite practical. If you have a deep understanding of it, you will be able to make a little effort in daily database management.