How to install, configure, and use PostgreSQL

How to install, configure, and use PostgreSQL

I. Introduction to PostgreSQL

1. What is PostgreSQL?

PostgreSQL database is currently the most powerful open-source database and supports a wide range of data types (such as JSON, JSONB, and array types) and custom types. Moreover, it provides a variety of interfaces that can easily expand its functions, such as implementing its own index type under the GiST framework, it also supports using C language to write user-defined functions and triggers, and also supports using popular languages to write user-defined functions, for example, PL/Perl provides the ability to write user-defined functions using the Perl language, including PL/Python, PL/Tcl, and so on.

2. Advantages of PostgreSQL databases

PostgreSQL database is currently the most powerful open-source database. It is the query language closest to the industrial standard SQL92, and 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 and cost-saving: PostgreSQL databases are open-source and free, and are BSD protocols. There are basically no restrictions on use and secondary development.

Widely supported: PostgreSQL databases support 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 version every three months. This means that known bugs will be fixed soon, and demands for application scenarios will be promptly responded.

Ii. PostgreSQL installation and configuration

# Preparations before installation:

1. System Version

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

2. Install yum (find the yum source of the corresponding version on the official website and 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 #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. Source Code Installation

# First download source code to the official website (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: Closes the database immediately, which is equivalent to stopping the database process immediately and exiting directly. The database needs to be repaired next time.

4. simple configuration of PostgreSQL

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. Memory parameter settings

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

# The default value of shared_buffers is 32 MB. If you have enough memory, you can increase the value of this parameter so that the database can cache more databases. When reading data, it can be read from the shared memory without reading from the file.

Work_mem: memory used by sorting and hash join during execution of a single SQL statement. After the SQL statement is run, the memory is released. setting this value to a greater value will make the sorting operation faster.

Iii. SQL syntax

1. SQL statement syntax

(1) Statement classification (SQL commands are generally divided into DDL, DML, and DQL)

DDL: the abbreviation of Data Definition Language (DDL). It is mainly used to create and delete Data and modify Database Object languages such as tables and indexes.

DML: Short for Data Manipulation Language (Data Manipulation Language) is mainly used to INSERT, UPDATE, and DELETE Data. Therefore, it can be divided into INSERT, UPDATE, and DELETE statements.

DQL: a database query statement that uses the SELECT query command in a timely manner for data query.