planet.postgresql.org

Jignesh Shah: Tuning DB Parameters for PostgreSQL 12 in Amazon RDS

In my last entry, we saw how to setup PostgreSQL 12 beta 3 in Amazon RDS. In that entry I purposely left out how to change database parameters as I realized that it deserves an entry (or more) by itself.Using the AWS CLI you can create a new database parameter as follows:$ aws rds create-db-parameter-group --db-parameter-group-name jkpg12pg \ --db-parameter-group-family postgres12 --description "My PostgreSQL 12 Parameter Group" \ --region us-east-2 --endpoint https://rds-preview.us-east-2.amazonaws.com We have just created a...

planet.postgresql.org

Kaarel Moppel: A Primer on PostgreSQL Upgrade Methods

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, v12. 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...

planet.postgresql.org

Granthana Biswas: Remote Backup and Restore with pgBackRest

In my 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...

planet.postgresql.org

Kaarel Moppel: Updates for the Postgres Showcase project

In order to prepare for the start of training season (you can see our “stock” training offerings here by 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 v11 release and also from the upcoming v12. Short project background The project was introduced some two years ago – the main idea of the project itself is to provide a compact and “hands-on” set of commented samples to help newcomers get up to speed with...

planet.postgresql.org

Pavel Stehule: Why you need plpgsql_check (if you write procedures in PLpgSQL)

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....

planet.postgresql.org

Johann Oskarsson: PostgreSQL: Retroactively Adding Binary I/O to a Type

Here we go through the motions af adding binary input/output functions to an already existing type. We start by introducing a program that tests binary I/O for the given types, then show how to implement the binary I/O in C and finally how to fix the PostgreSQL catalog tables for the new functionality. We also include the changes in the installation script for normal installation of the new version. Table of Contents Testing Binary I/O Adding the Binary I/O Functions Fixing the Hashtypes...

planet.postgresql.org

Luca Ferrari: Compute day working hours in PL/pgsql

How many working hours are there in a range of dates? Compute day working hours in PL/pgsql A few days ago there was a very nice thread in the pgsql-general mailing list asking for ideas about how to compute working hours in a month. The idea is quite simple: you must extract the number of working days (let’s say excluding sundays) and multiple each of them for the number of hours per day and then get the sum. There are a lot of nice and almost one-liner solutions in the thread, so I strongly encourage you to read it all! I came up with...

planet.postgresql.org

Jignesh Shah: Setting up PostgreSQL 12 Beta 3 for Testing in Amazon RDS

One of the amazing things about the PostgreSQL community is launching releases like clockwork. On 8/8/2019 the PostgreSQL community not only launched the minor versions for PostgreSQL 11 and old major versions but also a new Beta 3 version for upcoming PostgreSQL 12.On AWS, you can check versions of PostgreSQL available in your region as follows: $ aws rds describe-db-engine-versions --engine postgres --query 'DBEngineVersions[*].EngineVersion'[ "9.3.12", ... "11.2", "11.4"] You will not...

planet.postgresql.org

Luca Ferrari: PL/Proxy on PostgreSQL 12 ?

I spent some more time on the PL/Proxy code base in order to make it compiling against upcoming PostgreSQL 12. PL/Proxy on PostgreSQL 12 ? In my yesterday blog post I reported some stupid thougth about compiling PL/Proxy against PostgreSQL 12. I was too stupid to hit the removal of HeapTupleGetOid (as of commit 578b229718e8f15fa779e20f086c4b6bb3776106 ), and after having read the commit comment with more accuracy, I found how to fix the code (at least I hope so!). Essentially, wherever I found usage of HeapTupleGetOid I placed a...

planet.postgresql.org

Luca Ferrari: PL/Proxy on PostgreSQL 11 and FreeBSD 12

PL/Proxy is a procedural language implementation that makes really easy to do database proxying, and sharding as a consequence. Unluckily getting it to run on PostgreSQL 11 and FreeBSD 12 is not for free. PL/Proxy on PostgreSQL 11 and FreeBSD 12 PL/Proxy is a project that allows database proxying, that is a way to connect to remote databases, and as a consequence allows for /sharding/ implementations. The idea behind PL/Proxy is as simple as elegant: define a minimalistic language to access remote (database) objects and, more in particular,...

planet.postgresql.org

Johann Oskarsson: Loading a Huge Textfile into PostgreSQL 11 on Windows 10

On Windows and as of 11.5, PostgreSQL has a bug when trying to copy from a huge file. According to stack overflow, this does not manifest on earlier releases like PostgreSQL 10. For example, given a table create table pwned( passwd sha1 not null, count int4 not null ); and when trying to copy from the pwned password list which is about 24GB uncompressed, copy pwned( passwd, count ) from 'c:\src\postgres\pwned\pwned-passwords-sha1-ordered-by-hash-v5.txt' with ( format text, delimiter ':' ); fails and the message follows. ERROR: could not...

planet.postgresql.org

Hans-Juergen Schoenig: PostgreSQL Row Level Security, views and a lot of magic

Row Level Security (RLS) is one of the key features in PostgreSQL. It can be used to dramatically improve security and help to protect data in all cases. However, there are a couple of corner cases which most people are not aware of. So if you are running PostgreSQL and you happen to use RLS in a high-security environment, this might be the most important piece of text about database security you have ever read. Row-Level-Security in PostgreSQL: Corner cases To prepare for my examples let me create some simple data first. The following code is...