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

A brief introduction to PostgreSQL

1. What is PostgreSQL

PostgreSQL database is currently the most powerful open source database, supporting rich data types (such as JSON and JSONB types, array types) and custom types. And it provides a wealth of interfaces, you can easily expand its functions, such as the GiST framework to achieve their own index types, it also supports the use of C language to write custom functions, triggers, but also supports the use of popular languages to write custom functions, such as PL/Perl which provides the use of Perl language to write custom functions, of course, PL/Python, PL/Tcl, and so on.

2. Advantages of PostgreSQL database

PostgreSQL database is the most powerful open source database at present, it is the query language closest to the industry standard SQL92, and it is implementing new functions that are compatible with the latest SQL standard: SQL2003.

Stable and reliable: PostgreSQL is the only open source database that can achieve zero data loss. It is reported that some foreign banks are also using PostgreSQL.

Open source saves money: PostgreSQL database is open source, free, and is a BSD protocol, there are basically no restrictions on use and secondary development.

Wide range of support: PostgreSQL database supports a large number of mainstream development languages, including C, C++, Perl, Python, Java, Tcl, and PHP.

The PostgreSQL community is active: PostgreSQL basically releases a patch every three months, which means that the known BUG will soon be fixed and the needs of application scenarios will be responded to in a timely manner.

II. Installation and configuration of PostgreSQL

# prepare before installation:

1. System version

[root@node1 ~] # cat / etc/redhat-releaseCentOS Linux release 7.2.1511 (Core)

2. Yum installation (find the source of the corresponding version of yum on the official website, and then install it locally.

[root@node1 ~] # yum-y install pgdg-centos96-9.6-3.noarch.rpm # yum Source installation [root@node1 ~] # yum-y install postgresql-server # installation postgreesql# installation generated files [root@node1 ~] # rpm-ql postgresql-server/etc/pam.d/postgresql/usr/bin/initdb/usr/bin/pg_basebackup/usr/bin/pg_controldata/usr/bin/pg_ctl/usr/bin/pg_receivexlog/usr/bin / pg_resetxlog/usr/bin/postgres/usr/bin/postgresql-check-db-dir/usr/bin/postgresql-setup/usr/bin/postmaster/usr/lib/systemd/system/postgresql.service/usr/lib/tmpfiles.d/postgresql.conf/var/lib/pgsql/var/lib/pgsql/.bash_profile/var/lib/pgsql/backups/var/lib/pgsql/data/var/run/postgresql 、 # if you start postgresql# directly, you will get an error: [root@node1 ~] # systemctl start postgresql.serviceJob for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl-xe" for details.# indicates that the database hasn't been initialized yet, so let's initialize postgresql-setup initdbInitializing database first. OK # prompts initialization successful # restart Postgresql [root @ node1 ~] # systemctl start postgresql.service [root@node1 ~] # netstat-tnlpProto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0127.0.0.1root@node1 5432 0.0.0.0 * LISTEN 1512/postgres tcp6 0 0:: 1 root@node1 5432:: * LISTEN 1512/postgres # View running status [root@node1 ~] # systemctl status postgresql.service ● postgresql.service-PostgreSQL database server Active: active (running) since Sat 2016-11-26 22:49:07 CST 1min 33s ago# switches to the "postgres" user under the operating system and logs in to the database [root@node1 ~] # su-postgres-bash-4.2$ psqlpsql (9.2.15) Type "help" for help.postgres=# helpYou are using psql, the command-line interface to PostgreSQL.Type:\ copyright for distribution terms\ h for help with SQL commands\? For help with psql commands\ g or terminate with semicolon to execute query\ Q to quit# ends here, and the basic installation is complete.

3. Source code installation

# first go to the official website to download the source code (https://www.postgresql.org/ftp/source/)

# start compiling and installing [root@node1 soft] # tar xf postgresql-9.6.1.tar.bz2 [root@node1 soft] # cd postgresql-9.6.1# yum-y groupinstall "Development tools" # Development package Group # yum-y install perl-ExtUtils-Embed readline-devel zlib-devel python-devel # dependent package #. / configure-- prefix=/usr/local/postgresql-9.6.1-- with-perl-- with-python-- with-blocksize=32-- with -wal-blocksize=64-- with-wal-segsize=64# make & & configuration after make install# installation [root@node1 postgresql-9.6.1] # cat / etc/profile.d/postgresql.shexport PATH=$PATH:/usr/local/pgsql/binexport PGDATA=/data/pgdata [root@node1 postgresql-9.6.1] # source / etc/profile.d/postgresql.sh [root@node1 postgresql-9.6.1] # echo "/ usr/local/pgsql/lib" > / etc/ld.so.conf .d / pgsql.conf [root@node1 postgresql-9.6.1] # ldconfig# create a database directory and initialize the database [root@node1 postgresql-9.6.1] # mkdir / data/pgdata/ [root@node1 postgresql-9.6.1] # chown-R postgres.postgres / data/pgdata/ [root@node1 postgresql-9.6.1] # su-postgres-bash-4.2$ initdbThe database cluster will be initialized with locale "en_US.UTF-8" database default Database encoding has accordingly been set to "UTF8" default text search configuration will be set to "english" .fixing permissions on existing directory / data/pgdata. 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... OkSuccess. You can now start the database server using: pg_ctl-D / data/pgdata-l logfile start# install tools in the contrib directory # cd postgresql-9.6.1/contrib/# make# make install# start and stop the database # pg_ctl start-D $PGDATA # PGDATA is the pgsql data directory # pg_ctl stop-D $PGDATA [- m SHUTDOWN-MODE] where-m is the method to stop the database. There are three types of smart: close the database after all connections are terminated. If the client does not abort, the database cannot be shut down. Fast: quickly shut down the database, disconnect the client, let the existing transactions roll back, and then shut down the database normally. Immediate: shutting down the database immediately means that the database process stops immediately and exits directly. The next time you start the database, you need to repair it.

4. Simple configuration of PostgreSQL

Edit the postgresql.conf file under the data directory and find the following: # listen_addresses = 'localhost' # what IP address (es) to listen on;#port = 5432 # (change requires restart) listen_addresses indicates the address to listen on. For hosts on the network to log in to this database, you need to change this address to "*" or 0.0.0.0. Port indicates the listening port, which can not be changed. After you modify these two parameters, you need to restart to take effect. # parameters related to database Log logging_collector = on # Log collection On means to open log_directory = 'pg_log' # to define the switch of the collection directory log and whether to choose to overwrite it or not, you can use the following scenario one: to produce a new log file every day log_filename =' postgresql-%Y-%m-%d_%H%M%S.log'log_truncate_on_rotation = offlog_rotation_age = 1dlog_rotation_size = 0 scenario two: whenever the log is written to a certain size (such as 10MB space) Then switch to a log log _ filename = 'postgresql-%Y-%m-%d_%H%M%S.log'log_truncate_on_rotation = offlog_rotation_age = 0log_rotation_size = 10m scenario 3: keep only 7-day logs and cycle over log_filename =' postgresql-%a.log'log_truncate_on_rotation = offlog_rotation_age = 1dlog_rotation_size = 0

5. Setting of memory parameters

Shared_buffers: the size of shared memory, mainly used to share data blocks.

The default value of # shared_ buffers is 32MB. If you have enough memory, you can make this parameter larger, so that the database can cache more databases, and when reading data, it can be read from shared memory instead of from files.

Work_mem: the memory used by sorting and hash join when a single SQL is executed. After SQL runs, the memory is freed. Setting this value higher will make sorting faster.

Introduction to SQL Grammar

1. Brief introduction to the syntax of SQL statement

(1). The classification of sentences (SQL commands are generally divided into DDL, DML and DQL)

The abbreviation of DDL:Data Definition Language, that is, data definition language, is mainly used to create, delete, and modify database object languages such as tables and indexes.

The abbreviation of DML:Data Manipulation Language, namely data manipulation language, is mainly used to insert, update and delete data, so it is also divided into three statements: INSERT, UPDATE and DELETE.

DQL: database query statement, basic timely SELECT query command, used for data query.

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.