PostgreSQL installation configuration and easy use

1. PostgreSQL profile

1. What is PostgreSQL

The PostgreSQL database is currently the most powerful open source database that supports both rich data types (such as JSON and JSONB types, array types) and custom types. And it provides a rich interface, can easily extend the functionality of it, such as can realize their index under GiST frame type, etc., it also supports the use of C language to write a custom function, trigger, also support the use of popular language to write a custom function, such as the use Perl PL/Perl provides the function of language to write a custom function, and of course PL/Python, PL/Tcl, and so on.

2. Advantages of PostgreSQL database

The PostgreSQL database is currently the most powerful open source database, it is the closest to the industry standard SQL92 query language, and is implementing new features 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. Some foreign Banks are also reported to be using PostgreSQL.

Open source to save money: the PostgreSQL database is open source, free, and under the BSD license, with virtually no restrictions on usage and 2-time development.

Extensive support: the PostgreSQL database supports a wide range of major development languages, including C, C++, Perl, Python, Java, Tcl, and PHP.

The PostgreSQL community is active: PostgreSQL basically releases a patch version every 3 months, which means that known BUG will soon be fixed and the need for application scenarios will be answered in a timely manner.

2. Installation and configuration of PostgreSQL

Preparation before installation:

1. System version

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

2. Installation of yum (find the corresponding version of yum source on the official website and install it locally.

[root@node1 ~]# yum -y install pgdg-centos96-9.6-3.noarch.rpm #yum The source is installed 
[root@node1 ~]# yum -y install postgresql-server # The installation postgreesql
# Install the 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 , 
# Start the postgresql
# Direct start will report an error: 
[root@node1 ~]# systemctl start postgresql.service
Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details.
# The message above is that the database is not initialized yet, so let's initialize it first 1 Under the 
postgresql-setup initdb
Initializing database ... OK # Indicates successful initialization 
# Restart the Postgresql
[root@node1 ~]# systemctl start postgresql.service
[root@node1 ~]# netstat -tnlp
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name 
tcp  0  0 127.0.0.1:5432  0.0.0.0:*  LISTEN  1512/postgres  
tcp6  0  0 ::1:5432    :::*  LISTEN  1512/postgres 
# View running status 
[root@node1 ~]# systemctl status postgresql.service
 low  postgresql.service - PostgreSQL database server
 Active: active (running) since Sat 2016-11-26 22:49:07 CST; 1min 33s ago
# Switch to "under operating system" postgres "User, login to database 
[root@node1 ~]# su - postgres
-bash-4.2$ psql
psql (9.2.15)
Type "help" for help.
postgres=# help
You 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
# At this point, the basic installation is complete. 

3, source installation

# in the first place 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 kit set 
# yum -y install perl-ExtUtils-Embed readline-devel zlib-devel python-devel # Depend on the 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 && make install
# Post-installation configuration 
[root@node1 postgresql-9.6.1]# cat /etc/profile.d/postgresql.sh
export PATH=$PATH:/usr/local/pgsql/bin
export 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 the 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$ initdb
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
fixing permissions on existing directory /data/pgdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Success. You can now start the database server using:
 pg_ctl -D /data/pgdata -l logfile start
# The installation contrib Tools under the directory 
# cd postgresql-9.6.1/contrib/
# make
# make install
# Start and stop the database 
# pg_ctl start -D $PGDATA #PGDATA is pgsql Data directory 
# pg_ctl stop -D $PGDATA [-m SHUTDOWN-MODE]
 Among them -m Is to make the database stop method, there are the following 3 Kind of 
smart : after all connections are aborted, close the database. If the client does not abort, the database cannot be shut down. 
fast : close the database quickly, disconnect the client, roll back the existing transactions, and then close the database normally. 
immediate : immediately close the database, equivalent to the database process immediately stop, exit directly, the next time start the database need to repair. 

4. Simple configuration of PostgreSQL

 Edit in the data directory postgresql.conf File, find the following: 
#listen_addresses = 'localhost'   # what IP address(es) to listen on;
#port = 5432       # (change requires restart)
listen_addresses Represents the address to be listened to, and to get a host on the network to log into the database, you need to change this address to "*" Or, 0.0.0.0 . 
port Represents the listening port, which can not be changed. After these two parameters are modified, they need to be restarted to take effect. 
# With the database Log Related parameters 
logging_collector = on # The collection of logs, on Said to open 
log_directory = 'pg_log' # Define the collection directory for the logs 
 Switching between logs and whether to select overwrite can be done in several ways 
 plan 1 Production per day 1 New log files 
log_filename =  ' postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
 plan 2 : when the log is full 1 Fixed size (e.g 10MB Space), then switch 1 A log 
log_filename =  ' postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 0
log_rotation_size = 10M
 plan 3 : keep only 7 Day log, for circular coverage  
log_filename =  ' postgresql-%a.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0

5. Memory parameter setting

shared_buffers: the size of Shared memory, primarily used to share data blocks.

The default value of #shared_buffers is 32MB. If you have enough memory, you can make this parameter 1 larger so that the database can cache more databases and when you read the data, you can read from Shared memory instead of reading from a file.

work_mem: the memory used for sorting and hash join when a single SQL is executed. When SQL is finished, the memory is freed. If this value is set to be 1 larger, the sorting operation will be 1 faster.

3. Introduction to SQL grammar

1. Introduction to the syntax of SQL statement

(1) classification of statements (SQL command 1 is divided into DDL, DML and DQL)

DDL: short for data definition language, Data Definition Language is used to create, delete, and modify tables, indexes, and other database object languages.

DML: the abbreviation of Data Manipulation Language, namely data manipulation language, is mainly used for inserting, updating and deleting data, so it is also divided into INSERT, UPDATE and DELETE3 statements.

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