PostgreSQL installation and simple use

I. Introduction of PostgreSQL

1. What is PostgreSQL

The PostgreSQL database is the most powerful open source database available, 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 the ability to implement its own index type in the gist framework, and it also supports writing custom functions, triggers in the C language, and writing custom functions in popular languages, such as pl/ Perl provides the ability to write custom functions in the Perl language, and of course Pl/python, PL/TCL, and so on.

2. The advantages of PostgreSQL database

PostgreSQL database is currently the most powerful open source database, it is closest to industry standard SQL92 query Language, and is implementing new functionality is compatible with the latest SQL standard: SQL2003.

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

Open source saves money: PostgreSQL database is open source, free, and is BSD protocol, in use and two times development basically no limit.

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

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

second, PostgreSQL installation and configuration

#安装前准备:

1. System version

[[email protected] ~]# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)

2. Yum Installation ( find the corresponding version on the official website Yum source, and then install to Local.

[[email protected] ~] # yum -y install pgdg-centos96-9.6-3.noarch.rpm #yum source installation
[[email protected] ~] # yum -y install postgresql-server #Install postgreesql
#Install generated files
[[email protected] ~] # 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:
[[email protected] ~] # 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
[[email protected] ~] # systemctl start postgresql.service
[[email protected] ~] # 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
[[email protected] ~] # 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
[[email protected] ~] # 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. Source code Installation

#首先到官方网站下载源代码 ( https://www.postgresql.org/ftp/source/ )

#Start compilation and installation
[[email protected] soft] # tar xf postgresql-9.6.1.tar.bz2
[[email protected] 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
[[email protected] postgresql-9.6.1] # cat /etc/profile.d/postgresql.sh
export PATH = $ PATH: / usr / local / pgsql / bin
export PGDATA = / data / pgdata
[[email protected] postgresql-9.6.1] # source /etc/profile.d/postgresql.sh
[[email protected] postgresql-9.6.1] # echo "/ usr / local / pgsql / lib"> /etc/ld.so.conf.d/pgsql.conf
[[email protected] postgresql-9.6.1] # ldconfig
#Create a database directory and initialize the database
[[email protected] postgresql-9.6.1] # mkdir / data / pgdata /
[[email protected] postgresql-9.6.1] # chown -R postgres.postgres / data / pgdata /
[[email protected] 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's 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. Setting of memory parameters

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

#shared_buffers默认值为32MB, if there is enough memory, you can change this parameter larger, so that the database can cache more databases, when reading data, you can read from the shared memory, and do not need to read from the file.

Work_mem: When a single SQL executes, the memory used by the sort, hash join, and when SQL runs out, the memory is freed and the value is set larger, making the sort operation faster.

iii. Getting Started with SQL syntax

1. Introduction to SQL statement syntax

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

The abbreviation for 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 abbreviation, that is, data manipulation language, mainly used for inserting, updating, deleting data, so also divided into insert, UPDATE, delete three kinds of statements.

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

PostgreSQL installation and simple use