Planet PostgreSQL, Hacker News

  We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL (Windows) – bit.

  To use these, copy the files into your PostgreSQL (Windows) – bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt

Continue reading “PostgreSQL 11 64 – bit Windows FDWs “

  Soon it’s that time of the year again – basically a 2nd Christmas for followers of the “blue elephant cult” if you will :). I’m, of course, referring to the upcoming release of the next PostgreSQL major version, v 12. So I thought it’s about time to go over some basics on upgrading to newer major versions! Database upgrades (not only Postgres) are quite a rare event for most people (at least for those running only a couple of DB-s). Since upgrades are so rare, it’s quite easy to forget how easy upgrading actually is. Yes, it is easy – so easy that I can barely wrap my head around why a lot of people still run some very old versions. Hopefully, this piece will help to convince you to upgrade faster in the future :). For the TLDR; (in table version) please scroll to the bottom of the article.

  Why should I upgrade in the first place?

  Some main points that come to my mind:

• More security
      Needless to say, I think security is massively important nowadays! Due to various government regulations and the threat of being sued over data leaks, mistakes often carry a hefty price tag. Both new and modern authentication methods and SSL / TLS version support are regularly added to new PostgreSQL versions.   

  

• More performance without changing anything on the application level!
      Based on my gut feeling and on some previoustesting, typically in total around ~ 5 – 15 % of runtime improvements can be observed following an upgrade (if not IO-bound). So not too much… but on some releases (9.6 parallel query, 10 .0 JIT for example) a possibly game changing 30 – 40% could be observed. As a rule, the upgrades only resulted in upsides – over the last 8 years, I’ve only seen a single case where the new optimizer chose a different plan so that things got slower for a particular query, and it needed to be re- written.   

  

• New features for developers   

  

• Every year, new SQL standard implementations, non-standard feature additions, functions and constructs for developers are introduced, in order to reduce the amounts of code writt

[…]

  The world’s most valuable resource is no longer the oil or gold, but data. And at the heart of data lies the database which is expected to store, process and retrieve the desired data as quickly as possible. But having a single database server doesn’t mostly serve the purpose. A single server has its drawbacks with a huge risk of data loss and downtime.

  So most data platforms use multiple replicated database servers to ensure high availability and fault tolerance of their databases. In such an environment, how can we maximize performance against the resourced being used?

  One of the questions I get asked very often from Pgpool-II users is, what is the performance benefit of using load balancing of Pgpool-II? Once in a while, we get complains from users that they get better performance when they connect directly to PostgreSQL than through Pgpool-II, even when the load balancing is working fine. How true is this complain?

  In this blog, I’ll benchmark the performance of the Pgpool-II load balancing feature against a direct connection to PostgreSQL.

  Before we start measuring the actual performance, let’s start with what is Pgpool-II load balancer and why we should use it.

  Replicated PostgreSQL servers.

  Almost in every data setup, we need more than one copy of database servers to ensure minimum or zero downtime and to safeguard against data loss. For that, we use the replicated database servers.

  There are many ways to create a replicated PostgreSQL cluster, which is the topic for some other blog post, but most of the replication solution exists for PostgreSQL supports one-way replication. That means one master PostgreSQL server feeding data to one or multiple standby servers. and in this setup, only maser server is capable of handling the write queries while standby servers sit idle waiting for the moment when the master goes down and one of them gets promoted to become a new master.

  This setup is good enough to handle server failures and to provide the high availability but it is not

[…]

  ThePostreSQL Operatorprovides users with a few different methods to perform PostgreSQL cluster operations, via:

• a REST API   

  

• pgo, PostgreSQL Operator Command Line Interface (CLI)   

  

• Directly interfacing with Kubernetes, including various APIs and custom resource definitions (CRDs).   

  While the REST API and pgo provide many conveniences, there are use cases that require the third method, i.e. interacting directly with Kubernetes to create the desired PostgreSQL cluster setup.

  To demonstrate this, let’s look at how we can manipulate the CRD responsible for managing PostgreSQL clusters to create a new cluster managed by the PostgreSQL Operator.

  PostgreSQL connection stringsembedded in your application can take two different forms: the key-value notation or thepostgresql: //URI scheme. When it comes to usingpsqlthough, another form of connection string is introduced, with command line options- h -p -Uand environment variable support.

  In this short article you will learn that you can use either of the three different forms inpsqland thus easily copy & paste you application connection string right at the console to test it!

  Inmy previous post about pgBackRest, we saw how to install and setup pgBackRest and make a backup of a PostgreSQL database with it. It was a very basic single server setup, only intended to get the hang of the tool. Such setups are not used in a production environment, as it is not recommended (or rather does not serve the purpose) to perform the backup on the same server where the database is running.

  So: let’s get familiar with how remote backup servers are set up with pgBackRest, and how a full and incremental backup is performed from the backup server and restored on the database server.

  We need two servers. Let’s call ours:

1.     pgbackup  

  

2.     DB1  

  Installing pgbackrest:

  We need to install pgBackRest on the database and the backup server. Make sure you install the same version on both.

  For the database server, please follow the installation steps frommy previous post. The steps are slightly different for the backup server, since it is a better practice to create a separate user to own the pgBackRest repository.

  Create apgbackrestuser on the backup server

sudo adduser --disabled-password --gecos "" pgbackrest

  Install required Perl package and pgBackRest from a package or manually on pgbackup as below

sudo apt-get install libdbd-pg-perl sudo scp BUILD_HOST: / root / pgbackrest-release-2. 14 / src / pgbackrest / usr / bin / sudo chmod 755 / usr / bin / pgbackrest

  Create pgBackRest configuration files, directories and repository onpgbackup

sudo mkdir -p -m 770 / var / log / pgbackrest sudo chown pgbackrest: pgbackrest / var / log / pgbackrest sudo mkdir -p / etc / pgbackrest sudo mkdir -p /etc/pgbackrest/conf.d sudo touch /etc/pgbackrest/pgbackrest.conf sudo chmod 640 /etc/pgbackrest/pgbackrest.conf sudo chown pgbackrest: pgbackrest /etc/pgbackrest/pgbackrest.conf  sudo mkdir -p / var / lib / pgbackrest sudo chmod 750 / var / lib / pgbackrest sudo chown pgbackrest: pgbackrest / var / lib / pgbackrest

  Now we are ready to proceed with enabling c

[…]

Master branch ofPSPGsupports sort by column selected by vertical cursor.

Why I wrote this feature? You can try to list of tables inpsqlby DT command. The result is sorted by schema and by name. Sometimes can be interesting to see result ordered by table’s sizes. Now it is easy. PressAlt-vto show vertical cursor. Later move to right to “Size” column. Then pressdas descendent sort. You can get resuly like attached screenshot:

The sort is working only on numeric columns (but units used byPSQLare supported)

  PostgreSQL12, the latest version of the “world’s most advanced open source relational database,” is being released in the next few weeks, barring any setbacks. This follows the project’s cadence of providing a raft of new database features once a year, which is quite frankly, amazing and one of the reasons why I wanted to be involved in the PostgreSQL community.

  In my opinion, and this is a departure from previous years, PostgreSQL 12 does not contain one or two single features that everyone can point to and say that “this is the ‘FEATURE’ release,” (partitioning and query parallelism are recent examples that spring to mind). I’ve half-joked that the theme of this release should be “PostgreSQL 12: Now More Stable “- which of course is not a bad thing when you are managing mission critical data for your business.

  And yet, I believe this release is a lot more than that: many of the features and enhancements in PostgreSQL 12 will just make your applications run betterwithout doing any work other than upgrading!

  (… and maybe rebuild your indexes, which, thanks to this release, is not as painful as it used to be)!

  It can be quite nice to upgrade PostgreSQL and see noticeable improvements without having to do anything other than the upgrade itself. A few years back when I was analyzing an upgrade of PostgreSQL 9.4 to PostgreSQL 10, I measured that my underlying application was performing much more quickly: it took advantage of the query parallelism improvements introduced in PostgreSQL 10. Getting these improvements took almost no effort on my part (in this case, I set themax_parallel_workersconfig parameter).

  Having applications work better by simply upgrading is a delightful experience for users, and it’s important that we keep our existing users happy as more and more people adopt PostgreSQL.

  So, how can PostgreSQL 12 make your applications better just by upgrading? Read on!

  The PostgreSQL Conference Europe team will allow all PostgreSQL related Open Source projects to place flyers or stickers on the PGEU table during the conference.

  The following conditions apply:

• The material must be about a PostgreSQL related project   

  

• No company sponsoring or endorsement is allowed on the material (this includes the backside, small print, and also includes any advertisement for the printing company )   

  We reserve the right to remove or return any material which we deem not suitable for the conference. Please talk to our staff before placing material on the table.

  We expect around 550 visitors this year, but people might grab more material or stickers for friends or colleagues.

  In order to prepare for the start of training season (you can see our “stock” training offeringshereby the way ), I’ve updated our small “Postgres features showcase”project, and thought I’d echo it out too. Main changes – coverage on some features of the current v 11 release and also from the upcoming v 12.

  Short project background

  The project was introduced some two yearsago– the main idea of ​​theprojectitself is to provide a compact and “hands-on” set of commented samples to help newcomers get up to speed with Postgres. Of course, one cannot beat official documentation but sadly, the documentation for Postgres doesn’t have a dedicated folder for working code samples. However, thetutorialcan be helpful. So, the idea is to provide some commented SQL for the “code-minded” people to quickly see and “feel” the concepts to get the gist of it faster.

  Updated list of topics covered

• Connecting   

  

• Creating databases / roles   

  

• Transaction management   

  

• Creating / altering tables   

  

• Partitioning and other table modifiers   

  

• Most common data types   

  

• Constraints   

  

• Views / materialized views   

  

• Stored functions / procedures   

  

• Triggers   

  

• Enums / custom types   

  

• Extensions   

  

• Row-level security   

  

• Analytical and parallel queries   

  

• Indexing   

  

• String processing and arrays   

  RFC

  If you see that this project is somewhat useful but could be improved even more, we would be very glad if you take the time to provide your ideas asGithub issues– or even better – directly asPull Requests. Thanks!

  The postUpdates for the Postgres Showcase projectappeared first onCybertec.

  Nine months ago, I started development of a key management extension forpgcrypto.The tool is calledpgcryptokeyand is now ready for beta testing.

  It uses two-levels of encryption, with an access password required to use the cryptographic keys. It supports setting and changing the access password, multiple cryptographic keys, key rotation, data reencryption, and key destruction. It also passes the access password from client to server without it appearing in clear text in SQL queries, and supports boot-time setting. The extension leverages pgcrypto and Postgrescustom server variables.

PLpgSQL is simple (but relatively power) specialized language. It is specialized for usage inside PostgreSQL as glue of SQL statements. It is great language for this purpose (and can be bad if it is used differently – for high cycles numeric calculation for example).

Originally a implementation of PLpgSQL language was really simple – it was very simple language with possibility to execute embedded SQL. But there was not possibility to check syntax of this SQL in validation time.

Note: PLpgSQL is a interpret of AST (abstract syntax tree) nodes. There is a valid ation stage (when code is parsed into AST), and evaluation stage (runtime), when AST (parsed again when code is first executed) is interpreted.

Old SPI (Stored Procedure Interface) had not any API for validation of SQL without execution. Almost all checks in this time was at runtime. It was not too practical – so some checks (check of SQL syntax) are at validation stage now. Still PLpgSQL validator doesn’t check a validity of SQL identifiers (tables, columns, functions, … names). Now, there are two reason why the validator doesn’t do it: a) we have not any other tools how to solve cyclic dependencies, b) Postgres’s local temp tables – PostgreSQL temporary tables are created at runtime and doesn’t exists at validation time – so PLpgSQL validator should not to check validity of SQL identifiers – they should not to exists in this time.

CREATE TABLE bigtable (id int, v int);
INSERT INTO bigtable
(SELECT random ()  ***********************************************************************************************************************************), random () * 10000
FROM generate_series (1, 1000000);
CREATE INDEX ON bigtable (id);
VACUUM ANALYZE bigtable;
CREATE OR REPLACE FUNCTION example 01 (_ id numeric)
RETURNS numeric AS $$
DECLARE
r record;
s numeric DEFAULT 0;
BEGIN
FOR r IN SELECT * FROM bigtable WHERE id=_id
LOOP
s:=s   rk;
END LOOP;
END;
$$ LANGUAGE plpgsql;