Shulou(Shulou.com)06/01 Report--

Currently, the latest version of postgresql is 10.4; this installation is 9.6, and there are still a lot of new features between major versions.

1. New features of postgresql9.6:

①: parallel query

Parallel queries are the biggest highlight of version 9.6. In previous versions, even with multiple idle processors, the database was limited to taking advantage of the computing power of a single CPU. Version 9.6 supports parallel query operations, so you can use several or all of the CPU cores on the server for operations, which makes it faster to return query results. At present, the operations that support the parallel feature include sequential table scanning, aggregation and edge connection. According to the operation details and the number of available kernels, this parallel feature can improve the retrieval efficiency of big data, which can be as high as 32 times at the fastest.

②: improvements in synchronous replication:

The synchronous replication function of postgresql can be improved so that it can be used for the maintenance of consistent reads in database clusters. First, it now allows the configuration of synchronous replication groups, and second, the "remote_apply" mode creates a more uniform instance through multiple nodes. These features support the use of built-in self-replication to maintain load balancing for independent nodes.

③: phrase search

Postgresql's text search function now supports phrase search. Users can search for a precise phrase or search for phrases with certain similarities.

Using words from fast GIN indexes, combined with the new feature of fine-tuned text search, postgresql has become the best choice for "hybrid search".

④: better lock monitoring

The pg_stat_activity view provides more detailed wait information, and when a process is waiting for a lock, the user will see the type of lock and the details of the wait event that will block the query. In addition, postgresql adds the pg_blocking_pids () function to know which processes are blocking a given server process.

⑤: control table expansion

Up to now, a long-running report or cursor showing query results may prevent the cleaning of invalid rows, resulting in the expansion of frequently changing tables in the database, resulting in database performance problems and overuse of storage space.

The old_snapshot_threshold parameter was added in version 9.6, and the cluster can be configured to allow invalid rows to be cleared when a transaction is updated or deleted, thereby limiting table ballooning.

In addition, version 9.6 also added other features, such as support for cascading operations (need to install an extension module to implement), better space recovery mechanism for frozen pages, scanning only local indexes, support for command execution status reports, performance improvements for external sorting operations, and so on.

-- after downloading the PG binary package, decompress:

[root@localhost] # tar-zxvf postgresql-9.6.9-1-linux-x64-binaries.tar.gz-C / usr/src/

[root@localhost ~] # groupadd pguser

[root@localhost ~] # useradd-g pguser pguser

[root@localhost ~] # passwd pguser

[root@localhost ~] # su-pguser

-- initialize the PG database:

[pguser@localhost ~] $cd / usr/src/pgsql/bin/ [pguser@localhost 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 [pguser@localhost 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" default text search configuration will be set to "english" .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--: configure environment variables ~ / .bash_profile add the following content: [pguser@localhost ~] $source .bash _ profile [pguser@localhost ~] $pg_ctl-D / pgsql/data/-1 / home/pguser/postgres.log startserver starting or use:. / postgres-D / pgsql/data > / pgsql/data/postgres.log &

Note:

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

[root@localhost ~] # 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 testing:

[pguser@localhost] $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 (see from above that postgres is a database,)

[pguser@localhost ~] $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:

[pguser@localhost ~] $createdb testdb

[pguser@localhost] $psql-d testdb-Log in to 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-shuts down the PG database

Note:

Pgadmin is a general tool for designing, maintaining and managing postgresql databases, which can be run on the Windows,Linux,freeBSD,mac,solaris platform. The pgadmin tool is simple and intuitive, which can access, query, control and manage the database. At the same time, it also integrates a variety of graphical tools with a variety of fully functional script editors, which greatly facilitates the access of various developers to postgresql.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.