Date: October 24, 2019

Guest Speaker: Andrew Nelson, Developer Advocate from YugaByte

About Vancouver Postgres User Meetup Group

Vancouver Postgres is a Postgres user meetup group based in Vancouver, Canada. It specializes in building Postgres users to the related ecosystem including but not limited to technologies such as RDS Postgres, Aurora for Postgres, Google Postgres, PostgreSQL.Org Postgres, Greenplum, Timescale and ZomboDB.

User Group Home Page: https://www.meetup.com/Vancouver-Postgres/

Guest Speaker: Andrew Nelson

We are pleased to announced Andrew Nelson from YugaByte as guest speaker, who will share his extensive knowledge in distributed SQL databases and kubernetes deployment to the local Vancouver meetup group.

About the Presentation

Here in Vancouver, Andrew shared the 4 important stages to deploy distributed databases with Kubernetes with great emphasis on the Design stage.

• Design
• Release Management
• Operations
• Monitoring

Andrew did a fantastic job to deliver this technical presentation in a fun and interesting way by using real life references to demonstrate the 4 important stages.

He uses bricks and mortars to emphasize the importance of fundational work, the Mondadnock Building in Chicago as a building built solely by bricks to illustrate the importance of good management and operation, and finally to the empire state building in New York as a skyscraper build by bricks supported b

[...]

We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 12 Windows 64-bit.

To use these, copy the files into your PostgreSQL 12 Windows 64-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 12 64-bit for Windows FDWs"

When you build replication in PostgreSQL using Streaming replication, you cannot perform writes to a standby node; only reads. This way, you could offload reads or reporting queries to standby servers and send writes to master. Additionally, starting from PostgreSQL 10,

libpq

  and

psql

clients could probe the connection for a master and allow connections to a master for

read-write

or

any

node for

read-only

connections automatically.

For example, consider three database nodes – Server_A, Server_B, and Server_C in replication using streaming replication, with Server_A being the Master/Primary node. You could specify all three servers in a connection string and request the connection to be redirected to a

read-write

node only, which is Server_A in this scenario. If a failover or a switchover happened to Server_B, the

read-write

connections will be automatically redirected to Server_B. To understand this in detail, let us see a simple scenario in action.

I have set up a three-node replication cluster using streaming replication with the following roles.

192.168.70.10 is the master
192.168.70.20 is the first standby
192.168.70.30 is the second standby

$psql -h 192.168.70.10
Password for user postgres:
psql (11.5)
Type "help" for help.

postgres=# select inet_server_addr() as "connected_to";
connected_to
---------------
192.168.70.10
(1 row)

postgres=# select client_addr, write_lag,flush_lag,replay_lag from pg_stat_replication;
client_addr | write_lag | flush_lag | replay_lag
---------------+-----------------+-----------------+-----------------
192.168.70.20 | 00:00:00.058204 | 00:00:00.058617 | 00:00:00.058619
192.168.70.30 | 00:00:00.03639 | 00:00:00.036689 | 00:00:00.036693
(2 rows)

Now, let us use

psql

with all the three IPs specified in the connection string. We would, however, use

target_session_attrs

this time to connect to a master node.

Connecting to Master Using Read-Write Mode

$ psql 'postgres://192.168.70.20:5432,192.168.70.10:5432,192.168.70.30:5432/postgres?target_session_attrs=read-write' -

[...]

Registration for pgDay Paris 2020 is now open.

We have some new ticket types this year, including a very cheap BLIND ticket that is only sold until the schedule is published. If you know you are coming to the conference no matter what, hurry up and grab one of these!

We also have cheap tickets this year for students and the unemployed. Valid proof is required.

https://2020.pgday.paris/registration/

© Laurenz Albe 2019

 

“Durability”, the D of ACID, demands that a committed database transaction remains committed, no matter what. For normal outages like a power failure, this is guaranteed by the transaction log (WAL). However, if we want to guarantee durability even in the face of more catastrophic outages that destroy the WAL, we need more advanced methods.

This article discusses how to use pg_receivewal to maintain durability even under dire circumstances.

Archiving WAL with archive_command

The “traditional” method of archiving the transaction log is the archive_command in postgresql.conf. The DBA has to set this parameter to a command that archives a WAL segment after it is completed.

Popular methods include:

• Use cp (or copy on Windows) to copy the file to network attached storage like NFS.
• Call a command like scp or rsync to copy the file to a remote machine.
• Call an executable from your favorite PostgreSQL backup software

The important thing to consider is that the archived WAL segment is stored somewhere else than the database.

I have a redundant distributed storage system, do I still need to store WAL archives somewhere else?

Yes, because there is still a single point of failure: the file system.
If the file system becomes corrupted through a hardware or software problem, all the redundant distributed copies of your WAL archive can vanish or get corrupted.

If you believe that this is so unlikely that it borders on the paranoid: I have seen it happen.
A certain level of professional paranoia is a virtue in a DBA.

When archive_command isn’t good enough

If your database server gets destroyed so that its disks are no longer available, we will still lose some committed transactions: the transactions in the currently active WAL segment. Remember that PostgreSQL archives a WAL segment usually when it is full. So up to 16MB worth of committed transactions can vanish with the active WAL segment.

To reduce the impact, you can set arch

[...]

The call for papers for pgDay Paris 2020 is now open. Submit your proposals for interesting talks about all things PostgreSQL, and join us in March!

https://2020.pgday.paris/callforpapers/

I prepared dll libraries. You can load it from url plpgsql_check-1.7.6-x64-win.zip.

Installation

1. Download, unzip and choose related dll file
2. rename to plpgsql_check.dll and copy to PostgreSQL's lib directory (Program Files/PostgreSQL/10/lib)
3. copy plpgsql_check-1.7.sql and plpgsql_check.control to PostgreSQL's share/extension directory (PostgreSQL/10/share/extension).
4. with super user rights (user postgres) run command CREATE EXTENSION plpgsql_check;.
   

It can needed installed a Microsoft Visual C++ 2015 SP1 Redistributable Package https://www.microsoft.com/en-US/download/details.aspx?id=48145.

Please, check it.

I upload precompiled libraries to postgres.cz/files/orafce-3.8.0-x64.zip. These libraries are 64bit only (there is not 32bit build for Postgres 11 and 12). For 32bit or older PostgreSQL releases, please, use older orafce builds. Windows build is partially reduced - there are not support for PLVlex for PostgreSQL 10 and 11 (due compilation problems), and there are not support for utl_file (due crash in these functions - I am not able to fix it on MSWIN platform).

Installation - a) install Visual C++ Redistributable for Visual Studio 2015, b) copy *.sql and *.control to ../PostgreSQL/version/share and *.dll (after to rename to just orafce.dll to ../PostgreSQL/version/lib.

The PostGIS development team is pleased to release PostGIS 3.0.0.

This release works with PostgreSQL 9.5-12 and GEOS >= 3.6.

If you are using postgis_sfcgal extension, you need to compile against SFCGAL 1.3.1 or higher.

Best served with PostgreSQL 12 , GEOS 3.8.0 and pgRouting 3.0.0-beta.

Continue Reading by clicking title hyperlink ..

The call for papers for Nordic PGDay 2020 in Helsinki, Finland, is now open. Submit your proposals for interesting talks about all things PostgreSQL, and join us in March.

Just like two years ago, the conference is held in cooperation with pgDay.paris which is held two days later. So if you are interested in both, you can submit the same proposal to both conferences at once!

When I was in the market for an EV, one of the things I wondered about was how far I would be able to go outside the city before I had to charge it. Having a number for the range isn't enough to know offhand whether I'd be able to reach a particular destination. So I wanted to make the range more obvious in my EV guide by visualising vehicle range on a map. The trivial solution is to use range as a radius and show a circle: Distance circle But of course, that's going to be massively inaccur...

PostGIS 3.0.0 is planned for release early next week. In the meantime you will find PostGIS 3.0.0rc1 or rc2 available via yum.postgresql.org, apt.postgresql.org, and EDB Windows 64-bit stackbuilder for PostgreSQL 12.

Continue reading "PostGIS 3.0.0 coming soon - Try 3.0.0rc2 at a package repo near you"

We recently had the chance to help a customer with some IO related issues that ended up being unconfigured checkpoints. Something that may not always be obvious but can actually be somewhat common.

Let's start with how things roughly work.
Postgres smallest IO unit is a disk block that is 8kb (by default). Each time postgres needs a new block it will fetch it from the disks and load it to an area in RAM called shared_buffers.
When postgres needs to write, it does it in the same manner:

• Fetches the block(s) from the disks if the block is not in shared_buffers
• Changes the page in shared buffers.
• Marks the page as changed (dirty) in shared buffers.
• It writes the change in  a "sequential ledger of changes" called WAL to ensure durability.

This basically means that the writes are not yet "on disk". This operation is taken care of by a postgres process called checkpointer. Checkpoints are how postgres guarantees that data files and index files will be updated with all the changes that happened before that checkpoint. In case of a crash, postgres will go back to the latest checkpoint record and it will start a REDO operation from WAL. Checkpoints are triggered every checkpoint_timeout (default at 5min) or when changes reach max_wal_size (default at 1GB). This is an IO intensive operation and postgres tries to spread this IO
with checkpoint_completion_target (default at 0.5).

checkpoint_timeout* is the maximum time between checkpoints in seconds.
min_wal_size minimum size of wals that will be recycled rather than removed
max_wal_size** maximum size allowed for wals between checkpoints
checkpoint_completion_target allows data changes to spread over a longer period of time, making the  final fsync() much cheaper.
* Affects recovery time, change only after reviewing the documentation
** This is a softmax, it can exceed this value in special cases.

Best way to start is to set checkpoint_timeout value to something reasonable and set max_wal_size high enough so you won't reach the timeout. To make sens[...]

PostgreSQL uses shared_buffers to cache blocks in memory. The idea is to reduce
disk I/O and to speed up the database in the most efficient way
possible. During normal operations your database cache will be pretty useful and
ensure good response times. However, what happens if your database instance is
restarted – for whatever reason? Your PostgreSQL database performance will suffer
until your I/O caches have filled up again. This takes some time and it can
be pretty damaging to your query response times.

pg_prewarm: Filling up your database cache

Fortunately, there are ways in PostgreSQL to fix the problem. pg_prewarm is a
module which allows you to automatically prewarm your caches after a database
failure or a simple restart. The pg_prewarm module is part of the PostgreSQL
contrib package and is usually available on your server by default.
There is no need to install additional third party software. PostgreSQL has all
you need by default.

Warming caches manually or automatically.

Basically, pg_prewarm can be used in two ways:

• Manual caching
• Automatic caching on startup

Let us take a look at both options and see how the module works in detail. In general automatic pre-warming is, in my judgement, the better way to preload caches – but in some cases, it can also make sense to just warm caches manually (usually for testing purposes).

pg_prewarm: Putting data into shared_buffers manually

Prewarming the cache manually is pretty simple. The following section explains how the process works in general.

The first thing to do is to enable the pg_prewarm extension in your database:

test=# CREATE EXTENSION pg_prewarm;
CREATE EXTENSION

To show how a table can be preloaded, I will first create a table and put it into
the cache:

test=# CREATE TABLE t_test AS
SELECT * FROM generate_series(1, 1000000) AS id;
SELECT 1000000
test=# SELECT * FROM pg_prewarm('public.t_test');
pg_prewarm
------------
4425
(1 row)

All you have to do is to call the

[...]

I have written about managing a PostgreSQL commitfest before. During the PostgreSQL 13 development cycle, I did it again. This time I used a different strategy, mostly because I felt that there was excessive accumulation of very old patches that had received insufficient attention. So apart from bugfixes (which are always special cases), I focused […]

With PostgreSQL 12 the generated columns are now supported natively. Until the version Postgresql 11 it were possible to have generated columns using a trigger.

In this post we’ll see how to configure a generated column via trigger and natively then we’ll compare the performances of both strategies.

Sometimes I have to fix some object ownership such as tables and views. Let's figure out if there is such object in your database:

--
-- list tables, views, foreign tables and sequences not owned by role postgres
--
SELECT n.nspname AS SCHEMA,
       c.relname AS relation,
       pg_get_userbyid(c.relowner) AS ROLE,
       'ALTER TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
  AND nspname <> 'information_schema'
  AND relkind = 'r'
  AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT n.nspname AS SCHEMA,
       c.relname AS relation,
       pg_get_userbyid(c.relowner) AS ROLE,
       'ALTER VIEW ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
  AND nspname <> 'information_schema'
  AND relkind = 'v'
  AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT n.nspname AS SCHEMA,
       c.relname AS relation,
       pg_get_userbyid(c.relowner) AS ROLE,
       'ALTER FOREIGN TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
  AND nspname <> 'information_schema'
  AND relkind = 'f'
  AND pg_get_userbyid(c.relowner) <> 'postgres'
UNION ALL
SELECT n.nspname AS SCHEMA,
       c.relname AS relation,
       pg_get_userbyid(c.relowner) AS ROLE,
       'ALTER SEQUENCE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' OWNER TO postgres;' AS command
FROM pg_class c
INNER JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname !~ '^pg_'
  AND nspname <> 'information_schema'
  AND relkind = 'S'
  AND pg_get_userbyid(c.relowner) <> 'postgres';

This UNION ALL query list tables, views, foreign tables and sequences whose owner is not role postgres. They should be candidates for a new owner (mainly because you are adjus[...]

Read more »

Now that PostgreSQL 12 is out, we consider foreign keys to be fully compatible with partitioned tables. You can have a partitioned table on either side of a foreign key constraint, and everything will work correctly. Why do I point this out? Two reasons: first, when partitioned tables were first introduced in PostgreSQL 10, they […]

Photo by unsplash-logoCharles 🇵🇭 It is well known that database design should be as simple as possible, and follow the normalization process. Except in some cases, sometimes, for scalability purposes. Partitioning might be used to help deal with large amount of data for instance. But what is a large amount of data? Do you need to pay attention to those scalability trade-offs now, or can you wait until later?

Photo by unsplash-logoNicole Honeywill / Sincerely Media Each part of The Art of PostgreSQL can be read on its own, or you can read this book from the first to the last page in the order of the parts and chapters therein. A great deal of thinking have been put in the ordering of the parts, so that reading “The Art of PostgreSQL” in a linear fashion should provide the best experience.

Since version 12, PostgreSQL collations are created with a parameter named deterministic, that can be true or false, so that collations are now either deterministic (which they are by default), or nondeterministic.

What does that mean? This term refers to what Unicode calls deterministic comparisons between strings:

This is a comparison where strings that do not have identical binary contents (optionally, after some process of normalization) will compare as unequal

So before version 12, comparisons for collatable types in Postgres are always deterministic according to the above definition. Specifically, when the underlying collation provider (libc or ICU) reports that two strings are equal, a tie-breaker bytewise comparison is performed, so that it’s only when the strings consist of identical binary contents that they are truly equal for Postgres.

Starting with version 12, the new “deterministic” property can be set to false at CREATE COLLATION time to request that string comparisons skip the tie-breaker, so that the memory representations being different is not an obstacle to recognize strings as equal when the underlying locale says they are. This does not only affect direct comparisons or lookups through WHERE clauses, but also the results of GROUP BY, ORDER BY, DISTINCT, PARTITION BY, unique constraints, and everything implying the equality operator.

So what can be achieved with nondeterministic collations?

The most obvious features are case-insensitive and accent-insensitive matching implemented with COLLATE clauses, as opposed to calling explicit functions to do case-mapping (upper, lower) and removal of accents (unaccent). Now that these are accessible through the collation service, the traditional recommendation to use the citext datatype for case-insensitive lookups may start to be reconsidered.

Beyond that, nondeterministic collations allow to match strings that are canonically equivalent (differing only by which Unicode normal form they use), or differ only by com

[...]

The PostGIS development team is pleased to release PostGIS 3.0.0rc2. This will be the final RC before release.

This release works with PostgreSQL 9.5-12 and GEOS >= 3.6

Best served with PostgreSQL 12 , GEOS 3.8.0 and pgRouting 3.0.0-alpha.

Continue Reading by clicking title hyperlink ..

PostgreSQL 12 can be considered revolutionary considering the performance boost we observe with partitioning enhancements, planner improvements, several SQL features, Indexing improvements, etc. You may see some of such features discussed in future blog posts. But, let me start this blog with something interesting. You might have already seen some news that there is no

recovery.file

in standby anymore and that the replication setup (streaming replication) has slightly changed in PostgreSQL 12. We have earlier blogged about the steps involved in setting up a simple Streaming Replication until PostgreSQL 11 and also about using replication slots for the same. Let’s see how different is it to set up the same Streaming Replication in PostgreSQL 12.

Installing PostgreSQL 12 on Master and Standby

On CentOS/RedHat, you may use the rpms available in the PGDG repo (the following link may change depending on your OS release).

# as root:
yum install -y https://yum.postgresql.org/12/redhat/rhel-7.4-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y
yum install -y postgresql12-server

Steps to set up Streaming Replication in PostgreSQL 12

In the following steps, the Master server is: 192.168.0.108 and the Standby server is: 192.168.0.107

Step 1 :
Initialize and start PostgreSQL, if not done already on the Master.

## Preparing the environment
$ sudo su - postgres
$ echo "export PATH=/usr/pgsql-12/bin:$PATH PAGER=less" >> ~/.pgsql_profile
$ source ~/.pgsql_profile

## As root, initialize and start PostgreSQL 12 on the Master
$ /usr/pgsql-12/bin/postgresql-12-setup initdb
$ systemctl start postgresql-12

 

Step 2 :
Modify the parameter

listen_addresses

to allow a specific IP interface or all (using *). Modifying this parameter requires a restart of the PostgreSQL instance to get the change into effect.

# as postgres
$ psql -c "ALTER SYSTEM SET listen_addresses TO '*'";
ALTER SYSTEM

# as root, restart the service
$ systemctl restart postgresql-12

You may not have to set any other parameters on the Master

[...]

I got tired of looking up regression_output/log/postmaster.log to find the PGSOCKET to use to connect to a running pg_regress‘s temp-install postgres so I wrote a little shell function for it. My patch to print a connst in pg_regress never got merged so I needed a workaround. I’m sure I’m not the only one, so here’s […]

2019 October 17th Meeting 6pm-8pm

Location:

PSU Business Accelerator
2828 SW Corbett Ave · Portland, OR
Parking is open after 5pm.

Speaker: Mark Wong

pg_top was born in 2007 from a fork of the unixtop, a terminal program displaying top processes on the system, where pg_top focuses on the processes with the PostgreSQL database you are connected to. Recently pg_systat was forked from systat to display additional database statistics.

These tools have can help do more such as explore query execution plans and create reports from system and database resources.

Come learn about the statistics PostgreSQL keeps and how to use these tools to view them.

Mark leads the 2ndQuadrant performance practice as a Performance Consultant for English Speaking Territories, based out of Oregon in the USA. He is a long time Contributor to PostgreSQL, co-organizer of the Portland PostgreSQL User Group, and serves as a Director and Treasurer for the United States PostgreSQL Association.

The transactional model has been in PostgreSQL since the early versions. PostgreSQL implementation follows the guidelines of the SQL standard some notable exceptions.

When designing an application it’s important to understand how the concurrent access to data happens in order to avoid unexpected results or errors.

Did you know that your temporary tables are not cleaned up by autovacuum? If you did not, consider reading this blog post about PostgreSQL and autovacuum. If you did – well, you can still continue to read this article.

Autovacuum cleans tables automatically

Since the days of PostgreSQL 8.0, the database has provided this miraculous autovacuum daemon which is in charge of cleaning tables and indexes. In many cases, the default configuration is absolutely ok and people don’t have to worry about VACUUM much. However, recently one of our support clients sent us an interesting request related to temporary tables and autovacuum.

What is the problem? The main issue is that autovacuum does not touch temporary tables. Yes, it’s true – you have to VACUUM temporary tables on your own. But why is this the case? Let’s take a look at how the autovacuum job works in general: Autovacuum sleeps for a minute, wakes up and checks if a table has seen a sufficiently large number of changes before it fires up a cleanup process. The important thing is that the cleanup process actually has to see the objects it will clean, and this is where the problem starts. An autovacuum process has no way of seeing a temporary table, because temporary tables can only be seen by the database connection which actually created them. Autovacuum therefore has to skip temporary tables. Unfortunately, most people are not aware of this issue. As long as you don’t use your temporary tables for extended periods, the missing cleanup job is not an issue. However, if your temp tables are repeatedly changed in long transactions, it can become a problem.

Proving my point

The main question now is: How can we verify what I have just said? To show you what I mean, I will load the pgstattuple extension and create two tables– a “real” one, and a temporary one:

test=# CREATE EXTENSION pgstattuple;
CREATE EXTENSION
test=# CREATE TABLE t_real AS
SELECT * FROM generate_series(1, 5000000) AS id;
SELECT 5000000
test=# CREATE TEMPORARY TABLE t_te

[...]

While managing a small team of development resources working on PostgreSQL development, I sometimes get resources in my team that have good development experience but are new to PostgreSQL. I have developed a short set of training instructions in order to get these resources started with PostgreSQL and get them to familiarise themselves with Postgres and its internals. The purpose of this blog is to share these instructions so it can benefit others in a similar situation. The instructions involve going through a lot of documentation, white-papers, online books, it also includes few development exercises that can be helpful in understanding PostgreSQL codebase. I have found these helpful in the initial training for resources that are new to PostgreSQL, putting them in the blog so they are also helpful to others.

Online Learning Resources

For someone who is new to PostgreSQL, the obvious starting point is understanding PostgreSQL functionality. I would recommend the following resources for reading about PostgreSQL functionality.

http://www.postgresqltutorial.com/

This is a really good place for Postgres tutorials, the tutorial available on this site vary from basic “Getting started with PostgreSQL” to complex features like common table expressions, partition tables, etc. It also contains tutorials for Postgres client’s tools, programming interfaces, etc.

This presentations and tutorial available at Bruce Momjain site are also very useful, this site contains presentations, online books are other material on all sorts of topics related to PostgreSQL. Whether it is talking about query processing, Postgres internal, horizontal scalability with sharding, security, etc, this site contains a lot of useful training material related to PostgreSQL.

https://momjian.us/main/presentations/extended.html

There is of-course community official documentation for every release of PostgreSQL. The difference between each release document is new features added to the release or changes to existing features

[...]