postgresql9.6 Installation

The latest version of PostgreSQL is now 10.4, the version of this installation is 9.6, there are many new features between the large version.

1. New features of postgresql9.6:

①: Parallel Query

Parallel queries are the biggest highlight of the 9.6 release. In previous versions, even though there were multiple idle processors, database throttling could only take advantage of the computing power of a single CPU. Version 9.6 supports parallel query operations, so it is possible to use several or all of the CPU cores on the server to perform operations, so that the query results are returned faster. Parallel features are currently supported in sequential table scans, aggregations, and edges, which, depending on the operational details and the number of cores available, can improve the retrieval efficiency of big data up to 32 times times as fast as possible.

②: Improvements to the synchronous replication feature:

The synchronous replication feature of PostgreSQL can be improved so that it can be used for consistent read maintenance of the database cluster. First, it now allows you to configure a synchronous replication group, and secondly, the "remote_apply" mode creates a more uniform instance through multiple nodes. These features enable the use of built-in self-replication to maintain load balancing for independent nodes.

③: Phrase Search

PostgreSQL's Text Search feature now supports phrase search. Users can search for exact phrases or search for phrases with a certain similarity.

With the words in the Fast Gin index combined with the new features of fine-tuned text search, PostgreSQL has become the best choice for hybrid search.

④: Better lock monitoring

The pg_stat_activity view provides more verbose waiting information, and when a process is waiting for a lock, the user sees the type of lock and the details of the wait event that blocks the query. In addition, PostgreSQL adds the Pg_blocking_pids () function to know which processes are blocking a given server process.

⑤: Control Table expansion

So far, a long-running report or cursor that displays the results of a query can prevent the cleanup of failed rows, thus inflating the frequently changing tables in the database, resulting in database performance problems and excessive use of storage space.

The Old_snapshot_threshold parameter was added in version 9.6 to restrict the table bloat by configuring the cluster to allow cleanup of stale rows when the transaction is updated or deleted.

In addition, the 9.6 version adds additional features, such as support for cascading operations (to install extension implementations), a better spatial recycling mechanism for frozen pages, scanning only local indexes, support for command execution progress status reporting, performance improvements for external sorting operations, and more.

--After downloading the PG binary package, unzip:

[Email protected] ~]# TAR-ZXVF postgresql-9.6.9-1-linux-x64-binaries.tar.gz-c/usr/src/

[Email protected] ~]# Groupadd Pguser

[Email protected] ~]# useradd-g pguser pguser

[Email protected] ~]# passwd Pguser

[Email protected] ~]# Su-pguser

--Initialize the PG database:

[[email protected] ~]$ cd /usr/src/pgsql/bin/[[email protected] bin]$ ./ initdb -e uft8 -d /pgsql/data/the files belonging to this  database system will be owned by user  "Pguser". This user must also own the server process. the database cluster will be initialized with locale  "En_US. UTF-8 ".initdb: " Uft8 " is not a valid server encoding name[[email  Protected] bin]$ ./initdb -e utf8 -d /pgsql/data/the files belonging  to this database system will be owned by user  "Pguser". This user must also own the server process. the database cluster will be initialized with locale  "En_US. UTF-8 ". the default text search configuration will be set to  "中文版". data page checksums are disabled.fixing permissions on existing  Directory /pgsql/data ... okcreating subdirectories ... okselecting default  max_connections ... 100selecting default shared_buffers ... 128mbselecting  dynamic shared memory implementation ... posixcreating configuration  files ... okrunning bootstrap script ... okperforming post-bootstrap  Initialization ... oksyncing data to disk ... okwarning: enabling   "Trust"  authentication for local connectionsYou can change this  By editing pg_hba.conf or using the option -a, or--auth-local and  --auth-host, the next tiMe you run initdb. success. you can now start the database server using:     ./pg_ctl -d /pgsql/data/ -l logfile start--: Configuring Environment Variables,~/.bash_profile  Add the following path=/usr/local/pgsql/bin: $PATHexport  path--start the database: [[Email protected] ~]$ source  .bash_profile [[email protected] ~]$ pg_ctl -d /pgsql/data/ -l / Home/pguser/postgres.log startserver starting or use:./postgres -d /pgsql/data >  /pgsql/data/postgres.log &

Attention:

When the database is installed, we will have a system user, a database, a database user, their default name is: Postgres

[Email protected] ~]# Ps-ef |grep postgre

Pguser 14160 1 0 01:36 pts/0 00:00:00/usr/src/pgsql/bin/postgres-d/pgsql/data

Pguser 14162 14160 0 01:36? 00:00:00 Postgres:checkpointer Process

Pguser 14163 14160 0 01:36? 00:00:00 Postgres:writer Process

Pguser 14164 14160 0 01:36? 00:00:00 Postgres:wal Writer Process

Pguser 14165 14160 0 01:36? 00:00:00 postgres:autovacuum Launcher Process

Pguser 14166 14160 0 01:36? 00:00:00 postgres:stats Collector Process

Pguser 14246 14160 0 01:42? 00:00:00 postgres:pguser testdb [local] Idle

Root 14256 14009 0 01:42 pts/1 00:00:00 grep--color=auto postgre

--Connection test:

[Email protected] ~]$ Psql--list

List of databases

Name | Owner |   Encoding |    Collate | Ctype | Access Privileges

-----------+--------+----------+-------------+-------------+-------------------

Postgres | Pguser | UTF8 | en_US. UTF-8 | en_US. UTF-8 |

Template0 | Pguser | UTF8 | en_US. UTF-8 | en_US. UTF-8 | =c/pguser +

|          |             |             | | Pguser=ctc/pguser

template1 | Pguser | UTF8 | en_US. UTF-8 | en_US. UTF-8 | =c/pguser +

|          |             |             | | Pguser=ctc/pguser

TestDB | Pguser | UTF8 | en_US. UTF-8 | en_US. UTF-8 |

(4 rows)

--Log in to the database (from the above to see that Postgres is a database,)

[Email protected] ~]$ Psql Postgres

Psql.bin (9.6.9)

Type ' help ' for help.

postgres=# select version ();

Version

----------------------------------------------------------------------------------------------------------

PostgreSQL 9.6.9 on X86_64-pc-linux-gnu, compiled by GCC (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit

(1 row)

postgres=#

postgres=# \q---Exit the PG database:

or create a DB:

[Email protected] ~]$ createdb TestDB

[[email protected] ~]$ psql-d testdb---login testdb

Psql.bin (9.6.9)

Type ' help ' for help.

testdb=# select version ();

Version

----------------------------------------------------------------------------------------------------------

PostgreSQL 9.6.9 on X86_64-pc-linux-gnu, compiled by GCC (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit

(1 row)

testdb-# \q

pg_ctl-d/pgsql/data Stop--Close PG Database

Attention:

Pgadmin is a common tool for designing, maintaining, and managing PostgreSQL databases and can be run on Windows,linux,freebsd,mac,solaris platforms. The Pgadmin tool is simple and intuitive to access, query, control, and manage databases, while integrating a variety of graphical tools with a wide range of full-featured scripting editors, greatly facilitating access to PostgreSQL for various developers.

postgresql9.6 Installation