PostgreSQL installation configuration and easy use
- 2020-05-09 19:31:56
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.