技术控

    今日:0| 主题:63445
收藏本版 (1)
最新软件应用技术尽在掌握

[其他] What’s new in PostgreSQL 9.5

[复制链接]
空心印末夕情 发表于 2016-10-4 14:25:35
63 0
Fedora 24 ships with    PostgreSQL 9.5, a major upgrade from version 9.4 that is included in Fedora 23. The new version 9.5 provides several enhancements and new features, but also brings some compatibility changes, as it has been very common between PostgreSQL major versions. Note that in the PostgreSQL versioning scheme, 9.4 and 9.5 are two major versions, while the first number is mostly marketing and increments when major features are introduced in the release.  
  New features and    enhancementsin 9.5  

  GROUPING SETS

  PostgreSQL has been traditionally OLTP, rather than OLAP, but this may change in the future; small steps like    GROUPING SETShelp on this path, since the GROUPING SETS allow to use more complex aggregation operations (grouping). CUBE and ROLEUP are then just specific variants of GROUPING SETS.  
  ON CONFLICT

  Users may also be very happy about    ON CONFLICTenhancement, that allows to do something sane in case the current statement would generate a conflict. That is quite general approach with two possible solutions — we can either turn the INSERT statement to UPDATE or ignore the statement at all. This feature is often called UPSERT, and in other DBMS we may know something very similar as MERGE command. However, it is not 100% MERGE implementation of the SQL standard in case of PostgreSQL, so it is not called like that. UPSERT implementation in PostgreSQL should also be more safe because CTE (common table expressions) might lead to race condition if not used properly. An example of inserting new tags into database and ignoring duplicate records may look like this:  
  1. INSERT INTO tags (tag) VALUES ('PostgreSQL'),('Database') ON CONFLICT DO NOTHING;
复制代码
Row-level security control

  Another feature that may substantially simplify SQL queries, is    row-level security control, that allowing check access on particular rows. For example, if every row includes information about who is owner of the record, we would need to check in the application that the currently logged user equals the owner column. With row-level security feature we may leave this to the DBMS and thus we can not only keep application logic clear, but we can also be a bit more sure that potential attacker would not get around, because it’s checked on one layer further. An example how to use the row-level security control may look like this:  
  1. CREATE POLICY policy_article_user ON articles
  2. FOR ALL TO PUBLIC
  3. USING (user = current_user);
  4. ALTER TABLE articles ENABLE ROW LEVEL SECURITY;
  5. SELECT * FROM articles;
  6. id | user | title
  7. ---+------+-------------------
  8.   1 | joe  | How I went to Rome
  9.   4 | joe  | My Favorite Recipe
  10. (2 rows)
复制代码
With this, currently logged user can only see items that were created by the user that is logged in.
  Other improvements

  Have you ever tried connecting two database servers into one instance, so application does not need to care about connections to more servers separately? This was already possible using CREATE FOREIGN TABLE, but one needed to re-define every table with every single column. And of course change it again, once structure of the foreign table changed.
  From version 9.5 we can import whole schema as easy as this, so not only we have simpler and less error-prone way to connect two remote databases, but it can be very handy also for data migration:
  1. IMPORT FOREIGN SCHEMA invoices
  2. LIMIT TO (customers, customers_invoices)
  3. FROM SERVER invoice.example.com INTO remote_invoices;
复制代码
What else we find in 9.5? Of course there are several performance enhancements, but that is almost a must for every release, right? What might not be that common for every release though is a brand new index type —    BRIN (Block Range Index). According to the documentation, it is designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table. In such cases the bitmap index scans may be used and performance of especially analytical queries might be substantially better.  
      Upgrading from PostgreSQL 9.4      

  Note that upgrading from PostgreSQL 9.4 is not automatic. If you have already used PostgreSQL in some previous version, you need to proceed with upgrade. Upgrade procedure, as it is common in case of PostgreSQL, is not an automatic procedure and admins are required to proceed with the steps manually. Fedora helps here a lot by providing    postgresql-setupbinary that accepts either    –initdb(for initializing the datadir) or    –upgradearguments and helps proceeding with the whole procedure almost automatically.  
  Warning: Do not forget to back-up all your data before proceeding with the upgrade.

  After system upgrade (F23 to F24 in this case), you will probably see something like this after trying to run PostgreSQL server:
  1. $ sudo systemctl start postgresql.service
  2. Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details.
复制代码
That’s because server knows about version of the datadir and refuses to start to not break anything. So let’s proceed with upgrade — install the    upgradesubpackage first:  
  1. $ sudo dnf install postgresql-upgrade
复制代码
Then run upgrade itself:
  1. $ sudo postgresql-setup --upgrade
  2. * Upgrading database.
  3. * Upgraded OK.
  4. WARNING: The configuration files were replaced by default configuration.
  5. WARNING: The previous configuration and data are stored in folder
  6. WARNING: /var/lib/pgsql/data-old.
  7. * See /var/lib/pgsql/upgrade_postgresql.log for details.
复制代码
At this point you should really look at the log file as suggested, but you should also be able to start the service now:
  1. $ sudo systemctl start postgresql.service
  2. $ sudo systemctl status postgresql.service
复制代码
And that’s all. Easy, right? As always, any feedback is welcome.
我要投稿

推荐阅读


回页顶回复上一篇下一篇回列表
手机版/c.CoLaBug.com ( 粤ICP备05003221号 | 粤公网安备 44010402000842号 )

© 2001-2017 Comsenz Inc.

返回顶部 返回列表