PostgreSQL installation configuration and easy to use

  • 2020-05-06 11:57:26
  • OfStack

Introduction to

1. What is PostgreSQL

The PostgreSQL database is the most powerful open source database available, supporting 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 the most powerful open source database available, 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 capable of zero data loss. There are reports that some foreign Banks are also using PostgreSQL.

Open source savings: the PostgreSQL database is open source, free, and under the BSD license, with virtually no restrictions on use or redevelopment.

Wide support: the PostgreSQL database supports a large number 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 three months, which means that known BUG will soon be fixed, and that requests for application scenarios will be promptly answered.


ii, PostgreSQL installation and configuration

Preparation before installation:

1. System version


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

2. Install 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 
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 There are three ways to stop a database 
smart : after all connections are aborted, close the database. If the client does not abort, the database cannot be closed. 
fast : quickly close the database, disconnect the client, roll back existing transactions, and then close the database as normal. 
immediate : immediately close the database, equivalent to the database process immediately stop, exit directly, the next time start the database needs 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 you want to listen to. 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, can not be changed, after the modification of these two parameters, you need to restart 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 logs and whether to choose to override can be done using the following options 
 Plan 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
 Scenario 2: every time the log is filled to a certain size (e.g 10MB Space), then switch to 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 it 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. Setting

for memory parameters

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 make this parameter larger so that the database can cache more databases and when the data is read, it can be read from Shared memory instead of from a file.

work_mem: when a single SQL is executed, the memory used by hash join is sorted. When SQL is finished, the memory is freed.


, SQL, ,

1. Introduction to SQL syntax

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

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

DML: Data Manipulation Language is short for data manipulation language, which is mainly used for inserting, updating and deleting data, so it is also divided into INSERT, UPDATE and DELETE.

DQL: database query statement, basically in time SELECT query command, for data query.


Related articles: