Notes for using PostgreSQL

1. log Configuration
The system log and error log of postgresql are directly output by default and are not written to files. If you want to view these logs, modify PostgreSQL. conf as follows:

Log_destination = 'stderr'
Logging_collector = on
Log_directory = 'pg _ Log'
Log_min_duration_statement = 5000

The preceding parameters mean that the pgsql logs are output to the pg_log directory of the pgsql data directory, and operations over 5000 milliseconds are recorded.

 
2. User Authentication and listening

PostgreSQL user authentication is set through pg_mirror.conf. By default, all users are trusted, that is, local accounts can log on directly. Obviously, it is not safe. You can modify it as follows:

# "Local" is for Unix domain socket connections only
Local all MD5
# IPv4 local connections:
Host all 127.0.0.1/32 md5
Host all 192.168.1.0/24 md5

The above parameters mean that the local connection to pgsql requires md5 authentication, the 192.168.1.xx network segment allows connection, and the password also requires md5 authentication.
If you need to connect from other clients, you also need to modify the liston_addresses parameter of postgresql. conf. this parameter is similar to that of oracle listeners.
Listen_addresses = '*'

 

3. Password File

After 2 is set to md5 for verification, although it improves security, it may cause some trouble at the same time. For example, your shell script needs to be directly connected using psql, in this case, you can use the password file. Create a. pgpass file under the root directory of the user. And set the permission to 0600.

The file format is as follows:
Hostname: port: database: username: password

 

4. psql commonly used confusing commands

When you log on to PostgreSQL through psql, some commands may be different from other databases. Here, we will introduce some of the commands that you may mix up with mysql:
Pgsql mysql
/C use
/Q quit
/D desc

 

5. system parameter configuration

When installing PostgreSQL, PostgreSQL does not require you to modify the default system configuration because its default memory and other parameters are very small and will not exceed the default system configuration. For example, when you increase the value of shared_buffers and other parameters, the value will basically exceed the default system parameter. You need to modify these configurations.
Edit/etc/sysctl. conf and add the following content:
Kernel. shmall = 3145728
Kernel. shmmax = 12884901888
Kernel. shmmni = 4096
Kernel. sem = 250 32000 100 128
Fs. file-max = 65536
Net. ipv4.ip _ local_port_range = 1024 65000
Net. core. rmem_default = 262144
Net. core. rmem_max = 262144
Net. Core. wmem_default = 262144
Net. Core. wmem_max = 262144
Run this command to check whether the settings are correct after the disk is exited:
# Sysctl-P

 

6. Process Language
In PostgreSQL, stored procedures and functions are called functions in a unified manner. Of course, the difference between stored procedures and functions is that one must return a value and the other is not required.
Currently, four process languages are available in the standard PostgreSQL release:
PL/pgsql,
PL/TCL,
PL/perl,
PL/python.
It is not installed by default. For example, you need to install
Create [trusted] [procedural] language 'language-name'
Handler handler_function_name
[Validator validator_function_name];

 

7. autovacuum

The vacuum command is similar to the optimize command in MySQL. When performing such operations on large tables, you need to pay attention to the impact on the business. It should be done when the business is relatively idle.
Starting from PostgreSQL 8.1, the system has an optional autovacuum daemon to automatically execute vacuum and analyze commands. You can configure it in PostgreSQL. conf.

 

8. Get the object creation statement

ORACLE:
Select dbms_metadata.get_ddl ('object _ type', 'object _ name', 'owner') from dual;

Mysql:
Show create OBJECT OBJECT_NAME;

The statements for obtaining object creation from PostgreSQL are rather difficult and need to be processed separately.
Table 8.1
It seems that only pg_dump can be used to export the table t1 of the test database.
Pg_dump-s-U test-t t1

8.2 process and Function
Pg_get_functiondef (oid)

8.3 trigger
Pg_get_triggerdef (oid)

8.4 Index
Pg_get_indexdef (oid)

8.5 View
Pg_get_viewdef (oid)

Oid can be found through the corresponding system table. For example, function can be found in pg_proc. Note that oid is a hidden column.

 

9. database, schema, objects

The database in PostgreSQL is different from the database in Mysql. It is a bit similar to the Oracle instance. Each database is independent between databases and objects.

There is also a schema. Each database can create multiple schemas, and each shcema can create multiple objects. The following is a simple list of relationships:

PostgreSQL: database-shcma-object
Mysql: database-object
Oracle: instance-schema-object

 

10. pg_dump and pg_dumpall

The difference between the two tools is that one is to back up a single database and the other is to back up all the databases. I am used to mysqldump and pg_dumpall is easy to ignore. Why not integrate these two tools into one?

 

In general, I personally think PostgreSQL is similar to Oracle in many aspects, especially in multi-process mode, which is more robust than Mysql. However, PostgreSQL is rarely used in China, and there are not many Chinese materials. Generally, java corresponds to oracle, php corresponds to mysql, and python corresponds to postgresql. Can you tell me that python is widely used in China?