Neues vom PostgreSQL Planet
© Laurenz Albe 2020
We know that PostgreSQL does not update a table row in place. Rather, it writes a new version of the row (the PostgreSQL term for a row version is “tuple”) and leaves the old row version in place to serve concurrent read requests. VACUUM later removes these “dead tuples”.
PostgreSQL is a great open source database developed and maintained by many great software engineers around the world. In each release, there are many features added to this open source database. For example, one feature is very helpful for developer is backtrace_functions introduced in PostgreSQL 13 , which allows a developer to dump out the backtrace when certain errors happened on the server. In this blog, I am going to explain it in a little bit more detail.
The community has spent a lot of time optimizing features over the years. Excellent examples include parallel query and partitioning which have been multi-year efforts to increase the quality, performance, and extend features of the original commit. We should consider the documentation in a similar manner. Just like code, documentation can sometimes use a bug fix, optimization, and/or new features added to the original implementation.
TDE is an important feature missing from PostgreSQL, it is also a crucial tick in the box which people are deciding to move to PostgreSQL from other DBMS systems. The community has been trying to add this feature in PG however it has taken too long because of lack of attention from serious community members for reaching consensus on basic spec and design.
As part of my work on the open source PostgreSQL team at Microsoft, I recently committed a new feature into PostgreSQL 14 to track dependencies on collation versions, with help from co-author Julien Rouhaud and the many others who contributed ideas.
If you’ve seen the following error, you know that your CentOS6 has reached end of life (EOL).Loaded plugins: fastestmirror, ovl Setting up Install Process YumRepo Error: All mirror URLs are not using ftp, http[s] or file. Eg. Invalid release/repo/arch combination/ removing mirrorlist with no valid mirrors: /var/cache/yum/x86_64/6/base/mirrorlist.txt Error: Cannot retrieve repository metadata (repomd.xml) for repository: base. Please verify its path and try again
I found this out a couple of days ago. So today I decided to fix it.
The osm2pgsql project has seen quite a bit of development over the past couple of years. This is a core piece of software used by a large number of people to load OpenStreetMap data into PostGIS / PostgreSQL databases, so it has been great to see the activity and improvements. Recently, I was contacted by Jochen Topf to see if I would give one of those (big!) improvements, osm2pgsql's new Flex output, a try.
Recently, PostgreSQL 13 was released. People are asking what are best ways upgrading and updating PostgreSQL 12 or some other version to PostgreSQL 13. This blog post covers how you can move to the latest release.
Before we get started, we have to make a distinction between two things:
- Updating PostgreSQL
- Upgrading PostgreSQL
Let’s take a look at both scenarios.
While we talk about "PostGIS" like it's one thing, it's actually the collection of a number of specialized geospatial libraries, along with a bunch of code of its own.
You have a file, possibly a huge CSV, and you want to import its content into your database. There are lots of options to do this but how would you decide which one to use. More often than not the question is how much time would the bulk load would take. I found my self doing the same few days back when I wanted to design a data ingestion process for PostgreSQL where we needed to bulk load around 250GB of data from CSV files every 24 hours.
When using logical replication with PostgreSQL, the wal level needs to be set to ‘logical’, so the logical level wal contains more data to support logical replication than the replicate wal level. I think it’s unclear to many users or DBAs about the difference between logical and replicate level. This blog is about the difference of logical level and replicate level, which the reader can use to evaluate or analyze the impact of changing to logical level on wal log bloat.
If you’ve built your application on Postgres, you already know why so many people love Postgres.
On November 17-20, 2020, PostgresConf.CN & PGconf.Asia2020 (referred to as 2020 PG Asia Conference) was held online for the very first time! This conference was jointly organized by the PG China Open Source Software Promotion Alliance, PostgresConf International Conference Organization, and PGConf.Asia Asian Community. This conference was broadcast exclusively via the Modb Technology Community platform in China with a record-high number of viewers streaming the conference events.
PostgreSQL offers a nice BLOB interface which is widely used. However, recently we came across problems faced by various customers, and it makes sense to reflect a bit and figure out how PostgreSQL handles BLOBs – and especially BLOB cleanup.Using the PostgreSQL BLOB interface
In PostgreSQL, you can use various means to store binary data. The simplest form is definitely to make use of the “bytea” (= byte array) data type. In this case a binary field is basically seen as part of a row.
Here is how it works: