PostgreSQL installation, configuration and simple use method _postgresql

Introduction of PostgreSQL

1, what is PostgreSQL

The PostgreSQL database is currently the most powerful open source database, supporting rich data types (such as JSON, JSONB type, array type), and custom types. And it provides a rich interface that can easily extend its functionality, such as implementing its own index type in the gist framework, and it also supports writing custom functions and triggers using the C language, and also supports writing custom functions in popular languages, such as the pl/ Perl provides the ability to write custom functions in the Perl language, as well as Pl/python, PL/TCL, and so on.

2, the 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 be lost in data 0. There are reports that some banks abroad are also using PostgreSQL.

Open Source and save money: The PostgreSQL database is open source, free, and BSD, with virtually no restrictions on use and two development.

Broad support: The PostgreSQL database supports a large number of mainstream development languages, including C, C + +, Perl, Python, Java, TCL, and PHP.

PostgreSQL Community Active: PostgreSQL basically releases a patch version every three months, which means that known bugs will soon be repaired, and that the need for a scenario will be answered in time.

second, PostgreSQL installation and configuration

#Preparation before installation:

1. System version

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

2, yum installation (on the official network to find the corresponding version of the Yum source, and then installed to local.

[root @ node1 ~] # yum -y install pgdg-centos96-9.6-3.noarch.rpm #yum source installation
[root @ node1 ~] # yum -y install postgresql-server #Install postgreesql
#Install 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 postgresql
#Direct startup 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 above is a reminder that the database has not been initialized, so let's initialize it first
postgresql-setup initdb
Initializing database ... OK #prompt initialization success
#Restart 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
● postgresql.service-PostgreSQL database server
 Active: active (running) since Sat 2016-11-26 22:49:07 CST; 1min 33s ago
#Switch to the "postgres" user under the operating system and log in to the 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
#So far, the basic installation is complete.

3, the source code installation

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

#Start compilation and installation
[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 #Dependency 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
#Configuration after installation
[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 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 $ 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
#Install tools under 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 data directory of pgsql
# pg_ctl stop -D $ PGDATA [-m SHUTDOWN-MODE]
Where -m is the method for stopping the database.
smart: After all connections are terminated, close the database. If the client does not suspend, the database cannot be shut down.
fast: Close the database quickly, disconnect the client, let the existing transactions roll back, and then close the database normally.
immediate: Close the database immediately, which is equivalent to stopping the database process immediately and exiting directly. The database needs to be repaired next time.

4, PostgreSQL simple configuration

Edit the postgresql.conf file in 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 addresses to listen to. To allow 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 be left unchanged. After modifying these two parameters, a restart is required to take effect.
#Database related parameters
logging_collector = on #log collection, on means open
log_directory = 'pg_log' #Define the collection directory for logs
The log switching and whether to choose to cover can use the following schemes
Option 1: produce a new log file every day
log_filename = ‘postgresql-% Y-% m-% d_% H% M% S.log '
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
Option 2: Whenever a log is filled with a certain size (such as 10MB space), switch 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
Option 3: Keep logs for only 7 days and perform cyclic coverage
log_filename = ‘postgresql-% a.log '
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0

5, the memory parameter setting

Shared_buffers: The size of shared memory, used primarily for shared data blocks.

#The default value of shared_buffers is  32MB, if you have enough memory, you can change this parameter larger so that the database can cache more databases, and when you read the data, you can read it from shared memory without having to read it from the file.

Work_mem: When a single SQL executes, the memory used by the sort, hash join, when the SQL runs out, the memory is released, and the value is set to a larger number, which makes the sort operation faster.

Introduction to SQL Syntax

1. Introduction to SQL statement syntax

(1), the classification of statements (SQL commands are generally divided into DDL, DML, dql several categories)

Ddl:data definition language, the data definition language, is used primarily for creating, deleting, and modifying database object languages such as tables, indexes, and so on.

Dml:data manipulation language, the data manipulation language, is mainly used for inserting, updating and deleting data, so it is also divided into three kinds of statements, insert, UPDATE and delete.

DQL: Database query statement, basic timely Select query command, for data query.