Hi,

Given the recent Covid-19 outbreak, PGDG (PostgreSQL Global Devrim Group) recently announced a few updates to the project: Continue reading "Updates from the PostgreSQL project"

Recently I was talking in a more general way about some common auditing / change tracking approaches for PostgreSQL…but it also made me curious, how it roughly looks from the performance side?

To quickly recap the previous blog post: the most common approaches for tracking important changes are mostly solved with writing some triggers. There are two main variations: table specific triggers / audit tables and a more generic approach with only one trigger function and one (or also many) generic audit tables, usually relying on PostgreSQL’s NoSQL features that allow quite a high degree of genericness in quite a convenient and usable way.

Obviously one could “guesstimate” that the generic approach would perform worse than the tailored approach as this is commonly the case in software. But the question is – how much worse? Can it be considered negligible? Well there’s only one way to find out I guess….and finally after finding some time to set up a small test case I can say that I was in for a bit of a surprise! But do read on for details or see the last paragraph for the executive summary.

Test schema for the “table specific” approach

As usual I turned to my old buddy pgbench, that aims to effortlessly aid with a typical OLTP transaction scenario (3 updates, 1 select, 1 insert – detailed SQL seen here). Based on that it quickly hacked up some SQL to create the “background” auditing tables for the 3 pgbench tables that get UPDATE-s in the default mode (FYI – there’s also other modes built in + one can use custom scripts) and attached the per table triggers. Although it’s quite a simple schema, there’s probably still too much code to list here – so this is only an excerpt to get the gist of it. The full script can be found here. Note that I didn’t bother to drop the history table populated by pgbench itself as there are no indexes on it, so it shouldn’t slow things down too much, plus I left it there for both test cases so everything should still be equal.

CREATE TABLE pgbench_accounts_log (
  mtim

[...]

Postgres Ibiza 2020: cancelled. Mark 2021 in your calendars!

The World has been taken by storm by the COVID-19. Our thoughts are with the people who, for any reason, are suffering from this. We are with you.

At Fundación PostgreSQL we have been watching carefully the events, and trying to keep Postgres Ibiza happening, as long as we could ensure a perfectly safe and healthy condition for organizing it. Even though there is a ray of light and we think a period like September may be safe to organize it, unfortunately, organizing a conference requires quality time. The same is needed for speakers, attendees, sponsors, volunteers to plan. Even in the more optimistic case, we will not reach a clear perspective with time enough for successful organization of the event.

So we have decided to postpone Postgres Ibiza until 2021. Refunds for booked attendees will be processed at the earliest –you will be contacted individually– as well as for sponsors.

Postgres Ibiza is not a conference that may go virtual. Postgres Ibiza, if anything, is about human connection. It’s about the hallway track, as Bruce Momjian said. It is an experience to network with peers, colleagues, partners, customers, friends. It is a conference to innovate and disrupt the future of Postgres. It is about quality time with the Postgres ecosystem people. This, we haven’t found a way to do online. Ping us if you know how. Otherwise, we will hope to meet you, in person, in 2021.

We will announce the event later this year, but we have secured some initially tentative days: 23rd-25th of June, 2021. Mark them in your calendar!

One very interesting PostgerSQL's feature are polymorphic types. It's interesting mainly for people who writes extensions.

Example: I can write own isnull function:

CREATE OR REPLACE FUNCTION isnull(anyelement, anyelement)
RETURNS anyelement AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql;

postgres=# SELECT public.isnull(NULL, 1);
┌────────┐
│ isnull │
╞════════╡
│      1 │
└────────┘
(1 row)

postgres=# SELECT public.isnull(NULL, CURRENT_DATE);
┌────────────┐
│   isnull   │
╞════════════╡
│ 2020-03-31 │
└────────────┘
(1 row)

-- but
postgres=# SELECT public.isnull(1, 1.1);
ERROR:  function public.isnull(integer, numeric) does not exist
LINE 1: SELECT public.isnull(1, 1.1);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

It is working for all supported types. One and big limit of type family any* is rigidity. It is working for all types, but for previous example, first and second parameter has to have exactly same types. Similar buildin functions are more tolerant - usually requires same type category only. This limit should be removed with new family of polymorphic types anycompatible. When there are more arguments with this type, then most common type is calculated and all arguments of anycompatible are casted to selected most common type:

CREATE OR REPLACE FUNCTION isnull(anycompatible, anycompatible)
RETURNS anycompatible AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql;

postgres=# SELECT public.isnull(1, 1.1);
┌────────┐
│ isnull │
╞════════╡
│      1 │
└────────┘
(1 row)

postgres=# SELECT public.isnull(NULL, 1.1);
┌────────┐
│ isnull │
╞════════╡
│    1.1 │
└────────┘
(1 row)

Now, it is working as expected.

This feature is interesting mainly for authors of extensions that has to emulate some different databases (like Orafce or similar).

PostgreSQL has a rich set of indexing functionality, and there are many articles explaining the syntax, usage, and value of the index. In this article, I will write basic and useful queries to see the state of database indexes. People develop databases and after some time, when there is a demand to do changes in the architecture of software, they forget to do the previous indexes’ cleanup. This approach creates a mess and sometimes slows down the database because of too many indexes. Whenever we do an update or insert, the index will be updated along with the actual table, therefore there is a need for cleanup.

There is a wiki page that has some queries related to PostgreSQL Index Maintenance.

Before writing the queries I want to introduce a catalog table pg_index. The table contains information about the index. This is the basic catalog table, all the index-based views use the same table.

1 – Sometimes you need to see how many indexes your table has. This query will show the schema-qualified table name and its index names.

db=# SELECT CONCAT(n.nspname,'.', c.relname) AS table,
    i.relname AS index_name FROM pg_class c
     JOIN pg_index x ON c.oid = x.indrelid
     JOIN pg_class i ON i.oid = x.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE c.relkind = ANY (ARRAY['r', 't']) AND c.relname like 'pgbench_accounts';
          table          | index_name       
-------------------------+------------------------
 public.pgbench_accounts | pgbench_accounts_pkey
 public.pgbench_accounts | pgbench_accounts_index
(2 rows)

2 – As we all know, an index is a performance feature, but along with that, it is also used to ensure uniqueness. But to ensure the uniqueness we need a separate type of index called a unique index. To check whether an index is unique or not, pg_index has a column named “indisunique” to identify the uniqueness of the index.

SELECT    i.relname AS index_name,
          indisunique is_unique
FROM      pg_class c
JOIN      pg_index x ON c.oid = x.indrelid
JOIN   

[...]

On 18th of March 2020, Alvaro Herrera committed patch: Enable BEFORE row-level triggers for partitioned tables   ... with the limitation that the tuple must remain in the same partition.   Reviewed-by: Ashutosh Bapat Discussion: https://postgr.es/m/20200227165158.GA2071@alvherre.pgsql So, I never made triggers on partitioned tables, so I was kinda sure that they would work anyway. So … Continue reading "Waiting for PostgreSQL 13 – Enable BEFORE row-level triggers for partitioned tables"

This is kind of frustrating… You’ve been at it for the best part of the day and you can’t figure it out. You already tested that Postgres was running and that you were attempting to connect to the right port and you checked the host several times but you still can’t connect to Postgres! I won’t give you a full review of all possible causes, because they are too many and I’m pretty sure I will forget some, but here ar some clues for you.

1. Overview

In previous two blogs, we explained how to setup Kerberos, and how to configure PostgreSQL to support GSSAPI user authentication. This blog will be focusing on how to check GSSAPI authentication, encryption and user principal information when given different connection options.

2. pg_stat_gssapi view

According to the official PostgreSQL document, “PostgreSQL supports GSSAPI for use as either an encrypted, authenticated layer, or for authentication only.“ To check the authentication, encryption and user principal, we need to use pg_stat_gssapi view, which is a dynamic statistics views containing one row per backend and showing the information about GSSAPI authentication and encryption used on this connection.

Before start the test below, make sure the PostgreSQL server and the psql client has the option --with-gssap enabled during build time.

3. Authentication and Encryption status

• Scenario 1:

Both authentication and encryption are enabled when the host-based authentication is configured with hostgssenc and gss in pg_hba.conf
Set below user authentication rule to pg_hba.conf and disable all other rules.

hostgssenc  postgres  postgres  192.168.0.102/32  gss include_realm=0 krb_realm=HIGHGO.CA

Initiate the user postgres credential cache using kinit, and then connect to PostgreSQL server with user postgres

postgres@pg:~$ psql -d postgres -h pg.highgo.ca -U postgres
psql (12.2)
GSSAPI-encrypted connection
Type "help" for help.

postgres=# SELECT pid, gss_authenticated, encrypted, principal from pg_stat_gssapi where pid = pg_backend_pid();
 pid  | gss_authenticated | encrypted |     principal      
------+-------------------+-----------+--------------------
 2274 | t                 | t         | postgres@HIGHGO.CA
(1 row)

postgres=#

From the result, we can see this connection is encrypted and the user is authenticated with principal postgres@HIGHGO.CA.

• Scenario 2:

The encryption will be disabled, but user authentication is still enabled when

[...]

PostgreSQL Person of the Week Interview with Sarah Conway Schnurr: I am from Southern California, where I’ve spent most of my free time hiking in the beautiful local deserts and pursuing many creative endeavors. Primarily, I am a software engineer & front-end website developer at Crunchy Data, a violin teacher & violin/viola performer, as well as a creator of zero-waste and all-natural homemade goods. I am also the co-parent of four beautiful cats, as well as the many adoptive fosters, strays, and friendly neighborhood cats that my husband and I visit on our daily walks.

This is the second part of the topic, the more historical version is described in the previous part, and here is the vitality of wal in PostgreSQL which born replication、logical replication and more performance related configure, let’s continue to redo it.

1. Replication(V9.0)

Replication is implemented here, and many corresponding GUC are added for replication. Corresponding to warm standby, replication can also be called hot standby, which helps to achieve the data synchronization using WAL record between the primary and the standby.

Most GUCs appear as [GUC6] in the image, will not talk about all of them but pick some important.

WAL_LEVEL

The current version of wal supports three levels: minimal, aichive and hot standby.

WAL_SENDER_DELAY

The wal sending process sends the wal logs generated by the master to the standby machine every other period of time, this parameter is used to configure the time interval.

WAL_KEEP_SEGMENTS

For some reasons, if the synchronization delay between the master and the standby is too long, it will cause the wal segments of the master to be removed before they are sent to the standby. In this case, the standby machine cannot synchronize the masterdata. Therefore, by configuring the wal ‘keep_segments’ parameter on the master, the WAL logs generated by the master will not be removed immediately, and the backup can have enough time complete data synchronization.

HOT_STANDBY

Configure whether to connect to this standby machine for query.

MAX_STANDBY_ARCHIVE_DELAY && MAX_STANDBY_STREAM_DELAY

When a wal redo operation conflicts with the currently executing query, you need to decide whether to wait for the query to complete redo or cancel the query to execute redo.This parameter sets a time to allow the query to continue executing. After this time, the query will be cancelled, and to finish the redo.

MAX_STANDBY_ARCHIVE_DELAY is used for file level wal transfer, that is, warm standby.

MAX_STANDBY_STREAM_DELAY is used for

[...]

WAL is one of the most important parts of PostgreSQL., WAL records all the database activity. Hense we can regard wal as a change roadmap of the history of PostgreSQL database, and the crash recovery, logical replication etc aren’t possible without WAL. The following picture describes the various wal related GUC (based on PG12) involved in the production and use of wal logs. It is very important for us to know the meaning of each parameter to optimize database performance and configure high availability cluster.. We can find the definition of each GUC in the PostgreSQL document, but we can hardly understand the intrinsic meaning of the parameter from a few simple lines, or we don’t know why it exists. What’s more, when you configure the database according to someone else’s blog, you find that your database version doesn’t know the configuration parameters in the blog. This blog will start from the PostgreSQL version 7.1 to review the development of wal log.

However, the history is so long, I want to divide it into two parts, first is for (V7.1-V8.3) which is a relatively old version, and another is for (V9.0-V12) which being developed and used.

1. WAL Genesis(V7.1)

I will not describe the POSTGRES project of Berkeley again. The topic today is a prehistoric civilization that is hard to search. Let us look at wal on PostgreSQL7.1 below, so clearly and we can hold everything.

The above diagram describes the WAL’s normal job only. The main concept of WAL is that any changes to data files must be written only after those changes have been logged – that is, when log records have been flushed to permanent storage. In this way, we don’t need to flush the data files in the shared cache from time to time, because if the database crashes, we can load the data from the wal. The purpose of this method is to replace random data writing with sequential writing wal to obtain higher execution efficiency.

“log records have been flushed to permanent storage”, there are similar words in the general i

[...]

When I started this, I thought it was be straight forward and take only a few hours at most. It turned into multiple hours over two days. This is part two of a two-part post. The first part dealt with changing a database column from time to timestamp. In this post: PostgreSQL 12.2 Nagios 3.5 [...]

The plan for today was not decided until after I got out of bed. I sat down with coffee and looked through the list of tasks. I found one which appealed: monitor the cache_clearing_ports & cache_clearing_dates tables This is part one of a two-part story. The first part deals with fixing a database table before [...]

Long time I am playing with psql and I try to enhance and fix some limits of this client. I like it. The work with this client is pretty fast, and with pspg the result's browsing is almost comfortable. But still there is disadvantage - is not possible to see result and query in one time, and isn't possible to work with more results in one time. But with new inotify support these limits are removed. You can create file that will be used as input/output buffer. Then you can start pspg against this file with --inotify option. When this file will be changed (by psql), then pspg reread this file immediately.

[pavel@nemesis ~]$ touch ~/r1
[pavel@nemesis ~]$ touch ~/r2
[pavel@nemesis ~]$ touch ~/r3

Then start in three terminals pspg (I use old terminator or new tilix)

[pavel@nemesis ~]$ pspg -f ~/r1 --inotify
[pavel@nemesis ~]$ pspg -f ~/r2 --inotify
[pavel@nemesis ~]$ pspg -f ~/r3 --inotify

and now, you can play

psql
postgres=# select * from pg_class \g ~/r1
postgres=# select * from pg_proc \g ~/r2
postgres=# select * from pg_database \g ~/r3

And I see queries and results

After 20 years in professional PostgreSQL support and consulting we are finally able to answer one of the most frequently asked questions: “How can I see all active query plans?” Ladies and gentlemen, let me introduce you to pg_show_plans, an extension which does exactly that. pg_show_plans is Open Source and can be used free of charge as a standard PostgreSQL extension.

How does pg_show_plans work?

pg_show_plans uses a set of hooks in the PostgreSQL core to extract all the relevant information. These plans are then stored in shared memory and exposed via a view. This makes it possible to access these plans in an easy way.

 

 

The performance overhead of pg_show_plans will be discussed in a future blog post. Stay tuned for more information and visit our blog on a regular basis.

Installing pg_show_plans

pg_show_plans is available on GitHub for free and can be used free of charge.

Just clone the GitHub repo:

iMac:src hs$ git clone https://github.com/cybertec-postgresql/pg_show_plans.git

Cloning into 'pg_show_plans'...

remote: Enumerating objects: 54, done.

remote: Counting objects: 100% (54/54), done.

remote: Compressing objects: 100% (25/25), done.

remote: Total 54 (delta 31), reused 52 (delta 29), pack-reused 0

Unpacking objects: 100% (54/54), done.

Then “cd” into the directory and set USE_PGXS. USE_PGXS is important if you want to compile the code outside of the PostgreSQL source tree:

iMac:src hs$ cd pg_show_plans

iMac:pg_show_plans hs$ export USE_PGXS=1

Finally you can run “make” and “make install”

iMac:pg_show_plans hs$ make

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -g -O2  -I. -I./ -I/Users/hs/pg12/include/postgresql/server -I/Users/hs/pg12/include/postgresql/internal    -c -o pg_show_plans.o pg_show_plans.c

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-

[...]

1. Overview

In previous blog, we have setup Kerberos, added all required principals and verified each principal. This blog will explain all the necessary configuration, i.e. postgresql.conf, pg_hba.conf and pg_ident.conf, in PostgreSQL for user authentication using GSSAPI with Kerberos.

2. Build PostgreSQL with GSSAPI

The official PostgreSQL release for Ubuntu has GSSAPI enabled for user authentication with Kerberos, however if you want to build it from source code, you can simply enable it by giving the option --with-gssapi in configure process like below.

$ ./configure --with-gssapi --prefix=/home/postgres/pgapp

There is another option --with-krb-srvnam, PostgreSQL uses the default name of the Kerberos service principal. According to Postgres official document, “There’s usually no reason to change this unless you have a Windows environment, in which case it must be set to upper case POSTGRES“. In this blog, we keep it as default, and no extra configuration required for it. After enabled --with-gssapi option, you can build, install, initialize database, change configuration and start database service as normal. The commands used in this blog are list below.

make && make install
export PATH=/home/postgres/pgapp/bin:$PATH
export PGDATA=/home/postgres/pgdata/data 
initdb -D $PGDATA

3. Keytab file

If you have followed the previous blog “part-1: how to setup Kerberos on Ubuntu”, then you should already have the keytab file. Now, copy the keytab file to Service Server (Postgres Server) and put it to a folder with appropriate permissions to the user owning the Postgres process. For example, user postgres and folder /home/postgres/pgdata/data in this blog. The instance principal extracted from KDC server can be verified using ktutil on Service Server.

postgres@pg:~$ ktutil 
ktutil:  list
slot KVNO Principal
---- ---- ---------------------------------------------------------------------
ktutil:  read_kt postgres.keytab 
ktutil:  list
slot KVNO Principal
---- ---- ------------------------------

[...]

This is my first blog in a series of SQL optimization blogs. So expect some basic information in here along with some nice insights. My aim is to help you walk through a complete process of understanding and optimizing queries for improved performance. A PostgreSQL server attempts to find the most effective way of building a result set for the query. The first step in that direction is the ability to understand what the “EXPLAIN” command is. 

When a statement is sent to a PostgreSQL server for execution, it must decipher various parts of that query and define an execution plan. Given that data can be retrieved and managed/manipulated in various different ways, a PostgreSQL server attempts to find the most effective way of building a result set for the query. Especially in case of long running queries where performance matters, an execution plan for our statement on a given platform with our existing server configuration determines overall query (and in most cases, application) performance. It is therefore important to be able to understand how a PostgreSQL server breaks down a query execution in form of an execution plan. This is where the “EXPLAIN” command comes in. In very basic terms, “EXPLAIN” command shows (explains) the execution plan of a statement.

Query processing in PostgreSQL is divided into 5 main components:

1. Parser
2. Analyzer
3. Rewriter
4. Planner
5. Executor

Whereas (1) parser and (2) analyzer are concerned with ensuring that query is written correctly, the result is a query tree which is passed onto (3) rewriter that may transform it based on rules defined in the pg_rules system catalog table. So this transformed query tree is passed on the (4) planner which generates a plan for execution by (5) executor.

The EXPLAIN Command

“EXPLAIN” command has a simple grammar as defined in PostgreSQL documentation. “EXPLAIN” command only explains a given statement, however when run with the “ANALYZE” option, it also executes it. The result set is never thrown back to the

[...]

We recently helped migrate a production PostgreSQL database running on a given DBaaS platform… to another DBaaS platform. When it comes to vendor “lock-in”, some providers are easier (and even friendlier) to deal with than others, but it is never a straightforward process. While in a traditional environment we would usually approach this problem by creating a replica of the server and switching over database requests at the right moment, the situation complicates when it is not possible to create a direct replication flow outside the provider’s platform.

In this post, we’ll share the approach we used for this particular case. It is not intended to provide a step-by-step, one-size-fits-all solution, but we hope it might serve you as “inspiration” when dealing with a similar situation. You may find some parts of our approach more useful than others; consider the ones that suit your unique needs and be creative. As always, regardless of the plan you design, make sure you test all involved steps in a testing/staging environment first as in practice we often step into yet another stone we didn’t know was laying in our path.

Intermediating DBaaS Instances

When we cannot have direct replication between DBaaS instances, we can still attempt to “chain it” somehow through an intermediate player (read: server):

source DBaaS → Intermediate server → target DBaaS

In this particular case, the source DBaaS was producing daily base backups and streaming WAL segments to externally accessible storage, an S3 bucket, for archiving purposes. We took advantage of this to create an intermediate PostgreSQL server from the latest base backup available and configured it to consume the WAL segments available on this storage. Be mindful of where to install this intermediate server: you may want to install it either closer to the source DBaaS or yet closer to the target DBaaS instead. When making your choice, consider network bandwidth as well as the availability of common tools by the infrastructure provider.

Howe

[...]

© Laurenz Albe 2020

 

Embedded SQL is by no means a new feature — in fact it is so old-fashioned that many people may not know about it at all. Still, it has lots of advantages for client code written in C. So I’d like to give a brief introduction and talk about its benefits and problems.

What is embedded SQL?

Typically you use a database from application code by calling the API functions or methods of a library. With embedded SQL, you put plain SQL code (decorated with “EXEC SQL”) smack in the middle of your program source. To turn that into correct syntax, you have to run a pre-processor on the code that converts the SQL statements into API function calls. Only then you can compile and run the program.

Embedded SQL is mostly used with old-fashioned compiled languages like C, Fortran, Pascal, PL/I or COBOL, but with SQLJ there is also a Java implementation. One reason for its wide adoption (at least in the past) is that it is specified by the SQL standard ISO/IEC 9075-2 (SQL/Foundation). This enables you to write fairly portable applications.

To be able to discuss the features in some more detail, I’ll introduce a sample C program using embedded SQL.

Sample embedded SQL program

The sample program operates on a database table defined like this:

CREATE TABLE atable(
   key integer PRIMARY KEY,
   value character varying(20)
);

The program is in a file sample-pgc and looks like this:

#include 
#include 

/* error handlers for the whole program */
EXEC SQL WHENEVER SQLERROR CALL die();
EXEC SQL WHENEVER NOT FOUND DO BREAK;

static void die(void)
{
        /* avoid recursion on error */
        EXEC SQL WHENEVER SQLERROR CONTINUE;

        fprintf(
                stderr,
                "database error %s:\n%s\n",
                sqlca.sqlstate,
                sqlca.sqlerrm.sqlerrmc
        );

        EXEC SQL ROLLBACK;
        EXEC SQL DISCONNECT;

        exit(1);

        /* restore the original handler */
        EXEC SQL WHENEVER SQLERROR CALL d

[...]

1. Introduction

PostgreSQL and MongoDB are two popular open source relational (SQL) and non-relational (NoSQL) databases available today. Both are maintained by groups of very experienced development teams globally and are widely used in many popular industries for adminitration and analytical purposes. MongoDB is a NoSQL Document-oriented Database which stores the data in form of key-value pairs expressed in JSON or BSON; it provides high performance and scalability along with data modelling and data management of huge sets of data in an enterprise application. PostgreSQL is a SQL database designed to handle a range of workloads in many applications supporting many concurrent users; it is a feature-rich database with high extensibility, which allows users to create custom plugins, extensions, data types, common table expressions to expand existing features

I have recently been involved in the development of a MongoDB Decoder Plugin for PostgreSQL, which can be paired with a logical replication slot to publish WAL changes to a subscriber in a format that MongoDB can understand. Basically, we would like to enable logical replication between MongoDB (as subscriber) and PostgreSQL (as publisher) in an automatic fashion. Since both databases are very different in nature, physical replication of WAL files is not applicable in this case. The logical replication supported by PostgreSQL is a method of replicating data objects changes based on replication identity (usually a primary key) and it would be the ideal choice for this purpose as it is designed to allow sharing the object changes between PostgreSQL and multiple other databases. The MongoDB Decoder Plugin will play a very important role as it is directly responsible for producing a series of WAL changes in a format that MongoDB can understand (ie. Javascript and JSON).

In this blog, I would like to share some of my initial research and design approach towards the development of MongoDB Decoder Plugin.

2. Architecture

Since it is not possibl

[...]

There is a lot of interest and discussions lately in the PostgreSQL world to make it a scale-out solution. Among other possible solutions, one of the most promising ones is to implement the sharding using FDW and table partitioning for distributing the data on multiple servers. As of now, PostgreSQL can only fetch the data from FDW in serial and that is one thing that needs to be improved to increase the performance. Other than that performance improvement currently we are missing two major features in PostgreSQL to make it full fledge distributed database. These are related to the ACID property, atomic commit, and atomic visibility.

This article is to explain what is atomic commit and atomic visibility and why we need it in PostgreSQL to make it a full fledge scale-out database solution.

Atomic commit

When we talk about database system that consists of multiple servers the first thing that comes in mind is two-phase commit protocol (2PC). Using a 2PC protocol we can achieve an atomic commit for transactions involving multiple servers (distributed transactions). As the name of the two-phase commit protocol suggests that, 2PC commits the transaction in two steps rather that one. In the first step, the transaction is prepared across all the participating servers and depending upon the result of prepare the second step performs the commit prepared or rollback prepared. Dividing the commit into two steps helps in building the consensus among all nodes if every node can commit the transaction or not, before performing the actual commit. But unlike simple transactions, the prepared transactions have a very interesting property that they are not bound to the database session and keep persisting even when the session that created that transaction dies.

This very property of the prepared transactions makes them very powerful and usable for 2PC. As to implement 2PC we don’t want to see a prepared transaction vanished (because of session disconnection or even a server crash) once after we get confirm

[...]

Let’s wrap this up. In part 1 of this blog series, I explained the different Windows build targets supported by PostgreSQL. In part 2, I showed how to test patches for Windows compatibility and how to easily spin up a Windows environment for testing and simple development. Now, in this third and final part, we’ll […]

In our previous posting on tile serving, we showed off how pg_tileserv can use database functions to generate tiles by querying existing tables with user parameters.

We can also use functions to build geometry on the fly without input from tables. For example, hexagons!

 

Hexagons are a favourite input for visualizations, because they have a nice symmetric shape and provide equal areas for summarization.

Hexagonal Grid

A filling of the plane with hexagons is a hexagonal grid. Curiously, it's possible to address any hexagon in the grid with a two-dimensional coordinate, just like with a square grid, the only difference being that every second column of the hexgrid is offset one-half a hexagon vertically.

Users had often asked where they could find older versions of packages from apt.postgresql.org. I had been collecting these since about April 2013, and in July 2016, I made the packages available via an ad-hoc URL on the repository master host, called "the morgue". There was little repository structure, all files belonging to a source package were stuffed into a single directory, no matter what distribution they belonged to. Besides this not being particularly accessible for users, the main problem was the ever-increasing need for more disk space on the repository host. We are now at 175 GB for the archive, of which 152 GB is for the morgue.

Our friends from yum.postgresql.org have had a proper archive host (yum-archive.postgresql.org) for some time already, so it was about time to follow suit and implement a proper archive for apt.postgresql.org as well, usable from apt.

So here it is: apt-archive.postgresql.org

The archive covers all past and current Debian and Ubuntu distributions. The apt sources.lists entries are similar to the main repository, just with "-archive" appended to the host name and the distribution:

deb https://apt-archive.postgresql.org/pub/repos/apt DIST-pgdg-archive main
deb-src https://apt-archive.postgresql.org/pub/repos/apt DIST-pgdg-archive main

The oldest PostgreSQL server versions covered there are 8.2.23, 8.3.23, 8.4.17, 9.0.13, 9.1.9, 9.2.4, 9.3beta1, and everything newer.

Some example:

$ apt-cache policy postgresql-12
postgresql-12:
  Installed: 12.2-2.pgdg+1+b1
  Candidate: 12.2-2.pgdg+1+b1
  Version table:
 *** 12.2-2.pgdg+1+b1 900
        500 http://apt.postgresql.org/pub/repos/apt sid-pgdg/main amd64 Packages
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archive/main amd64 Packages
        100 /var/lib/dpkg/status
     12.2-2.pgdg+1 500
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archive/main amd64 Packages
     12.2-1.pgdg+1 500
        500 https://apt-archive.postgresql.org/pub/repos/apt sid-pgdg-archi

[...]

ncurses-st-menu is small library over ncurses for pulldown and menubar menu. Stripped size of library has 43kB, so it is not extra light, but it is in category smaller libraries. One use case, that I would to support, is using in smaller application on smaller devices. For this purpose this library supports scrollable menu now. When the content of menu is longer then display, then the content will be scrollable - so cursor will be visible every time.

PostgreSQL Person of the Week Interview with Dave Cramer: I’m a Canadian currently hiding from winter in a warm southern state. As for hobbies: I started taking my car to the race track to see how well I understood physics. Turns out the theory and application are more interesting when you are the object in motion. I’ve been involved in PostgreSQL since around 2000. Through reading and answering many emails on the JDBC list I found myself contributing code to it and eventually maintaining it. I consider myself to be very fortunate to be able to make a living from PostgreSQL.

This post is part of several blog posts (at least two!) about Troubleshooting Postgres. Last time, we found out if Postgres was running and how to start it. Please find here all the troubleshooting posts < ref “/tags/troubleshooting/” >. Here is my checklist of troubleshooting Postgres: [] Try to start postgres (if this works, stop here) [] Locate the log file [] Look at the messages in Postgres logfile To start Postgres, please refer to < relref “content/post/troubleshooting-01.

As for now, there are no strict official restrictions in Ukraine. However, the trend seems to be evident, and the COVID-19 epidemic will be in an active phase until June-July, 2020.

Taking this into account, we are postponing the conference. We’d like to kindly remind you, we’re not canceling the PGDay Ukraine 2020, it’s moved to Fall 2020. All purchased tickets are valid. You are still our participants and guests.

We believe September 5th or September 26th are the best options, but we are still in negotiations within the community and other conference teams about the date.

The Conference Remains in Force!

We understand that there are tough times nowadays. So if you want to refund, please, fill in the form, and we will return the funds to your card during the next 72 hours.

We are looking forward to seeing you!

Sincerely,
PGDay Ukraine team

pg_timetable: Advanced PostgreSQL cron-like scheduler released!

Hello there, this is your developer speaking…

I’m struggling with how to start this post, since I’ve procrastinated a lot. There should be at least 5 published posts on this topic already. But there is only one by Hans-Jürgen Schönig. I’m thanking him for trying to cover my back, but the time has come!

Major release? But why version 2?

Well, that’s a tricky question. We’ve started working on pg_timetable a year ago, aiming our own targets. Then we’ve found out that this project might be interesting for other people and we opened it.

Soon we received some feedback from users and improved our product. There were no releases and versions at that time. But suddenly, we faced a situation where you cannot grow or fix anything vital, until you introduce changes in your database schema. Since we are not a proprietary project anymore, we must provide back-compatibility for current users.

Precisely at this time, you understand that there is a need for releases (versions) and database migrations to the new versions.

Major versions usually change the internal format of system tables and data representation. That’s why we go for the new major version 2, considering the initial schema as version 1.

This means that, if you have a working installation of the pg_timetable, you will need to use –upgrade command-line option, but don’t forget to make a backup beforehand.

So what are those dramatic changes?

Brand new cron-style schedules

How scheduling information was stored before:

CREATE TABLE timetable.chain_execution_config (
...
  run_at_minute INTEGER,
  run_at_hour INTEGER,
  run_at_day INTEGER,
  run_at_month INTEGER,
  run_at_day_of_week INTEGER,
...
);

Turned out, it’s incredibly inconvenient to store schedules one job per line because of the UI. Consider this simple cron syntax:
0 */6 * * * which stands for “every 6th hour”.

So in the old model, it will produce 4 rows for hours 6, 12, 18, 24.

It’s

[...]

This troubled times made me work harder than before to finish my ongoing missions and after that, I found myself somehow left with little to do and that’s kind of disturbing. So, why not take this time to write down that article born into my brain this morning while eating breakfast ? I’m on Postgres slack and Telegram group, more rarely on [IRC[(https://www.postgresql.org/community/irc/). I often see the same question, again and again : “Postgres doesn’t work, can you help me?